Oracle 12.2 and Transparent Data Encryption

Since the new Oracle 12.2.0 version is released, I decided to test the Transparent Data Encryption as new features are available. The following tests have been made in a multitenant environment, DB1 and two pluggable databases DB1PDB1 and DB1PDB2.

The first step consists in creating a software keystore. A software keystore is a container that stores the Transparent Data Encryption key. We define its location in the sqlnet.ora file if we need to use it for a software keystore location:

In the sqlnet.ora file, we have to define the ENCRYPTION_WALLET_LOCATION parameter:

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/u00/app/oracle/local/wallet)))

We can verify in the view:

SQL> select * from v$encryption_wallet;

WRL_TYPE  WRL_PARAMETER                  STATUS	WALLET_TYPE	WALLET_OR   FULLY_BAC      CON_ID

FILE    /u00/app/oracle/local/wallet/     NOT_AVAILABLE		UNKNOWN      SINGLE       UNDEFINED

Then we create the software keystore using sqlplus. We must be connected with a user with the ADMINISTER KEY MANAGEMENT or SYSKM privilege:

SQL> connect c##sec_admin as syskm
Enter password: 
Connected.

SQL> administer key management create keystore '/u00/app/oracle/local/wallet' identified by manager; 

keystore altered.

Once the keystore is created the ewallet.p12 is generated in the keystore file location:

oracle@localhost:/u00/app/oracle/local/wallet/ [db1] ls
afiedt.buf  ewallet.p12

Therefore, depending of the type of the keystore we have created, we must manually open the keystore. We can check in the v$encryption_wallet view to see if the keystore is opened.

If not you have to run the following command:

oracle@localhost:/u00/app/oracle/local/wallet/ [db1] sqlplus c##sec_admin as syskm

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 13 11:59:47 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> administer key management set keystore open identified by manager container = ALL;

keystore altered.

If we ask the view:

SQL> select * from v$encryption_wallet;

WRL_TYPE.    WRL_PARAMETER                STATUS             WALLET_TYPE  WALLET_OR   FULLY_BAC   CON_ID

FILE     /u00/app/oracle/local/wallet/  OPEN_NO_MASTER_KEY    PASSWORD 	    SINGLE    UNDEFINED

Now we must set the Software TDE master encryption key, once the keystore is open, as we are in a multitenant environment, we have to specify CONTAINER=ALL in order to set the keystone in all the PDBs:

SQL> administer key management set keystore close identified by manager;

keystore altered.

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

keystore altered.

SQL> administer key management set key identified by manager with backup using 'kex_backup' container =ALL;

keystore altered.

Now the v$encryption_wallet view is up to date:

SQL> select * from v$encryption_wallet;

WRL_TYPE   WRL_PARAMETER.               STATUS  WALLET_TYPE	    WALLET_OR FULLY_BAC   CON_ID

FILE.   /u00/app/oracle/local/wallet/.   OPEN	 PASSWORD 	    SINGLE      NO          1

When you startup your CDB and your PDBs, you must do things in a good way:

You shutdown and startup the database

oracle@localhost:/u00/app/oracle/admin/db1/ [db1] sq

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 14 13:53:09 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3405774848 bytes
Fixed Size		    8798456 bytes
Variable Size		  805310216 bytes
Database Buffers	 2583691264 bytes
Redo Buffers		    7974912 bytes
Database mounted.
Database opened.

You open the wallet:

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

keystore altered.

The pluggable databases are not yet opened:

SQL> connect sys/manager@db1pdb1
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


Warning: You are no longer connected to ORACLE.

You start the pluggable databases:

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database all open;

Pluggable database altered.

The wallet is closed on the pluggable databases:

SQL> connect sys/manager@db1pdb1 as sysdba
Connected.
SQL> select status from v$encryption_wallet;

STATUS
------------------------------
CLOSED

You first have to close the wallet then to open it again:

SQL> connect / as sysdba
Connected.
SQL> administer key management set keystore open identified by manager container = all;
administer key management set keystore open identified by manager container = all
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open


SQL> administer key management set keystore close identified by manager;

keystore altered.

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

keystore altered.

The wallet is opened on every pluggable database:

SQL> connect sys/manager@db1pdb1 as sysdba
Connected.
SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

SQL> connect sys/manager@db1pdb2 as sysdba
Connected.
SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

Once the software keytore is set, you have the possibility now to encrypt your data.You have the possibility to encrypt columns in tables, or realise encryption in tablespaces or databases.

Concerning the columns in a table, you can encrypt many data types, Oracle recommend not to use TDE in case of transportable tablespace, or columns used in foreign keys constraints. The TDE default algorithm used is AES192.

Let’s create the classical empire table and insert some values:

SQL> create table emp1 (name varchar2(30), salary number(7) encrypt);

Table created.


SQL> insert into emp1 values ('Larry', 1000000);

1 row created.

SQL> select * from emp1;

NAME				   SALARY
------------------------------ ----------
Larry				  1000000

If now we close the keystore, the data are not viewable anymore:

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

keystore altered.

SQL> connect psi/psi@db1pdb1
Connected.
SQL> select * from emp1;
select * from emp1
*
ERROR at line 1:
ORA-28365: wallet is not open


SQL> select name from emp1;

NAME
------------------------------
Larry

SQL> select name, salary from emp1;
select name, salary from emp1
                         *
ERROR at line 1:
ORA-28365: wallet is not open

We can also use non default algorithms as 3DES168, AES128, AES256, for example:

SQL> create table emp2 (
  2  name varchar2(30),
  3  salary number(7) encrypt using 'AES256');

Table created.

If your table has a high number of rows and encrypted columns, you have the possibility to use the NOMAC parameter to bypass the TDE checks and to save some disk space:

SQL> create table emp3 (
  2  name varchar2(30),
  3  salary number (7) encrypt 'NOMAC');

Table created.

For existing tables, you can add encrypted columns with the ALTER table XXX add SQL statement, or you can encrypt an existing column with the alter table modify statement:

SQL> create table emp4 (name varchar2(30));

Table created.

SQL> alter table emp4 add (salary number (7) encrypt);

Table altered.

SQL> create table emp5 (
  2  name varchar2(30),
  3  salary number(7));

Table created.

SQL> alter table emp5 modify (salary encrypt);

Table altered.

Eventually, you can turn off the encryption for a table:

SQL> alter table emp5 modify (salary decrypt);

Table altered.

One of the main 12.2 new feature is the tablespace encryption. You have now the possibility to encrypt new and existing tablespace, you can also encrypt the database including the SYS SYSAUX TEMP and UNDO tablespaces in online mode.

For example, in the previous Oracle versions, you had the possibility to encrypt tablespace when they were in offline mode or the database in mount state, in 12.2 we can encrypt in online mode.

The encryption for the TEMP tablespace is the same as the Oracle previous releases, you cannot convert the TEMP tablespace, but you can create a new temporary encrypted tablespace and make it default temporary tablespace.

You can encrypt the UNDO tablespace, but Oracle recommends not to decrypt the tablespace once it has been encrypted.

At first the compatible parameter must be set to 11.2.0 when encrypting tablespaces, and at the 12.2.0.0 when encrypting SYS SYSAUX or UNDO tablespaces.

SQL> create tablespace PSI_ENCRYPT
  2  datafile '/u01/oradata/db1/db1pdb1/psi_encrypt.dbf' size 10M
  3  encryption using 'AES128' encrypt;

Tablespace created.

We have the possibility to realise Online conversion for existing tablespaces:

SQL> select file_name from dba_data_files where tablespace_name = 'PSI';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/db1/db1pdb1/psi.dbf

The compatible parameter is set to 12.2.0:

SQL> show parameter compatible

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
compatible			     string	 12.2.0

Now you have the possibility to encrypt the data file by using the following command, be sure that you have available free space:

SQL> alter tablespace PSI ENCRYPTION online using 'AES256' ENCRYPT FILE_NAME_CONVERT = ('psi.dbf', 'psi_encrypt.dbf');

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name = 'PSI';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/db1/db1pdb1/psi_encrypt.dbf

You can also decrypt online a tablespace:

QL> alter tablespace PSI ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT = ('psi_encrypt.dbf', 'psi.dbf');

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name = 'PSI';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/db1/db1pdb1/psi.dbf

