Fun with Oracle Data Dictionary Views

Played around with some data dictionary views in Oracle. "USER_TAB_PRIVS" is a view which contains information about object privileges that are associated with the user (object privileges for which the current user is object owner, privilege granter, or privilege grantee). "ROLE_TAB_PRIVS" is a view of object privileges granted to roles given to the user.

I wanted to find out exactly how these views work. So I set up a test in the following way to see how "ROLE_ROLE_PRIVS" and "ROLE_TAB_PRIVS" work.


create user testuser identified by "pass";
create role testrole1;
create role testrole2;
create role testrole3;
grant testrole1 to testuser;
grant testrole2 to testrole1;
grant testrole3 to testrole2;

grant select on testschema.testview1 to testrole1;
grant select on testschema.testview2 to testrole2;
grant select on testschema.testview3 to testrole3;


I logged in as "testuser". Querying "ROLE_ROLE_PRIVS" displayed both "testrole2" and "testrole3" as "GRANTED_ROLE". Querying "ROLE_TAB_PRIVS" displayed all three views under the column "TABLE_NAME" for which the user has been granted "SELECT" privileges via the roles.

I mainly wanted to find out whether I'd need to recursively query these views for all object privileges a user has. But it seems the task will simply require querying "USER_TAB_PRIVS" and "ROLE_TAB_PRIVS".

Reading through descriptions of other data dictionary views, it occurs to me that I might as well query the "ALL_TAB_PRIVS_RECD" since it will list all privileges user has on objects the user does not own. Then querying "USER_OBJECTS" for views would yield the views user owns.

Alternatively, querying "ALL_OBJECTS" for views could also work if it is a assumed that the user has "SELECT" privilege to those views.