Roles and Privileges

Listing All System Privilege Grants

The following query returns all system privilege grants made to roles and users:

SELECT * FROM DBA_SYS_PRIVS;

GRANTEE            PRIVILEGE                         ADM

--------------     --------------------------------- ---

SECURITY_ADMIN     ALTER PROFILE                     YES

SECURITY_ADMIN     ALTER USER                        YES

SECURITY_ADMIN     AUDIT ANY                         YES

SECURITY_ADMIN     AUDIT SYSTEM                      YES

SECURITY_ADMIN     BECOME USER                       YES

SECURITY_ADMIN     CREATE PROFILE                    YES

SECURITY_ADMIN     CREATE ROLE                       YES

SECURITY_ADMIN     CREATE USER                       YES

SECURITY_ADMIN     DROP ANY ROLE                     YES

SECURITY_ADMIN     DROP PROFILE                      YES

SECURITY_ADMIN     DROP USER                         YES

SECURITY_ADMIN     GRANT ANY ROLE                    YES

SWILLIAMS          CREATE SESSION                    NO

JWARD              CREATE SESSION                    NO

Listing All Role Grants

The following query returns all the roles granted to users and other roles:

SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE            GRANTED_ROLE                         ADM

------------------ ------------------------------------ ---

SWILLIAMS          SECURITY_ADMIN                       NO

Listing Object Privileges Granted to a User

The following query returns all object privileges (not including column-specific privileges) granted to the specified user:

SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS

    WHERE GRANTEE = 'jward';

TABLE_NAME   PRIVILEGE    GRANTABLE

-----------  ------------ ----------

EMP          SELECT       NO

EMP          DELETE       NO

To list all the column-specific privileges that have been granted, use the following query:

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE

    FROM DBA_COL_PRIVS;

GRANTEE      TABLE_NAME     COLUMN_NAME      PRIVILEGE

-----------  ------------   -------------    --------------

SWILLIAMS    EMP            ENAME            INSERT

SWILLIAMS    EMP            JOB              INSERT

JWARD        EMP            NAME             INSERT

JWARD        EMP            JOB              INSERT

Listing the Current Privilege Domain of Your Session

The following query lists all roles currently enabled for the issuer:

SELECT * FROM SESSION_ROLES;

If user swilliams has the security_admin role enabled and issues the previous query, then Oracle Database returns the following information:

ROLE

------------------------------

SECURITY_ADMIN

The following query lists all system privileges currently available in the security domain of the issuer, both from explicit privilege grants and from enabled roles:

SELECT * FROM SESSION_PRIVS;

If user swilliams has the security_admin role enabled and issues the previous query, then Oracle Database returns the following results:

PRIVILEGE

----------------------------------------

AUDIT SYSTEM

CREATE SESSION

CREATE USER

BECOME USER

ALTER USER

DROP USER

CREATE ROLE

DROP ANY ROLE

GRANT ANY ROLE

AUDIT ANY

CREATE PROFILE

ALTER PROFILE

DROP PROFILE

If the security_admin role is disabled for user swilliams, then the first query would return no rows, while the second query would only return a row for the CREATE SESSION privilege grant.

Listing Roles of the Database

You can use the DBA_ROLES data dictionary view to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:

SELECT * FROM DBA_ROLES;

ROLE                  PASSWORD

----------------      --------

CONNECT               NO

RESOURCE              NO

DBA                   NO

SECURITY_ADMIN        YES

Listing Information About the Privilege Domains of Roles

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information about the privilege domains of roles. For example, the following query lists all the roles granted to the system_admin role:

SELECT GRANTED_ROLE, ADMIN_OPTION

   FROM ROLE_ROLE_PRIVS

   WHERE ROLE = 'SYSTEM_ADMIN';

GRANTED_ROLE              ADM

----------------          ----

SECURITY_ADMIN            NO

The following query lists all the system privileges granted to the security_admin role:

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

ROLE                    PRIVILEGE                      ADM

----------------------- -----------------------------  ---

SECURITY_ADMIN           ALTER PROFILE                 YES

SECURITY_ADMIN           ALTER USER                    YES

SECURITY_ADMIN           AUDIT ANY                     YES

SECURITY_ADMIN           AUDIT SYSTEM                  YES

SECURITY_ADMIN           BECOME USER                   YES

SECURITY_ADMIN           CREATE PROFILE                YES

SECURITY_ADMIN           CREATE ROLE                   YES

SECURITY_ADMIN           CREATE USER                   YES

SECURITY_ADMIN           DROP ANY ROLE                 YES

SECURITY_ADMIN           DROP PROFILE                  YES

SECURITY_ADMIN           DROP USER                     YES

SECURITY_ADMIN           GRANT ANY ROLE                YES

The following query lists all the object privileges granted to the security_admin role:

SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS

    WHERE ROLE = 'SECURITY_ADMIN';

TABLE_NAME                     PRIVILEGE

---------------------------    ----------------

AUD$                           DELETE

AUD$                           SELECT

I hope you all have enjoyed reading this article. Comments are welcome....