Print Friendly, PDF & Email
How to find out all provided system, object, and role privileges to the specific user in the Oracle database?

REQUEST
I want to get a list of all privileges given to a user by querying data dictionary views.

RELATED
RETRIEVING AN OBJECT’S DDL STATEMENT AND USER GRANTS

SOLUTION
This kind of information can be retrieved from 3 data dictionary views

1 The DBA_SYS_PRIVS lists system privileges granted to users and roles.
2 The ROLE_SYS_PRIVS lists system privileges granted to roles.
3 The DBA_TAB_PRIVS lists all grants on all objects in a database.

TESTCASE

Create the TESTCASE user and grant some privileges to it

-- The TESTCASE user 

CREATE USER TESTCASE IDENTIFIED BY TESTCASE DEFAULT TABLESPACE USERS;

-- Grant some system roles to the TESTCASE user

GRANT CONNECT, RESOURCE TO TESTCASE;

-- Grant some system privileges to TESTCASE user

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

-- Grant SELECT privilege on some system views to TESTCASE user

GRANT SELECT ON V_$SESSION TO TESTCASE;
GRANT SELECT ON V_$SESSION_EVENT TO TESTCASE;

Now when user is created query mentioned data dictionary views to retrieve list of all provided privileges

-- Define the variable having the user name

DEFINE USERNAME='TESTCASE';

--------------------------------
-- System grants : DBA_SYS_PRIVS 
--------------------------------

COL GRANTEE FOR A10 
COL PRIVILEGE FOR A40 

SELECT GRANTEE, PRIVILEGE 
FROM   DBA_SYS_PRIVS 
WHERE  GRANTEE='&USERNAME';

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

--------------------------------
-- Role grants : DBA_ROLE_PRIVS 
--------------------------------

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='&USERNAME') 
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 : DBA_TAB_PRIVS 
--------------------------------

COL PRIVILEGE FOR A10
COL OBJECT_NAME FOR A20

SELECT GRANTEE, PRIVILEGE, OWNER || '.' || TABLE_NAME OBJECT_NAME 
FROM   DBA_TAB_PRIVS 
WHERE  GRANTEE='&USERNAME';

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

As can been seen from the above output all 3 views contains all necessary information about all granted privileges to the user. In this example, the privileges returned by the views are exactly the same as what was formerly provided to the TESTCASE user.

By using the same views you can easily construct DDL statements for each issued user privilege.

-- Define the variable having the user name

DEFINE USERNAME='TESTCASE';

--------------------------------
-- System grants : DBA_SYS_PRIVS 
--------------------------------

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 : DBA_ROLE_PRIVS 
--------------------------------

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 : DBA_TAB_PRIVS 
--------------------------------

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;

That’s it. Drop the TESTCASE user

SQL> DROP USER TESTCASE CASCADE;

User dropped.

 

Version  : 13:14 17.01.2018
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0