Some examples of using these views follow.

For these examples, assume the following statements have been issued:[@more@]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; See Also: Oracle9i Database Reference for a detailed description of these data dictionary views 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 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 The DBA_ROLES data dictionary view can be used 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 on 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