Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query to get objects list for which the current user has 'SELECT' privilege

712634Oct 1 2012 — edited Oct 1 2012
Hi,

I want to get tables and views list for which the current user has 'SELECT' privilege excluding sytem tables and views. The privilege information for the current user is scattered in more than one system views. I have following system views

USER_TAB_PRIVS_RECD - Object grants for which the current user is the grantee

ROLE_TAB_PRIVS - describes table privileges granted to roles. Information is provided only about roles to which the user has access.

If the object privilege is granted explicitly like "GRANT ALL ON TABLE_NAME TO "USERNAME" then this privilege entry goes to USER_TAB_PRIVIS_RECD

If the object privilege is granted by ROLE. Role is created with certain object privileges. this role is assigned to user "GRANT ROLE_NAME TO "USERNAME"", then this entry goes to ROLE_TAB_PRIVS

From these findings, I have composed below query
(SELECT DISTINCT TABLE_NAME FROM USER_TAB_PRIVS_RECD) UNION (SELECT DISTINCT TABLE_NAME FROM ROLE_TAB_PRIVS WHERE PRIVILEGE = 'SELECT')

Have I got user's all prvilege details? should I look some other system views?
How to exclude system table privileges details?
(SELECT DISTINCT TABLE_NAME FROM USER_TAB_PRIVS_RECD) UNION (SELECT DISTINCT TABLE_NAME FROM ROLE_TAB_PRIVS WHERE PRIVILEGE = 'SELECT' AND OWNER NOT IN ('SYS','SYSTEM') Is this right?
What about Public role in Oracle? Should I consider public role here?

Oracel Version :11g
I want make this query to work on Oracle 8i or above version

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 29 2012
Added on Oct 1 2012
2 comments
2,683 views