- Oracle Database 12c Security Cookbook
- Zoran Pavlovi? Maja Veselica
- 444字
- 2021-07-02 16:43:12
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 userprivilege, in the second database (for example,zoran)
How to do it...
- Connect to the first database as a user who has a DBA role:
$ sqlplus / - Find a Data Definition Language (DDL) statement (
ddl) that is used for user creation (for example, userjessica):SQL> select dbms_metadata.get_ddl('USER', 'JESSICA') from dual; - Connect to the second database as a user who has
create userprivilege:$ sqlplus zoran@orcl2 - 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.
This way requires select on the sys.user$ table:
- Connect to the first database as a user who has the
selectprivilege on thesys.user$table (for example, user who has thesysdbaprivilege):$ sqlplus / as sysdba - Find the hash value of a user's password (for example, user
jessica):SQL> select spare4 from user$ where name='JESSICA'; - Connect to the second database as a user who has
create userprivilege:$ sqlplus zoran@orcl2 - 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