How to list all users with SELECT ANY TABLE permission in Oracle?-CodePudding

I want to write PL/SQL scripts to print out all users that have been granted permission to Select Any Table in Oracle database but I still don't know where to start

CodePudding user response：

SELECT ANY TABLE is a system privilege. So to answer your question you need to query the static data dictionary view DBA_SYS_PRIVS. You'll need to be a DBA or power user to query this view.

select grantee
from dba_sys_privs
where privilege = 'SELECT ANY TABLE';

CodePudding user response：

To expand on APC's answer, you often need to recursively dig into dba_role_privs to find a complete list of users who have access (directly or indirectly) to a privilege.

There are similar questions that go into more detail, and people have written exhaustive scripts to do this with table and column privileges as well. But here's a lazy version:

with rc (grantee, privilege, roles) as (
    select grantee, privilege, null as roles
    from dba_sys_privs sp
    where sp.privilege = 'SELECT ANY TABLE'
    union all
    select rp.grantee, rc.privilege, case when rc.roles is not null then rc.roles || '>' end || rp.granted_role as roles
    from dba_role_privs rp
    join rc on rp.granted_role = rc.grantee
      and nvl(rc.roles,'x') not like '%>' || rp.granted_role || '%' -- avoid cycles
    )
select * from rc
order by 1;