21

We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the database and grab information from it.

We have an existing user in the database for this. However, it is a production ID and we cannot afford to have it locked out. We would like to clone / duplicate this ID including its roles and rights.

Is there a way we can do so in Oracle?

0

2 Answers 2

23

User creation:

select dbms_metadata.get_ddl( 'USER', 'PHIL' ) from dual;

Default role:

select dbms_metadata.get_granted_ddl( 'DEFAULT_ROLE', 'PHIL' ) from dual;

System grants:

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'PHIL' ) from  dual;

Object grants:

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'PHIL' ) from dual;

Role grants:

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'PHIL' ) from dual;

Quotas:

select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;

If any of the above have no output, you'll get an exception that looks something like this:

SQL> select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;
ERROR:
ORA-31608: specified object of type TABLESPACE_QUOTA not found
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5991
ORA-06512: at line 1

no rows selected

SQL>

Then do a search and replace on the output to change the username.

3
  • Note that the DDL generation wraps the existing user in double-quotes; this makes replacing the existing user name very simple. Commented Oct 12, 2017 at 21:55
  • 1
    The ROLE_GRANT has to precede the DEFAULT_ROLE statement when creating the new user in order to succeed.
    – Theofilos
    Commented Mar 2, 2018 at 15:37
  • @Theofilos thanks, edited
    – Betlista
    Commented Dec 4, 2019 at 12:00
-1

SELECT DBMS_METADATA.get_ddl ('USER', 'SASIO') FROM DUAL UNION ALL SELECT DBMS_METADATA.get_granted_ddl ('DEFAULT_ROLE', 'SASIO') FROM DUAL UNION ALL SELECT DBMS_METADATA.get_granted_ddl ('SYSTEM_GRANT', 'SASIO') FROM DUAL UNION ALL SELECT DBMS_METADATA.get_granted_ddl ('OBJECT_GRANT', 'SASIO') FROM DUAL UNION ALL SELECT DBMS_METADATA.get_granted_ddl ('ROLE_GRANT', 'SASIO') FROM DUAL

1
  • 1
    This looks a lot like the existing answer. Commented Aug 18, 2022 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.