Message-Id: <10481.104453@fatcity.com> From: Benhayoune khalid Date: Fri, 28 Apr 2000 16:54:37 -0000 Subject: 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. ------ =_NextPart_001_01BFB132.71237440 Content-Type: text/plain 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@fatcity.com [ mailto:root@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@mwh.com 5505 Morehouse Drive 858-552-6229 San Diego, CA 92121 If you don't use vi, then you shouldn't be working on UNIX! -- Author: Charlie Mengler INET: charliem@mwh.com 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@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). ------ =_NextPart_001_01BFB132.71237440 Content-Type: text/html Content-Transfer-Encoding: quoted-printable RE: ROLES & such

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=3D'your_role';

select granted_role,admin_option
from dba_role_privs
where grantee=3D'your_role';

select * from dba_sys_privs
where grantee=3D'your_role';

-----Message d'origine-----
De : root@fatcity.com [ mailto:root@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  =3D 'ALTAC' =
  4  /

GRANTEE         &n= bsp;           &n= bsp;  GRANTED_ROLE
------------------------------ = ------------------------------
ALTAC         &nbs= p;           &nbs= p;    CONNECT
ALTAC         &nbs= p;           &nbs= p;    HR_ALL

SQL> select role from role_role_privs
  2  where role =3D 'HR_ALL'
  3  /

no rows selected

SQL> select role from role_sys_privs
  2  where role =3D 'HR_ALL'
  3  /

no rows selected

SQL> select role from role_tab_privs
  2  where role =3D 'HR_ALL'
  3  /

no rows selected

--
Charlie = Mengler           = ;            = Maintenance Warehouse 
charliem@mwh.com        = ;            = ;  5505 Morehouse Drive  
858-552-6229       = ;            = ;       San Diego, CA = 92121   
If you don't use vi, then you shouldn't be working = on UNIX!
--
Author: Charlie Mengler
  INET: charliem@mwh.com

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@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 =