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).
