How To Clone An Oracle User


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 : –

@/path_to_user_clone.sql

or

@\path_to_user_clone.sql

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s