RelationalDBDesignRelationalDBDesign


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.

Listing privileges for a 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

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.
The next lesson shows how to list the roles assigned to a user.