RETRIEVING USER GRANT BY QUERYING A DATABASE VIEWS

 
REQUEST

 
RELATED

 
SOLUTION

 
TESTCASE

-------------------
-- System grants
-------------------

COL GRANTEE FOR A10 
COL PRIVILEGE FOR A40 
SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='TESTCASE';

GRANTEE    PRIVILEGE
---------- ----------------------------------------
TESTCASE   UNLIMITED TABLESPACE
TESTCASE   CREATE ANY DIRECTORY

-------------------
-- Role grants 
-------------------

BREAK ON ROLE 
COL ROLE FOR A10 
COL PRIVILEGE FOR A20
SELECT ROLE,PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='TESTCASE') ORDER BY ROLE;

ROLE       PRIVILEGE
---------- --------------------
CONNECT    SET CONTAINER
           CREATE SESSION
RESOURCE   CREATE TYPE
           CREATE PROCEDURE
           CREATE OPERATOR
           CREATE INDEXTYPE
           CREATE TABLE
           CREATE SEQUENCE
           CREATE TRIGGER
           CREATE CLUSTER

-------------------
-- Ojbect grants 
-------------------

COL PRIVILEGE FOR A10
COL OBJECT_NAME FOR A20
SELECT GRANTEE,PRIVILEGE,OWNER || '.' || TABLE_NAME OBJECT_NAME FROM DBA_TAB_PRIVS WHERE GRANTEE='TESTCASE';

GRANTEE    PRIVILEGE  OBJECT_NAME
---------- ---------- --------------------
TESTCASE   SELECT     SYS.V_$SESSION
TESTCASE   SELECT     SYS.V_$SESSION_EVENT
TESTCASE   EXECUTE    SYS.MYDIRECTORY
TESTCASE   READ       SYS.MYDIRECTORY
TESTCASE   WRITE      SYS.MYDIRECTORY

You can easy generate DDL statements by the following queries

-------------------
-- System grants 
-------------------

COL SYSTEM_GRANTS FOR A50 
SELECT 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || 
       DECODE(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION', NULL) || ';' SYSTEM_GRANTS 
       FROM DBA_SYS_PRIVS WHERE GRANTEE='TESTCASE';

SYSTEM_GRANTS
--------------------------------------------------
GRANT UNLIMITED TABLESPACE TO TESTCASE;
GRANT CREATE ANY DIRECTORY TO TESTCASE;

-------------------
-- Role grants 
-------------------

COL ROLE_GRANTS FOR A30
SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || 
       DECODE(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION', NULL) || ';' ROLE_GRANTS 
       FROM DBA_ROLE_PRIVS WHERE GRANTEE='TESTCASE';

ROLE_GRANTS
------------------------------
GRANT RESOURCE TO TESTCASE;
GRANT CONNECT TO TESTCASE;

-------------------
-- Ojbect grants 
-------------------

SELECT 'GRANT ' || PRIVILEGE || ' ON ' || OWNER || '.' || TABLE_NAME || ' TO ' || GRANTEE || 
       DECODE(GRANTABLE, 'YES', ' WITH GRANT OPTION', NULL) || ';' OBJECT_GRANTS 
       FROM DBA_TAB_PRIVS WHERE GRANTEE='TESTCASE';

OBJECT_GRANTS
--------------------------------------------------------------------------------
GRANT SELECT ON SYS.V_$SESSION TO TESTCASE;
GRANT SELECT ON SYS.V_$SESSION_EVENT TO TESTCASE;
GRANT EXECUTE ON SYS.MYDIRECTORY TO TESTCASE WITH GRANT OPTION;
GRANT READ ON SYS.MYDIRECTORY TO TESTCASE WITH GRANT OPTION;
GRANT WRITE ON SYS.MYDIRECTORY TO TESTCASE WITH GRANT OPTION;