| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: getting ALL user's sys privileges [from Oracle9i DBA 101]
Thalis,
You are right, the first part of the query makes no sense. Perhaps you shouldcheck the 'errata 'section on the publisher's site, if there is one.
That said, the question you raise is a thorny one. The trouble is that it assumes that we have a two-level rights structure (either you grant directly a privilege, or you grant a privilege to a role, and then the role to the end-user). As you must know, even if IMHO it is better to keep this type of hierarchy rather flat, nothing prevents you from granting roles to roles, andtherefore the user may inherit a privilege after, so to speak, several levels of indirection.
That should be, in theory, the perfect case for CONNECT BY. The only trouble is that CONNECT BY is (I have not checked with 10g) not allowed on views - even dictionary views. You can run it, though, on sysauth$ which underlies both dba_sys_privs and dba_role_privs (depending on the sigh of privilege# isis a role or a real privilege). In practice, if you really want a true, clean view of privileges, the sad truth is that you must run your queries against SYS tables.
Don't forget also that, even if I would advise against it, you may find caseswhere privileges are granted to PUBLIC, and therefore *implicitly* granted to all users.
Regards,
Stephane Faroult
RoughSea Ltd
http://www.roughsea.com
On Fri, 5 Nov 2004 10:59 , 'Thalis Kalfigopoulos' <t.kalfigopoulos_at_alumil.com>sent:
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
inthe 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.
--
http://www.freelists.org/webpage/oracle-l[2]
 |
 |