Database Security

Creating PDBs in Data Guard Environments while using OCI Vault for TDE Key Management

Introduction

Oracle Databases on Oracle Cloud use the Multitenant Architecture and are encrypted with TDE by default. Creating new PDBs in Data Guard environments while using a local wallet for TDE master encryption keys requires some additional steps to copy the wallet file and recover the PDB on standby. We discussed these approaches in previous blog posts.

The issue with the local wallet is as soon as you create a TDE master encryption key for the newly created PDB, the local wallet on the standby will not be aware of it. The solution is to have a centralized wallet being used by both primary and standby simultaneously. Oracle provides two solutions:

This blog post describes the case when using OCI Vault.

The Environment

In a previous blog post, we enabled a VM DB System on Oracle Cloud to use OCI Vault for TDE key management. Here, we will continue using that system by enabling Data Gaurd, provisioning a new PDB, and creating a TDE master encryption key for that PDB.

As we are using a centralized key management solution, creating the PDB and the TDE master encryption key should be fully transparent without requiring any additional steps.

Preparation

For the standby DB System to access the TDE keys, you need to allow it to access OCI Vault. But how to do so without having the OCID of the DB System, as it is not provisioned yet? The answer is to allow the resources at the compartment level so all DB Systems in the specified compartment have access to OCI Vault.

Add a rule in your IAM Dynamic Group as follows using resource.compartment.id (NOT instance.compartment.id):

Any {resource.compartment.id = '<compartment_OCID>'}

Enable Data Guard

On the database details page, under Resources on the left side, click on Data Guard Associations, then on Enable Data Guard:

As OCI Vault is a regional service, hence, only VM DB Systems in the same region can access the same TDE keys, only in-region Data Guard is available as of today. Choose the same region as the primary, fill out the needed information, and click Enable Data Guard.

Create new PDBs

Unfortunately, creating new PDBs when Data Guard is enabled is not yet available via Cloud Tooling, so we need to do it the traditional way using SQL:

SQL> create pluggable database PDB002 admin user pdb2admin identified by SecPDBpw#2;

Pluggable database created.

SQL> alter pluggable database PDB002 open instances=all;

Pluggable database altered.

Create the TDE Master Encryption Key

By creating the TDE Master Encryption Key for the newly created PDB, a new key version will be created in OCI Vault, which is accessible from both primary and standby:

SQL> alter session set container = PDB002;

Session altered.

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

*
ERROR at line 1:
ORA-46627: keystore password mismatch

The password for the local TDE wallet has been changed in the background while switching to OCI Vault. So the error message is correct but might be misleading if you don’t know what happened.

As the keys are managed by OCI Vault, we need to create and rotate the keys via the dbcli command line instead of SQL statements. Log in to the primary database host as user root:

# check database id
[root@hosttestcmk ~]# dbcli list-databases
ID                                       DB Name    DB Type  DB Version           CDB        Class    Shape    Storage    Status        DbHomeID                       
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
eb3a9e23-b45b-4185-b701-ecaa12a94ea3     CDBCMK     Si       19.15.0.0.0          true       Oltp              ASM        Configured   3a773a43-8074-4dc0-9c99-fc0cfec2b453

# rotate the key (will create one if non exists and rotate it)
[root@hosttestcmk ~]# dbcli update-tdekey --databaseId eb3a9e23-b45b-4185-b701-ecaa12a94ea3 --pdbNames PDB002
{
  "jobId" : "521b107e-e8b5-4e19-ab62-49dd21b5de74",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "May 29, 2022 16:19:41 PM UTC",
  "resourceList" : [ ],
  "description" : "TDE update CDBCMK - PDBs: [PDB002]",
  "updatedTime" : "May 29, 2022 16:19:44 PM UTC",
  "percentageProgress" : "0%",
  "cause" : null,
  "action" : null
}

Looking at the key versions page in the Cloud Console, you will see a new key version being created:

After a little while, the key will be rotated, and a new version will be created:

Get back to the PDB and check the key via SQL:

SQL> alter session set container = PDB002;

Session altered.

SQL> select con_id, key_id, keystore_type from v$encryption_keys order by con_id;

    CON_ID KEY_ID                                                       KEYSTORE_TYPE
---------- ------------------------------------------------------------ --------------------
         4 0684D3D9AB307A4F67BF3DB2BAC6B84523                           OKV

You can also double-check what KEY_ID in the v$encryption_keys view is associated with which OCI Vault Key Version. Log in to the primary database host as user oracle:

