Datapump, remap_schema and passwords 2
Something that is quite logical after you think about, but surprises you at first.
When you do expdp/impdp with the remap_schema parameter, the password of the created user on the target database will not be a valid one.
This because on Oracle 10g the password hash is calculated using the “username” as a seed. When you import a user with impdp, the password hash is not recalculated. So when the user changes (by the remap_schema parameter), the new schema password will not work.
Clean environment:
SQL> select username, password from dba_users where username='T1'; no rows selected
Create user T1 with simple password…
SQL> grant create session to t1 identified by t1; Grant succeeded.
Check password hash_values
SQL> select username, password from dba_users where username='T1'; USERNAME PASSWORD ------------------------------ ------------------------------ T1 2A6EC3E5F234DF52
We can connect:
SQL> connect t1/t1 Connected.
Run the export…
$expdp schemas=t1 Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 19 April, 2012 11:16:30 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA schemas=t1 ... Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:17:00
And import with remap_schema:
$impdp remap_schema=t1:t2 Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 19 April, 2012 11:18:30 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA remap_schema=t1:t2 Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 11:18:35
We see now that the password hash of the two users is the same:
SQL> select username, password from dba_users where username in ('T1','T2');
USERNAME PASSWORD
------------------------------ ------------------------------
T1 2A6EC3E5F234DF52
T2 2A6EC3E5F234DF52
But connecting with the original password does not work:
SQL> connect t2/t1 ERROR: ORA-01017: invalid username/password; logon denied