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

From: Thalis Kalfigopoulos <t.kalfigopoulos_at_alumil.com>
Date: Fri, 5 Nov 2004 10:59:19 +0200
Message-ID: <000e01c4c315$bcdb3a70$dc00000a@mantis>

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_at_alumil.com

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

Received on Fri Nov 05 2004 - 02:50:44 CST