Re: Granting SELECT to all tables in a schema

"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