Script to pull/show a database user’s privileges or clone a user by query.
CREATE OR REPLACE PROCEDURE ASSIGNMENT2(P_USERNAME1 VARCHAR2, P_USERNAME2 VARCHAR2)
IS
CURSOR CUR_PRIV IS SELECT A.*
FROM (SELECT GRANTEE USERNAME,
GRANTED_ROLE PRIVILEGE,
'--' OWNER,
'--' TABLENAME,
'--' COLUMN_NAME,
ADMIN_OPTION ADMIN_OPTION,
'ROLE' ACCESS_TYPE
FROM DBA_ROLE_PRIVS RP JOIN DBA_ROLES R ON RP.GRANTED_ROLE = R.ROLE
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER(P_USERNAME1))
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
'--' OWNER,
'--' TABLENAME,
'--' COLUMN_NAME,
ADMIN_OPTION ADMIN_OPTION,
'SYSTEM' ACCESS_TYPE
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER(P_USERNAME1))
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
OWNER OWNER,
TABLE_NAME TABLENAME,
'--' COLUMN_NAME,
GRANTABLE ADMIN_OPTION,
'TABLE' ACCESS_TYPE
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER(P_USERNAME1))
UNION
SELECT DP.GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
OWNER OWNER,
TABLE_NAME TABLENAME,
COLUMN_NAME COLUMN_NAME,
'--' ADMIN_OPTION,
'ROLE' ACCESS_TYPE
FROM ROLE_TAB_PRIVS RP, DBA_ROLE_PRIVS DP
WHERE RP.ROLE = DP.GRANTED_ROLE
AND DP.GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER(P_USERNAME1))
UNION
SELECT GRANTEE USERNAME,
PRIVILEGE PRIVILEGE,
OWNER OWNER,
TABLE_NAME TABLENAME,
COLUMN_NAME COLUMN_NAME,
GRANTABLE ADMIN_OPTION,
'COLUMN' ACCESS_TYPE
FROM DBA_COL_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME = UPPER(P_USERNAME1))) A;
BEGIN
FOR REC IN CUR_PRIV
LOOP
BEGIN
IF (REC.PRIVILEGE LIKE 'CREATE %') OR (REC.PRIVILEGE LIKE 'DROP%') OR (REC.PRIVILEGE LIKE 'ALTER%') THEN
IF REC.ACCESS_TYPE = 'SYSTEM' THEN
DBMS_OUTPUT.PUT_LINE('REVOKE ' || REC.PRIVILEGE || ' FROM ' || P_USERNAME2 || ';');
ELSE
DBMS_OUTPUT.PUT_LINE('REVOKE ' || REC.PRIVILEGE || ' ON ' || REC.OWNER ||'.'|| REC.TABLENAME || ' FROM ' || P_USERNAME2 || ';');
END IF;
ELSE
IF REC.COLUMN_NAME <> '--' THEN
DBMS_OUTPUT.PUT_LINE('GRANT ' || REC.PRIVILEGE || ' (' || REC.COLUMN_NAME || ')' || ' ON ' || REC.OWNER ||'.'|| REC.TABLENAME || ' TO ' || P_USERNAME2 || ';');
ELSE
DBMS_OUTPUT.PUT_LINE('GRANT ' || REC.PRIVILEGE || ' ON ' || REC.OWNER ||'.'|| REC.TABLENAME || ' TO ' || P_USERNAME2 || ';');
END IF;
END IF;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'AN ERROR WAS ENCOUNTERED - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/
SQL> set serveroutput on
SQL>
SQL> exec ASSIGNMENT2('USER_APP','SAMAD');
GRANT INSERT ON HR.EMPLOYEES TO SAMAD;
GRANT SELECT ON HR.EMPLOYEES TO SAMAD;
GRANT SELECT ON HR.JOB_HISTORY TO SAMAD;
PL/SQL procedure successfully completed.
