Performing Oracle Transparent Data Encryption Master Key Rotation in Delphix Virtual Databases (KBA6398)

Prerequisites

The process detailed here requires the following:

• An Oracle database with Transparent Data Encryption (TDE) enabled and this database having been ingested by the Delphix Engine as a dSource.
• A correctly configured and enabled Oracle Wallet that is responsible for holding the keystore used by Oracle TDE.
• A target host with an Oracle Home in place conforming to all of the pre-requisites set out in the Delphix documentation for Oracle Target hosts.

Overview of the process

• Establish Transparent Data Encryption in the Oracle source database.
• Link the source database to Delphix as a dSource.
• Configure the target host environment and Oracle Wallet in preparation for provisioning a TDE enabled virtual database.
• Provision the virtual database.
• Rotate the Transparent Data Encrytion Master Key in the Virtual Database.

To Complete Oracle TDE Master Key Rotation

Complete the following procedure to provision a new virtual database from a TDE enabled dSource and rotate the TDE Master Key in the Oracle Virtual Database. This ensures the source database and virtual database have different master keys.

Establish a Wallet and enable Transparent Data Encryption (TDE) in the dSource database 

[oracle@oel7si1 admin]$ cp sqlnet.ora sqlnet.ora.cdb122

[oracle@oel7si1 admin]$ vi sqlnet.ora

[oracle@oel7si1 admin]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
ADR_BASE = /u01/app/oracle

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCP, TCPS)
SSL_VERSION = 1.2
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (TLS_RSA_WITH_AES_128_GCM_SHA256)

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
     (DIRECTORY = /u01/app/oracle/dbwallet/$ORACLE_SID)
    )
  )

[oracle@oel7si1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 27 16:45:57 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 8620224 bytes
Variable Size 452986688 bytes
Database Buffers 788529152 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER                    STATUS        WALLET_TYPE  WALLET_OR  FULLY_BAC  CON_ID
----------------------------------------  ------------- -----------  ---------  ---------  ------
FILE     /u01/app/oracle/dbwallet/tde1/   NOT_AVAILABLE UNKNOWN      SINGLE     UNDEFINED       0

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oel7si1 admin]$ mkdir -p /u01/app/oracle/dbwallet/tde1/

