Listing Privilege and Role Information

• ALL_COL_PRIVS, USER_COL_PRIVS, DBA_COL_PRIVS

• ALL_COL_PRIVS_MADE, USER_COL_PRIVS_MADE

• ALL_COL_PRIVS_RECD, USER_COL_PRIVS_RECD

• ALL_TAB_PRIVS, USER_TAB_PRIVS, DBA_TAB_PRIVS

• ALL_TAB_PRIVS_MADE, USER_TAB_PRIVS_MADE

• ALL_TAB_PRIVS_RECD, USER_TAB_PRIVS_RECD

• DBA_ROLES

• USER_ROLE_PRIVS, DBA_ROLE_PRIVS

• USER_SYS_PRIVS, DBA_SYS_PRIVS

• COLUMN_PRIVILEGES

• ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS

• SESSION_PRIVS, SESSION_ROLES

Listing Privilege and Role Information: Examples

CREATE ROLE security_admin IDENTIFIED BY honcho;

GRANT create profile, alter profile, drop profile,

   create role, drop any role, grant any role, audit any,

   audit system, create user, become user, alter user, drop user

   TO security_admin WITH ADMIN OPTION;

GRANT SELECT, DELETE ON sys.aud$ TO security_admin;

GRANT security_admin, create session TO swilliams;

GRANT security_admin TO system_administrator;

GRANT create session TO jward;

GRANT SELECT, DELETE ON emp TO jward;

GRANT INSERT (ename, job) ON emp TO swilliams, jward;

Listing All System Privilege Grants

SELECT * FROM sys.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

SELECT * FROM sys.dba_role_privs;

GRANTEE            GRANTED_ROLE                                ADM

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

SWILLIAMS          SECURITY_ADMIN                              NO

Listing Object Privileges Granted to a User

SELECT table_name, privilege, grantable FROM sys.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 sys.dba_col_privs;

GRANTEE       TABLE_NAME    COLUMN_NAME         PRIVILEGE

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

SWILLIAMS     EMP           ENAME               INSERT

SWILLIAMS     EMP           JOB                 INSERT

JWARD         EMP           ENAME               INSERT

JWARD         EMP           JOB                 INSERT

Listing the Current Privilege Domain of Your Session

SELECT * FROM session_roles;

If SWILLIAMS has enabled the SECURITY_ADMIN role and issues this query, Oracle returns the following information:

ROLE

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

SECURITY_ADMIN

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

SELECT * FROM session_privs;

If SWILLIAMS has the SECURITY_ADMIN role enabled and issues this query, Oracle 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 SWILLIAMS, the first query would have returned no rows, while the second query would only return a row for the CREATE SESSION privilege grant.

Listing Roles of the Database

SELECT * FROM sys.dba_roles;

ROLE                           PASSWORD

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

CONNECT                        NO

RESOURCE                       NO

DBA                            NO

SECURITY_ADMIN                 YES

Listing 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