How can I retrieve a list of all tables to which a user is has SELECT permission (either granted directly or through an assigned role)?
Asked
Active
Viewed 1,150 times
2 Answers
2
select owner||'.'||table_name from user_tab_privs where privilege='SELECT'
union
select rtp.owner||'.'||rtp.table_name from user_role_privs urp, role_tab_privs rtp
where urp.granted_role = rtp.role and rtp.privilege='SELECT'
union
select user||'.'||table_name from user_tables;

Erich Kitzmueller
- 36,381
- 5
- 80
- 102
1
select table_name from TABLE_PRIVILEGES where grantee='USER' and select_priv='Y'
union
select table_name from user_tables

chance
- 6,307
- 13
- 46
- 70
-
Exactly what I needed! Thanks. I didn't know TABLE_PRIVILEGES exist. – ivan Dec 12 '11 at 11:50