Procedure calls within Procedures issue - SPL5000:W(L__), E(3523)

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Procedure calls within Procedures issue - SPL5000:W(L__), E(3523)

Hello guru's,

 

We're having an issue with Procedure calls within Procedure. Following is the explanation.

 

User layout.JPG

 

 

 

 

 

 

> We're logging into ETLADMIN and deploying the following procedures on DEV_DB

> All procedure calls are also made from ETLADMIN user

>Procedure Hierarchy

Procedure call hierarchy.JPG

While deploying the procedures, we've observed the following

         > Procedure #4 has been deployed without any warnings and it executes fine

         > Procedure #3, #2, and #1 are also deployed but with warnings in the lines where other procs are being called. Eg

               SPL5000:W(L17), E(3523):An owner referenced by user does not have EXECUTE PROCEDURE access to Dev_DB.WriteToLog.

         > When executing wither #1, #2 or #3, they fail with this error

               CALL Failed. 3523: PR_MINUS_QUERY_PROCESSING_P3:An ownerreferenced by user does not have EXECUTE PROCEDURE access to Dev_DB. WriteToLog

         > 

         > 

 

We've tried 

         > Different SECURITY options as shown in this table

SQL Security Options.JPG

         > Giving GRANTS using the following statement

            SyntaxEditor Code Snippet

GRANT EXECUTE ON PROCEDURE dev_db.pr_src_minus_tgt TO etladmin WITH GRANT OPTION;

 

**The same procedure setup and calls work in another environment where we are directly deploying in and calling with the same user.

 

Nothing has worked so far...

> Is there anything that the DBA has to do in terms of permissions/roles?

> Are there any specific SECURITY options that, us as developers need to use?

> Is there anything that we're doing wrong?

 

Any help is much appreciated.

 

Thanks,

Pattabhi.


Accepted Solutions
Teradata Employee

Re: Procedure calls within Procedures issue - SPL5000:W(L__), E(3523)

It is the Owner (DEV_DB) that lacks the necessary access.

 

GRANT EXECUTE PROCEDURE ON DEV_DB.called_procedure_name  TO DEV_DB;

 

You could also grant at the database level rather than specific procedures. Or you could change SQL SECURITY to CREATOR instead of DEFINER.

 

By the way, your CALL statement is Static, not Dynamic. If it were Dynamic, you would have the same issue at run time but would not be seeing the compile-time warning.

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Procedure calls within Procedures issue - SPL5000:W(L__), E(3523)

It is the Owner (DEV_DB) that lacks the necessary access.

 

GRANT EXECUTE PROCEDURE ON DEV_DB.called_procedure_name  TO DEV_DB;

 

You could also grant at the database level rather than specific procedures. Or you could change SQL SECURITY to CREATOR instead of DEFINER.

 

By the way, your CALL statement is Static, not Dynamic. If it were Dynamic, you would have the same issue at run time but would not be seeing the compile-time warning.

Enthusiast

Re: Procedure calls within Procedures issue - SPL5000:W(L__), E(3523)

Thanks a ton Fred.

 

"You could also grant at the database level rather than specific procedures". We did try this too but as mentioned in the first post, we did the same to ETLADMIN user instead :|