Starting with Oracle 12.2 it is possible to encrypt all Tablespaces including SYSTEM, SYSAUX, TEMP, and UNDO. Off the top of my head I can think of a couple of reasons why encrypting SYSTEM and SYSAUX might be desired:
- Histograms in SYSAUX might contain sensitive data
- Application tables/indexes in SYSTEM or SYSAUX Tablespace
- Hard-coded secrets in PL/SQL objects (yes, I’ve seen it all!)
Strangely enough, the 12.2 TDE FAQ discourages encryption of “internal objects”:
I believe this to be a leftover from previous releases which hasn’t been cleaned up, yet. Because it is supported and the Advanced Security Guide provides plenty of information about it.
Splitting hairs, “Fully Encrypt Databases” is not entirely correct. There is currently no supported way to encrypt PDB$SEED Tablespaces…and I’ve tried for the fun of it:-)
Forcing the PDB$SEED open read-write with “alter pluggable database PDB$SEED open force” and then open the keystore works. I could even create a new encryption key, but it won’t let me activate it:
SQL> show con_name CON_NAME ------------------------------ PDB$SEED SQL> administer key management create encryption key using tag 'PDBSEED_MK_1' force keystore identified by "Strng-4-Scrty" with backup using 'bck1'; keystore altered. SQL> column key_id new_value new_key_id SQL> select key_id from v$encryption_keys where con_id = 0 and tag = 'PDBSEED_MK_1'; KEY_ID ------------------------------------------------------------------------------ AVVfIyuTLk9sv2EyAw9dJ18AAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> administer key management use encryption key '&new_key_id' identified by "Strng-4-Scrty" with backup using 'bck2'; * ERROR at line 1: ORA-46656: operation not valid for container database
I know it doesn’t make a lot of sense as for instance, cloning from a encrypted PDB$SEED would be kind of a Catch-22.
Anyway, back to encrypting Oracle’s “internal” Tablespaces. It’s pretty straight forward. It’s the same as you would setup the database for any TDE encryption.
Add ENCRYPITON_WALLET_LOCATION to your sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/wallet/tde)
)
)
Setup a keystore in the CDB$ROOT and generate a new key to use:
alter session set container=cdb$root; -- create PKCS#12 keystore (ewallet.p12) administer key management create keystore '/u01/app/oracle/admin/DEV1/wallet/tde' identified by "Strng-4-Scrty" ; -- open the keystore administer key management set keystore open identified by "Strng-4-Scrty" container = current ; -- create a new encryption key administer key management create encryption key using tag 'DEV1_ROOT_MK_1' identified by "Strng-4-Scrty" with backup using 'Before-Create-DEV1_ROOT_MK_1' container = current ; -- activate the key for use in this container -- note: newly created keys have "con_id = 0", as soon as they are activated they get assigned to the respective PDB column key_id new_value new_key_id select key_id from v$encryption_keys where con_id = 0 and tag = 'DEV1_ROOT_MK_1'; administer key management use encryption key '&new_key_id' using tag 'DEV1_ROOT_MK_1' identified by "Strng-4-Scrty" with backup using 'Before-Activate-DEV1_ROOT_MK_1' ; -- we also want a local auto-login keystore (cwallet.sso) -- otherwise manual keystore password input is required during database startup administer key management create local auto_login keystore from keystore '/u01/app/oracle/admin/DEV1/wallet/tde' identified by "Strng-4-Scrty" ;
Now, that we have the key setup it’s as simple as an ALTER TABLESPACE statement. We used the default algorithm AES128 when generating the encryption key so we specify the same here.
alter tablespace system
encryption online using 'AES128' encrypt
file_name_convert = ('system', 'system-enc')
;
alter tablespace sysaux
encryption online using 'AES128' encrypt
file_name_convert = ('sysaux', 'sysaux-enc')
;
Be careful, if the specified algorithm is different from the one of the key the statement will still succeed. You’ll see following warning in the alert.log:
An algorithm different from the database key is specified for SYSTEM tablespace. The algorithm has been overridden to AES128 per V$DATABASE_KEY_INFO view.
Let’s move to my PDB.
alter session set container=pdbdev1; -- assuming auto-login keystore is now open -- otherwise open password keystore administer key management create encryption key using tag 'PDBDEV1_MK_1' force keystore identified by "Strng-4-Scrty" with backup using 'Before-Create-PDBDEV1_MK_1' ; column key_id new_value new_key_id select key_id from v$encryption_keys where con_id = 0 and tag = 'PDBDEV1_MK_1'; administer key management use encryption key '&new_key_id' using tag 'PDBDEV1_MK_1' force keystore identified by "Strng-4-Scrty" with backup using 'Before-Activate-PDBDEV1_MK_1' ;
To encrypt the SYSTEM/SYSAUX Tablespace on our PDB you can use the same statements from above:
alter tablespace system
encryption online using 'AES128' encrypt
file_name_convert = ('system', 'system-enc')
;
alter tablespace sysaux
encryption online using 'AES128' encrypt
file_name_convert = ('sysaux', 'sysaux-enc')
;
According to the documentation, undo, temp, and redo data originating from objects in encrypted tablespaces will also be encrypted.
There’s still reason to encrypt temporary Tablespaces in full:
- Temporary LOBs
- Global temporary tables
Encrypting existing TEMP Tablespaces is not supported, we just create a new one and drop the old.
create temporary tablespace tempts_enc tempfile '/u02/oradata/DEV1/tempts_enc-001.dbf' size 512M autoextend off encryption using 'AES128' encrypt ; alter database default temporary tablespace tempts_enc; drop tablespace tempts including contents and datafiles;
Once I have encrypted SYSTEM, SYSAUX, TEMP, and all application Tablespaces there should be no need to encrypt the UNDO Tablespace, right? All undo data is then based on objects from encrypted Tablespaces and should therefore also be encrypted in the UNDO stream. This is to be further investigated. If you have any input, please leave a comment.
One final note: if you want to understand the performance impact of TDE I highly recommend to use SLOB.