Home  >  Blog  >   Oracle DBA

Listing privilege and role information in Oracle DBA

Rating: 5
  
 
8463

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

MindMajix Youtube Channel

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

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
Oracle DBA TrainingMar 30 to Apr 14View Details
Oracle DBA TrainingApr 02 to Apr 17View Details
Oracle DBA TrainingApr 06 to Apr 21View Details
Oracle DBA TrainingApr 09 to Apr 24View Details
Last updated: 14 Nov 2023
About Author

I am Ruchitha, working as a content writer for MindMajix technologies. My writings focus on the latest technical software, tutorials, and innovations. I am also into research about AI and Neuromarketing. I am a media post-graduate from BCU – Birmingham, UK. Before, my writings focused on business articles on digital marketing and social media. You can connect with me on LinkedIn.

read more
Recommended Courses

1 / 15