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.

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.

SELECT PRIVILEGE
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'ROLE_NAME';

SELECT OWNER, TABLE_NAME, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'ROLE_NAME';

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.

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

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

SELECT * FROM SYS.DBA_TAB_PRIVS 
WHERE GRANTOR = 'BIDDER';