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)
[EMAIL PROTECTED]
[EMAIL PROTECTED]
============================================================================================================
[EMAIL PROTECTED]
nkrupp.com To: [EMAIL
PROTECTED]
Sent by: [EMAIL PROTECTED] 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
>>> [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
