Re: How to Grant 100 Tables to User?

hari_om_at_hotmail.com said...
> 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)?
>
> 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?
>
> Thanks!
>
> HARI OM
>

You might want to check out Roles. You can grant access to all 100 tables to a Role, then grant that Role to any Users. Much easier than 100 grants every time you add a user.

create role myrole;
grant all on table1 to myrole;
...
grant all on table100 to myrole;
grant myrole to gates;
grant myrole to larry;

Gates needs to create private synonyms (or DBA create public synonyms).

connect gates/password
create synonym table1 for larry.table1;
...
create synonym table100 for larry.table100; select * from table1;

-- 
/Karsten