RE: Role Privileges

"Maybe the role your are concerned with has do privs granted to it???"

Oops... Make that no privs granted to it...

:-)

-----Original Message-----
Sent: Tuesday, January 15, 2002 7:15 PM
To: Multiple recipients of list ORACLE-L

You are either encountering a bug or doing it incorrectly... If it is a bug, you should call support... But, I'm guessing that you are doing it incorrectly... Run the following test...

Log on as system...

Create Role DeleteMe;

Grant Select On SQLPLUS_PRODUCT_PROFILE To DeleteMe;

Log on as a different dba id...

Select * From Dba_Tab_Privs Where Grantee = 'DELETEME';

You should see the following...

SQL>
SQL> connect system/system_pass_at_yourdb
Connected.
SQL> Create Role DeleteMe;

Role created.

SQL>
SQL> Grant Select On SQLPLUS_PRODUCT_PROFILE To DeleteMe;

Grant succeeded.

SQL>
SQL> connect other_dba_id/other_dba_id_pass_at_yourdb Connected.
SQL>
SQL> Select * From Dba_Tab_Privs Where Grantee = 'DELETEME';

GRANTEE                        OWNER
------------------------------ ------------------------------
TABLE_NAME                     GRANTOR
------------------------------ ------------------------------
PRIVILEGE                                GRA
---------------------------------------- ---
DELETEME                       SYSTEM
SQLPLUS_PRODUCT_PROFILE        SYSTEM
SELECT                                   NO

SQL> In this case, the table owned by system is granted to the DELETEME role but you can see it from another dba account... If you do not get these results, then you are encountering a bug of some sort... If you do see these results, you are probably doing something wrong with your other lookup... Maybe the role your are concerned with has do privs granted to it???

Tim

PS - FYI... This assumes you have run pupbld.sql... And, don't forget to drop the DELETEME role when you are done...

-----Original Message-----
Sent: Tuesday, January 15, 2002 6:31 PM
To: Multiple recipients of list ORACLE-L

At 02:51 PM 1/15/02 -0800, you wrote:
>Wrong... DBA_TAB_PRIVS will show you ANYONE who granted privs to the
>grantee you specify...

I'm sure it's supposed to, but it does not. I need another way.

>-----Original Message-----
>Sent: Tuesday, January 15, 2002 5:29 PM
>To: Multiple recipients of list ORACLE-L
>
>
>At 12:25 PM 1/15/02 -0800, you wrote:
> >dba_tab_privs will show you privileges granted to anyone, including
> >roles
>
>Yes, it will show privileges granted TO anyone, but only those privileges
>granted BY me (or whoever I am logged in as). I need to know how to see
>the privileges granted even when I don't know who they were granted by.
>
>
> >select table_name, privilege from dba_tab_privs where grantee='<role>';
> >
> >
> >--- Regina Harter <rharter_at_emc-inc.com> wrote:
> > > Okay, I knew this was going to happen one day, but I kept hoping as
> > > we
> > > upgraded the problem would be corrected eventually.
> > >
> > > ROLE_TAB_PRIVS will only show me tables privileges granted to a role
> > > by the
> > > logged on user. It wasn't such a problem before because I created
> > > most of
> > > the roles and knew who was doing the granting. Now I have a couple
> > > of
> > > roles I didn't create and need to know what has been granted to them.
> > > How
> > > do I find out without knowing who did the granting?
> > >
> > > Thank you, any help will be appreciated.
> > >
> > > Regina
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Regina Harter
> > > INET: rharter_at_emc-inc.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_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).
> >
> >
> >__________________________________________________
> >Do You Yahoo!?
> >Send FREE video emails in Yahoo! Mail!
> >http://promo.yahoo.com/videomail/
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Rachel Carmichael
> > INET: wisernet100_at_yahoo.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_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).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Regina Harter
> INET: rharter_at_emc-inc.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_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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Johnston, Tim
> INET: TJohnston_at_quallaby.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_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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Regina Harter
  INET: rharter_at_emc-inc.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_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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johnston, Tim
  INET: TJohnston_at_quallaby.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_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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johnston, Tim
  INET: TJohnston_at_quallaby.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_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).