Daily Archives: 19.04.2012


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