| 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: ROLES & such
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
To find all defined roles in the database :
select * from dba_roles;
To find roles/system privileges/object privileges granted to a defined role :
select grantor,table_name,privilege,grantable
from dba_tab_privs
where grantee='your_role';
select granted_role,admin_option
from dba_role_privs
where grantee='your_role';
select * from dba_sys_privs
where grantee='your_role';
-----Message d'origine-----
De : root_at_fatcity.com [ mailto:root_at_fatcity.com]De la part de Charlie
Mengler
Envoye : ven. 28 avril 2000 16:24
A : Multiple recipients of list ORACLE-L
Objet : ROLES & such
I'm trying to identify who has which privs in one of my instances.
I have a handleful of users (schemas) that show they have been granted
"HR_ALL" role.
At this point in time, I'm not sure that this role really exists. (See
below.)
Where else should I be looking to see which, if any, privs are
associated
with the HR_ALL role?
SQL> set echo on
SQL> select grantee, granted_role
2 from dba_role_privs
3 where grantee = 'ALTAC'
4 /
GRANTEE GRANTED_ROLE ------------------------------ ------------------------------ ALTAC CONNECT ALTAC HR_ALL
SQL> select role from role_role_privs
2 where role = 'HR_ALL'
3 /
no rows selected
SQL> select role from role_sys_privs
2 where role = 'HR_ALL'
3 /
no rows selected
SQL> select role from role_tab_privs
2 where role = 'HR_ALL'
3 /
no rows selected
--
Charlie Mengler Maintenance Warehouse charliem_at_mwh.com 5505 Morehouse Drive 858-552-6229 San Diego, CA 92121If you don't use vi, then you shouldn't be working on UNIX!
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =charset=3DUS-ASCII">
<TITLE>RE: ROLES & such</TITLE> </HEAD> <BODY>
<P><FONT SIZE=3D2>To find all defined roles in the database : </FONT> </P>
<P><FONT SIZE=3D2>select * from dba_roles; </FONT> </P>
<P><FONT SIZE=3D2>To find roles/system privileges/object privileges =
granted to a defined</FONT>
<BR><FONT SIZE=3D2>role : </FONT>
</P>
<P><FONT SIZE=3D2>select grantor,table_name,privilege,grantable </FONT> <BR><FONT SIZE=3D2>from dba_tab_privs </FONT> <BR><FONT SIZE=3D2>where grantee=3D'your_role'; </FONT> </P> <P><FONT SIZE=3D2>select granted_role,admin_option </FONT> <BR><FONT SIZE=3D2>from dba_role_privs </FONT><BR><FONT SIZE=3D2>where grantee=3D'your_role'; </FONT> </P>
<P><FONT SIZE=3D2>select * from dba_sys_privs </FONT> <BR><FONT SIZE=3D2>where grantee=3D'your_role'; </FONT> </P>
<P><FONT SIZE=3D2>-----Message d'origine----- </FONT>
<BR><FONT SIZE=3D2>De : root_at_fatcity.com [ <A =
HREF=3D"mailto:root_at_fatcity.com" =
TARGET=3D"_blank">mailto:root_at_fatcity.com</A>]De la part de Charlie =
</FONT> <BR><FONT SIZE=3D2>Mengler </FONT> <BR><FONT SIZE=3D2>Envoye : ven. 28 avril 2000 16:24 </FONT> <BR><FONT SIZE=3D2>A : Multiple recipients of list ORACLE-L </FONT> <BR><FONT SIZE=3D2>Objet : ROLES & such </FONT> </P> <BR>
<P><FONT SIZE=3D2>I'm trying to identify who has which privs in one of =
my instances. </FONT>
<BR><FONT SIZE=3D2>I have a handleful of users (schemas) that show they =
have been granted</FONT>
<BR><FONT SIZE=3D2>"HR_ALL" role. </FONT>
<BR><FONT SIZE=3D2>At this point in time, I'm not sure that this role =
really exists. (See</FONT>
<BR><FONT SIZE=3D2>below.) </FONT>
<BR><FONT SIZE=3D2>Where else should I be looking to see which, if any, =
privs are</FONT>
<BR><FONT SIZE=3D2>associated </FONT> <BR><FONT SIZE=3D2>with the HR_ALL role? </FONT> </P> <P><FONT SIZE=3D2>SQL> set echo on </FONT> <BR><FONT SIZE=3D2>SQL> select grantee, granted_role </FONT> <BR><FONT SIZE=3D2> 2 from dba_role_privs </FONT> <BR><FONT SIZE=3D2> 3 where grantee =3D 'ALTAC' = </FONT>
<P><FONT =
SIZE=3D2>GRANTEE &n=
bsp; &n=
bsp; GRANTED_ROLE </FONT>
<BR><FONT SIZE=3D2>------------------------------ = ------------------------------ </FONT>
SIZE=3D2>ALTAC &nbs= p; &nbs= p; CONNECT </FONT>
SIZE=3D2>ALTAC &nbs= p; &nbs= p; HR_ALL </FONT>
<P><FONT SIZE=3D2>SQL> select role from role_role_privs </FONT> <BR><FONT SIZE=3D2> 2 where role =3D 'HR_ALL' </FONT> <BR><FONT SIZE=3D2> 3 / </FONT> </P>
<P><FONT SIZE=3D2>no rows selected </FONT> </P>
<P><FONT SIZE=3D2>SQL> select role from role_sys_privs </FONT> <BR><FONT SIZE=3D2> 2 where role =3D 'HR_ALL' </FONT> <BR><FONT SIZE=3D2> 3 / </FONT> </P>
<P><FONT SIZE=3D2>no rows selected </FONT> </P>
<P><FONT SIZE=3D2>SQL> select role from role_tab_privs </FONT> <BR><FONT SIZE=3D2> 2 where role =3D 'HR_ALL' </FONT> <BR><FONT SIZE=3D2> 3 / </FONT> </P>
<P><FONT SIZE=3D2>no rows selected </FONT> </P>
<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Charlie =
Mengler  =
; =
Maintenance Warehouse </FONT>
<BR><FONT =
SIZE=3D2>charliem_at_mwh.com  =
;  =
; 5505 Morehouse Drive </FONT>
<BR><FONT SIZE=3D2>858-552-6229  = ;  = ; San Diego, CA =92121 </FONT>
<BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Charlie Mengler </FONT> <BR><FONT SIZE=3D2> INET: charliem_at_mwh.com </FONT> </P>
<P><FONT SIZE=3D2>Fat City Network Services -- (858) =
538-5051 FAX: (858) 538-5051 </FONT>
<BR><FONT SIZE=3D2>San Diego, =
California -- Public Internet =
access / Mailing Lists </FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=----- </FONT>
 |
 |