Mastering Oracle Permission Views: Query Roles, Privileges, and Sessions

1. DBA_ROLE_PRIVS

This view lets you discover which roles a user or role possesses and which users/roles have been granted a particular role.

select * from DBA_ROLE_PRIVS where GRANTEE='FIRGTRS';

Result shows the GRANTEE, the GRANTED_ROLE and whether the grant includes ADMIN OPTION.

select * from DBA_ROLE_PRIVS where GRANTED_ROLE='GTRS_DMM_UPDATE_ROLE';

This query lists all users/roles that have received the specified role.

2. DBA_TAB_PRIVS

Despite its name, this view reports object‑level privileges, not only table privileges.

select GRANTOR,GRANTEE,TABLE_NAME,PRIVILEGE 
from DBA_TAB_PRIVS 
where TABLE_NAME='PAYAGENT' 
order by GRANTEE;

The output lists the grantor, grantee, object name and the specific privilege (INSERT, UPDATE, DELETE, SELECT, etc.).

3. DBA_SYS_PRIVS

This view shows which system privileges a user has.

select * from DBA_SYS_PRIVS where GRANTEE='FIRGTRS';

Typical columns are GRANTEE, PRIVILEGE and ADM (ADMIN OPTION flag).

4. ROLE_SYS_PRIVS

Lists system privileges granted to a role.

select * from ROLE_SYS_PRIVS where ROLE='DBA_SUPPORT';

Example output includes privileges such as SELECT ANY SEQUENCE and SELECT ANY DICTIONARY.

5. SESSION_PRIVS

Shows the system privileges currently active for the logged‑in user. select * from SESSION_PRIVS; Typical rows include CREATE SESSION, SELECT ANY SEQUENCE, SELECT ANY DICTIONARY, etc.

6. SESSION_ROLES

Lists all roles that are enabled for the current session. select * from SESSION_ROLES; Result includes roles such as DBA_SUPPORT, CONNECT, SELECT_CATALOG_ROLE, HS_ADMIN_ROLE.

7. WITH ADMIN OPTION (and WITH GRANT OPTION)

The ADMIN OPTION applies to system privileges. Users granted a privilege with ADMIN OPTION, or users holding GRANT ANY PRIVILEGE / GRANT ANY OBJECT PRIVILEGE, can further grant that privilege to others.

Example experiment:

Create two test users as DBA:

A105024@O02DMS1>create user testuser1 identified by test1;
A105024@O02DMS1>create user testuser2 identified by test2;

Grant CREATE SESSION to testuser1 without ADMIN OPTION:

A105024@O02DMS1>grant CREATE SESSION to testuser1;

Login as testuser1 and view its privileges: TESTUSER1@O02DMS1>select * from user_sys_privs; Result shows CREATE SESSION with ADM = NO.

Attempt to grant CREATE SESSION to testuser2 from testuser1 – fails with ORA‑01031 insufficient privileges because ADM is NO.

Grant CREATE SESSION to testuser1 with ADMIN OPTION:

A105024@O02DMS1>grant CREATE SESSION to testuser1 with admin option;

Re‑query user_sys_privs – ADM now shows YES.

Now testuser1 can successfully grant CREATE SESSION to testuser2:

TESTUSER1@O02DMS1>grant CREATE SESSION to testuser2;

For object privileges, the analogous clause is WITH GRANT OPTION.