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
REPLACE PROCEDURE 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.
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.
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();
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
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.