Re: How to Grant 100 Tables to User?

From: Ronald <devnull_at_ronr.nl>
Date: 12 Feb 2003 04:47:17 -0800
Message-ID: <67ce88e7.0302120447.73699c23@posting.google.com>

hari_om_at_hotmail.com (Hari Om) wrote in message news:<d1d5ebe4.0302111521.2f8eb918_at_posting.google.com>...
> Hi:
>
> I have a DB User LARRY and he has around 100 Tables (and other
> objects). I want to GRANT ALL these 100 Tables to another User GATES.
> How can I do that? I know I could do it manually eg:
> GRANT ALL ON TABLE1 to GATES;
> GRANT ALL ON TABLE1 to GATES;
> ...and so on.... but is there any way to do that only once (in one
> statement)?

as table owner:
begin
 for i in (select 'grant select,delete,insert,update on '||table_name||

                     ' to gates' cmd
                   from user_tables
           )

 loop
   execute immediate (i.cmd);
 end loop;
end;
/

> Secondly, when user GATES accesss Tables from user LARRY (after GRANTS
> is successful) then GATES has to use LARRY.TABLE1? So is there any way
> for GATES to access tables without prefixing LARRY?
> Do I have to CREAT SYNONYM for all the 100 Tables...? Or is there any
> better approach?

as table user (having create synonym):
begin
 for i in (select 'create synonym '||table_name||' for gates.'||table_name cmd

             from all_tables
            where owner = 'LARRY'
           )

 loop
   execute immediate (i.cmd);
 end loop;
end;
/

dynamic sql is fun. Can't do without.

Ronald.

---

http://ronr.nl/unix-dba Received on Wed Feb 12 2003 - 06:47:17 CST