Lesson 9 | Viewing information about Roles |
Objective | List 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:
- ROLE_SYS_PRIVS: Contains information about system privileges granted to roles.
- ROLE_TAB_PRIVS: Contains information about object privileges (such as SELECT, INSERT, UPDATE, DELETE) granted to roles for tables, views, or materialized views.
- 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:
- The
ROLE_ROLE_PRIVS
view lists the roles granted to a role, so you can investigate a hierarchy of roles.
- 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.