Oracle Key Vault in an Active Data Guard Environment

In this post I will show how to use Oracle Key Vault server to store a shared virtual wallet for Oracle Database primary and standby server. Oracle Key Vault is a centralized key store that provides key life cycle management, alerts (e.g. key expiration), reports, and other administrative functions.

By default the Master Encryption Key that is used for Transparent Data Encryption is stored in a software wallet on the file system of the database server. Problems you might have with wallets on a file system in large TDE deployments are
- user errors like forgotten password,
- skipped key rotations,
- accidental deletion of wallets
- etc.

I'll start to set up Transparent Data Encryption (TDE) with a software wallet on the file system. Eventually I will migrate that wallet into Oracle Key Vault.

Having the Master Encryption Keys for Transparent Data Encryption stored in Key Vault makes it particularly easy to perform re-key operations in an Active Data Guard environment. There's no need to manually copy the wallet from the primary to the standby server anymore as you will see in the Test at the end of this blog post.

For more information about Oracle Key Vault, click here.

Environment
The environment I used consists of the following:

• Oracle Database 11g Enterprise Edition Release 2; 11.2.0.4 on the primary database and the standby database. 
• Physical Data Guard is already configured.
• Primary database server name is dg1 with a database name dg1.
• Standby database server name is dg2 with a database name dg2.
• Oracle Key Vault server is version 12.1.0.0.0.

It is important to do the steps in the right order on the primary and standby server.

Configure Transparent Data Encryption

Create Wallet and Master Encryption Key

On the Standby Serer

Create the directory in which the wallet will be created.

[oracle@dg2 ~]$ mkdir /u01/app/oracle/admin/dg2/wallet

Add parameter to the $ORACLE_HOME/network/admin/sqlnet.ora file.

ENCRYPTION_WALLET_LOCATION=  (SOURCE=   (METHOD=FILE)    (METHOD_DATA=     (DIRECTORY=/u01/app/oracle/admin/dg2/wallet)))

On the Primary Server

Create a directory in which the wallet will be created.

[oracle@dg1 ~]$ mkdir /u01/app/oracle/admin/dg1/wallet

Add parameter to $ORACLE_HOME/network/admin/sqlnet.ora file.

ENCRYPTION_WALLET_LOCATION=  (SOURCE=   (METHOD=FILE)    (METHOD_DATA=     (DIRECTORY=/u01/app/oracle/admin/dg1/wallet)))

Create the Wallet and the Master Encryption Key.

SQL> alter system set encryption key identified by "Oracle_123"; System altered.

Copy the wallet to the standby server.

[oracle@dg1 ~]$ scp /u01/app/oracle/admin/dg1/wallet/ewallet.p12 oracle@dg2:/u01/app/oracle/admin/dg2/wallet oracle@dg2's password:  ewallet.p12                        100% 2845     2.8KB/s   00:00

On the Standby Server

Open the wallet on the standby server.

SQL> alter system set encryption wallet open identified by "Oracle_123"; System altered.

Create some Encrypted Sample Data

Create encrypted tablespace emp.

SQL> CREATE TABLESPACE "EMP" DATAFILE '/u01/app/oracle/oradata/dg1/emp_enc01.dbf' SIZE 10M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256'; Tablespace created.

Create user emp.

SQL> CREATE USER "EMP" PROFILE "DEFAULT" IDENTIFIED BY "Oracle123" DEFAULT TABLESPACE "EMP" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "EMP"; User created.

Create table employees.

SQL> create table emp.employees ( eid number, firstname varchar2(32), lastname varchar2(32), dob varchar2(32) ) tablespace emp;  Table created.

Create some sample data.

SQL> insert into emp.employees values (1, 'John','Doe','28/08/1978'); insert into emp.employees values (2, 'Marie','Curie','15/06/1956'); insert into emp.employees values (3, 'Mario','Mann','14/01/1992'); insert into emp.employees values (4, 'Ted','Turner','30/09/1964'); insert into emp.employees values (5, 'Ben','Brunell','30/09/1964'); commit; 1 row created. SQL>  1 row created. SQL>  1 row created. SQL>  1 row created. SQL>  1 row created. SQL>  Commit complete.

Perform log switch.

SQL> alter system switch logfile; System altered.

On the Standby Server

Query encrypted data.

SQL> column firstname format a10 word_wrapped SQL> column lastname format a10 word_wrapped SQL> column dob format a12 word_wrapped SQL> select * from emp.employees;        EID FIRSTNAME  LASTNAME   DOB          ---------- ---------- ---------- ------------          1 John       Doe        28/08/1978            2 Marie      Curie      15/06/1956            3 Mario      Mann       14/01/1992            4 Ted        Turner     30/09/1964            5 Ben        Brunell    30/09/1964

Configure Endpoints and Install Key Vault Clients

Add Enpoints in Key Vault

Create Virtual Wallet

Connect to the Key Vault management console: https://192.168.56.7 Log in as keyadmin.
Click on the Keys & Wallets tab. On the wallets page, click Create. Enter the name of the wallet and, optionally, a description. Click Save.

Add Endpoint for Primary Database

Select the Endpoints tab. Click Add on the Endpoints page. On the Register Endpoint page, enter the metadata for the new endpoint. Click Register.
When the Endpoint page appears, copy the Enrollment Token for DG1. Click the Link DG1.
Assign DG_WALLET as the Default Wallet. Click Save.

