Oracle-L: RE: Schema specific grants

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 12 Feb 2003 09:59:04 -0800
Message-ID: <F001.0054ADF5.20030212095904@fatcity.com>

Bob,

the best thing I can offer is the following:

set lines 150
set pages 2000
set trimspool on
select 'grant insert on ' || table_name || ' to Guest_Role;' from user_tables
/

Create the role named in the script (or change the role name to the actual Oracle username) and grant the role to the user.

Run it from the schema where the tables exist. Spool the output to a .sql file and run the resulting file back thru sqlplus.

You may also want to create either public or private synonyms for the user to make their life a little easier.

I like Roles better than assigning stuff directly to the user - just easier to manage.

good luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, February 12, 2003 10:30 AM To: Multiple recipients of list ORACLE-L

Good Morning All

Im looking at trying to grant privilidges to a "guest" user (who does not own the tables)

I know I can do it for individual tables

Eg
GRANT INSERT ANY SCHEMA_NAME.TABLE_NAME TO Guest;

but I need to grant to an entrie schema Like
GRANT INSERT ANY SCHEMA_NAME.* TO Guest;

Anyone have the syntax for that?

Many thanks
bob
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Bob Metelsky
INET: bmetelsky_at_cps92.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 also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------