Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to Grant 100 Tables to User?
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 )
> 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' )
dynamic sql is fun. Can't do without.
Ronald.