Listing privilege and role information in Oracle DBA

To list the grants made for objects, a user can query the following data dictionary views:

• 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

Table of Content - Listing privilege and role information in Oracle DBA

➤ Listing all system privilege grants ➤ Listing all role grants ➤ Listing object privileges granted to a user ➤ Listing roles of the database ➤ Listing information about the privilege domains of roles

Find Privilege and Role Information

For the following examples, assume that the following statements are issued:

Sql>create role security_admin identified by honcho;
Sql>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;
Sql>grant select, delete on sys.aud$ to security_admin;
Sql>grant security_admin, create session to swilliams;
Sql>grant security_admin to system_administrator;
Sql>grant create session to jward;
Sql>grant select, delete on emp to jward;
Sql>grant insert (ename, job) on emp to swilliams, jward;

 

If you want to enrich your career and become a professional in Oracle DBA, then enroll in "Oracle DBA Certification Training" - This course will help you to achieve excellence in this domain.

Listing all system privilege grants

The following query indicates all system privilege grants made to ROLES AND USERS:

Sql>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

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

Sql>select * from sys.dba_role_privs;

Grantee                       privilege                      adm

———-                ————-           ——————

Swilliams                  security_admin               no

Related Article: Oracle DBA Tutorial for Beginners with Examples

Listing object privileges granted to a user

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

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

Sql>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                        name                           insert
Jward                         emp                        job                               insert

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:

Wish to make a career in the world of Oracle DBA? Sign up for this online Oracle DBA Training in Hyderabad to enhance your career!

Sql>select * from sys.dba_roles;

Role                             password

———-               —————

Connect                           no
Resource                         no
Dba                                 no
Security_admin            yes

Related Article: Oracle DBA Interview Questions for Freshers

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:

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

Sql>select * from role_sys_privs where role = 'security_admin';

Related Article: Tablespaces in Oracle DBA

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:

Sql>select table_name, privilege from role_tab_privs     where role = 'security_admin';

Table_name                 privilege

—————         —————-

Aud$                               delete

Aud$                               select