[oracle@oel7si1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 27 16:48:07 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/dbwallet/tde1/' identified by keystore123;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystore123;
keystore altered.

SQL> select * from v$encryption_wallet;
WRL_TYPE    WRL_PARAMETER                   STATUS               WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
----------- ------------------------------- -------------------- ----------- --------- --------- ------
FILE        /u01/app/oracle/dbwallet/tde1/  OPEN_NO_MASTER_KEY   PASSWORD    SINGLE    UNDEFINED      0

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore123 with backup;
keystore altered.

SQL> select * from v$encryption_wallet;
WRL_TYPE    WRL_PARAMETER                   STATUS   WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
----------- ------------------------------- -------- ----------- --------- --------- ------
FILE        /u01/app/oracle/dbwallet/tde1/  OPEN     PASSWORD    SINGLE    NO             0

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/dbwallet/tde1/' identified by keystore123;
keystore altered.

SQL> ! ls -l /u01/app/oracle/dbwallet/tde1/
total 12
-rw-------. 1 oracle oinstall 3891 Aug 27 16:55 cwallet.sso
-rw-------. 1 oracle oinstall 2408 Aug 27 16:54 ewallet_2020082706542273.p12
-rw-------. 1 oracle oinstall 3848 Aug 27 16:54 ewallet.p12

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 8620224 bytes
Variable Size 452986688 bytes
Database Buffers 788529152 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

SQL> select * from v$encryption_wallet;

WRL_TYPE    WRL_PARAMETER                   STATUS   WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
----------- ------------------------------- -------- ----------- --------- --------- ------
FILE        /u01/app/oracle/dbwallet/tde1/  OPEN     AUTOLOGIN   SINGLE    NO             0

[oracle@oel7si1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 27 16:59:58 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE TABLESPACE ENCRYPTTEST datafile size 10M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> connect / as sysdba
Connected.

SQL> grant create table to delphix;

SQL> connect delphix/delphix 
Connected.

SQL> create table tblencrypt (id number(10) encrypt) tablespace encrypttest;
Table created.

SQL> connect / as sysdba
Connected.

SQL> grant unlimited tablespace to delphix;
Grant succeeded.

SQL> connect delphix/delphix
Connected.

SQL> insert into tblencrypt values(1);
1 row created.
SQL> insert into tblencrypt values(2);
1 row created.
SQL> insert into tblencrypt values(3);
1 row created.

SQL> commit;
Commit complete.

Link the dSource Database

1. Link the TDE enabled dSource using the Delphix GUI.

Configure the Target Environment Oracle Wallet in Preparation for Provisioning a VDB

1. Configure the VDB target host sqlnet.ora.

[oracle@oel7si2 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
ADR_BASE = /u01/app/oracleSQLNET.AUTHENTICATION_SERVICES= (BEQ, TCP, TCPS)

SSL_VERSION = 1.2
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (TLS_RSA_WITH_AES_128_GCM_SHA256)

WALLET_LOCATION =
(SOURCE =
  (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/vdbwallet/$ORACLE_SID)
    )
  )

2. Create the wallet directory for the VDB using a directory name that aligns with the ORACLE_SID/Instance name of the VDB about to be created.
3. This ORACLE_SID value is used to map the sqlnet.ora DIRECTORY directive used in locating each VDB's wallet.

[oracle@oel7si2 admin]$ mkdir -p /u01/app/oracle/vdbwallet/vtde1

4. Copy the wallet from the source to the target.

[oracle@oel7si2 admin]$ scp oel7si1:/u01/app/oracle/dbwallet/tde1/* /u01/app/oracle/vdbwallet/vtde1/

oracle@oel7si1's password: 

cwallet.sso 100% 3891 3.8KB/s 00:00 
ewallet_2020082706542273.p12 100% 2408 2.4KB/s 00:00 
ewallet.p12

Provision the VDB

1. Provision the VDB using the Delphix Administration GUI, naming it, in this case vtde1, ensuring its ORACLE_SID/Instance name (vtde1) aligns with the file system folder of that same name on the destination host.
2. The VDB is seen running post provision.

[oracle@oel7si2 provision]$ ps -ef | grep pmon
oracle 20276 1 0 19:34 ? 00:00:00 ora_pmon_vtde1

3. Set the environment for the VDB so that sqlplus can be used to log in to it.

[oracle@oel7si2 provision]$ set | grep ORA
OLD_ORACLE_BASE=
ORABASE_EXEC=/u01/app/oracle/product/12.2.0/dbhome_1/bin/orabase
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORACLE_SID=cdb122
ORAHOME=/u01/app/oracle/product/12.2.0/dbhome_1
ORASID=oracle
[oracle@oel7si2 provision]$ export ORACLE_SID=vtde1
[oracle@oel7si2 provision]$ sqlplus / as sysdba
SQL> select db_unique_name,open_mode,database_role from v$database;
DB_UNIQUE_NAME  OPEN_MODE            DATABASE_ROLE
--------------- -------------------- ----------------
vtde1           READ WRITE           PRIMARY

4. The wallet must be seen as open in the VDB.

SQL> select * from v$encryption_wallet;
WRL_TYPE    WRL_PARAMETER                            STATUS  WALLET_TYPE  WALLET_OR  FULLY_BAC    CON_ID
------------ ----------------------------------- --------- --------------- ------------- ------------- -------
FILE               /u01/app/oracle/vdbwallet/vtde1/     OPEN     AUTOLOGIN      SINGLE          NO 0

5. Querying the encrypted data must be successfully performed in the VDB when TDE is correctly configured.

SQL> select * from TBLENCRYPT;
ID
----------
1
2
3

Perform the Oracle Database TDE Master Key Rotation

Make sure the wallet is open before attempting the master key rotation.

SQL> select * from v$encryption_wallet;
WRL_TYPE    WRL_PARAMETER                            STATUS  WALLET_TYPE  WALLET_OR  FULLY_BAC    CON_ID
------------ ----------------------------------- --------- --------------- ------------- ------------- -------
FILE               /u01/app/oracle/vdbwallet/vtde1/     OPEN     AUTOLOGIN      SINGLE          NO                            0

1. If autologin is enabled, ensure both the autologin keystore, identified by the .sso file, and the encryption keystore, identified by the .p12 file, are present in the wallet location.

SQL> ! ls -l /u01/app/oracle/vdbwallet/vtde1/
total 12
-rw-------. 1 oracle oinstall 3891 Aug 27 19:08 cwallet.sso
-rw-------. 1 oracle oinstall 2408 Aug 27 19:08 ewallet_2020082706542273.p12
-rw-------. 1 oracle oinstall 3848 Aug 27 19:08 ewallet.p12

2. Display the contents of the wallet to obtain the current key details.

[oracle@oel7si2 provision]$ orapki wallet display -wallet /u01/app/oracle/vdbwallet/vtde1/ -pwd keystore123
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.
Requested Certificates: 
Subject: CN=oracle
User Certificates:
Oracle Secret Store entries: 
ORACLE.SECURITY.DB.ENCRYPTION.ARt9JJwGC08AvzNja03PcaMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.ARt9JJwGC08AvzNja03PcaMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:

3. Perform the key master key rotation using the administer key command.

[oracle@oel7si2 ]$ sqlplus / as sysdba

SQL> ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY FORCE KEYSTORE IDENTIFIED BY keystore123 WITH BACKUP;

keystore altered.

4. Display the contents of the wallet; the new key details will be seen (in this case key AVZzNf+M709ivwGxCrfsf0oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA).

[oracle@oel7si2 provision]$ orapki wallet display -wallet /u01/app/oracle/vdbwallet/vtde1/ -pwd keystore123

Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Requested Certificates: 
Subject: CN=oracle
User Certificates:
Oracle Secret Store entries: 
ORACLE.SECURITY.DB.ENCRYPTION.ARt9JJwGC08AvzNja03PcaMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AVZzNf+M709ivwGxCrfsf0oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
ORACLE.SECURITY.ID.ENCRYPTION.
ORACLE.SECURITY.KB.ENCRYPTION.
ORACLE.SECURITY.KM.ENCRYPTION.ARt9JJwGC08AvzNja03PcaMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.KM.ENCRYPTION.AVZzNf+M709ivwGxCrfsf0oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Trusted Certificates:

5. The master key is seen altered inside the database and a new creation time appears.

SQL> select KEY_ID,KEY_USE,CREATOR_DBNAME, CREATION_TIME from V$ENCRYPTION_KEYS order by creation_time;

KEY_ID                                                       KEY_USE    CREATOR_DBNAME  CREATION_TIME
------------------------------------------------------------ ---------- --------------- -------------------------------------
ARt9JJwGC08AvzNja03PcaMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         TDE        tde1            27/AUG/20 06:54:22.771516 AM +00:00
AVZzNf+M709ivwGxCrfsf0oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         TDE        vtde1           27/AUG/20 10:03:28.605733 AM +00:00

6. Confirm the key changes are active after a database restart.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 8620224 bytes
Variable Size 335546176 bytes
Database Buffers 905969664 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

SQL> select KEY_ID,KEY_USE,CREATOR_DBNAME, CREATION_TIME from V$ENCRYPTION_KEYS order by creation_time;

KEY_ID                                                       KEY_USE    CREATOR_DBNAME  CREATION_TIME
------------------------------------------------------------ ---------- --------------- -------------------------------------
ARt9JJwGC08AvzNja03PcaMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         TDE        tde1            27/AUG/20 06:54:22.771516 AM +00:00
AVZzNf+M709ivwGxCrfsf0oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA         TDE        vtde1           27/AUG/20 10:03:28.605733 AM +00:00

7. Ensure the encrypted data can be accessed in the VDB after the key rotation has occurred.

SQL> connect delphix/delphix
Connected.
SQL> select * from TBLENCRYPT;
ID
----------
1
2
3

The master key rotation is now complete.

Related Articles

The following articles may provide more information or related information to this article:

• https://docs.oracle.com/en/database/oracle/oracle-database/12.2/asoag/managing-keystore-and-tde-master-encryption-key.html#GUID-A687C8D0-7597-4D92-BB75-CEE35B012970