I wrote a dynamic DDL stored subprogram to do this. You compile it under
the schema for whose objects you want to grant access. I made defaults for
it with the intention of making it easy to grant all on all tables to a
given user. See the schutils downloadable zip on the Products page of my
web site: http://www.telerama.com/~dclamage. This grants procedure runs
faster than dumping granting statements to a script, and never needs
refreshing. Plus it works for other objects besides tables.
--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
Richard Gowan <roxl_at_c031.aone.net.au> wrote in article
<6s3l7q$lij$1_at_news.mel.aone.net.au>...
>
> You don't want to edit the thing! In cases like this you can write SQL to
> generate the grant script!
>
> simple eg
>
> <turn all page/heading stuff off using SQL*Plus here>
> SPOOL commandfilename
> SELECT 'GRANT SELECT ON ' || table_name || ' TO a_user; ' FROM CAT
> /
> SPOOL OFF
> <set up normal settings again>
>
>
> You can use similar scripts to set up synonyms in a new schema, drop all
> objects etc. Much easier then dynamic SQL in PL/SQL blocks I reckon.
Just
> use the data dictionary tables required for your particular task.
>
> Regards,
>
> Richard Gowan
> roxl_at_c031.aone.net.au
Received on Thu Aug 27 1998 - 00:00:00 CDT
