Re: Schema specific grants
Here is some code, which creates synonyms and grants permissions:
REM This script create public synonyms for all Tables and Views owned by
SCHEMA_NAME
REM and grants privileges on those objects to 'other' users.
declare lSyn integer;
BEGIN
-- Get Table(View) name
FOR Objects IN
(SELECT object_name FROM dba_objects
WHERE owner = 'SCHEMA_NAME'
AND object_type IN ('TABLE', 'VIEW')) LOOP
-- Find, if it has Synonym
SELECT COUNT(*) INTO lSyn
FROM dba_synonyms
WHERE synonym_name = Objects.object_name;
IF (lSyn = 0) THEN
-- Create Synonym
EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM ' || Objects.object_name ||
' for SCHEMA_NAME.' || Objects.object_name;
END IF;
-- Grant Privileges
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, DELETE, UPDATE ON ' ||
Objects.object_name || ' TO Guest';
END LOOP;
END;
/
You can modify it, if you don't want to create public synonyms, or want to
grant only specific privileges (i.e. only "select").
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, February 12, 2003 10:30 AM
> 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: Igor Neyman
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).
Re: Schema specific grants
select 'grant insert on '||table_name||' to guest;' from user_tables spool \tmp\grant_insert.sql / @\tmp\grant_insert.sql there is no "one shot" command that I know of --- Bob Metelsky <[EMAIL PROTECTED]> wrote: > 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). > __ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
RE: Schema specific grants
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: [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: Mercadante, Thomas F 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).
RE: Schema specific grants
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).
Re: Schema specific grants
CREATE ROLE GUEST_USER; SPOOL GIMME.SQL SELECT 'GRANT INSERT ON '||TABLE_NAME||' TO GUEST_USER;' FROM USER_TABLES; SPOOL OFF @GIMME GRANT GUEST_USER TO GUEST; "Bob Metelsky" @cps92.com> cc: Sent by: rootSubject: Schema specific grants 02/12/2003 10:30 AM Please respond to 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: [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: Thomas Day 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).
