At times we need to add a new Oracle database user with just the same roles/rights on an existing user. There can be any number of reasons to add this user 😉 Most common use is to give the same role/rights/privileges to other user(s) from production database to a test database. Anyway, below are the steps that we take to clone an Oracle user 🙂
I don’t just write down the steps. My aim is to explain the things in an easy to understand language so that it doesn’t become a “just-another-copy-paste” blog.
Our first task will be to identify which user we want to clone. Its done by issuing below query. Make sure to issue
set long 99999 before running any query. This will enable full output on your screen.
SELECT DBMS_METADATA.GET_GRANTED_DDL('USER', 'SHASHANK') FROM DUAL;
It will show you how my existing ID was created.
CREATE USER "SHASHANK" IDENTIFIED BY password;
Now we will run a few queries to see what all roles have been assigned to my ID SHASHANK.
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SHASHANK') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SHASHANK') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SHASHANK') FROM DUAL;
Now that we have all the roles & privileges for the user that we have to clone, lets start with creating a clone user SHASHANK_ADM. We will create a script called
user_clone.sql & execute it. Just make sure to follow the correct / or \ depending upon your OS. Windows uses \ & Nix* uses /.
Below are the contents of my script. You can see that this is nothing but the output of above queries 😉 I have just replaced the user name with the new user.
CREATE USER "SHASHANK_ADM" IDENTIFIED BY password GRANT CREATE TRIGGER TO "SHASHANK_ADM"; GRANT CREATE PROCEDURE TO "SHASHANK_ADM"; GRANT CREATE DATABASE LINK TO "SHASHANK_ADM"; GRANT CREATE SEQUENCE TO "SHASHANK_ADM"; GRANT CREATE VIEW TO "SHASHANK_ADM"; GRANT CREATE TABLE TO "SHASHANK_ADM"; GRANT UNLIMITED TABLESPACE TO "SHASHANK_ADM"; GRANT CREATE SESSION TO "SHASHANK_ADM"; GRANT "CONNECT" TO "SHASHANK_ADM"; GRANT "RESOURCE" TO "SHASHANK_ADM"; GRANT "SELECT_CATALOG_ROLE" TO "SHASHANK_ADM"; ALTER USER "SHASHANK_ADM" DEFAULT ROLE ALL;
Then execute this script : –
That’s it 🙂 We now have a clone user SHASHANK_ADM with just the same rights as user SHASHANK. Hope, this was useful & easy to understand.