All Forums Database
I_Luv_SQL 2 posts Joined 09/14
19 Sep 2014
Grants needed for a trigger

A Delete trigger on table T1 is intended to populate an audit table T1_Audit, but the insert fails with a 3523.  Here are the gory details;

 

CREATE MULTISET TABLE DEV_DB.T1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      TypeCode CHAR(1) NOT NULL,

      Description VARCHAR(64) 

 )

UNIQUE PRIMARY INDEX ( TypeCode );

 

CREATE MULTISET TABLE DEV_DB.T1_AUDIT ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      TypeCode CHAR(1) NOT NULL,

      Description VARCHAR(64) 

 )

UNIQUE PRIMARY INDEX ( TypeCode );

 

The trigger syntax is:

CREATE TRIGGER dev_db.HiHo

AFTER DELETE ON dev_db.T1

REFERENCING OLD ROW AS OldRow

FOR EACH ROW

INSERT INTO dev_db.T1_Audit

VALUES(OldRow.TypeCode, OldRow.Description );

 

When this Delete statement is run:

DELETE FROM dev_db.T1

WHERE TypeCode = 'X';

 

DELETE Failed.  [3523] An owner referenced by user does not have INSERT WITH GRANT OPTION access to DEV_DB.T1_AUDIT.

 

Yet, this succeeds:

INSERT INTO dev_db.T1_AUDIT

SELECT * FROM dev_db.T1;

 

Since triggers have a dependency on objects in DBC, I thought that perhaps DBC was the owner being r, since the trigger and tables T1 & T1_Audit exist in dev_db:

 

GRANT  INSERT ON "DEV_DB" TO "DBC" WITH GRANT OPTION; 

 

Did not fix resolve the issue, nor did any other permutation of GRANT that I could imagine.  

 

Does a GRANT exist that will resolve this issue?  How does one identify the owner referenced?

dnoeth 4628 posts Joined 11/04
20 Sep 2014

The owner of an object is the database where it's created.
This should fix the error:

GRANT  INSERT ON "DEV_DB" TO "DEV_DB" WITH GRANT OPTION;

Dieter

I_Luv_SQL 2 posts Joined 09/14
20 Sep 2014

Thanks Dieter!  It appears a trigger shares the need for this same grant needed by a stored procedure.

You must sign in to leave a comment.