Re: Granting users access to another user's objects

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Wed, 06 Dec 2000 08:35:04 -0500
Message-Id: <10702.123775@fatcity.com>

Helmut,
The public synonym for packages and procedures must be created or you = will have to reference them with the scott.package as the package name. I believe that the rights to a sequence can be granted to a role as well = as the userid.
ROR m=AA=BF=AAm
>>> hdaiminger_at_vivonet.com 12/05/00 07:51PM >>>
Hi all!

I am about to implement a more sophisticated security policy at our shop = (to
be more precise: so far, there is none....).

The user SCOTT owns all the objects for our application (i.e. tables, sequences, procedures, functions).

Now I want to give the users PETE, MARC and JOE access to select and = insert
into SCOTT's objects.

I create a role R_SCOTT, log in as SCOTT and grant insert and select on = all
tables to that role. Then I grant select and alter privileges on the sequences to the role as well. (Some tables have LOB colums - i.e. LOB segments - but that should not make a difference, right?).

After that I grant execute privileges on all procedures and functions to PETE, MARC and JOE directly (since they can't be granted through a role, right?).

Then I log in as a DBA and create public synonyms for the tables and sequences in the SCOTT schema, e.g. create public synonym tablename for scott.tablename.

The question is: do I also have to create public synonyms for the packages and procedures as well? PETE, JOE and MARC will access the procedures only through their name (i.e. not specifying execute scott.procedurename).

Do I have to grant rights on sequences to the user or can this be done through a role?

This is 8.1.6 on Win2k.

Thanks,
Helmut

--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20 --=20
Author: Helmut Daiminger
INET: hdaiminger_at_vivonet.com=20

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). Received on Wed Dec 06 2000 - 07:35:04 CST