Dear Team
I am using DB2 v10 z/os database . Need expert guidance to figure out best way to track table activities ( Ex Delete, Insert,Update )
Scenario
We have a table which is critical and many developer/testing team access on daily basis . We had instance where some deleted entire table . Since its test environment - the privileges are given as expected.
Expectations: Is there a way to capture/track at user level ? Can we track which user deleted this table or updated/inserted etc.
What I Tried : Trigger was my first thought . Second was temporal tables ( Not explored)
I created a trigger with table that captures insert records with below example . I am able to figure out whenever insert/update/delete happens . however I am unable to capture which user did this . How can be track this ? Can we use SYSIBM.SYSTABAUTH ( Grantee column has user name ) But how can this be mapped
Example of insert logic
CREATE TRIGGER TEST_SAMPLE_INSERT
AFTER INSERT ON TEST_SAMPLE
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO X_TEST_SAMPLE ( action_timestamp,action_type,before_or_after, Other attributes of table )
Values (current timestamp,'I','A',Other attributes of table )
Any help appreciated . Would love to learn new ways to capture this at user level
This is my second warning... do not post by cutting and pasting formatting "junk" from another web site here.
You need to get rid of all this "other invalid formatting" before posting.
Dear Team
I am using DB2 v10 z/os database . Need expert guidance to figure out best way to track table activities ( Ex Delete, Insert,Update )
Scenario
We have a table which is critical and many developer/testing team access on daily basis . We had instance where some deleted entire table . Since its test environment - the privileges are given as expected.
Expectations: Is there a way to capture/track at user level ? Can we track which user deleted this table or updated/inserted etc.
What I Tried : Trigger was my first thought . Second was temporal tables ( Not explored)
I created a trigger with table that captures insert records with below example . I am able to figure out whenever insert/update/delete happens . however I am unable to capture which user did this . How can be track this ? Can we use SYSIBM.SYSTABAUTH ( Grantee column has user name ) But how can this be mapped
Example of insert logic
CREATE TRIGGER TEST_SAMPLE_INSERT
AFTER INSERT ON TEST_SAMPLE
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO X_TEST_SAMPLE ( action_timestamp,action_type,before_or_after, Other attributes of table )
Values (current timestamp,'I','A',Other attributes of table )
Any help appreciated . Would love to learn new ways to capture this at user level