DB2 z/OS: SYSIBM.SYSAUDITPOLICIES too much data

Aurora Emanuela Dellanno

DB2 z/OS: SYSIBM.SYSAUDITPOLICIES too much data

hi all,

 

I have had a look at previous posts in the list about the use of audit policies, and I actually was wondering about direct experience of any of you, since we have an audit requirement that is, in fact, ending with unforeseen results in the catalog.

 

Why? oh, it's all working as designed, say IBM: 

"When an audit policy that specifies EXECUTE category is started, it starts IFCIDs 143,144, and 145. Any table that is defined with AUDIT clause will also be audited. Audit policy includes those tables specified with AUDIT clause. . For a table audited through audit policy, you also get additional information in the trace record such as statement ID and every statement in the transaction is audited, not just the first one."

 

Aside from the logical security/privacy implications, we are seeing a lot of data there, which is a performance hit.

 

IBM are good enough to say that other customers have had the same result and problems, and that maybe documentation could be improved (minor effort) or, in the long run, "more discussion is needed with two other development teams about exclusively tracing thru the audit policy".

 

We need to know if and who is accessing a certain group of tables, so we need to go back to the drawing board: any alternative ideas, please?

 

Thanks.

 

Aurora

Philip Sevetson

DB2 z/OS: SYSIBM.SYSAUDITPOLICIES too much data
(in response to Aurora Emanuela Dellanno)
Aurora,

My Agency uses a third-party tool which captures all dynamic SQL requests (IFCID 350??) and makes them available for text search. The overhead appears to be sustainable.

Your other choices are pretty much unchanged for the last several releases:

· Turn on the SQL Reporting IFCIDs and generate reports out of SMF or similar tool

· Write an exit routine for the purpose

· Limit access to the tables to particular SQLIDs (this limits your people but doesn’t identify the information they’re retrieving).

· Limit access to the tables to particular SQL, by encapsulating in stored procedures and disallowing other access

Anyone else “tuned in” on this is invited to add to the list!!!

-phil (sevetson)

From: Aurora Emanuela Dellanno [mailto:[login to unmask email]
Sent: Tuesday, June 11, 2019 8:27 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 z/OS: SYSIBM.SYSAUDITPOLICIES too much data


hi all,



I have had a look at previous posts in the list about the use of audit policies, and I actually was wondering about direct experience of any of you, since we have an audit requirement that is, in fact, ending with unforeseen results in the catalog.



Why? oh, it's all working as designed, say IBM:

"When an audit policy that specifies EXECUTE category is started, it starts IFCIDs 143,144, and 145. Any table that is defined with AUDIT clause will also be audited. Audit policy includes those tables specified with AUDIT clause. . For a table audited through audit policy, you also get additional information in the trace record such as statement ID and every statement in the transaction is audited, not just the first one."



Aside from the logical security/privacy implications, we are seeing a lot of data there, which is a performance hit.



IBM are good enough to say that other customers have had the same result and problems, and that maybe documentation could be improved (minor effort) or, in the long run, "more discussion is needed with two other development teams about exclusively tracing thru the audit policy".



We need to know if and who is accessing a certain group of tables, so we need to go back to the drawing board: any alternative ideas, please?



Thanks.



Aurora

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Aurora Emanuela Dellanno

RE: DB2 z/OS: SYSIBM.SYSAUDITPOLICIES too much data
(in response to Philip Sevetson)

thanks Phil,

 

I was just wondering how other people dealt with the mass of information they get in the catalog, and the cost, with the AUDIT ALL option.

 

This is an interesting thread too, giving me an idea of how to handle it on our side, without giving the interested parties access to our catalog tables:

 

https://www.idug.org/p/fo/et/thread=36683

 

"we run a daily report against our SMF file using DB2/PE,

AUDIT

REDUCE

REPORT LEVEL(DETAIL)

TYPE(ALL)

DB2PM EXEC



This shows who accessed it and the plan/package,

And if is was dynamic SQL, you can see the SQL statement they issued."

 

in the same thread, Raymond Bell pointed out that "it always used to be the Audit class trace would only catch the first read of an object in a UOW. So if the UOW read something perfectly acceptable but then went on to read other objects it wouldn't be recorded", which is another point I need to look into - and yes it's still so and it doesn't really fulfil our audit requirement...

 

again, any input will be helpful.

 

Thanks.

 

Aurora

Chris Duellmann

RE: DB2 z/OS: SYSIBM.SYSAUDITPOLICIES too much data
(in response to Aurora Emanuela Dellanno)

Hello Aurora!

To reply to your note from a BMC perspective, AUDIT reporting in Log Master for Db2 is simply a semantic similarity. One of the key functions is to produce AUDIT reports based on various log analysis techniques. Log Master is looking at existing log records, not IFI trace records, and the execution of an AUDIT report is not dependent in any way on the AUDIT clause setting for a table.

The IFI trace records are generated in real time when the object is updated which does not do us much good if we are looking at log records from, say, last month. So, we are AUDIT setting neutral. That said, Log Master could be used to determine who is accessing those tables!  

IFI tracing tends to be transient. Usually it is turned on and off as required and often at the behest of IBM to track down problems. The BMC product doesn't count on the IFI records existing. It relies on the Db2 Log. With this said, Log Master gives you every chance possible to filter and scan log information and returns it into any standard or customizable report of your choice. Any even archived log file including a 'logical log' can be input for the tool.

 

Regards,

Chris.

 

 

 



In Reply to Aurora Emanuela Dellanno:

thanks Phil,

 

I was just wondering how other people dealt with the mass of information they get in the catalog, and the cost, with the AUDIT ALL option.

 

This is an interesting thread too, giving me an idea of how to handle it on our side, without giving the interested parties access to our catalog tables:

 

https://www.idug.org/p/fo/et/thread=36683

 

"we run a daily report against our SMF file using DB2/PE,

AUDIT

REDUCE

REPORT LEVEL(DETAIL)

TYPE(ALL)

DB2PM EXEC



This shows who accessed it and the plan/package,

And if is was dynamic SQL, you can see the SQL statement they issued."

 

in the same thread, Raymond Bell pointed out that "it always used to be the Audit class trace would only catch the first read of an object in a UOW. So if the UOW read something perfectly acceptable but then went on to read other objects it wouldn't be recorded", which is another point I need to look into - and yes it's still so and it doesn't really fulfil our audit requirement...

 

again, any input will be helpful.

 

Thanks.

 

Aurora