Re: EXECUTE ANY PROCEDURE

On 14 May 2007 13:25:15 -0700, joel garry <joel-garry_at_home.com> wrote:

>On May 14, 11:19 am, Jeff Kish <jeff.k..._at_mro.com> wrote:
>
>>
>> I logged in as myuser and ran this to give JOE access to the schema data:
>>
>> begin
>> for tab in (select view_name from all_views where owner = 'MYUSER') loop
>> execute immediate 'grant select,insert,update, delete on ' || tab.view_name
>> || ' to JOE';
>> commit;
>> end loop;
>>
>> for tab in (select table_name from all_tables where owner = 'MYUSER') loop
>> execute immediate 'grant select,insert,update, delete on ' || tab.table_name
>> || ' to JOE';
>> commit;
>> end loop;
>>
>> for tab in (select sequence_name from all_sequences where sequence_owner =
>> 'MYUSER') loop
>> execute immediate 'grant select on ' || tab.sequence_name || ' to JOE';
>> commit;
>> end loop;
>>
>> end;
>> /
>
>I'm thinking you might be trying to grant from schemata that are the
>names of the tables or sequences with this code. What happens when
>you remove the tab. from tab.table_name?
>
>jg

yeah, it turned ot to be a combination of wierdness using numbers as usernames, and case sensitivity as you probably saw. when I removed tab. and just queried on table_name it worked fine.

thanks
Jeff Kish Received on Tue May 15 2007 - 06:39:46 CDT