Toad World

Encrypt database link passwords with Data Pump in Oracle 18c

Avatar photo
Post author:Written By

 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.

How does it work?

  • The feature Credential Encryption in the Dictionary has to be enabled.
  • The following message will be displayed as a warning in an export and import operation with Data Pump (expdp & impdp):

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:

  • Oracle Database 12.2.0.1
    • Schema DGOMEZ
      • Table1 (unencrypted) – 1 Row with value ‘Data – Row 1’
      • Table2 (encrypted using TDE) – 1 Row with value ‘Data – Row 2’
      • Database link “DBLINK” created.
    • Oracle Database 18.3.0.0
      • Schema DGOMEZ
        • Table1 (unencrypted) – 1 Row with value ‘Data – Row 1’
        • Table2 (encrypted using TDE) – 1 Row with value ‘Data – Row 2’
        • Database link “DBLINK” created.

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:

In the Oracle Database 12.2.0.1 environment

[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:

  • The data in TABLE2 was exported in clear text (unencrypted), even when TDE is used to store the data in the database.
  • The database link was exported normally, which means the obfuscated real password was stored in the dump file.

In the Oracle Database 18.3.0.0 environment

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 data in TABLE2 was exported in clear text (unencrypted), even when TDE is used to store the data in the database.
  • The database link was exported but its password was replaced by invalid data; thus, the database link password must be reset after import.

 

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:

  • The data in TABLE2 was imported in clear text (unencrypted).
  • The database link was imported but its password must be reset since the password has an invalid value.

 

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 password specified is visible in the “expdp” command, in the terminal.
  • All the data was encrypted using the password that was provided, including Tables row data and also Database Link passwords.
  • The database link was exported but its password was replaced by invalid data; thus, the database link password must be reset after import.

 

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:

  • All the data was encrypted using the password that was provided, including Tables row data and also Database Link passwords.
  • The password is not visible in the terminal since it was asked in the prompt; the password is typed but not displayed.
  • The database link was exported but its password was replaced by invalid data; thus, the database link password must be rest after import.

 

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:

  • The password is not visible in the terminal since it was asked in the prompt, the password is typed but not displayed.
  • The database link was exported but its password was replaced by invalid data, thus the database link password must be rest after import.

 

Conclusion

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.

 

 

 

Tags: ,

About the Author

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.

Start the discussion at forums.toadworld.com