Add Endpoint for Standby Database

Select the Endpoints tab. Click Add on the Endpoints page. On the Register Endpoint page, enter the metadata for the new endpoint. Click Register.
When the Endpoint page appears, copy the Enrollment Token for DG1. Click the Link DG1.
Assign DG_WALLET as the Default Wallet. Click Save.

Enroll Endpoint and install Oracle Key Vault Client

On the Primary Server

Connect to the Key Vault management console: https://192.168.56.7 Under the login area, click on the Endpoint Enrollment and Software Download link.
The Enrollment page appears. Enter the Enrollment Token for DG1 and press Submit Token. Click Enroll to complete process. Save the okvclient.jar to e.g. /home/oracle.

On the primary server, navigate to /home/oracle. Run java command to install okvclient.jar.

When asked for a password, press <enter> for auto-login.

[oracle@dg1 ~]$ java -jar okvclient.jar -d /home/oracle/okvutil -v Detected JAVA_HOME: /usr/java/jdk1.7.0_71/jre Enter new Key Vault endpoint password (<enter> for auto-login):  Oracle Key Vault endpoint software installed successfully.

Run root.sh script to copy liborapkcs.so (located in the lib directory).

[oracle@dg1 ~]$ sudo /home/oracle/okvutil/bin/root.sh  Creating directory: /opt/oracle/extapi/64/hsm/oracle/1.0.0/ Copying PKCS library to /opt/oracle/extapi/64/hsm/oracle/1.0.0/ Setting PKCS library file permissions Installation successful.

On the Standby Server

Connect to the Key Vault management console: https://192.168.56.7 Under the login area, click on the Endpoint Enrollment and Software Download link.
The Enrollment page appears. Enter the Enrollment Token for DG2 and press Submit Token. Click Enroll to complete process. Save the okvclient.jar to e.g. /home/oracle.

On the standby server, navigate to /home/oracle. Run java command to install okvclient.jar.

When asked for a password, press <enter> for auto-login.

[oracle@dg1 ~]$ java -jar okvclient.jar -d /home/oracle/okvutil -v Detected JAVA_HOME: /usr/java/jdk1.7.0_71/jre Enter new Key Vault endpoint password (<enter> for auto-login):  Oracle Key Vault endpoint software installed successfully.

Run root.sh script to copy liborapkcs.so (located in the lib directory).

[oracle@dg1 ~]$ sudo /home/oracle/okvutil/bin/root.sh  Creating directory: /opt/oracle/extapi/64/hsm/oracle/1.0.0/ Copying PKCS library to /opt/oracle/extapi/64/hsm/oracle/1.0.0/ Setting PKCS library file permissions Installation successful.

Migrate Wallet

On the Standby Server

Close wallet on the standby server.

SQL> alter system set encryption wallet close identified by "Oracle_123"; System altered.

Modify parameter in the $ORACLE_HOME/network/admin/sqlnet.ora file.

ENCRYPTION_WALLET_LOCATION=  (SOURCE=   (METHOD=HSM))

Open wallet.

SQL> alter system set encryption wallet open identified by "null"; System altered.

On the Primary Server

Close wallet (In 12c wallet does not have to be closed).

SQL> alter system set encryption wallet close identified by "Oracle_123"; System altered.

Upload existing wallet into Key Vault.

[oracle@dg1 ~]$ /home/oracle/okvutil/bin/okvutil upload -l /u01/app/oracle/admin/dg1/wallet -t WALLET -g DG_WALLET Enter source wallet password:  Upload succeeded

Modify parameter in the $ORACLE_HOME/network/admin/sqlnet.ora file.

ENCRYPTION_WALLET_LOCATION=  (SOURCE=   (METHOD=HSM)    (METHOD_DATA=     (DIRECTORY=/u01/app/oracle/admin/dg1/wallet)))

Check status of the wallet in v$encryption_wallet.

SQL> select wrl_type, status from v$encryption_wallet; WRL_TYPE             STATUS -------------------- ------------------ File                 CLOSED HSM                  CLOSED

Migrate wallet.

SQL> alter system set encryption key identified by "null" migrate using "Oracle_123"; System altered.

Check status of the wallet in v$encryption_wallet.

SQL> select wrl_type, status from v$encryption_wallet; WRL_TYPE             STATUS -------------------- ------------------ File                 CLOSED HSM                  OPEN

Test

On the Primary Server

Rekey the Master Encryption Key on the primary database.

SQL> alter system set encryption key identified by "null"; System altered.

Enter new record into emp.employees.

SQL> insert into emp.employees values (6, 'Alice','Bond','12/04/1954'); 1 row created. SQL> commit; Commit complete.

Switch logfile.

SQL> alter system switch logfile; System altered.

On the Standby Server

Query table emp.employees.

SQL> column firstname format a10 word_wrapped SQL> column lastname format a10 word_wrapped SQL> column dob format a12 word_wrapped SQL> select * from emp.employees;        EID FIRSTNAME  LASTNAME   DOB          ---------- ---------- ---------- ------------          1 John       Doe        28/08/1978            2 Marie      Curie      15/06/1956            3 Mario      Mann       14/01/1992            4 Ted        Turner     30/09/1964            5 Ben        Brunell    30/09/1964          6 Alice      Bond       12/04/1954

As you can see, encrypted data is readable on the standby database without manually copying the wallet across.

That's it. It's a long post but the process is fairly straightforward I believe.