This is post #1 in a series of posts explaining how to implement TDE (Transparent Data Encryption). In this first post I will take my Multitenant 19c database (remember Multitenant is mandatory with 21c) and configure TDE in my 3 (no extra license needed) pluggable databases.
The database I created for this example contains 3 PDBs as this will give me the flexibility to unplug and move PDBs around.
The names I used are
- TDETEST - This is my CDB, and I will only be encrypting users tablespace and the catalog.
- TDEPDB1,TDEPDB2, TDEPDB3 - My 3 PDBs. I will be encrypted all user tablespaces that make up these 3 PDBS .
Since I have only a single instance, the location I chose to put the local wallet file is under $ORACLE_BASE/admin/$ORACLE_SID/wallet (/home/oracle/app/oracle/admin/tdetest/admin).
In a RAC environment you have a few choices of where to put the wallet file. The most important thing though, is to ensure each node has an identical copy in a RAC environment.
In a RAC environment you can put the wallet file:
- On the local file system on each node, but be sure they are all identical. It is best to put them within the $ORACLE_BASE to make it easier in an out-of-place upgrade.
- On a shared filesystem. This can be ACFS, DBFS, or just NFS.
- With ASM. It is possible to set the location to be an ASM file for example "+DATA/TDETEST/wallet"
1) You need to backup your wallet. Without the wallet file you can't access your database. Once encryption is implemented, the wallet needs to be available.
2) The wallet needs to be backed up separate from the database. If the wallet and the Database backup are stored together, anyone can read the backup.
In my further blogs I will be converting from using a local wallet file to store my encryption keys, to using OKV along with a local wallet that caches the autologin information.
To migrate to TDE, I chose to perform the quickest method "Restore tablespace as encrypted". With my test database, I did not have a standby database.
NOTE: With a standby database the fastest way to convert to TDE would be with a "standby first" strategy. This is explained in this MAA document which includes an automated procedure. With this strategy you would convert your standby database to utilize TDE with a restore as encrypted, while the primary database stays untouched. Once the standby is converted, you would perform a switchover (to the standby) and encrypt the "current standby", which was the primary. Once both are encrypted, you would switch back and the process is completed.
Step 1 - Perform a full backup of the database immediately prior to starting the process. Since I am using "restore tablespace as encrypted" this will allow me to open the database with minimal recovery. Once backed up, you also should create a restore point to quickly identify the point after the full backup prior to the encryption.
create restore point pretde;
Step 2 - Set the location of the wallet_root, and the tde configuration. I chose to use the WALLET_ROOT parameter (new with 19 I believe) because it gives the most flexibility. Keep in mind in order to go through step 2 completely the database will need to be bounced.
alter system set WALLET_ROOT='/home/oracle/app/oracle/admin/tdetest/wallet/' scope=spfile;
startup force;
alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;
Step 3 - We are going to take a look at the database and the parameters that are set for the encryption wallet. Below is the formatted query I am going to be using throughout this post.
Below is the output of the query and the current settings as of this point. You can see that there are rows for all my PDBs, and that the status is "NOT_AVAILABLE" since I have not created a wallet or any master keys yet. You can also see that the keystore is UNITED, meaning that all the keys (both for the CDB and all the PDBs) are assumed to be contained in the same Wallet file.
Also note that the WRL_PARAMETER is set based on the WALLET_ROOT setting. The TDE wallet file is defaulted to be the WALLET_ROOT/tde directory for the CDB.
PDB Name Type WRL_PARAMETER Status WALLET_TYPE KEYSTORE Backed Up
---------- ---------- -------------------------------------------------- ------------------------------ -------------------- -------- ----------
CDB$ROOT FILE /home/oracle/app/oracle/admin/tdetest/wallet//tde/ NOT_AVAILABLE UNKNOWN NONE UNDEFINED
PDB$SEED FILE NOT_AVAILABLE UNKNOWN UNITED UNDEFINED
TDEPDB1 FILE NOT_AVAILABLE UNKNOWN UNITED UNDEFINED
TDEPDB2 FILE NOT_AVAILABLE UNKNOWN UNITED UNDEFINED
TDEPDB3 FILE NOT_AVAILABLE UNKNOWN UNITED UNDEFINED
Step 4. Now I need to create the keystore and open it for the CDB, and all my individual PDBs. Note that each PDB shares the keystore with the CDB. In isolated mode, I would create an individual keystore for each PDB and they would be in subdirectories under the WALLET_ROOT location.
But first I need to create the directory to hold the keystore wallet.
mkdir /home/oracle/app/oracle/admin/tdetest/wallet/tdeADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/home/oracle/app/oracle/admin/tdetest/wallet/tde' IDENTIFIED BY "F1LE2021!";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "F1LE2021!";
alter session set container=tdepdb1;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "F1LE2021!" CONTAINER = CURRENT;
alter session set container=tdepdb2;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "F1LE2021!" CONTAINER = CURRENT;
alter session set container=tdepdb3;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "F1LE2021!" CONTAINER = CURRENT;
Now let's look at the encryption settings in v$encryption_wallet. Below you can see that there is a single wallet setting (UNITED keystore), and the status is "OPEN_NO_MASTER_KEY". The master key has not been set for CDB, or the PDBs.
PDB Name Type WRL_PARAMETER Status WALLET_TYPE KEYSTORE Backed Up
---------- ---------- -------------------------------------------------- ------------------------------ -------------------- -------- ----------
CDB$ROOT FILE /home/oracle/app/oracle/admin/tdetest/wallet//tde/ OPEN_NO_MASTER_KEY PASSWORD NONE UNDEFINED
PDB$SEED FILE CLOSED UNKNOWN UNITED UNDEFINED
TDEPDB1 FILE OPEN_NO_MASTER_KEY PASSWORD UNITED UNDEFINED
TDEPDB2 FILE OPEN_NO_MASTER_KEY PASSWORD UNITED UNDEFINED
TDEPDB3 FILE OPEN_NO_MASTER_KEY PASSWORD UNITED UNDEFINED
Step 5. Now we create the master keys for the CDB and each PDB. A master key is needed to encrypt the tablespace encryption keys stored in the datafiles.
NOTE: I added a tag that identifies the key with the CDB or PDB it is created for. I highly recommend using tags to identify the keys within the wallet. Identifying the master encryption key for an individual PDB will be important when moving PDBs between CDBs.
ADMINISTER KEY MANAGEMENT SET encryption KEY using tag 'TDETEST MASTERKEY_APRIL19' IDENTIFIED BY "F1LE2021!" WITH BACKUP USING 'TDETEST_TDEKEY_APR1_backup';
alter session set container=tdepdb1;
ADMINISTER KEY MANAGEMENT SET encryption KEY using tag 'TDEPDB1 MASTERKEY_APRIL19' IDENTIFIED BY "F1LE2021!" WITH BACKUP USING 'TDEPDB1_TDEKEY_APR1_backup' container=current;
alter session set container=tdepdb2;
ADMINISTER KEY MANAGEMENT SET encryption KEY using tag 'TDEPDB2 MASTERKEY_APRIL19' IDENTIFIED BY "F1LE2021!" WITH BACKUP USING 'TDEPDB2_TDEKEY_APR1_backup' container=current;
alter session set container=tdepdb3;
ADMINISTER KEY MANAGEMENT SET encryption KEY using tag 'TDEPDB3 MASTERKEY_APRIL19' IDENTIFIED BY "F1LE2021!" WITH BACKUP USING 'TDEPDB3_TDEKEY_APR1_backup' container=current;
And once again let's look at the settings in v$encryption_wallet. This time you will see that the wallet is open for all CDBs/PDBs except for the PDB$SEED. The wallet type is "PASSWORD" which means that the wallet needs to be manually opened with a password.
PDB Name Type WRL_PARAMETER Status WALLET_TYPE KEYSTORE Backed Up
---------- ---------- -------------------------------------------------- ------------------------------ -------------------- -------- ----------
CDB$ROOT FILE /home/oracle/app/oracle/admin/tdetest/wallet//tde/ OPEN PASSWORD NONE NO
PDB$SEED FILE CLOSED UNKNOWN UNITED UNDEFINED
TDEPDB1 FILE OPEN PASSWORD UNITED NO
TDEPDB2 FILE OPEN PASSWORD UNITED NO
TDEPDB3 FILE OPEN PASSWORD UNITED NO
Step 6 - We have the master keys set and the wallets are open. We now need to implement TDE by encrypted the tablespaces in my PDBs. As I said before, in my example, I used "restore tablespace as encrypted".
Another option is to encrypt online (as of 12c). In this process the database will encrypt each datafile sequentially while the database is online and active.
NOTE : If using online encryption be aware that
- It takes much longer than performing a restore, as datafiles are encrypted sequentially. Using "restore tablespace as encrypted" You can parallelize the restore across multiple channels.
- The process needs enough space for 2 copies of the largest datafile. If using bigfiles, this can be quite a bit of storage.
- You need to monitor the process to ensure it completes successfully.
Next step is I am going to startup mount and open the wallets, restore my 3 PDBs users tablespaces, along with the users tablespace in my CDB, and then recover and open the database.
sqlplus> shutdown immediate;
sqlplus> startup mount;
sqlplus> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "F1LE2021!";
sqlplus> alter session set container=tdepdb1;
sqlplus> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "F1LE2021!" CONTAINER = CURRENT;
sqlplus> alter session set container=tdepdb2;
sqlplus> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "F1LE2021!" CONTAINER = CURRENT;
sqlplus> alter session set container=tdepdb3;
sqlplus> SDMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "F1LE2021!" CONTAINER = CURRENT;
rman target / catalog rmancat/oracle@rmancat
rman> restore tablespace users as encrypted;
rman> restore tablespace tdepdb1:users as encrypted;
rman> restore tablespace tdepdb2:users as encrypted;
rman> restore tablespace tdepdb3:users as encrypted;
rman> recover database;
rman> alter database open;
Step 7 - Make sure all new tablespaces are encrypted by default
In order to ensure all new tablespaces are encrypted I am going to set the database parameter.
sql> alter system set encrypt_new_tablespaces = ALWAYS scope = both sid = '*';
Step 9 - I am going to verify that the pluggable databases are encrypted, along with the catalog.
First let's look at the existing keys using the query below
I can see the keys that are created in each container, including the ROOT
PDB Name Activation Time Key ID Tag
--------------- -------------------------- ------------------------------------------------------- ----------------------------------------
CDB$ROOT 19-Apr-2021 05:12:41pm AbwcWGicr0+rvyhrSB+rKQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDETEST MASTERKEY_APRIL19
TDEPDB1 19-Apr-2021 05:19:11pm AX9pkB+zQE/Wv6Qek13TeLkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDEPDB1 MASTERKEY_APRIL19
TDEPDB2 19-Apr-2021 05:19:11pm AUKkROD1TE8wv0jfJhN63JYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDEPDB2 MASTERKEY_APRIL19
TDEPDB3 19-Apr-2021 05:19:13pm AZUWZDWpxk9sv0GrljDFr7sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDEPDB3 MASTERKEY_APRIL19
I am going to use the query below to look at the encryption setting on each datafile.
Below is the output. I see that all the datafiles were properly encrypted and are available.
PDB Name Tablespace Name Encrypted Master Key ID
--------------- ------------------------------ ---------- ------------------------------------------------------------
CDB$ROOT SYSAUX NO AbwcWGicr0+rvyhrSB+rKQA=
SYSTEM NO AbwcWGicr0+rvyhrSB+rKQA=
TEMP NO AbwcWGicr0+rvyhrSB+rKQA=
UNDOTBS1 NO AbwcWGicr0+rvyhrSB+rKQA=
USERS YES AbwcWGicr0+rvyhrSB+rKQA=
TDEPDB1 SYSAUX NO AX9pkB+zQE/Wv6Qek13TeLk=
SYSTEM NO AX9pkB+zQE/Wv6Qek13TeLk=
TEMP NO AX9pkB+zQE/Wv6Qek13TeLk=
UNDOTBS1 YES AX9pkB+zQE/Wv6Qek13TeLk=
USERS YES AX9pkB+zQE/Wv6Qek13TeLk=
TDEPDB2 SYSAUX NO AUKkROD1TE8wv0jfJhN63JY=
SYSTEM NO AUKkROD1TE8wv0jfJhN63JY=
TEMP NO AUKkROD1TE8wv0jfJhN63JY=
UNDOTBS1 YES AUKkROD1TE8wv0jfJhN63JY=
USERS YES AUKkROD1TE8wv0jfJhN63JY=
TDEPDB3 SYSAUX NO AZUWZDWpxk9sv0GrljDFr7s=
SYSTEM NO AZUWZDWpxk9sv0GrljDFr7s=
TEMP NO AZUWZDWpxk9sv0GrljDFr7s=
UNDOTBS1 YES AZUWZDWpxk9sv0GrljDFr7s=
USERS YES AZUWZDWpxk9sv0GrljDFr7s=
And I am going to look at the catalog to be sure
select enforcement from DICTIONARY_CREDENTIALS_ENCRYPT;
ENFORCEM
--------
ENABLED
Step 10 - I am adding the ability to access the keystore without having to enter the password. This can make it much easier to open the keystore if you don't chose to make the Keystore Wallet AUTO_LOGON.
I am putting my keystore password in an AUTO_LOGIN wallet as a secret. This wallet needs to be created in a directory called TDE_SEPS. In my case since, I am using WALLET_ROOT, the directory containing the secret must be WALLET_ROOT/tde_seps.
The password portion of "ADMINISTER KEY" becomes IDENTIFIED by EXTERNAL STORE;
ADMINISTER KEY MANAGEMENT
ADD SECRET 'F1LE2021!' FOR CLIENT 'TDE_WALLET'
USING TAG 'TDE file keystore password'
TO LOCAL AUTO_LOGIN KEYSTORE '/home/oracle/app/oracle/admin/tdetest/wallet/tde_seps';
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY EXTERNAL STORE;
alter session set container=tdepdb1;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY EXTERNAL STORE CONTAINER = CURRENT;
alter pluggable database open;
alter session set container=tdepdb2;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY EXTERNAL STORE CONTAINER = CURRENT;
alter pluggable database open;
alter session set container=tdepdb3;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY EXTERNAL STORE CONTAINER = CURRENT;
alter pluggable database open;
PDB Name Type WRL_PARAMETER Status WALLET_TYPE KEYSTORE Backed Up
---------- ---------- -------------------------------------------------- ------------------------------ -------------------- -------- ----------
CDB$ROOT FILE /home/oracle/app/oracle/admin/tdetest/wallet//tde/ OPEN PASSWORD NONE NO
PDB$SEED FILE CLOSED UNKNOWN UNITED UNDEFINED
TDEPDB1 FILE OPEN PASSWORD UNITED NO
TDEPDB2 FILE OPEN PASSWORD UNITED NO
TDEPDB3 FILE OPEN PASSWORD UNITED NO
sqlplus / as sysdba
sql> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY EXTERNAL STORE;
sql> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/home/oracle/app/oracle/admin/tdetest/wallet/tde/' IDENTIFIED BY "F1LE2021!";
sql> shutdown immediate
sql> startup
And v$encryption_wallet shows me that my wallets are all open, and that they are AUTOLOGIN.
PDB Name Type WRL_PARAMETER Status WALLET_TYPE KEYSTORE Backed Up
---------- ---------- -------------------------------------------------- ------------------------------ -------------------- -------- ----------
CDB$ROOT FILE /home/oracle/app/oracle/admin/tdetest/wallet//tde/ OPEN AUTOLOGIN NONE NO
PDB$SEED FILE OPEN AUTOLOGIN UNITED NO
TDEPDB1 FILE OPEN AUTOLOGIN UNITED NO
TDEPDB2 FILE OPEN AUTOLOGIN UNITED NO
TDEPDB3 FILE OPEN AUTOLOGIN UNITED NO
Now I am ready to perform a new FULL backup of the pluggable databases, and they are ready for use.
That's all there is to implementing TDE with a wallet file. Next post, I am going to convert my wallet to OKV managed wallets.
