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.”