<<Back to Oracle DB Security Main Page
Error ORA-39395 Introduced To Support Encrypted Passwords For Database Links
Assuming that your have keystore and configured Step by Step instruction for the same is provided in Step by Step How to Configure Software Keystore/ Oracle Wallet post.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
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. Lets See with an example.
Step1> Check the Keystore Status
SQL> select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;
STATUS WALLET_DIR WALLET_TYPE
--------------------- -------------------------------- --------------------
OPEN /u01/dbatst1/stage/wallet/tde/ AUTOLOGIN
OPEN AUTOLOGIN
OPEN AUTOLOGIN
--------------------- -------------------------------- --------------------
OPEN /u01/dbatst1/stage/wallet/tde/ AUTOLOGIN
OPEN AUTOLOGIN
OPEN AUTOLOGIN
Step2> Check Dictionary Encryption Status
SQL> select enforcement from dictionary_credentials_encrypt;
ENFORCEM
--------
DISABLED<=== The feature is not enabled
--------
DISABLED<=== The feature is not enabled
Step3<Create a test User and grant necessary Privileges
SQL> alter session set container=PDB01;
Session altered.
SQL> create user test_user identified by test_user;
User created.
SQL> grant dba,connect,resource,alter database link to test_user;
Grant succeeded.
Step4> Create Public & Private Database Links
SQL> CREATE PUBLIC DATABASE LINK my_public_dblink CONNECT TO admin identified by password using 'PDB01';
SQL> CREATE PUBLIC DATABASE LINK my_public_dblink CONNECT TO admin identified by password using 'PDB01';
Database link created.
SQL> select sysdate from dual@my_public_dblink;
SYSDATE
---------
19-FEB-19
---------
19-FEB-19
$ sqlplus test_user/test_user@PDB01
SQL> CREATE DATABASE LINK my_private_dblink CONNECT TO test_user identified by test_user using 'PDB01';
Database link created.
SQL> select sysdate from dual@my_private_dblink;
SYSDATE
---------
19-FEB-19
---------
19-FEB-19
Step5> Create directory
SQL> create directory exp as '/u01/dbatst1/stage';
Directory created.
SQL> grant read, write on directory exp to admin;
SQL> grant read, write on directory exp to admin;
Grant succeeded.
Step6> Perform Export While Dictionary Encryption is disabled
$expdp admin/password@PDB01 directory=exp full=yes dumpfile=dblink.dmp include=db_link
Export: Release 18.0.0.0.0 - Production on Tue Feb 19 11:33:25 2019
Version 18.3.0.0.0
Export: Release 18.0.0.0.0 - Production on Tue Feb 19 11:33:25 2019
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 "ADMIN"."SYS_EXPORT_FULL_01": admin/********@PDB01 directory=exp full=yes dumpfile=dblink.dmp include=db_link
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "ADMIN"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_FULL_01 is:
/u01/dbatst1/stage/dblink.dmp
Job "ADMIN"."SYS_EXPORT_FULL_01" successfully completed at Tue Feb 19 11:34:10 2019 elapsed 0 00:00:38
Starting "ADMIN"."SYS_EXPORT_FULL_01": admin/********@PDB01 directory=exp full=yes dumpfile=dblink.dmp include=db_link
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "ADMIN"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_FULL_01 is:
/u01/dbatst1/stage/dblink.dmp
Job "ADMIN"."SYS_EXPORT_FULL_01" successfully completed at Tue Feb 19 11:34:10 2019 elapsed 0 00:00:38
Step6> Perform import While Dictionary Encryption is disabled
$ impdp admin/password@PDB01 directory=exp full=yes dumpfile=dblink.dmp
Import: Release 18.0.0.0.0 - Production on Tue Feb 19 11:38:33 2019
Version 18.3.0.0.0
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 "ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_FULL_01": admin/********@PDB01 directory=exp full=yes dumpfile=dblink.dmp
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "ADMIN"."SYS_IMPORT_FULL_01" successfully completed at Tue Feb 19 11:38:40 2019 elapsed 0 00:00:05
Master table "ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_FULL_01": admin/********@PDB01 directory=exp full=yes dumpfile=dblink.dmp
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "ADMIN"."SYS_IMPORT_FULL_01" successfully completed at Tue Feb 19 11:38:40 2019 elapsed 0 00:00:05
SQL> alter session set container=PDB01;
Session altered.
SQL> select sysdate from dual@my_public_dblink;
SYSDATE
---------
19-FEB-19
SQL> select sysdate from dual@my_private_dblink;
SYSDATE
---------
19-FEB-19
Session altered.
SQL> select sysdate from dual@my_public_dblink;
SYSDATE
---------
19-FEB-19
SQL> select sysdate from dual@my_private_dblink;
SYSDATE
---------
19-FEB-19
NOTE: As you can see there was no warning and DB links are working fine as long as you got the right TNS Entry. You could have seen the warning ORA-39173: Encrypted data has been stored unencrypted in dump file set if you would have encrypted data in the database
This is how the expdp and impdp reacted until 12c.
This is how the expdp and impdp reacted until 12c.
Now Let us Enable the DICTIONARY ENCRYPT Feature and see how expdp/impdp behaves
Step7> Enable the Dictionary Encryption
$ sqlplus admin/password@PDB01 as syskm
SQL*Plus: Release 18.0.0.0.0 - Production on Tue Feb 19 13:06:09 2019
Version 18.3.0.0.0
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL>
SQL> ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS;
SQL> ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS;
Database dictionary altered.
SQL> select enforcement from dictionary_credentials_encrypt;
ENFORCEM
--------
ENABLED<== Dictionary Encryption is now enabled
--------
ENABLED<== Dictionary Encryption is now enabled
Step8> Perfrom the export
$ expdp admin/password@PDB01 directory=exp full=yes dumpfile=dblink.dmp include=db_link
Export: Release 18.0.0.0.0 - Production on Tue Feb 19 13:08:13 2019
Version 18.3.0.0.0
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 "ADMIN"."SYS_EXPORT_FULL_01": admin/********@PDB01 directory=exp full=yes dumpfile=dblink.dmp include=db_link
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
ORA-39395: Warning: object TEST_USER.MY_PRIVATE_DBLINK requires password reset after import
Starting "ADMIN"."SYS_EXPORT_FULL_01": admin/********@PDB01 directory=exp full=yes dumpfile=dblink.dmp include=db_link
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
ORA-39395: Warning: object TEST_USER.MY_PRIVATE_DBLINK requires password reset after import
ORA-39395: Warning: object PUBLIC.MY_PUBLIC_DBLINK requires password reset after import
Master table "ADMIN"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_FULL_01 is:
/u01/dbatst1/stage/dblink.dmp
Job "ADMIN"."SYS_EXPORT_FULL_01" completed with 2 error(s) at Tue Feb 19 13:08:29 2019 elapsed 0 00:00:15
NOTE: As you can see the Password is no more usable and you have to reset the password after import to make the DB_LINKS working again ******************************************************************************
Dump file set for ADMIN.SYS_EXPORT_FULL_01 is:
/u01/dbatst1/stage/dblink.dmp
Job "ADMIN"."SYS_EXPORT_FULL_01" completed with 2 error(s) at Tue Feb 19 13:08:29 2019 elapsed 0 00:00:15
$ impdp admin/password@PDB02 directory=exp full=yes dumpfile=dblink.dmp
Import: Release 18.0.0.0.0 - Production on Tue Feb 19 13:26:09 2019
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 "ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_FULL_01": admin/********@PDB02 directory=exp full=yes dumpfile=dblink.dmp
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
ORA-39395: Warning: object TEST_USER.MY_PRIVATE_DBLINK requires password reset after import
ORA-39395: Warning: object PUBLIC.MY_PUBLIC_DBLINK requires password reset after import
Job "ADMIN"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Tue Feb 19 13:26:15 2019 elapsed 0 00:00:06
Import: Release 18.0.0.0.0 - Production on Tue Feb 19 13:26:09 2019
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 "ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ADMIN"."SYS_IMPORT_FULL_01": admin/********@PDB02 directory=exp full=yes dumpfile=dblink.dmp
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
ORA-39395: Warning: object TEST_USER.MY_PRIVATE_DBLINK requires password reset after import
ORA-39395: Warning: object PUBLIC.MY_PUBLIC_DBLINK requires password reset after import
Job "ADMIN"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Tue Feb 19 13:26:15 2019 elapsed 0 00:00:06
Comments
Post a Comment