Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Not sure if you can get everything, but dbms_metadata.get_XXX() might be a good start –  a_horse_with_no_name Dec 13 '12 at 10:01
add comment

1 Answer 1

up vote 9 down vote accepted

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.

share|improve this answer
    
Thanks Phil, will give it a try. –  Gommel Dec 14 '12 at 4:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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