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

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?

TIA.

______________________________
Thalis Kalfigopoulos
IT Department
Alumil S.A.
E-mail:  t.kalfigopoulos@xxxxxxxxxx

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

Other related posts: