{"id":9865,"date":"2017-03-14T15:20:45","date_gmt":"2017-03-14T14:20:45","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/"},"modified":"2017-03-14T15:20:45","modified_gmt":"2017-03-14T14:20:45","slug":"oracle-12-2-and-transparent-data-encryption","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/","title":{"rendered":"Oracle 12.2 and Transparent Data Encryption"},"content":{"rendered":"

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.<\/p>\n

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:<\/p>\n

In the sqlnet.ora file, we have to define the ENCRYPTION_WALLET_LOCATION parameter:<\/p>\n

ENCRYPTION_WALLET_LOCATION=\n\u00a0(SOURCE=\n\u00a0 (METHOD=FILE)\n\u00a0\u00a0 (METHOD_DATA=\n\u00a0 \u00a0 (DIRECTORY=\/u00\/app\/oracle\/local\/wallet)))\n\n<\/pre>\n
We can verify in the view:<\/span><\/p>\n
SQL> select * from v$encryption_wallet;\n\nWRL_TYPE  WRL_PARAMETER                  STATUS\tWALLET_TYPE\tWALLET_OR   FULLY_BAC      CON_ID\n\nFILE    \/u00\/app\/oracle\/local\/wallet\/     NOT_AVAILABLE\t\tUNKNOWN      SINGLE\u00a0 \u00a0    UNDEFINED<\/pre>\n
Then we create the software keystore using sqlplus. We must be connected with a user with the ADMINISTER KEY MANAGEMENT or SYSKM privilege:<\/p>\n
SQL> connect c##sec_admin as syskm\nEnter password:\u00a0\nConnected.\n\nSQL> administer key management create keystore '\/u00\/app\/oracle\/local\/wallet' identified by manager;\u00a0\n\nkeystore altered.\n\n<\/pre>\n
Once the keystore is created the ewallet.p12 is generated in the keystore file location:<\/p>\n
oracle@localhost:\/u00\/app\/oracle\/local\/wallet\/ [db1] ls\nafiedt.buf\u00a0 ewallet.p12\n\n<\/pre>\n
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.<\/p>\n
If not you have to run the following command:<\/p>\n
oracle@localhost:\/u00\/app\/oracle\/local\/wallet\/ [db1] sqlplus c##sec_admin as syskm\n\nSQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 13 11:59:47 2017\n\nCopyright (c) 1982, 2016, Oracle.\u00a0 All rights reserved.\n\nEnter password:\u00a0\n\nConnected to:\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nSQL> administer key management set keystore open identified by manager container = ALL;\n\nkeystore altered.<\/pre>\n
If we ask the view:<\/p>\n
SQL> select * from v$encryption_wallet;\n\nWRL_TYPE.    WRL_PARAMETER                STATUS             WALLET_TYPE  WALLET_OR   FULLY_BAC   CON_ID\n\nFILE     \/u00\/app\/oracle\/local\/wallet\/  OPEN_NO_MASTER_KEY    PASSWORD \t\u00a0 \u00a0 SINGLE\u00a0 \u00a0 UNDEFINED<\/pre>\n
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:<\/p>\n
SQL> administer key management set keystore close identified by manager;\n\nkeystore altered.\n\nSQL> administer key management set keystore open identified by manager\u00a0 container =all;\n\nkeystore altered.\n\nSQL> administer key management set key identified by manager with backup using 'kex_backup' container =ALL;\n\nkeystore altered.<\/pre>\n
Now the v$encryption_wallet view is up to date:<\/p>\n
SQL> select * from v$encryption_wallet;\n\nWRL_TYPE   WRL_PARAMETER.               STATUS  WALLET_TYPE\t\u00a0 \u00a0 WALLET_OR FULLY_BAC   CON_ID\n\nFILE.   \/u00\/app\/oracle\/local\/wallet\/.   OPEN\t PASSWORD \t\u00a0 \u00a0 SINGLE\u00a0 \u00a0   NO          1<\/pre>\n
When you startup your CDB and your PDBs, you must do things in a good way:<\/p>\n
You shutdown and startup the database<\/p>\n
oracle@localhost:\/u00\/app\/oracle\/admin\/db1\/ [db1] sq\n\nSQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 14 13:53:09 2017\n\nCopyright (c) 1982, 2016, Oracle.\u00a0 All rights reserved.\n\n\nConnected to:\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nSQL> shutdown immediate\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nSQL> startup\nORACLE instance started.\n\nTotal System Global Area 3405774848 bytes\nFixed Size\t\t\u00a0 \u00a0 8798456 bytes\nVariable Size\t\t\u00a0 805310216 bytes\nDatabase Buffers\t 2583691264 bytes\nRedo Buffers\t\t\u00a0 \u00a0 7974912 bytes\nDatabase mounted.\nDatabase opened.<\/pre>\n
You open the wallet:<\/p>\n
SQL> administer key management set keystore open identified by manager container = all;\n\nkeystore altered.<\/pre>\n
The pluggable databases are not yet opened:<\/p>\n
SQL> connect sys\/manager@db1pdb1\nERROR:\nORA-01033: ORACLE initialization or shutdown in progress\nProcess ID: 0\nSession ID: 0 Serial number: 0\n\n\nWarning: You are no longer connected to ORACLE.<\/pre>\n
You start the pluggable databases:<\/p>\n
SQL> connect \/ as sysdba\nConnected.\nSQL> alter pluggable database all open;\n\nPluggable database altered.<\/pre>\n
The wallet is closed on the pluggable databases:<\/p>\n
SQL> connect sys\/manager@db1pdb1 as sysdba\nConnected.\nSQL> select status from v$encryption_wallet;\n\nSTATUS\n------------------------------\nCLOSED<\/pre>\n
You first have to close the wallet then to open it again:<\/p>\n
SQL> connect \/ as sysdba\nConnected.\nSQL> administer key management set keystore open identified by manager container = all;\nadminister key management set keystore open identified by manager container = all\n*\nERROR at line 1:\nORA-28354: Encryption wallet, auto login wallet, or HSM is already open\n\n\nSQL> administer key management set keystore close identified by manager;\n\nkeystore altered.\n\nSQL> administer key management set keystore open identified by manager container = all;\n\nkeystore altered.<\/pre>\n
The wallet is opened on every pluggable database:<\/p>\n
SQL> connect sys\/manager@db1pdb1 as sysdba\nConnected.\nSQL> select status from v$encryption_wallet;\n\nSTATUS\n------------------------------\nOPEN\n\nSQL> connect sys\/manager@db1pdb2 as sysdba\nConnected.\nSQL> select status from v$encryption_wallet;\n\nSTATUS\n------------------------------\nOPEN<\/pre>\n
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.<\/p>\n
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.<\/p>\n
Let’s create the classical empire table and insert some values:<\/p>\n
SQL> create table emp1 (name varchar2(30), salary number(7) encrypt);\n\nTable created.\n\n\nSQL> insert into emp1 values ('Larry', 1000000);\n\n1 row created.\n\nSQL> select * from emp1;\n\nNAME\t\t\t\t \u00a0 SALARY\n------------------------------ ----------\nLarry\t\t\t\t\u00a0 1000000<\/pre>\n
If now we close the keystore, the data are not viewable anymore:<\/p>\n
SQL> administer key management set keystore close identified by manager container = all;\n\nkeystore altered.\n\nSQL> connect psi\/psi@db1pdb1\nConnected.\nSQL> select * from emp1;\nselect * from emp1\n*\nERROR at line 1:\nORA-28365: wallet is not open\n\n\nSQL> select name from emp1;\n\nNAME\n------------------------------\nLarry\n\nSQL> select name, salary from emp1;\nselect name, salary from emp1\n\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 *\nERROR at line 1:\nORA-28365: wallet is not open<\/pre>\n
We can also use non default algorithms as 3DES168, AES128, AES256, for example:<\/p>\n
SQL> create table emp2 (\n\u00a0 2\u00a0 name varchar2(30),\n\u00a0 3\u00a0 salary number(7) encrypt using 'AES256');\n\nTable created.<\/pre>\n
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:<\/p>\n
SQL> create table emp3 (\n\u00a0 2\u00a0 name varchar2(30),\n\u00a0 3\u00a0 salary number (7) encrypt 'NOMAC');\n\nTable created.<\/pre>\n
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:<\/p>\n
SQL> create table emp4 (name varchar2(30));\n\nTable created.\n\nSQL> alter table emp4 add (salary number (7) encrypt);\n\nTable altered.\n\nSQL> create table emp5 (\n\u00a0 2\u00a0 name varchar2(30),\n\u00a0 3\u00a0 salary number(7));\n\nTable created.\n\nSQL> alter table emp5 modify (salary encrypt);\n\nTable altered.<\/pre>\n
Eventually, you can turn off the encryption for a table:<\/p>\n
SQL> alter table emp5 modify (salary decrypt);\n\nTable altered.<\/pre>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
You can encrypt the UNDO tablespace, but Oracle recommends not to decrypt the tablespace once it has been encrypted.<\/p>\n
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.<\/p>\n
SQL> create tablespace PSI_ENCRYPT\n\u00a0 2\u00a0 datafile '\/u01\/oradata\/db1\/db1pdb1\/psi_encrypt.dbf' size 10M\n\u00a0 3\u00a0 encryption using 'AES128' encrypt;\n\nTablespace created.<\/pre>\n
We have the possibility to realise\u00a0Online conversion for existing tablespaces:<\/p>\n
SQL> select file_name from dba_data_files where tablespace_name = 'PSI';\n\nFILE_NAME\n--------------------------------------------------------------------------------\n\/u01\/oradata\/db1\/db1pdb1\/psi.dbf<\/pre>\n
The compatible parameter is set to 12.2.0:<\/p>\n
SQL> show parameter compatible\n\nNAME\t\t\t\t \u00a0 \u00a0 TYPE\t VALUE\n------------------------------------ ----------- ------------------------------\ncompatible\t\t\t \u00a0 \u00a0 string\t 12.2.0<\/pre>\n
Now you have the possibility to encrypt the data file by using the following command, be sure that you have available free space:<\/p>\n
SQL> alter tablespace PSI ENCRYPTION online using 'AES256' ENCRYPT FILE_NAME_CONVERT = ('psi.dbf', 'psi_encrypt.dbf');\n\nTablespace altered.<\/pre>\n
SQL> select file_name from dba_data_files where tablespace_name = 'PSI';\n\nFILE_NAME\n--------------------------------------------------------------------------------\n\/u01\/oradata\/db1\/db1pdb1\/psi_encrypt.dbf<\/pre>\n
You can also decrypt online a tablespace:<\/p>\n
QL> alter tablespace PSI ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT = ('psi_encrypt.dbf', 'psi.dbf');\n\nTablespace altered.\n\nSQL> select file_name from dba_data_files where tablespace_name = 'PSI';\n\nFILE_NAME\n--------------------------------------------------------------------------------\n\/u01\/oradata\/db1\/db1pdb1\/psi.dbf<\/pre>\n
Therefore our PSI tablespace is not encrypted anymore, let\u2019s 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:<\/p>\n
SQL> select file_name from dba_data_files where tablespace_name = 'PSI';\n\nFILE_NAME\n--------------------------------------------------------------------------------\n\/u01\/oradata\/db1\/db1pdb1\/psi.dbf\n\nSQL> connect psi\/psi@db1pdb1\nConnected.\nSQL> create table emp (name varchar2(30), salary number(7));\n\nTable created.\n\nSQL> insert into emp values ('Larry', 1000000);\n\n1 row created.\n\nSQL> commit;\n\nCommit complete.\n\nSQL> select * from emp;\n\nNAME\t\t\t\t \u00a0 SALARY\n------------------------------ ----------\nLarry\t\t\t\t\u00a0 1000000\n\nSQL> select tablespace_name from user_tables where table_name = 'EMP';\n\nTABLESPACE_NAME\n------------------------------\nPSI\n\nSQL> alter tablespace PSI ENCRYPTION online using 'AES256' ENCRYPT FILE_NAME_CONVERT = ('psi.dbf', 'psi_encrypt.dbf');\n\nTablespace altered.\n\nSQL> select * from emp;\n\nNAME\t\t\t\t \u00a0 SALARY\n------------------------------ ----------\nLarry\t\t\t\t\u00a0 1000000\n\noracle@localhost:\/u01\/oradata\/db1\/ [db1] sqlplus \/ as sysdba\n\nSQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 13 16:11:18 2017\n\nCopyright (c) 1982, 2016, Oracle.\u00a0 All rights reserved.\n\n\nConnected to:\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nSQL> administer key management set keystore close identified by manager container =all;\n\nkeystore altered.\n\nSQL> connect psi\/psi@db1pdb1\nConnected.\nSQL> select * from emp;\nselect * from emp\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 *\nERROR at line 1:\nORA-28365: wallet is not open<\/pre>\n
It works fine, non encrypted tables in a tablespace are encrypted when the tablespace is encrypted.<\/p>\n
When the tablespace is encrypted, the strings command gives no result:<\/p>\n
oracle@localhost:\/u01\/oradata\/db1\/db1pdb1\/ [db1] strings psi_encrypt.dbf | grep -i Larry\noracle@localhost:\/u01\/oradata\/db1\/db1pdb1\/ [db1]<\/pre>\n
When we open the wallet and decrypt the tablespace, we can find information in the datafile:<\/p>\n
oracle@localhost:\/u01\/oradata\/db1\/db1pdb1\/ [db1] strings psi.dbf | grep Larry\nLarry<\/pre>\n
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:<\/p>\n
For example let\u2019s encrypt the SYSTEM tablespace:<\/p>\n
SQL> alter tablespace SYSTEM ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT = ('system01.dbf','system01_encrypt.dbf');\n\nTablespace altered.\n\nSQL> select file_name from dba_data_files where tablespace_name = 'SYSTEM';\n\nFILE_NAME\n--------------------------------------------------------------------------------\n\/u01\/oradata\/db1\/db1pdb1\/system01_encrypt.dbf<\/pre>\n
For the temporary tablespace, we have to drop the existing temporary tablespace , and create a new one encrypted as follows:<\/p>\n
SQL> create temporary tablespace TEMP_ENCRYPT<\/p>\n
2\u00a0 tempfile ‘\/u01\/oradata\/db1\/db1pdb1\/temp_encrypt.dbf’ size 100M<\/p>\n
3\u00a0 ENCRYPTION ENCRYPT;<\/p>\n
Tablespace created.<\/p>\n
SQL> alter database default temporary tablespace TEMP_ENCRYPT;<\/p>\n
Database altered.<\/p>\n
SQL> drop tablespace TEMP;<\/p>\n
Tablespace dropped.<\/p>\n
For the undo tablespace:<\/p>\n
SQL> alter tablespace UNDOTBS1 ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT = (‘undotbs01.dbf’,’undotbs01_encrypt.dbf’);<\/p>\n
Tablespace altered.<\/p>\n
SQL> connect sys\/manager@db1pdb1 as sysdba\nConnected.\nSQL> administer key management set keystore close identified by manager;\nadminister key management set keystore close identified by manager\n*\nERROR at line 1:\nORA-28439: cannot close wallet when SYSTEM, SYSAUX, UNDO, or TEMP tablespaces\nare encrypted<\/pre>\n
On the pluggable db1pdb2, as the tablespaces are not encrypted, the wallet can be closed:<\/p>\n
SQL> connect sys\/manager@db1pdb2 as sysdba\nConnected.\nSQL> administer key management set keystore close identified by manager;\n\nkeystore altered.<\/pre>\n
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<\/p>\n
In order to export a table, you have to add the ENCRYPTION parameter and the ENCRYPTION_PWD_PROMPT parameter \u00a0for security reasons:<\/p>\n
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\n\nExport: Release 12.2.0.1.0 - Production on Tue Mar 14 11:53:52 2017\n\nCopyright (c) 1982, 2017, Oracle and\/or its affiliates.\u00a0 All rights reserved.\nPassword:\u00a0\n\nConnected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nEncryption Password:\u00a0\nStarting \"SYSTEM\".\"SYS_EXPORT_TABLE_01\":\u00a0 system\/********@db1pdb1 tables=psi.emp directory=DATA_PUMP_DIR dumpfile=emp.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY ENCRYPTION_PWD_PROMPT=YES\u00a0\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE_DATA\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/MARKER\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE\n. . exported \"PSI\".\"EMP\" \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 5.523 KB \u00a0 \u00a0 \u00a0 1 rows\nMaster table \"SYSTEM\".\"SYS_EXPORT_TABLE_01\" successfully loaded\/unloaded\n******************************************************************************\nDump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:\n\u00a0 \/u00\/app\/oracle\/admin\/db1\/dpdump\/4A3D428970DA5D68E055000000000001\/emp.dmp\nJob \"SYSTEM\".\"SYS_EXPORT_TABLE_01\" successfully completed at Tue Mar 14 11:54:16 2017 elapsed 0 00:00:21<\/pre>\n
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:<\/p>\n
racle@localhost:\/home\/oracle\/ [DB1PDB1] impdp system@db1pdb2 tables=psi.emp directory=DATA_PUMP_DIR dumpfile=emp.dmp ENCRYPTION_PWD_PROMPT=YES\n\nImport: Release 12.2.0.1.0 - Production on Tue Mar 14 12:15:24 2017\n\nCopyright (c) 1982, 2017, Oracle and\/or its affiliates.\u00a0 All rights reserved.\nPassword:\u00a0\n\nConnected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nEncryption Password:\u00a0\nORA-39002: invalid operation\nORA-39180: unable to encrypt ENCRYPTION_PASSWORD\nORA-28365: wallet is not open\n\nyou open the wallet:<\/pre>\n
SQL> administer key management set keystore open identified by manager;\n\nkeystore altered.<\/pre>\n
The impdp command runs fine:<\/p>\n
oracle@localhost:\/home\/oracle\/ [DB1PDB1] impdp system@db1pdb2 tables=psi.emp directory=DATA_PUMP_DIR dumpfile=emp.dmp ENCRYPTION_PWD_PROMPT=YES\n\nImport: Release 12.2.0.1.0 - Production on Tue Mar 14 12:21:47 2017\n\nCopyright (c) 1982, 2017, Oracle and\/or its affiliates.\u00a0 All rights reserved.\nPassword:\u00a0\n\nConnected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nEncryption Password:\u00a0\nORA-39175: Encryption password is not needed.\nMaster table \"SYSTEM\".\"SYS_IMPORT_TABLE_01\" successfully loaded\/unloaded\nStarting \"SYSTEM\".\"SYS_IMPORT_TABLE_01\":\u00a0 system\/********@db1pdb2 tables=psi.emp directory=DATA_PUMP_DIR dumpfile=emp.dmp ENCRYPTION_PWD_PROMPT=YES\u00a0\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE_DATA\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/MARKER\nJob \"SYSTEM\".\"SYS_IMPORT_TABLE_01\" completed with 1 error(s) at Tue Mar 14 12:21:55 2017 elapsed 0 00:00:05<\/pre>\n
But the generated dumpfile is not encrypted and you can find sensitive data in this file:<\/p>\n
oracle@localhost:\/u00\/app\/oracle\/admin\/db1\/dpdump\/ [db1] strings emp.dmp | grep -i Larry\nLarry<\/pre>\n
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.<\/p>\n
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\n\nExport: Release 12.2.0.1.0 - Production on Tue Mar 14 12:44:18 2017\n\nCopyright (c) 1982, 2017, Oracle and\/or its affiliates.\u00a0 All rights reserved.\nPassword:\u00a0\n\nConnected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nEncryption Password:\u00a0\nStarting \"SYSTEM\".\"SYS_EXPORT_TABLE_01\":\u00a0 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\u00a0\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE_DATA\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/MARKER\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE\n. . exported \"PSI\".\"EMP\" \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 5.531 KB \u00a0 \u00a0 \u00a0 1 rows\nMaster table \"SYSTEM\".\"SYS_EXPORT_TABLE_01\" successfully loaded\/unloaded\n******************************************************************************\nDump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:\n\u00a0 \/u00\/app\/oracle\/admin\/db1\/dpdump\/4A3D428970DA5D68E055000000000001\/emp_encrypt.dmp\nJob \"SYSTEM\".\"SYS_EXPORT_TABLE_01\" successfully<\/pre>\n
And now we cannot retrieve sensitive data from the dump file:<\/p>\n
oracle@localhost:\/u00\/app\/oracle\/admin\/db1\/dpdump\/ [db1] strings emp_encrypt.dmp | grep -i Larry\noracle@localhost:\/u00\/app\/oracle\/admin\/db1\/dpdump\/ [db1]<\/pre>\n
 <\/p>\n
Conclusion:<\/p>\n
Concerning the Transparent Data Encryption in the last 12.2.0.1 Oracle version,\u00a0I 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:<\/p>\n
“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.”<\/p>\n
<\/pre>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[209,448],"type_dbi":[],"class_list":["post-9865","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-oracle-12c","tag-tde"],"acf":[],"yoast_head":"\nOracle 12.2 and Transparent Data Encryption - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12.2 and Transparent Data Encryption\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-14T14:20:45+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12.2 and Transparent Data Encryption\",\"datePublished\":\"2017-03-14T14:20:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/\"},\"wordCount\":1204,\"commentCount\":0,\"keywords\":[\"Oracle 12c\",\"TDE\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/\",\"name\":\"Oracle 12.2 and Transparent Data Encryption - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-03-14T14:20:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12.2 and Transparent Data Encryption\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 12.2 and Transparent Data Encryption - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12.2 and Transparent Data Encryption","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/","og_site_name":"dbi Blog","article_published_time":"2017-03-14T14:20:45+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12.2 and Transparent Data Encryption","datePublished":"2017-03-14T14:20:45+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/"},"wordCount":1204,"commentCount":0,"keywords":["Oracle 12c","TDE"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/","name":"Oracle 12.2 and Transparent Data Encryption - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-03-14T14:20:45+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12-2-and-transparent-data-encryption\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12.2 and Transparent Data Encryption"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9865","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9865"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9865\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9865"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9865"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9865"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9865"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}