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?

Gommel
  • 213
  • 1
  • 2
  • 4

2 Answers2

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.

Philᵀᴹ
  • 31,952
  • 10
  • 86
  • 108
-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

Samat Marat
  • 1