How To Get The DDL For An Oracle User With All Roles And Privileges?
Sometimes we need to create a user similar to another user or we need to recreate a user in another database.In those situations,Oracle supplied package DBMS_METADATA can be used to generate the user creation DDL.
>> To generate the User creation script
SQL> SELECT dbms_metadata.get_ddl(‘USER’,’TEST’) FROM dual;
PROFILE “APP_USER”
>> To generate the script for granted roles
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’TEST’) from dual;
GRANT “DBA” TO “TEST”
>> To generate the script for system privileges
SQL> select DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’TEST’) from dual;
GRANT UPDATE ANY TABLE TO “TEST”
>> To generate the script for object privileges
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’TEST’) from dual;
GRANT SELECT ON “USER1″.”TEST_CHECK” TO “TEST”
>> To generate the script for tablespace quota
SQL> select dbms_metadata.get_granted_ddl( ‘TABLESPACE_QUOTA’, ‘TEST’) from dual;
WHERE TABLESPACE_NAME = ”USERS01” AND CONTENTS
END;
>> Below given query can be used to generate the consolidated script with all details.
/
Note : ORA-31608 and ORA-06512 will occur if there is no output ( no rows selected) for any of these statements.
Article by : Anju