Oracle Database 11g -- Find the Users Having Access on a Specific Object

Problem

You need identify all the users having a specific privilege on a specific object

Solution

Use the below query:

with
  root_roles as (select root_role, grantee username from
    (select  connect_by_root t.granted_role root_role, t.* from
      dba_role_privs t connect by prior  t.grantee =  t.granted_role) t1,
      dba_roles r ,
      dba_users u
      where t1.root_role = r.role and t1.grantee = u.username
    union all
    select 'PUBLIC', username from dba_users
    union all
    select username, username from dba_users)
select distinct  username from
  dba_tab_privs p, root_roles r 
  where p.grantee = root_role and
  p.table_name='<tablename>' and
  p.owner='<owner>' and
  p.privilege like '%';