Therefore our PSI tablespace is not encrypted anymore, let’s create a non-encrypted table, insert some values in it, and perform an encryption on the tablespace, then close the wallet and see what happens:

SQL> select file_name from dba_data_files where tablespace_name = 'PSI';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/db1/db1pdb1/psi.dbf

SQL> connect psi/psi@db1pdb1
Connected.
SQL> create table emp (name varchar2(30), salary number(7));

Table created.

SQL> insert into emp values ('Larry', 1000000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

NAME				   SALARY
------------------------------ ----------
Larry				  1000000

SQL> select tablespace_name from user_tables where table_name = 'EMP';

TABLESPACE_NAME
------------------------------
PSI

SQL> alter tablespace PSI ENCRYPTION online using 'AES256' ENCRYPT FILE_NAME_CONVERT = ('psi.dbf', 'psi_encrypt.dbf');

Tablespace altered.

SQL> select * from emp;

NAME				   SALARY
------------------------------ ----------
Larry				  1000000

oracle@localhost:/u01/oradata/db1/ [db1] sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 13 16:11:18 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

keystore altered.

SQL> connect psi/psi@db1pdb1
Connected.
SQL> select * from emp;
select * from emp
              *
ERROR at line 1:
ORA-28365: wallet is not open

It works fine, non encrypted tables in a tablespace are encrypted when the tablespace is encrypted.

When the tablespace is encrypted, the strings command gives no result:

oracle@localhost:/u01/oradata/db1/db1pdb1/ [db1] strings psi_encrypt.dbf | grep -i Larry
oracle@localhost:/u01/oradata/db1/db1pdb1/ [db1]

When we open the wallet and decrypt the tablespace, we can find information in the datafile:

oracle@localhost:/u01/oradata/db1/db1pdb1/ [db1] strings psi.dbf | grep Larry
Larry

Now in 12.2 Oracle version, you can convert online the entire database, i.e the SYSTEM SYSAUX TEMP and UNDO tablespace. The commands are the same as for a data tablespace as seen previously: always the same precautions have enough free space and the compatible parameter set to 12.2.0, just a little difference you cannot specify an encryption key:

For example let’s encrypt the SYSTEM tablespace:

SQL> alter tablespace SYSTEM ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT = ('system01.dbf','system01_encrypt.dbf');

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name = 'SYSTEM';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/db1/db1pdb1/system01_encrypt.dbf

For the temporary tablespace, we have to drop the existing temporary tablespace , and create a new one encrypted as follows:

SQL> create temporary tablespace TEMP_ENCRYPT

2  tempfile ‘/u01/oradata/db1/db1pdb1/temp_encrypt.dbf’ size 100M

3  ENCRYPTION ENCRYPT;

Tablespace created.

SQL> alter database default temporary tablespace TEMP_ENCRYPT;

Database altered.

SQL> drop tablespace TEMP;

Tablespace dropped.

For the undo tablespace:

SQL> alter tablespace UNDOTBS1 ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT = (‘undotbs01.dbf’,’undotbs01_encrypt.dbf’);

Tablespace altered.

SQL> connect sys/manager@db1pdb1 as sysdba
Connected.
SQL> administer key management set keystore close identified by manager;
administer key management set keystore close identified by manager
*
ERROR at line 1:
ORA-28439: cannot close wallet when SYSTEM, SYSAUX, UNDO, or TEMP tablespaces
are encrypted

On the pluggable db1pdb2, as the tablespaces are not encrypted, the wallet can be closed:

SQL> connect sys/manager@db1pdb2 as sysdba
Connected.
SQL> administer key management set keystore close identified by manager;

keystore altered.

I also wanted to test the expel and impdp behaviour between pluggable databases, as we are in a multitenant environment, we have to ensure the wallet is opened in the PDBs

In order to export a table, you have to add the ENCRYPTION parameter and the ENCRYPTION_PWD_PROMPT parameter  for security reasons:

oracle@localhost:/home/oracle/ [DB1PDB1] expdp system@db1pdb1 tables=psi.emp directory=DATA_PUMP_DIR dumpfile=emp.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY ENCRYPTION_PWD_PROMPT=YES

Export: Release 12.2.0.1.0 - Production on Tue Mar 14 11:53:52 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Encryption Password: 
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@db1pdb1 tables=psi.emp directory=DATA_PUMP_DIR dumpfile=emp.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY ENCRYPTION_PWD_PROMPT=YES 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "PSI"."EMP"                                 5.523 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u00/app/oracle/admin/db1/dpdump/4A3D428970DA5D68E055000000000001/emp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Mar 14 11:54:16 2017 elapsed 0 00:00:21

In the same way if we want to import the emp table in the second pluggable database, the wallet must be opened , otherwise it will not work:

racle@localhost:/home/oracle/ [DB1PDB1] impdp system@db1pdb2 tables=psi.emp directory=DATA_PUMP_DIR dumpfile=emp.dmp ENCRYPTION_PWD_PROMPT=YES

Import: Release 12.2.0.1.0 - Production on Tue Mar 14 12:15:24 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Encryption Password: 
ORA-39002: invalid operation
ORA-39180: unable to encrypt ENCRYPTION_PASSWORD
ORA-28365: wallet is not open

you open the wallet:

SQL> administer key management set keystore open identified by manager;

keystore altered.

The impdp command runs fine:

oracle@localhost:/home/oracle/ [DB1PDB1] impdp system@db1pdb2 tables=psi.emp directory=DATA_PUMP_DIR dumpfile=emp.dmp ENCRYPTION_PWD_PROMPT=YES

Import: Release 12.2.0.1.0 - Production on Tue Mar 14 12:21:47 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Encryption Password: 
ORA-39175: Encryption password is not needed.
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@db1pdb2 tables=psi.emp directory=DATA_PUMP_DIR dumpfile=emp.dmp ENCRYPTION_PWD_PROMPT=YES 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Mar 14 12:21:55 2017 elapsed 0 00:00:05

But the generated dumpfile is not encrypted and you can find sensitive data in this file:

oracle@localhost:/u00/app/oracle/admin/db1/dpdump/ [db1] strings emp.dmp | grep -i Larry
Larry

Oracle offers a solution to encrypt the dump file, you can use the ENCRYPTION_MODE parameter set to TRANSPARENT or DUAL to realise your expdp command. By using TRANSPARENT, you do not need a password, the dump file is encrypted transparently, the keystone must be present and open on the target database. By specifying DUAL, you need a password and the dump file is encrypted using the TDE master key encryption.

oracle@localhost:/home/oracle/ [db1] expdp system@db1pdb1 tables=psi.emp directory=DATA_PUMP_DIR ENCRYPTION=ALL ENCRYPTION_PWD_PROMPT=YES ENCRYPTION_ALGORITHM=AES256 ENCRYPTION_MODE=DUAL dumpfile=emp_encrypt.dmp

Export: Release 12.2.0.1.0 - Production on Tue Mar 14 12:44:18 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Encryption Password: 
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@db1pdb1 tables=psi.emp directory=DATA_PUMP_DIR ENCRYPTION=ALL ENCRYPTION_PWD_PROMPT=YES ENCRYPTION_ALGORITHM=AES256 ENCRYPTION_MODE=DUAL dumpfile=emp_encrypt.dmp 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "PSI"."EMP"                                 5.531 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u00/app/oracle/admin/db1/dpdump/4A3D428970DA5D68E055000000000001/emp_encrypt.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully

And now we cannot retrieve sensitive data from the dump file:

oracle@localhost:/u00/app/oracle/admin/db1/dpdump/ [db1] strings emp_encrypt.dmp | grep -i Larry
oracle@localhost:/u00/app/oracle/admin/db1/dpdump/ [db1]

Conclusion:

Concerning the Transparent Data Encryption in the last 12.2.0.1 Oracle version, I will mainly retain the SYSTEM, SYSAUX, UNDO or TEMP encryption giving more security for sensitive data, but be careful even if this functionality is documented in the Oracle documentation, Oracle also writes:

“Do not attempt to encrypt database internal objects such as SYSTEM, SYSAUX, UNDO or TEMP tablespaces using TDE tablespace encryption. You should focus TDE tablespace encryption on tablespaces that hold application data, not on these core components of the Oracle database.”