How to clone user in Oracle
49,679
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.
Author by
Petro Semeniuk
Updated on July 09, 2022Comments
-
Petro Semeniuk almost 2 years
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.
-
Gary Myers over 13 yearsPS. If you have java permissions, network ACLs etc, they may need some additional code.
-
Ashish Shetkar over 3 yearsthis is the perfect set of DDL to clone the user , thank you @Gary Myers