Re: Granting privileges to objects in a schema

On 18 Sep 2002 13:22:27 -0700, rbarratt_at_tygersoft.com.au (Ross Barratt) wrote:

>Hi Folks,
>
>I need to grant privileges to all objects in a schema to a particular
>role. I can do this manually ( grant priv on object to role ....) but
>there is more than 600 objects. I am sure that somebody has a script
>to automate this process. If you do have such a script could you
>please email/post me a copy.
>
>Thankyou very much.

Here are a few ideas, that would at least have you do something of the task.
- if you are on Unix, spool the list of objects to a file with the extention .sql Now vi the file and issue as command %s/*/grant select, insert, update, delete on & to <role/g. Type <esc>:x and @<filename>.sql and presto
- if you are using 8i or higher the following you should be dead easy to complete
begin
for i in (select object_name from user_objects) loop execute immediate 'grant select, insert, update, delete on '||i.object_name||' to <role>';
end loop;
end;
/

I admit using either solution you still have to do some typing.

In short: the purpose of this group is not to do your work for you.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Sep 18 2002 - 15:52:59 CDT