Error 3523 - An owner referenced by user does not have EXECUTE PROCEDURE access

Database
Enthusiast

Error 3523 - An owner referenced by user does not have EXECUTE PROCEDURE access

Hi,

I have several utility stored procedures that are called from other stored procedures, all within the SAME database.  But on trying to have one stored procedure call another I am getting Error 3523.  I have seen this error discussed in the forum with respect to views, and a few cases involving stored procedures, and the solution is always to have "execute grant access" or to grant access with "grant option".  I have run the following SQL successfully on all stored procedures involved, but still getting the error.  Our DBA's solution is to create an entirely new database to house our stored procedures and views and they do some grants in there, while our original database will hold our tables. To me, this sounds too complex and will require much rework to now include these extra database references in our code.  Can someone please explain how to solve this issue?  Thanks!

GRANT execute on procedure [proc_being_called] to [user] with grant option;

 

5 REPLIES
Junior Contributor

Re: Error 3523 - An owner referenced by user does not have EXECUTE PROCEDURE access

Which TD release?

Might be due to the SPs SECURITY option?

Dieter

Enthusiast

Re: Error 3523 - An owner referenced by user does not have EXECUTE PROCEDURE access

Hi Dieter,

We are on 13.10

-Greg

Junior Contributor

Re: Error 3523 - An owner referenced by user does not have EXECUTE PROCEDURE access

Hi Greg,

in TD13.10 there's the SECURITY option.

The settings are stored in dbc.tvm.SPObjectCodeRows, but i don't know how to decipher it. You have to SHOW all SPs to see if they're set to INVOKER, OWNER or CREATOR instead of the default DEFINER.

Dieter

Enthusiast

Re: Error 3523 - An owner referenced by user does not have EXECUTE PROCEDURE access

Hi Dieter,

Thanks for your reply.  I did

SHOW PROCEDURE MM110829_WRITE_LOG_PRC

but it only displayed the code (below), no security info.  (so I guess it is default right now?)  Can you explain what is the security issue that gives rise to having the "WITH GRANT" option in the first place?  I see the discussion of these options in the SP manual, and how the SQL SECURITY option can be set when you create the procedure, but dont know which option to choose.  I want to be able to have other users in the creator's ROLE run these SP's with the SP's being able to call one another.

CREATE PROCEDURE MM110829_WRITE_LOG_PRC(

/********************************************************************************

  PROCEDURE:     MM110829_WRITE_LOG_PRC

  DESCRIPTION:   Write an error or status message to the MSG_LOG table.

                 It would be nice to also log the line number (for errors), but

                 this may not be available in TD.

 ********************************************************************************/

IN in_sql_state VARCHAR(5),

IN in_sqlcode INTEGER,

IN in_project VARCHAR(30),

IN in_sp_name VARCHAR(30),

IN in_message VARCHAR(5000)

)

 BEGIN

  INSERT INTO MSG_LOG

  (PROJECT, MSG_DATE, TIME_STAMP, SP_NAME, SQL_STATE, SQLCODE, MESSAGE)

  VALUES

  (in_project, CURRENT_DATE, CURRENT_TIMESTAMP, in_sp_name, in_sql_state, in_sqlcode, in_message);

 END;

Teradata Employee

Re: Error 3523 - An owner referenced by user does not have EXECUTE PROCEDURE access

Concerning error 3523

It is reasonable to think the original issue was resolved but for those that might face it in the future, this is how I was able to resolve the same error this morning.

  • I was getting the error with a function, not a procedure.
  • The UDF resides inside a database.
  • I had granted 'execute function with grant option' to the user accounts that would be calling the UDF but I missed that the database wherein the UDF existed needed to be granted the permission as well.
  • I granted the owner of the function (which is not one of its users) the permission and the issue was resolved.

sql I ran: grant execute function on <UDF> to <owner> with grant option;

 

Instead of <owner>, for testing purposes, you can do <public> and it should resolve the issue.

Test sql to run: grant execute function on <UDF> to public with grant option;