1

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)?

ivan
  • 390
  • 3
  • 13

2 Answers2

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