3523 XXXXX:An owner referenced by user does not have XXXX access to XXXX.XXXXX

Check the owner have the access or not by using the following query and provide the missing grant.

SELECT  *

FROM dbc.allrightsV

WHERE username = '<OWNER NAME>'

and databasename= '<DATABASE NAME>'

-- and AccessRight='D' -- Provide the access that complaining in the Error e.g D for Delete 

;

 

Example:

Here I have created the problem for demonstration.  I have created a table in DATABASE1 and created a procedure in DATABASE2. The procedure executed by the user1 who have only execute procedure grant on DATABASE2.

 

CREATE TABLE DATABASE1.Category

(

CategoryName VARCHAR(20)

);

insert into DATABASE1.Category values('TEST');

 

CREATE PROCEDURE DATABASE2.CategoryInsert(IN c01 VARCHAR(20))

BEGIN

DELETE FROM DATABASE1.Category WHERE CategoryName = c01;

END;

 

-- Calling the Procedure from user1

CALL DATABASE2.CategoryInsert('TEST');

*** Error 3523 CATEGORYINSERT:An owner referenced by user does not have DELETE access to DATABASE1.Category.

 

Here the owner of the procedure is DATABASE2 so let’s check DATABASE2 have delete grant on DATABASE1 or not. Note here user1 does not have delete access on Table “DATABASE1.Category”. When user1 will execute the proceduer the delete will be perform by the owner of the Stored procedure which is DATABASE2 so user1 does not require delete access on table DATABASE1.Category.

 

SELECT  *

FROM dbc.allrightsV

WHERE username = 'DATABASE2'

and databasename= 'DATABASE1'

and AccessRight='D' 

;

*** Query completed. No rows found.

 

Let’s Provide the Grant to the owner and check the grant.

GRANT delete,select on DATABASE1 to DATABASE2;

 

SELECT  *

FROM dbc.allrightsV

WHERE username = 'DATABASE2'

and databasename= 'DATABASE1'

and AccessRight='D' 

;

UserName

DatabaseName

TableName

ColumnName

AccessRight

GrantAuthority

GrantorName

AllnessFlag

CreatorName

CreateTimeStamp

DATABASE2

DATABASE1

All

All

D

N

PRDATALAB

N

PRDataLab

2019-03-08 13:59:21

 





Now execute the procedure again from user1:

CALL DATABASE2.CategoryInsert('TEST');

*** Procedure has been executed.

 

Comments