As a DBA, we might often need to mirror the privileges of a user in Oracle to another user. Today we will learn how to mirror the privileges of a user in Oracle with detailed steps.
Steps to mirror the privileges of a user in Oracle :
Step 1:
By using below script, we can get the privilege details of the mirror user (TEST) similar to which we want to give
access to another user.
You can replace your user instead of TEST in the below script. Here we have replaced with JOHN user.
SET HEAD OFF
SET PAGESIZE 0
SET LONG 999999
SET ECHO OFF
SELECT DBMS_METADATA.GET_DDL('USER', UPPER('TEST')) || '/' DDL
FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', UPPER('TEST')) || '/' DDL
FROM DUAL
WHERE EXISTS (SELECT 'X' FROM DBA_ROLE_PRIVS DRP WHERE DRP.GRANTEE = UPPER('TEST'))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', UPPER('TEST')) || '/' DDL
FROM DUAL
WHERE EXISTS (SELECT 'X' FROM DBA_SYS_PRIVS DSP WHERE DSP.GRANTEE = UPPER('TEST'))
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', UPPER('TEST')) || '/' DDL
FROM DUAL
WHERE EXISTS (SELECT 'X' FROM DBA_TAB_PRIVS DTP WHERE DTP.GRANTEE = UPPER('JOHN'));
You will get the output as below :
Step 2:
Now if you want to grant similar access, you can replace JOHN with the required user and grant the same. Here we have replaced JOHN with HR user and have granted the same access.
So, here is the step by which you can get the steps how to mirror the privileges of a user in Oracle. Hope this helps!!
Related Articles :
- Grant All Privileges to User in Oracle
- How to check specific user account details in a Oracle database
For other privileges/grant related references, you can follow the Oracle Doc :