All Forums Database
barani_sachin 141 posts Joined 01/12
14 May 2013
calling a SP inside a SP.

Hi all,
I am facing the below error while trying to call a SP from another SP!!
CALL Failed. 3523:  TEMP:An owner referenced by user does not have EXECUTE PROCEDURE access to temp1. 

REPLACE PROCEDURE temp1() SQL SECURITY DEFINER
BEGIN
END;


REPLACE PROCEDURE temp()
BEGIN
CALL temp1();
END;


CALL temp();

The user has execute procedure access in that DB, other SP's are running without any issues.
is it the problem with SQL SECURITY clause?
Please provide u r thoughts on what can be done to avoid this.
 

KS42982 137 posts Joined 12/12
15 May 2013

I think the error that you get is not about the access at the user level, but at the database level. database 1 (temp) doesn't have execute procedure access to database 2 (temp1). I am not a DBA, so can't be sure, but I know the error you are getting is not about user but between the databases.

Fred 1096 posts Joined 08/04
15 May 2013

The "owner" (database or user in which TEMP is defined) does not have EXECUTE PROCEDURE right for TEMP1.
Rights of the user are checked when invoking TEMP, but because TEMP does not specify SQL SECURITY (and therefore defaults to DEFINER), rights of the owner are checked for the nested CALL TEMP1();

barani_sachin 141 posts Joined 01/12
16 May 2013

so the problem is with defining SQL SECURITY for TEMP?? I tried giving both owner and creator for TEMP still the issue persists.or the database in which the SP resides needs SP execution access??
 
REPLACE PROCEDURE temp() SQL SECURITY CREATOR/OWNER
BEGIN
CALL nle_bvws.temp1();
END;
 

Fred 1096 posts Joined 08/04
17 May 2013

See the explanation and chart in the DDL manual under CREATE PROCEDURE regarding which user's and/or database's rights are checked with different SQL SECURITY options. For this case specifically, if temp has SQL SECURITY
DEFINER (default): Both creator (user issuing CREATE PROCEDURE temp) and owner (database containing SP temp) must have EXECUTE PROCEDURE permission on temp1; invoker (caller of temp) does not need permission on temp1.
OWNER: Owner of temp must have permission on temp1; creator or invoker need not (if different from owner)
CREATOR: Creator of temp must have permission on temp1; owner or invoker need not (if different from creator)
INVOKER: Invoker of temp must also have permission on temp1; creator or owner need not (if different from invoker)
Note that a database / user does not automatically receive EXECUTE PROCEDURE permission on itself.

You must sign in to leave a comment.