0

I'm facing to a problem with PL/SQL stored procedure. I have a single SP, named sp1, which i deployed to my oracle schema using ODT for VS2010 and i can execute with no problems. The problem is pulled out when i try to execute sp1 from another stored procedure named sp2 already present in the schema before the deployment(i add the call to sp1 in the sp2's body and call sp2 directly from the same schema). In this way all i get is a ORA-01031 insufficient privilegesexception.

I googled and found that the problem is due to the lack of the clause AUTHID CURRENT_USER in the CREATE PROCEDURE statement. I created another stored procedure named sp3 in another package with that clause and inserted in it the call to sp1. With this modify the execution works. 

create or replace procedure sp3(string param)
authid current_user
as 
begin
  sp1(param);
end;

Unfortunately i can't accept this solution becouse i must use the already present package (there are at least M applications that use sp2 procedure and adding sp3 or sp1 directly in every one is obviusly impossible). I use version 11 for both oracle server and client.

Any ideas?

Riccardo
  • 1,490
  • 2
  • 12
  • 22
  • Not sure I understand. Why is "adding sp3 or sp1 directly in every one" "obviously impossible"? – Jeffrey Kemp Sep 24 '12 at 03:59
  • i have a bunch of old client applications defined to use sp2 procedure. For this issue i need to call sp1 inside it. If i had few client applications i could make them call directly sp1 instead of sp2(and this should solve my problem) but i can't becouse client application are too many(we are speacking about 3K applications..). – Riccardo Sep 24 '12 at 08:00
  • What i need is an explanation on how i can define a stored procedure with AUTHID CURRENT_USER clause that can be executed with current user privileges whatever package or stored procedure contains it. – Riccardo Sep 24 '12 at 08:02
  • Ok, so you have 3000 applications that are calling `sp2`, which is set to `AUTHID DEFINER`, and so it fails when it calls sp1 which needs the current user's privileges. Can you recompile `sp2` to use `AUTHID CURRENT_USER`? – Jeffrey Kemp Sep 24 '12 at 09:20
  • I did it but nothing changed. I suppose becouse the package is set to AUTHID DEFINER and i can't change it becouse there are other procedures inside it tested with that authorization policy. – Riccardo Sep 24 '12 at 10:41
  • how about if you remove `AUTHID DEFINER` from the package, and set all the other procedures in it to `AUTHID DEFINER` individually? (I haven't tried this myself, though) – Jeffrey Kemp Sep 25 '12 at 02:43
  • i'll try it right now. Just another question: i red in the Oracle official documentation that if a sp with AUTHID DEFINER clause calls inside a sp with AUTHID CURRENT_USER who execute the latter get the privileges of the former becouse it's AUTHID DEFINER and overwrite the AUTHID CURRENT_USER. However i can't find out what happens if the former has the AUTHID CURRENT_USER clause and the latter has the other one. i tried and the behaviour isn't symmetric.. – Riccardo Sep 25 '12 at 06:50
  • If sp1 with AUTHID DEFINER calls sp2 with AUTHID CURRENT_USER, I'd expect sp2 to execute with the rights of the definer of sp1, since in that context the "current user" is the definer of sp1. – Jeffrey Kemp Sep 25 '12 at 07:05
  • yes, it's true. But in the other case(that is, sp1 with AUTHID CURRENT_USER and sp2 with AUTHID DEFINER)? – Riccardo Sep 25 '12 at 07:25
  • if sp1 calls sp2, and sp2 is AUTHID DEFINER, I'd expect sp2 to execute with the permissions of the definer, so it doesn't matter whether sp1 is current_user or not. – Jeffrey Kemp Sep 25 '12 at 12:38

0 Answers0