Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Grant execute on stored Proc

Re: Grant execute on stored Proc

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 9 Aug 2004 09:47:16 GMT
Message-ID: <slrnchei0r.2hk.rene.nyffenegger@zhnt60m34.netarchitects.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US