Script for Mirroring Privileges of a user:
Scenario: A Database schema named “TEST2” is created and business wants to grant the privileges for the user same as already existing Database user “TEST1”.
Run the below queries as SYS or any Privileged user to get the DDL commands in Oracle for TEST1 user.
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’TEST1′) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’TEST1′) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’TEST1′) FROM DUAL;
Scenario: A Database schema named “TEST2” is created and business wants to grant the privileges for the user same as already existing Database user “TEST1”.
Run the below queries as SYS or any Privileged user to get the DDL commands in Oracle for TEST1 user.
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’TEST1′) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’TEST1′) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’TEST1′) FROM DUAL;
The output will be a sequence of grant commands related to privileges assigned to TEST1 user. Now replace the string TEST1 with new user name “TEST2” and run the commands.
This will grant all privileges same as TEST1 user to TEST2 user in Oracle Database.