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

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

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

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

0 Responses on Listing privilege and role information in Oracle DBA"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.