I'm working on Oracle 9.2.0.4 R2.
When I replay your script it works but with my real users it still
doesn't work.
Bj
Rene Nyffenegger a écrit :
> In article <411737a5$0$22907$636a15ce_at_news.free.fr>, Bj wrote:
>
>>Hello,
>>
>>I'm unable to grant execute to a stored procedure.
>>
>>My StroreProc "test" is on schema A and I want to execute it in schema B
>> in the same database instance.
>>
>>I do the following statement connected as A :
>>
>>GRANT EXECUTE ON A.test TO B;
>>
>>Then I try to execute this storeProc connected as B:
>>
>>Begin
>> A.test;
>>end;
>>
>>but, oracle do not manage to access the objet and send me an ORA-06550
>>error.
>>
>>When I add a synonym to the storeproc for schema B, it works.
>>
>>Is it the only way to access other users storeproc ? It would be weird, no ?
>>
>>Thx for your responses.
>
>
> It works on 10g on Windows:
>
>
> SYSTEM_at_ora10> create user a identified by a default tablespace
> 2 users temporary tablespace temp;
>
> User created.
>
> SYSTEM_at_ora10>
> SYSTEM_at_ora10> create user b identified by b default tablespace
> 2 users temporary tablespace temp;
>
> User created.
>
> SYSTEM_at_ora10>
> SYSTEM_at_ora10> grant create procedure to a;
>
> Grant succeeded.
>
> SYSTEM_at_ora10> grant create session to a;
>
> Grant succeeded.
>
> SYSTEM_at_ora10> grant create session to b;
>
> Grant succeeded.
>
> SYSTEM_at_ora10>
> SYSTEM_at_ora10> connect a/a
> Connected.
>
> Session altered.
>
> A_at_ora10>
> A_at_ora10> create procedure tst as
> 2 begin
> 3 null;
> 4 end;
> 5 /
>
> Procedure created.
>
> A_at_ora10>
> A_at_ora10> create procedure test as
> 2 begin
> 3 null;
> 4 end;
> 5 /
>
> Procedure created.
>
> A_at_ora10>
> A_at_ora10> grant execute on tst to b;
>
> Grant succeeded.
>
> A_at_ora10> grant execute on test to b;
>
> Grant succeeded.
>
> A_at_ora10>
> A_at_ora10> connect b/b
> Connected.
>
> Session altered.
>
> B_at_ora10>
> B_at_ora10> begin
> 2 a.tst;
> 3 end;
> 4 /
>
> PL/SQL procedure successfully completed.
>
> B_at_ora10>
> B_at_ora10> begin
> 2 a.test;
> 3 end;
> 4 /
>
> PL/SQL procedure successfully completed.
>
>
>
> HOWEVER, test is a reserved word, and should not be used:
>
>
> SYSTEM_at_ora10> select count(*) from v$reserved_words where keyword = 'TEST';
>
> COUNT(*)
> ----------
> 1
>
>
>
> Rene
>
Received on Mon Aug 09 2004 - 07:54:22 CDT