[oracle@hosttestcmk ~]$ kms-tde-cli find-ocid-for-mkid 0684D3D9AB307A4F67BF3DB2BAC6B84523
ocid1.keyversion.oc1.eu-frankfurt-1.cbrcwrdiaafxy.bc4kmkjz3ciaa.abtheljtx4jmj5vh5xpc7yf7ijxxqkmxcuhkuo6o4g2zqvcv47e3iuxwxyoa

Check the TDE key on standby

Log in to the standby database and check the keys from within the new PDB:

SQL> alter session set container = PDB002;

Session altered.

SQL> alter pluggable database open read only;

Pluggable database altered.

SQL> select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status, ew.wallet_order
from v$pdbs p join v$encryption_wallet ew on (ew.con_id = p.con_id)
order by p.con_id;

    CON_ID NAME                 OPEN_MODE  WRL_TYPE   WALLET_TYPE          STATUS                         WALLET_ORDER
---------- -------------------- ---------- ---------- -------------------- ------------------------------ ---------------
         6 PDB005               READ ONLY  FILE       AUTOLOGIN            OPEN_NO_MASTER_KEY             SINGLE
         6 PDB005               READ ONLY  OKV        OKV                  OPEN                           SINGLE

SQL> select con_id, key_id, keystore_type from v$encryption_keys order by con_id;

no rows selected

The standby is not showing the key yet, but it is there. To prove this, create an encrypted tablespace and a table, and you will see that these will be replicated successfully on standby.

The issue is that the standby is missing the metadata about the new PDB. We can update the metadata manually. Log in to the standby host as user oracle:

# kms-tde-cli add-tde-metadata --db-unique-name <db_unique_name> <key_version_OCID>
[oracle@hosttestcmkad2 ~]$ kms-tde-cli add-tde-metadata --db-unique-name CDBCMK_fra2gq ocid1.keyversion.oc1.eu-frankfurt-1.cbrcwrdiaafxy.bc4kmkjz3ciaa.abtheljtx4jmj5vh5xpc7yf7ijxxqkmxcuhkuo6o4g2zqvcv47e3iuxwxyoa
# the key_id will be displayed
0684D3D9AB307A4F67BF3DB2BAC6B84523

# double-check
[oracle@hosttestcmkad2 ~]$ kms-tde-cli find-ocid-for-mkid 0684D3D9AB307A4F67BF3DB2BAC6B84523
ocid1.keyversion.oc1.eu-frankfurt-1.cbrcwrdiaafxy.bc4kmkjz3ciaa.abtheljtx4jmj5vh5xpc7yf7ijxxqkmxcuhkuo6o4g2zqvcv47e3iuxwxyoa

Log in to the PDB again:

SQL> select con_id, key_id, keystore_type from v$encryption_keys order by con_id;

    CON_ID KEY_ID                                                       KEYSTORE_TYPE
---------- ------------------------------------------------------------ --------------------
         4 0684D3D9AB307A4F67BF3DB2BAC6B84523                           OKV

The standby database has access to the same key used by the primary. The Data Guard configuration is healthy, and redo is being applied:

DGMGRL> show configuration

Configuration - CDBCMK_fra1bp_CDBCMK_fra2gq

  Protection Mode: MaxAvailability
  Members:
  CDBCMK_fra1bp - Primary database
    CDBCMK_fra2gq - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 16 seconds ago)

TEMP file on Standby

As tempfiles are not copied to the standby PDB, add a new tempfile to the TEMP tablespace.

In the case of Active Data Guard, the PDB is already opened in READ ONLY mode as in the previous step.

On standby:

SQL> alter session set container = PDB002;
SQL> alter tablespace temp add tempfile;
SQL> select name from v$tempfile;

If you are NOT using Active Data Guard, then add the temp file after switchover/failover, or: stop the redo apply, open the database in READ ONLY, create the temp file, restart in MOUNT mode, and enable redo apply again:

DGMGRL> edit database <standby_db_unique_name> set state = 'apply-off';
SQL> alter database open;
SQL> alter pluggable database PDB002 open;
SQL> alter session set container = PDB002;
SQL> alter tablespace temp add tempfile;
SQL> alter session set container = cdb$root;
SQL> shutdown immediate
SQL> startup mount
DGMGRL> edit database <standby_db_unique_name> set state = 'apply-on';
DGMGRL> show configuration

Conclusion

Using OCI Vault as a centralized key management solution makes TDE key creation and rotation transparent in Data Guard environments. As soon as a key is created or rotated, both the primary and standby have access to it. The Data Gaurd replication continues without any interruptions.

Further Reading

Would you like to get notified when the next post is published?