Db2 z/OS 11: Tracking Updates or Deletes on a table

D.A. Libby

Db2 z/OS 11: Tracking Updates or Deletes on a table

We have an audit table that inserts rows via a couple of Db2 packages. The table also has a couple of secondary RACF groups that have been given select authorization to be allowed to execute jobs that unload data. Management wants to be able to report if there were any unauthorized updates or deletes on our audit table. (Not that there should be, but just in case.)

 

I can think of three ways of checking to see if there were any updates or deletes on our audit table. 1) We could create a job to scan the Db2 logs (via BMC’s Log Master) to report/count what activity has occurred on the table. 2) Create an InfoSphere Replication subscription to capture any updates or deletes. 3) Create a trigger to insert a row on a new table whenever an update or delete occurred on our audit table.

 

Is any one of the three (or a different way that I have not thought of) the best way to accomplish this? Thanks!

 

 

D.A.

Jørn Thyssen

RE: Db2 z/OS 11: Tracking Updates or Deletes on a table
(in response to D.A. Libby)

Hi D.A.,

You may also make the table a system temporal table. However, this and the trigger solution have the disadvantage that the audit history is stored in a Db2 table which can be modified by system administrators. Your auditors may not like this. 

Using the Db2 logs might be a bit more safe against tampering, but the ultimate solution is a dedicated auditing product where the audit information is hardened to an protected off-platform server (e.g., IBM Guardium).

Each solution comes with different costs in terms of software licensing, management, operations, and CPU overhead, so pick your poison :)


In Reply to D.A. Libby:

We have an audit table that inserts rows via a couple of Db2 packages. The table also has a couple of secondary RACF groups that have been given select authorization to be allowed to execute jobs that unload data. Management wants to be able to report if there were any unauthorized updates or deletes on our audit table. (Not that there should be, but just in case.)

 

I can think of three ways of checking to see if there were any updates or deletes on our audit table. 1) We could create a job to scan the Db2 logs (via BMC’s Log Master) to report/count what activity has occurred on the table. 2) Create an InfoSphere Replication subscription to capture any updates or deletes. 3) Create a trigger to insert a row on a new table whenever an update or delete occurred on our audit table.

 

Is any one of the three (or a different way that I have not thought of) the best way to accomplish this? Thanks!

 

 

D.A.



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal.