Using TDE in a 12c Data Guard environment

Oracle Database 12c introduces a new set of commands to manage TDE cryptographic keys.
The new ADMINISTER KEY MANAGMENT group of commands enable a security administrator
manage the cryptography, allowing the administrator to create, and maintain the cryptographic keystore.

This is a demonstration of TDE in a 12c multitenant database environment using Active Data Guard.

Overview
We start with a Data Guard configuration which consists of a 12c single-instance CDB on the primary
site, protected by a physical standby. The primary has these tenant databases.

• hrpdb - An demo HR database, which will contain the encrypted data
  
• salespdb - A demo sales database.
  

The demonstration will progress in stages.

Stage 1: Establish the cryptography infrastructure on the two sites.
This part will demonstrate the procedures involved in creation of the keystore on the databases, key generation, activation, and transport of keys to the physical standby. After that, TDE will be enabled across all container databases on both the sites.

Stage 2: Demonstrate TDE operations with an table containing an encrypted column.
The physical standby will be opened in Active Data Guard mode.
We will then connect to the primary's HRPDB container, and create a table for storing employee information.
This table will contain a column for employee social security numbers, which will be
encrypted transparently using 192 bit AES encryption.

At the standby, once it has applied the redo from the demo table creation, we will demonstrate that we are able to query the encrypted data on the standby database using the TDE environment we established.

Stage 1: Establish the cryptography infrastructure on the two sites.

We start off with a Data Guard configuration consisting of the primary and physical standby in
maximum performance configuration. The databases are distinguished as SCOTT (primary) and
TIGER (standby) by their db_unique_name init parameter value.
Content of the v$dataguard_config view:






DB_UNIQUE_NAME PARENT_DBUN DEST_ROLE CURRENT_SCN CON_ID
-------------- ------ ---------------- ----------- ------
SCOTT NONE PRIMARY DATABASE 1124183 0
TIGER SCOTT PHYSICAL STANDBY 1124170 0


TDE operations require the creation of a cryptographic keystore – the keystore is a protected repository for the cryptographic keys and other cryptography related data. It can be stored on a either on a hard disk as a protected file, or hsm. In this example, we will use a file-based key store - also known as a software keystore. In a multi-database environment, each databases requires its own cryptographic keystore.

Step #1 - As the oracle home owner, create a folder location for the keystore on
the primary host using OS commands.

For example:
$ > mkdir -p /u01/app/oracle/oradata/wallets
Using a text editor, configure the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora. The
default location of sqlnet.ora is <ORACLE_HOME>/network/admin.


ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/oradata/wallets)))




You should edit the sqlnet.ora pointed to by the TNS_ADMIN environment variable if a TNS_ADMIN environment variable has been configured.

Step #2 - Connect to the root of the primary as sysdba or syskm, and run the
ADMINSTER KEY MANAGEMENT...CREATE KEYSTORE command to create the
keystore

The create keystore command accepts an argument which points to the location of the keystore. The location should be empty, and match the location configured in sqlnet.ora. For all key management operations, you need to supply the keystore password using the IDENTIFIED BY <password> syntax.

ADMINISTER KEY MANAGEMENT
CREATE KEYSTORE '/u01/app/oracle/oradata/wallets' IDENTIFIED BY oracle;






For example:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/oradata/wallets'
2 IDENTIFIED BY oracle;
keystore altered.
SQL> !ls /u01/app/oracle/oradata/wallets
ewallet.p12
SQL> administer key management set keystore open identified by oracle;
keystore altered.



Notice above, that Oracle has created an empty key store with the name ewallet.p12.
We also open the keystore using the ADMINISTER KEY MANAGEMENT..SET KEYSTORE OPEN.

Note:
1. The keystore belongs to the entire CDB.
2. There is only one keystore per database.
3.The keystore can be open or closed in a given container.
4. It is necessary to open the keystore in root before a pdb can open it or perform TDE operations.

Step #3 - Still connected to the root container of the primary as sysdba or syskm,
generate keys on the primary (use ADMINISTER KEY MANAGEMENT..SET KEY)

The ADMINISTER KEY MANAGEMENT..SET KEY generates a key and also activates it.
It can be used for initial key generation and also for later key generation.

administer key management set key
using tag 'created_on_primary_for_all_pdbs'
identified by oracle with backup
container=all;
For example:






SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'created_on_primary_for_all_pdbs'
2 IDENTIFIED BY oracle WITH BACKUP CONTAINER=ALL;
keystore altered.




We generate the key with the CONTAINER=ALL modifier. This key will be available for TDE operations across all PDBS. We will use this key for the demonstration.

Note:
Key activation is not possible on the standby database, because it is doing managed recovery.
You need to do it on the primary before transporting the keys to it. If key activation is attempted on a standby, you will hit error ORA- 28388, which says that database is not open in read-write mode.

Optional - Query the key information on primary

Metadata information about the keys available in the currently opened keystore is exposed through the v$encryption_keys view.

The columns key_id, tag, activation_time, are useful for key administration
purposes. Still connected to the primary database, query the view as sysdba or syskm.
For example:


SQL> select key_id, tag, creator, creator_dbid from v$encryption_keys;
KEY_ID TAG
CREATOR CREATOR_DBID
------------------------------------------------------------ ---------------------------------------- -
--------- ------------
Aa4PJO1DBE8Yv3hQVA3F89YAAAAAAAAAAAAAAAAAAAAAAAAAAAAA created_on_primary_for_all_pdbs
SYS 576196403
AWjlhmj0cE/nv4FmnFJ6Gl8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA created_on_primary
SYS 576196403





The query results above show that we have generated two keys.
Each is identified by its key_id and also by the user-created tag.

Step #6 - Copy the primary wallet file ewallet.p12 to a standby  location

For example:
scp /u01/app/oracle/oradata/wallets/ewallet.p12 standby:/home/oracle
oracle@standby's password:
ewallet.p12 100% 5760 5.6KB/s 00:00

Above, the wallet file ewallet.p12 is copied from primary host to the standby host, using unix secure copy.

Step #7 - On standby, create the empty keystore using ADMINSTER..CREATE
KEYSTORE

Caution :
Before attempting this, the sqlnet.ora and keystore location itself should be configured correctly on the standby. For example, this time, run the create keystore command on the standby, ensuring that the keystoref older exists and sqlnet.ora is updated.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/oradata/wallets/cdb2'
2 IDENTIFIED BY oracle;

Step #8 - On the standby, do a keystore merge - merge the copy of the primary
keystore with the empty keystore on the standby

This step is critical. For a physical standby, a keystore merge is required. The merge operation, in this specific case, essentially copies the keys from the primary keystore file to the standby keystore file.
Caution:
The standby keystore should be explicitly re-opened after the merge in order that the imported keys become available on the standby. However, the target keystore need not be open for the merge. (See step 9 below)

In the sql below the source keystore is the copied file /home/oracle/ewallet.p12. The target keystore is the keystore which was created in step 6. Note that the command requires the user to specify the location of the source and destination keystores, and also requires the passwords to the respective keystores to be specified on the command line.

SQL> administer key management merge keystore '/home/oracle'
identified by oracle
into existing keystore '/u01/app/oracle/oradata/wallets'
identified by oracle
with backup using 'keystore_merge_bkp';
2 3 4 5
keystore altered.

Step #9 - After merge, explicitly re-open the keystore (on the standby)

This operation is required after a merge - and we use container=all so that the keys can be used in any container.

SQL> administer key management set keystore close identified by oracle container=all;
SQL> administer key management set keystore open identified by oracle container=all;

Stage 2: Demonstrate TDE operations

Step # 1 Open the standby in active data guard mode

The cryptography is now set up as required between the two databases. Now we can prepare to test TDE operations in Active Data Guard mode.

Below, we stop managed recovery on the standby, open the database as well as the pdb of interest in
read-only mode, and restart media recovery again so it can synchronize with the changes on primary in real-time. Note that PDBS always have to be explicitly opened and will not implicitly open when the CBD it started.


SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> alter pluggable database hrpdb open read only;
Pluggable database altered.
SQL> recover managed standby database

Step # 2 - If not already done, on the standby, open keystore in all containers

Normally, the key store would have been re-opened in step 8 above already after the merge operation

Step #3 - Create a table with an encrypted column on primary

We connect to the to the HRPDB pluggable database as hradmin user.
You can connect to a specific container of a pluggable database by specifying the PDB-specific service name in the SERVICE_NAME attribute in the tnsnames connect string.
The hradmin user is local user who exists in the HRPDB pluggable database, with storage quota on the USERS tablespace, which is also his default tablespace in the pdb. We create a test table named employee, it will have just two columns, an employee name and an encrypted column for storing his Social Security number. We also insert a couple of test emplyee records and commit.


SQL> create table employee ( name varchar(50), ssn varchar(50) encrypt using 'AES192');
Table created.
SQL> insert into hradmin.employee values ('emp1','123-456-001');
1 row created.
SQL> insert into hradmin.employee values('emp2','123-456-002');
1 row created.
SQL> commit;

Step # 4 - Check that you are able to query the newly created data on the standby

Create a tnsnames connect string on the standby which points to the standby hrpdb service.
Connect from sqlplus to the hrpdb container and query the table. We are able to see the data in plaintext on the standby due to TDE.

SQL> select * from employee;
NAME SSN
-------------------- --------------------
emp1 123-456-001
emp2 123-456-002

Step 5 # Verify that you cannot view the encrypted data with the keystore closed.

This is a kind of verification to make sure the data was indeed viewable in Step 10 due to TDE. On the standby, we will explicitly close the keystore and req-qury the table to make sure that the data is not accessible when TDE is deactivated. Log on as sysdba to the standby and close the key store in the hrpdb container

SQL> alter session set container=hrpdb;
Session altered.
SQL> administer key management set keystore close identified by oracle;
keystore altered.

Now, we connect as hradmin user on standby and query the table.
Note the ez-connect syntax used here to connect to a specific service in a pluggable database.

SQL> conn hradmin/xxx@localhost:1521/hrpdb.example.com
Connected.
SQL> select * from hradmin.employee;
select * from hradmin.employee
*
ERROR at line 1:
ORA-28365: wallet is not open

Conclusion:

Oracle Database 12c has new syntax for cryptographic key management, a group of ADMINISTER KEY MANAGEMENT commands. Above, we have seen the use of this group of commands to create a keystore, generate and activate keys.

We have seen one example of how keys can be transported from one site and made available at anotherto support operational needs.

We have seen how TDE can be configured to work in an Active Data Guard environment, so that a user or application can query and access protected data on a standby, even as its being modified on the primary.

Similar key management operations are also required when working with TDE-enabled
tables/tablespaces and doing database recovery, export/import, tablespace point-in-time recovery, etc.

Because TDE operations implicitly perform the cryptography, its important for the correct keys to be configured beforehand, in the environment which may require a TDE operation.