📋Steps in this guide1/6

Multitenant : Transparent Data Encryption (TDE) in Pluggable Databases (PDBs) in Oracle Database 12c Release 1 (12.1)

This article describes some of the basic key management operations as they relate to Transparent Data Encryption (TDE) in Oracle Database 12c Release 1.

oracle 12cconfigurationintermediate

by OracleDba

12 views

Keystore Location

A keystore must be created to hold the encryption key. The search order for finding the keystore is as follows. - If present, the location specified by the parameter in the "sqlnet.ora" file. - The default location for the keystore. If the $ORACLE_BASE is set, this is "$ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet", otherwise it is "$ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet", where DB_UNIQUE_NAME comes from the initialization parameter file. Keystores should not be shared between CDBs, so if multiple CDBs are run from the same you must do one of the following to keep them separate. - Use the default keystore location, so each CDB database has its own keystore. - Specify the location using the $ORACLE_SID. - Have a separate "sqlnet.ora" for each database, making sure the variable is set correctly. Regardless of where you place the keystore, make sure you don't lose it. Oracle 12c is extremely sensitive to loss of the keystore. During the writing of this article I was forced to revert to a clean snapshot several times.

Code/Command (click line numbers to comment):

1
2
3

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)))

Create a Keystore

Edit the "$ORACLE_HOME/network/admin/sqlnet.ora" files, adding the following entry. Create the directory to hold the keystore. Connect to the root container and create the keystore. You can open and close the keystore from the root container using the following commands. If the clause is omitted, the current container is assumed. Open the keystore for all containers. You need to create and activate a master key in the root container and one in each of the pluggable databases. Using the clause does it in a single step. If the clause is omitted, it will only be done in the current container and will need to be done again for each PDB individually. Information about the master key is displayed using the view. Information about the keystore is displayed using the view. Connect to the PDB. If you didn't create the key in the previous step, create a new master key for the PDB.

Code/Command (click line numbers to comment):

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)))

mkdir -p /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore

CONN / AS SYSDBA

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/cdb1/encryption_keystore/' IDENTIFIED BY myPassword;

HOST ls /u01/app/oracle/admin/cdb1/encryption_keystore/
ewallet.p12

SQL>

-- Open
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword CONTAINER=ALL;

-- Close
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY myPassword CONTAINER=ALL;

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY myPassword WITH BACKUP CONTAINER=ALL;

SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

    CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
         0 AdaYAOior0/3v0AoZDBV8hoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
         0 AYmKkQxl+U+Xv3UHVMgSJC8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL>

SET LINESIZE 200
COLUMN wrl_parameter FORMAT A50
SELECT * FROM v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE                 /u01/app/oracle/admin/cdb1/encryption_keystore/    OPEN                           PASSWORD             SINGLE    NO                 0

SQL>

CONN sys@pdb1 AS SYSDBA

-- We don't need to create a master key as we did it previously by using CONTAINER=ALL
-- ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword;
-- ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY myPassword WITH BACKUP;

SELECT con_id, key_id FROM v$encryption_keys;

    CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
         0 ATbrc0RkAE//v/jcxOecSGIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL>

Use the Keystore for TDE

You should now be able to create a table with an encrypted column in the PDB. We can also create encrypted tablespaces. If the PDB is restarted, the keystore must be opened in the PDB before the data can be accessed. If the CDB is restarted, the keystore must be opened in both the CDB and the PDBs.

Code/Command (click line numbers to comment):

CONN test/test@pdb1

-- Encrypted column
CREATE TABLE tde_test (
  id    NUMBER(10),
  data  VARCHAR2(50) ENCRYPT
);

INSERT INTO tde_test VALUES (1, 'This is a secret!');
COMMIT;

-- Encrypted tablespacew
CONN sys@pdb1 AS SYSDBA

CREATE TABLESPACE encrypted_ts
DATAFILE SIZE 128K
AUTOEXTEND ON NEXT 64K
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

CONN test/test@pdb1

CREATE TABLE tde_ts_test (
  id    NUMBER(10),
  data  VARCHAR2(50)
) TABLESPACE encrypted_ts;

INSERT INTO tde_ts_test VALUES (1, 'This is also a secret!');
COMMIT;

CONN sys@pdb1 AS SYSDBA

SHUTDOWN IMMEDIATE;
STARTUP;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword;

CONN test/test@pdb1

SELECT * FROM tde_test;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!

SQL>

SELECT * FROM tde_ts_test;

        ID DATA
---------- --------------------------------------------------
         1 This is also a secret!

SQL>

CONN / AS SYSDBA

SHUTDOWN IMMEDIATE;
STARTUP;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword CONTAINER=ALL;

CONN test/test@pdb1

SELECT * FROM tde_test;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!

SQL>

SELECT * FROM tde_ts_test;

        ID DATA
---------- --------------------------------------------------
         1 This is also a secret!

SQL>

Unplug/Plugin PDBs with TDE

This section describes the process of unplugging PDB1 from the CDB1 instance and plugging into the CDB2 instance on the same machine with a new name of PDB2. Switch to the CDB1 instance. Export the key information from PDB1. Unplug PDB1 from CDB1. Switch to the CDB2 instance. Plug in the PDB1, with the new name of PDB2 into the CDB2 instance. Opening PDB2 will result in the following error, which we can ignore that this point. If CDB2 doesn't already have a keystore at the root level, you will need to create it. Import the key information into PDB2 and restart it. Until it opens cleanly it will not register with the listener, so switch the container manually. The encrypted data is now available as expected.

Code/Command (click line numbers to comment):

ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
ORAENV_ASK=YES
sqlplus /nolog

CONN sys@pdb1 AS SYSDBA

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mySecret" TO '/tmp/export.p12' IDENTIFIED BY myPassword;

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/tmp/pdb1.xml';

ORAENV_ASK=NO
export ORACLE_SID=cdb2
. oraenv
ORAENV_ASK=YES
sqlplus /nolog

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb1.xml';

-- If you are not using OMF, you will have to convert the paths manually.
--CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb1.xml'
--  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb2/pdb2/');

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

Warning: PDB altered with errors.

CONN / AS SYSDBA
HOST mkdir -p /u01/app/oracle/admin/cdb2/encryption_keystore/
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/cdb2/encryption_keystore/' IDENTIFIED BY myPassword;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword;

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb2;

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "myPassword";
ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "mySecret" FROM '/tmp/export.p12' IDENTIFIED BY "myPassword" WITH BACKUP;

-- Restart the PDB and open the keystore.
SHUTDOWN;
STARTUP;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "myPassword";

CONN test/test@pdb2

SELECT * FROM tde_test;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!

SQL>

SELECT * FROM tde_ts_test;

        ID DATA
---------- --------------------------------------------------
         1 This is also a secret!

SQL>

Auto-Login Keystores

Creation of an auto-login keystore means you no longer need to explicitly open the keystore after a restart. The first reference to a key causes the keystore to be opened automatically, as shown below.

Code/Command (click line numbers to comment):

CONN / AS SYSDBA
ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/cdb1/encryption_keystore/' IDENTIFIED BY myPassword;

SHUTDOWN IMMEDIATE;
STARTUP

CONN test/test@pdb1

SELECT * FROM tde_test;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!

SQL>

SELECT * FROM tde_ts_test;

        ID DATA
---------- --------------------------------------------------
         1 This is also a secret!

SQL>

SYSKM

Key management can be performed by any member of the or group. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!