Re: Antw: Privileges and PL/SQL code

You will need to grant privileges directly to B and C on A's objects. Since B and C use PL/SQL code, other users only need to have execution permission on B and C's procedures. Using AUTHID CURRENT USER in the creation of the stored programs would require that the stored programs execute under the permissions and privileges of the user currently running the stored program. Otherwise the programs will be executed under the permissions and privileges of the owner of the stored programs. To enable a role with the privileges , grant the necessary privileges to a role, grant the role to the users, and at run time use the package procedure
DBMS_SESSION.SET_ROLE. RWB

Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) reginald.w.bailey_at_jpmorgan.com
baileyre_at_us.ibm.com

                                                                                                                                                   
                    Guido.Konsolke_at_tkserv-triaton.thysse                                                                                           
                    nkrupp.com                                 To:     ORACLE-L_at_fatcity.com                                                        
                    Sent by: ml-errors_at_fatcity.com             cc:                                                                                 
                                                               Subject:     Antw: Privileges and PL/SQL code                                       
                                                                                                                                                   
                    07/14/2003 07:54 AM                                                                                                            
                    Please respond to ORACLE-L                                                                                                     
                                                                                                                                                   
                                                                                                                                                   

Hi Stefan,

since 8 (i?):
have a look at AUTHID CURRENT USER in the docs. This enables using of database roles.

Greetings,
Guido

>>> Stefan.Jahnke_at_bov.de 14.07.2003 14.30 Uhr >>>
Hi list

I was wondering if there is any way to get the following to work:

User A owns all tables, users B,C own PL/SQL code to access A's tables. To work with A's tables from user B and C, I would like to create a role to contain all the object privileges and grant these to B and C.

Didn't use to work, since PL/SQL is not particularly interested in grants received through roles.
But I don't really want to grant all privileges directly to B and C (and many more users).

Is there a way in 9i that works better ? Any improvements I missed out on ?

Thanks in advance,
Stefan

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stefan Jahnke
  INET: Stefan.Jahnke_at_bov.de

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
--
Author: Guido Konsolke
  INET: Guido.Konsolke_at_tkserv-triaton.thyssenkrupp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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