try something like this:
set serveroutput on size 1000000;
declare
v_sql varchar2(4000);
cursor c_cur is select table_name from user_tables;
begin
begin
for v_cur in c_cur loop
v_sql = 'grant select,insert,update,delete on
'||v_cur.table_name||' to owner2';
dbms_output.put_line('did '||v_sql);
execute immediate v_sql;
exception
when others then
dbms_output.put_line ('problem with '||v_sql);
end;
end loop;
end;
/
jack silvey
--- "Mandal, Ashoke" <[EMAIL PROTECTED]>
wrote:
> Greetings,
>
> Here is the scenario.
> I have 2 users(owner1, owner2) in an oracle
> database.
> owner1 owns 150 tables.
> owner2 needs select,insert,update,delete privilege
> on all the tables owned by owner1.
>
> One option is : login as owner1 and
>
> grant select,insert,update,delete on owner1.table1
> to owner2;
> .
> .
> .
> grant select,insert,update,delete on owner1.table150
> to owner2;
>
> I was wondering if there is any way I can perform
> the same work using one sql statement instead of
> using 150 statements.
>
> Thanks,
> Ashoke
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Mandal, Ashoke
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
