Security Roles   «Prev  Next»

Lesson 9Viewing information about Roles
ObjectiveList privileges that have been granted to a role.

Viewing information about Roles

You can probably guess that you can get information about roles from Oracle's data dictionary views, because these views are the source of all knowledge about the objects within the database.

Query Data dictionary views to list Privileges granted

In Oracle, you can query the data dictionary views to list the privileges that have been granted to a role. The following data dictionary views provide information on the privileges granted to roles:
  1. ROLE_SYS_PRIVS: Contains information about system privileges granted to roles.
  2. ROLE_TAB_PRIVS: Contains information about object privileges (such as SELECT, INSERT, UPDATE, DELETE) granted to roles for tables, views, or materialized views.
  3. ROLE_ROLE_PRIVS: Contains information about roles that are granted to other roles.

Listing System Privileges:

SELECT PRIVILEGE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'ROLE_NAME';

Replace 'ROLE_NAME' with the name of the role for which you want to list the system privileges.

Listing Object Privileges:

SELECT OWNER, TABLE_NAME, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'ROLE_NAME';
Replace 'ROLE_NAME' with the name of the role for which you want to list the object privileges.

Listing Granted Roles:

SELECT GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'ROLE_NAME';

Replace 'ROLE_NAME' with the name of the role for which you want to list the granted roles.
Note that you need to have sufficient privileges to access the DBA_* views, such as DBA privileges or SELECT_CATALOG_ROLE. If you do not have these privileges, you can use the corresponding ALL_* views (ALL_ROLE_PRIVS, ALL_TAB_PRIVS, and ALL_ROLE_PRIVS) to query the privileges associated with the roles accessible to you.
In summary, you can list the privileges granted to a role in Oracle by querying the data dictionary views ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, and ROLE_ROLE_PRIVS. Make sure to replace 'ROLE_NAME' with the specific role name in the SQL queries.

Listing privileges for Role

The data dictionary view DBA_TAB_PRIVS provides information about the privileges granted on tables.
This view contains the following columns:

GRANTEE:the name of the person receiving the grant
OWNER:the owner of the object the privilege is granted on
TABLE_NAME:the object the privilege is granted on
GRANTOR:the user or role who grants the privilege
PRIVILEGE:the privilege granted
GRANTABLE:whether the privilege can be re-granted by the grantee

Determine Object Privileges

To use this view to discover the object privileges granted by a role, use the name of the role as a selection condition for the GRANTOR column. The DBA_TAB_PRIVS view is located in the SYS schema, so most users will have to include the qualified table identifier, SYS.DBA_TAB_PRIVS, to select from the table.
Two other views that can be used in investigating roles:
  1. The ROLE_ROLE_PRIVS view lists the roles granted to a role, so you can investigate a hierarchy of roles.
  2. The DBA_ROLES table lists whether a password is required for a role

Example

View the Code below. This query returns a row for each object privilege granted by the role.
SELECT * FROM SYS.DBA_TAB_PRIVS 
WHERE GRANTOR = 'BIDDER';

To find out what table privileges are granted to a role called BIDDER, use the following SQL command.

Listing Privileges

The next lesson shows how to list the roles assigned to a user.