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:[EMAIL PROTECTED]] > 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: [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: Bob Metelsky 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).
