Listing privilege and role information in Oracle DBA

  • (4.0)

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

Listing privilege and role information: examples:

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;

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

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.

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

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:

Sql>select * from sys.dba_roles;

Role                             password

———-               —————

Connect                           no
Resource                         no
Dba                                 no
Security_admin            yes

Checkout Oracle DBA Interview Questions

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';

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

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!


Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules