CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Petro Semeniuk
Petro Semeniuk

Reputation: 7038

How to clone user in Oracle

Have a need for a sql query which will do cloning of Oracle user. As source I have user which has all necessary privileges. I wonder if it is possible to create the same one, but with different username in the same database.

Upvotes: 9

Views: 58256

Answers (1)

Gary Myers
Gary Myers

Reputation: 35401

Briefly (from here)

select dbms_metadata.get_ddl('USER', '...') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','...') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','...') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','...') FROM DUAL;
SELECT DBMS_METADATA.GET_granted_DDL('TABLESPACE_QUOTA', '...') FROM dual;

Then just replace the username with the new one you want to create.

Upvotes: 17

Related Questions