Re: How to Grant 100 Tables to User?

From: Donovan R. <mdonovan_at_hotmail.com>
Date: Tue, 11 Feb 2003 20:38:39 -0500
Message-ID: <nn8j4v8udbvq4jaouo7oplmj9nmc0busoh@4ax.com>

You have to produce dynamically the sql to be executed: connect as Larry

set head off
set feedback off
spool grants.sql
SELECT 'GRANT ALL ON ' || TABLE_NAME || ' TO GATES;' FROM USER_TABLES; spool off

@grants.sql

On 11 Feb 2003 15:21:08 -0800, hari_om_at_hotmail.com (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 Tue Feb 11 2003 - 19:38:39 CST