Grants needed for a trigger

Database

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?

2 REPLIES
Junior Contributor

Re: Grants needed for a trigger

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;

Re: Grants needed for a trigger

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