Re: Granting SELECT to all tables in a schema

OK! So there is no single statement for this. Your idea and that of someone else who kindly replied, point me in the right direction.

Thank you.
Ivan

"Me" <0_at_127.0.0.1> wrote in message
news:Xdtv9.1493$yG1.37730_at_newsfep2-win.server.ntli.net...
> "ivan vasquez" <ivan_at_itos.uga.edu> wrote in message
> news:apkfvl$k8$1_at_cronkite.cc.uga.edu...
> > Is there a better way to GRANT user1 to SELECT from any table (or
object)
> in
> > user2's schema? I could create a role with all tables, but then I'd have
> to
> > update it every time I add a new table.
> >
> > Thank you.
> > Ivan.
> >
> >
>
> Ivan,
>
> There is an easy way, write a procedure to loop through all_objects, then
> construct the appropriate grant statement. Here's a quick example below,
> I'll leave it to you to add suitable error handling etc. You may also want
> to consider creating synonyms etc.
>
> Cheers, Clive.
>
> CREATE OR REPLACE procedure grant_all_proc(v_owner varchar2,v_user_name
> varchar2, v_object_type varchar2)
> as
>
> cursor c1 is
>
> select object_name
>
> from all_objects
>
> where object_type = v_object_type
>
> and owner = v_owner;
>
> begin
>
> FOR v_rec in c1 LOOP
>
> dbms_output.put_line('GRANT SELECT ON '|| v_rec.object_name || ' TO ' ||
> v_user_name);
>
> EXECUTE IMMEDIATE ('GRANT SELECT ON '|| v_rec.object_name || ' TO ' ||
> v_user_name);
>
> end loop;
>
> end;
>
> /
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.408 / Virus Database: 230 - Release Date: 24/10/2002
>
>
Received on Tue Oct 29 2002 - 07:27:18 CST