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.
Asked
Active
Viewed 5.4k times
1 Answers
17
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.
hmqcnoesy
- 4,165
- 3
- 31
- 47
Gary Myers
- 34,963
- 3
- 49
- 74
-
PS. If you have java permissions, network ACLs etc, they may need some additional code. – Gary Myers Dec 17 '10 at 08:35
-
this is the perfect set of DDL to clone the user , thank you @Gary Myers – Ashish Shetkar Dec 21 '20 at 11:08