RE: Schema specific grants

From: Bob Metelsky <bmetelsky_at_cps92.com>
Date: Wed, 12 Feb 2003 10:04:36 -0800
Message-ID: <F001.0054AE2B.20030212100436@fatcity.com>

Thnaks for the response. I realised that immediately after I posted. This is what Ive done

/*@D:\createinsert.sql

drop user Guest;
create user Guest identified by *****
default tablespace tables
temporary tablespace temp;
grant connect to Guest;

*/
set echo off
set feedback off
set pages 0
set heading off
set lines 80
set verify off

Spool D:\insertperm.sql

select 'GRANT INSERT ON SCHEMA_NAME.'||'' || table_name ||' TO Guest'
||';' from sys.all_tables where owner = 'SCHEMA_NAME';
select 'GRANT SELECT ON SCHEMA_NAME.'||'' || table_name ||' TO Guest'
||';' from sys.all_tables where owner = 'SCHEMA_NAME';
select 'GRANT UPDATE ON SCHEMA_NAME.'||'' || table_name ||' TO Guest'
||';' from sys.all_tables where owner = 'SCHEMA_NAME';
select 'GRANT DELETE ON SCHEMA_NAME.'||'' || table_name ||' TO Guest'
||';' from sys.all_tables where owner = 'SCHEMA_NAME';
select 'GRANT EXECUTE ON SCHEMA_NAME.'||'' || object_name ||' TO Guest'

||';' from all_objects where object_type = 'PACKAGE' and owner = 'SCHEMA_NAME' and object_name like 'CPS%'; spool off;
start D:\insertperm.sql
-- exit ;

I was hoping for a syntax parameter but this works as well

Thanks

Bob
> 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-----
> From: Bob Metelsky [mailto:bmetelsky_at_cps92.com]
> Sent: Wednesday, February 12, 2003 10:30 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Schema specific grants
>
>
> 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: 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).

Received on Wed Feb 12 2003 - 12:04:36 CST