Re: getting ALL user's sys privileges [from Oracle9i DBA 101]

Here's the SQL I use. It may not be perfect, but does include DBA_TAB_PRIVS, which the example SQL seems to have missed.

select grantee, 'ROLE' privtype, granted_role privname, null owner, null table_name, admin_option grantable
from dba_role_privs
where grantee like upper('&WhichUser')
union
select grantee, 'SYSPRIV' privtype, privilege privname, null owner, null table_name, admin_option grantable
from dba_sys_privs
where grantee like upper('&WhichUser')
union
select grantee, 'TABPRIV' privtype, privilege privname, owner, table_name, grantable
from dba_tab_privs
where grantee like upper('&WhichUser')
order by 1, 2, 3, 4, 5;

As Stephane has indicated, this discovers only privs that have been assigned directly to the user. A complete listing of privileges requires more digging.

The way I solved getting a complete listing of privileges was to dump all privileges to an Excel spreadsheet, which links to another sheet for role grants, so that all privileges may be easily drilled down on.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


On Fri, 5 Nov 2004 10:59:19 +0200, Thalis Kalfigopoulos
<t.kalfigopoulos_at_alumil.com> wrote:


> Hi all,

> 

> I'm reading the book 'Oracle9i DBA 101' and I have a question with regard to privileges.

> On p.160 the authors pose the question: "How do we get all the privileges that have been granted to

> JAMES and the roles he has been granted". What I think is meant is: "how do we find all sys

> privileges of user JAMES, both explicitly granted and implicitly granted though the user's roles".

> This questions arises because the table DBA_SYS_PRIVS doesn't return the sys privs the user has

> through his granted roles.

> 

> So the suggested solution is:

> 

>         select b.Grantee, b.Privilege

>         from DBA_ROLE_PRIVS a, DBA_SYS_PRIVS b

>         where a.Grantee=b.Grantee and a.Grantee='JAMES'

>                 UNION

>         select b.Grantee, b.Privilege

>         from DBA_ROLE_PRIVS a, DBA_SYS_PRIVS b

>         where a.Granted_Role=b.Grantee and a.Grantee='JAMES'

> 

> I understand that the two queries of the UNION do the following things:

> 1st query: gets JAMES's sys privs from DBA_SYS_PRIVS, for each of JAMES's assigned roles according

> to DBA_ROLE_PRIVS

> 2nd query: gets privileges assigned to each one of JAMES's granted roles

> 

> I feel/think that the first query of the UNION is redundant because it returns the same sys privs

> once for each of the user's roles. Of course this redundancy is eliminated in the final result due

> to the UNION operator.

> 

> Thus the first query could shrink down to the following:

> select b.Grantee, b.Privilege

> from DBA_SYS_PRIVS b

> where b.Grantee='JAMES'

> 

> Am I missing something here?

--
http://www.freelists.org/webpage/oracle-l