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.
Add parameter to $ORACLE_HOME/network/admin/sqlnet.ora file.
(SOURCE= (METHOD=FILE) (METHOD_DATA= |
Create the Wallet and the Master Encryption Key.
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.
|
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
 |
|
 |
Add Endpoint for Primary Database
 |
|
Click the Link DG1.
|
 |
Assign DG_WALLET as the Default Wallet.
Click Save.
|
 |
Add Endpoint for Standby Database
 |
|
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
 |
|
 |
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:
|
|
The Enrollment page appears.
|
 |
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.
