Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Grant execute on stored Proc
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
-- Rene Nyffenegger http://www.adp-gmbh.ch/Received on Mon Aug 09 2004 - 04:47:16 CDT