getting ALL user's sys privileges [from Oracle9i DBA 101]
- From: "Thalis Kalfigopoulos" <t.kalfigopoulos@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Nov 2004 10:59:19 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
