calling a SP inside a SP.

Database
Enthusiast

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.

Tags (2)
4 REPLIES
Enthusiast

Re: calling a SP inside a SP.

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.

Teradata Employee

Re: calling a SP inside a SP.

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();

Enthusiast

Re: calling a SP inside a SP.

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;

Teradata Employee

Re: calling a SP inside a SP.

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.