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
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> 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: 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: Igor Neyman
  INET: ineyman_at_perceptron.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).