Copy User Fron OneDB To Another In Oracle

I like to post a different way to create the same user in other database, including all privileges and permissions. For this task I’ll use the dbms_metadata package, remember this is a powerfull package if you work as DBA or modeling databases. In the following block textI show the four options from dbms_metadata;

 

 
SELECT DBMS_METADATA.GET_DDL('USER','ANTONION') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL  ('ROLE_GRANT','ANTONION') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL  ('OBJECT_GRANT','ANTONION') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL  ('SYSTEM_GRANT','ANTONION') FROM DUAL;

Okay, now execute it from sqlplus y get the next output;

 
CHANNEL*PLUS> SELECT DBMS_METADATA.GET_DDL('USER','ANTONION') FROM DUAL;

DBMS_METADATA.GET_DDL('USER','ANTONION')
________________________________________________________________________________

   CREATE USER "ANTONION" IDENTIFIED BY VALUES 'S:0D24E9133B34D60A835B434F7FC9B5
BBA34CAE777BE33F96923AA2204DCC;31D32ABE74C2D974'
      DEFAULT TABLESPACE "WORK"
      TEMPORARY TABLESPACE "TEMP"

Transcurrido: 00:00:00.12
CHANNEL*PLUS> SELECT DBMS_METADATA.GET_GRANTED_DDL  ('ROLE_GRANT','ANTONION') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','ANTONION')
________________________________________________________________________________

   GRANT "DBA" TO "ANTONION"

   GRANT "ROL_FOR_DBA" TO "ANTONION"

   GRANT "ROL_SEG_ADMINDB" TO "ANTONION"

Transcurrido: 00:00:00.13
CHANNEL*PLUS> SELECT DBMS_METADATA.GET_GRANTED_DDL  ('OBJECT_GRANT','ANTONION') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','ANTONION')
________________________________________________________________________________

  GRANT SELECT ON "SYS"."V_$SESSION" TO "ANTONION"

  GRANT SELECT ON "SYS"."V_$SGASTAT" TO "ANTONION"

  GRANT EXECUTE ON "SYS"."DBMS_SPACE" TO "ANTONION"

  GRANT EXECUTE ON "SYS"."DBMS_SCHEDULER" TO "ANTONION"

  GRANT EXECUTE ON "SYS"."DBMS_ISCHED" TO "ANTONION"

Transcurrido: 00:00:00.47
CHANNEL*PLUS> SELECT DBMS_METADATA.GET_GRANTED_DDL  ('SYSTEM_GRANT','ANTONION') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','ANTONION')
________________________________________________________________________________

  GRANT CREATE ANY JOB TO "ANTONION"

  GRANT SELECT ANY DICTIONARY TO "ANTONION"

  GRANT SELECT ANY TABLE TO "ANTONION"

  GRANT UNLIMITED TABLESPACE TO "ANTONION"

  GRANT RESTRICTED SESSION TO "ANTONION"

  GRANT CREATE SESSION TO "ANTONION"

Transcurrido: 00:00:00.24
CHANNEL*PLUS> 

Of course, this example is only for one user but you can “improve” it for user a loop for many users.

HTH – Antonio NAVARRO

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