All Forums Database
gpolanch 46 posts Joined 12/11
19 Jul 2012
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;

 

dnoeth 4628 posts Joined 11/04
19 Jul 2012

Which TD release?

Might be due to the SPs SECURITY option?

Dieter

Dieter

gpolanch 46 posts Joined 12/11
19 Jul 2012

Hi Dieter,

We are on 13.10

-Greg

 

dnoeth 4628 posts Joined 11/04
20 Jul 2012

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

Dieter

gpolanch 46 posts Joined 12/11
20 Jul 2012

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;

You must sign in to leave a comment.