Re: How to Grant 100 Tables to User?

Log on as the owner of the tables using sqlplus and ...

select 'grant all on '||table_name||' to gates;' from user_tables;

This produces your grant statements. Spool them to a file, then run that file.

Hari Om wrote:
> 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
Received on Wed Feb 12 2003 - 02:15:41 CET