Creating a user with the same credentials on another database

This recipe explains a way to create a user with the same credentials on another database.

Getting ready

To complete this recipe, you'll need:

• An existing user who has dba role in the first database (you can use an OS-authenticated user)
• An existing user in the first database (for example, jessica)
• An existing (for example, password-authenticated) user, who has create user privilege, in the second database (for example, zoran)

How to do it...

1. Connect to the first database as a user who has a DBA role:

          $ sqlplus /
   
   

2. Find a Data Definition Language (DDL) statement (ddl) that is used for user creation (for example, user jessica):

          SQL> select dbms_metadata.get_ddl('USER', 'JESSICA') from
   dual;
   
   

3. Connect to the second database as a user who has create user privilege:

          $ sqlplus zoran@orcl2
   
   

4. Create a user using the value you found in step 2:

   SQL> create user "JESSICA" identified by values
   'S:D82E6EF961F2EA7A878BCDDBC7E5C542BC148C4759D19A7
   20A96BBF65658;H:F297A50FD538EF4AB119EB0278C9E72D;
   C50B1E9C9AA52EC2';
   

How it works...

In step 1, you used OS authentication to connect to the database.

In step 2, you found a DDL statement that has been used for user creation. This DDL statement may contain default and temporary tablespace assignments (note that even if you haven't explicitly assigned these tablespaces during user creation, the system will assign them implicitly using default values for the database). For instance, output in step 2 may look like this:

SQL> select dbms_metadata.get_ddl('USER', 'JESSICA') from dual;
    
DBMS_METADATA.GET_DDL('USER','JESSICA')
------------------------------------------------------------------
CREATE USER "JESSICA" IDENTIFIED BY VALUES 'S:D82E6EF961F2EA7A878BCDDBC7E5C542BC148C4759D19A720A96BBF65658;H:F297A50FD538EF4AB1 19EB0278C9E72D;C50B1E9C9AA52EC2'
DEFAULT TABLESPACE "USERS"            TEMPORARY TABLESPACE "TEMP"

However, we used only the first part of this DDL in step 4 to create a user on the second database (and let the database decide about default tablespaces).

There's more...

There is another way to accomplish the task.

Note

You can only reveal the hash value of user's password (you cannot reveal the actual password).

This way requires select on the sys.user$ table:

1. Connect to the first database as a user who has the select privilege on the sys.user$ table (for example, user who has the sysdba privilege):

   $ sqlplus / as sysdba
   
   

2. Find the hash value of a user's password (for example, user jessica):

   SQL> select spare4
   from user$
   where name='JESSICA';
   
   

3. Connect to the second database as a user who has create user privilege:

   $ sqlplus zoran@orcl2
   
   

4. Create a user with the same username (for example, jessica) using the hash value of the password that you have found in step 2:

   SQL> create user jessica identified by values 
   'S:2724193130FC67E7E23E3E44E33AF143F7A6C36489792B
   5856133DCB331D;H:184895E50EA2FBCC2311ED76A3E5CF35;
   T:BECCD5FC6F6E62BC34DF1C826AEE899EC6A6025FA0D5071659DA
   7DD1ABB37763483B5C821E5A34C1184A56BE4B1C92CED79639D11101D
   61B86ACBE60A30F19CC277D5753F7D3756DC1B7705C0ACE81F3';
   

See also

• Creating and using OS-authenticated users