Nov 12, 2018 7:28:39 AM by Deiby Gomez
Before Oracle Database 18c, the only way to encrypt data (all types of data) was using the parameters ENCRYPTION_PASSWORD or ENCRYPTION_PWD_PROMPT. The data is exported in clear text in an export operation (expdp) even if Transparent Data Encryption (TDE) was used to store the data in the database, but the parameters ENCRYPTION_PASSWORD and ENCRYPTION_PWD_PROMPT were not used.
Several new features were introduced in Oracle Database 18c. One of them is encrypting sensitive credential data in the data dictionary, such as passwords for database links, in the table SYS.LINK$, and also sensitive credential data for Oracle Scheduler events in the table SYS.SCHEDULER$_CREDENTIAL.
When this feature is enabled, the exports (expdp) and imports (expdp) with Data Pump work differently.
In Oracle Database 12c, passwords for database links are stored obfuscated in the database, and when an export with Data Pump (expdp) is performed, the obfuscated value is stored in the dump file. This allows any intruder from decrypting the obfuscated database link password. In Oracle Database 18c, when the feature “credentials encryption in the dictionary” is enabled, passwords for database links are not exported, they are replaced with an invalid value; thus, the password has to be reset after import.
ORA-39395: Warning: object <owner>.<object_name> requires password reset after import
The real passwords for database links will not be stored in the dump file.
For this example, the following environments are used:
The first example in this article will display how the data is exported when the parameters ENCRYPTION_PASSWORD | ENCRYPTION_PWD_PROMPT are not used and the “credentials encryption in the dictionary” feature doesn’t exist:
[oracle@db12c ~]$ expdp dgomez/dgomez DIRECTORY=exports DUMPFILE=dgomez.dmp LOGFILE=dgomez.dmp.log CONTENT=ALL INCLUDE=DB_LINK,TABLE
Export: Release 12.1.0.2.0 - Production on Tue Nov 6 15:03:07 201
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DGOMEZ"."SYS_EXPORT_SCHEMA_01": dgomez/******** DIRECTORY=exports DUMPFILE=dgomez.dmp LOGFILE=dgomez.dmp.log CONTENT=ALL INCLUDE=DB_LINK,TABLE
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "DGOMEZ"."TABLE1" 5.070 KB 1 rows
. . exported "DGOMEZ"."TABLE2" 5.070 KB 1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "DGOMEZ"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DGOMEZ.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dgomez.dmp
Job "DGOMEZ"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Nov 6 15:03:39 2018 elapsed 0 00:00:29
Let’s see if the data is encrypted:
[oracle@db18 ~]$ strings /home/oracle/dgomez.dmp |grep 'Data - Row'
Data - Row 1
Data - Row 2
[oracle@db18 ~]$
The following notes can be highlighted:
Now, the export will be performed when the “credentials encryption in the dictionary” feature is enabled, but the parameters ENCRYPTION_PASSWORD | ENCRYPTION_PWD_PROMPT are not used:
SQL> select enforcement from dictionary_credentials_encrypt;
ENFORCEM
--------
ENABLED
[oracle@db18 ~]$ expdp dgomez/dgomez DIRECTORY=exports DUMPFILE=dgomez.dmp LOGFILE=dgomez.dmp.log CONTENT=ALL INCLUDE=DB_LINK,TABLE
Export: Release 18.0.0.0.0 - Production on Tue Nov 6 14:05:43 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "DGOMEZ"."SYS_EXPORT_SCHEMA_01": dgomez/******** DIRECTORY=exports DUMPFILE=dgomez.dmp LOGFILE=dgomez.dmp.log CONTENT=ALL INCLUDE=DB_LINK,TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-39395: Warning: object DGOMEZ.DBLINK1 requires password reset after import
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DGOMEZ"."TABLE1" 5.070 KB 1 rows
. . exported "DGOMEZ"."TABLE2" 5.070 KB 1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "DGOMEZ"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DGOMEZ.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dgomez.dmp
Job "DGOMEZ"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Tue Nov 6 14:06:09 2018 elapsed 0 00:00:25
Let’s see if the data is encrypted:
[oracle@db18 ~]$ strings /home/oracle/dgomez.dmp |grep 'Data - Row'
Data - Row 1
Data - Row 2
[oracle@db18 ~]$
The following notes can be highlighted:
The import operation works similarly:
[oracle@db18 ~]$ impdp dgomez/dgomez DIRECTORY=exports DUMPFILE=dgomez.dmp
Import: Release 18.0.0.0.0 - Production on Tue Nov 6 14:09:50 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "DGOMEZ"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DGOMEZ"."SYS_IMPORT_FULL_01": dgomez/******** DIRECTORY=exports DUMPFILE=dgomez.dmp
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-39395: Warning: object DGOMEZ.DBLINK1 requires password reset after import
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DGOMEZ"."TABLE1" 5.070 KB 1 rows
. . imported "DGOMEZ"."TABLE2" 5.070 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DGOMEZ"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Nov 6 14:09:56 2018 elapsed 0 00:00:05
[oracle@db18 ~]$
The following notes can be highlighted:
If the rest of the data must be encrypted, the parameters ENCRYPTION_PASSWORD or ENCRYPTION_PWD_PROMPT have to be used.
The parameter ENCRYPTION_PASSWORD encrypts the data that is being exported, but it shows the password in the terminal.
[oracle@db18 ~]$ expdp dgomez/dgomez DIRECTORY=exports DUMPFILE=dgomez.dmp LOGFILE=dgomez.dmp.log CONTENT=ALL INCLUDE=DB_LINK,TABLE ENCRYPTION_PASSWORD=MyPassword
Export: Release 18.0.0.0.0 - Production on Tue Nov 6 14:13:07 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "DGOMEZ"."SYS_EXPORT_SCHEMA_01": dgomez/******** DIRECTORY=exports DUMPFILE=dgomez.dmp LOGFILE=dgomez.dmp.log CONTENT=ALL INCLUDE=DB_LINK,TABLE ENCRYPTION_PASSWORD=********
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-39395: Warning: object DGOMEZ.DBLINK1 requires password reset after import
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DGOMEZ"."TABLE1" 5.078 KB 1 rows
. . exported "DGOMEZ"."TABLE2" 5.078 KB 1 rows
Master table "DGOMEZ"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DGOMEZ.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dgomez.dmp
Job "DGOMEZ"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Tue Nov 6 14:13:33 2018 elapsed 0 00:00:26
Let’s see if the data is encrypted:
[oracle@db18 ~]$ strings /home/oracle/dgomez.dmp |grep 'Data - Row'
[oracle@db18 ~]$
No data was stored in clear text in the dump file.
The following notes can be highlighted:
The same password specified in the export operation must be specified in the import operation, since it is the key to decrypt the data.
[oracle@db18 ~]$ impdp dgomez/dgomez DIRECTORY=exports DUMPFILE=dgomez.dmp ENCRYPTION_PASSWORD=MyPassword
Import: Release 18.0.0.0.0 - Production on Tue Nov 6 14:33:37 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "DGOMEZ"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DGOMEZ"."SYS_IMPORT_FULL_01": dgomez/******** DIRECTORY=exports DUMPFILE=dgomez.dmp ENCRYPTION_PASSWORD=********
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-39395: Warning: object DGOMEZ.DBLINK1 requires password reset after import
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DGOMEZ"."TABLE1" 5.078 KB 1 rows
. . imported "DGOMEZ"."TABLE2" 5.078 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DGOMEZ"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Nov 6 14:33:41 2018 elapsed 0 00:00:04
[oracle@db18 ~]$
The parameter ENCRYPTION_PWD_PROMPT encrypts the data that is being exported and doesn’t show the password in the terminal, since it is asked in the prompt.
[oracle@db18 ~]$ expdp dgomez/dgomez DIRECTORY=exports DUMPFILE=dgomez.dmp LOGFILE=dgomez.dmp.log CONTENT=ALL INCLUDE=DB_LINK,TABLE ENCRYPTION_PWD_PROMPT=YES
Export: Release 18.0.0.0.0 - Production on Tue Nov 6 14:34:54 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Encryption Password:
Starting "DGOMEZ"."SYS_EXPORT_SCHEMA_01": dgomez/******** DIRECTORY=exports DUMPFILE=dgomez.dmp LOGFILE=dgomez.dmp.log CONTENT=ALL INCLUDE=DB_LINK,TABLE ENCRYPTION_PWD_PROMPT=YES
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-39395: Warning: object DGOMEZ.DBLINK1 requires password reset after import
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "DGOMEZ"."TABLE1" 5.078 KB 1 rows
. . exported "DGOMEZ"."TABLE2" 5.078 KB 1 rows
Master table "DGOMEZ"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DGOMEZ.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dgomez.dmp
Job "DGOMEZ"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Tue Nov 6 14:35:28 2018 elapsed 0 00:00:33
Let’s see if the data is encrypted:
[oracle@db18 ~]$ strings /home/oracle/dgomez.dmp |grep 'Data - Row'
[oracle@db18 ~]$
No data was stored in clear text in the dump file.
The following notes can be highlighted:
The same password specified in the export operation must be specified in the import operation, since it is the key to decrypt the data.
[oracle@db18 ~]$ impdp dgomez/dgomez DIRECTORY=exports DUMPFILE=dgomez.dmp ENCRYPTION_PWD_PROMPT=YES
Import: Release 18.0.0.0.0 - Production on Tue Nov 6 14:37:08 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Encryption Password:
Master table "DGOMEZ"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DGOMEZ"."SYS_IMPORT_FULL_01": dgomez/******** DIRECTORY=exports DUMPFILE=dgomez.dmp ENCRYPTION_PWD_PROMPT=YES
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-39395: Warning: object DGOMEZ.DBLINK1 requires password reset after import
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DGOMEZ"."TABLE1" 5.078 KB 1 rows
. . imported "DGOMEZ"."TABLE2" 5.078 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DGOMEZ"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Nov 6 14:37:21 2018 elapsed 0 00:00:12
[oracle@db18 ~]$
The following notes can be highlighted:
Many companies use hybrid environments; some data is stored on-premises and some other in the Oracle Cloud. Thus, frequent data movement is performed, as well as communication between several databases through database links. It’s very important to secure all the data that is being accessed from external systems and also the data that is being transported to new environments. Oracle Database 18c provides several new security features that can be used to make your data more secure.
Written by Deiby Gomez
Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.
Certifications:
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.