DB2 Logs

Mohamed Esmael

DB2 Logs

Hello All,
I am trying to identify which user deleted some records or which user violate security 
Does anyone know of a way to tell from the logs what user has updated a table?

We are on version DB2 V10  for z/os v2.1. Any help on this will be greatly appreciated.

Roy Boxwell

DB2 Logs
(in response to Mohamed Esmael)
Without tooling it is very nasty... You must just trawl the log but it is *not* trivial! You must find out the DBID, OBID of the table you are interested in (which might have been dropped by now...) and you must work out which RBA/LRSN you are interested in and then the hard bit starts as you must post-process the output of the LOGP utility... I would rate this as a “hopeless task with no chance of success” without 3rd Party Tools...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Thursday, October 19, 2017 11:15 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Logs


Hello All,
I am trying to identify which user deleted some records or which user violate security
Does anyone know of a way to tell from the logs what user has updated a table?

We are on version DB2 V10 for z/os v2.1. Any help on this will be greatly appreciated.

-----End Original Message-----

Mohamed Esmael

RE: DB2 Logs
(in response to Roy Boxwell)

Dear Roy

Thanks  for replying , what about logs that display admin activity like (Audit Grant / Revoke) ? 

Roy Boxwell

DB2 Logs
(in response to Mohamed Esmael)
Unless you have had the IFCID 90/91 running all the time you must still trawl the logs... There are some pointers out there (IDUG Presentations all about the LOG etc.) but it is pretty horrible...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Thursday, October 19, 2017 12:24 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Logs


Dear Roy

Thanks for replying , what about logs that display admin activity like (Audit Grant / Revoke) ?

-----End Original Message-----

Steen Rasmussen

DB2 Logs
(in response to Mohamed Esmael)
In fact, GRANT, REVOKE etc. all is “SQL” behind the scenes doing insert,delete,update of rows in the catalog tables so these must be handled the same way – meaning you will have to traverse the Db2-log to find these based on the DBID, OBID for the Db2-catalog tables.
However, if you look at the Db2 12 catalog tables, it seems like IBM is getting ready to enable SYSTEM TIME TEMPORAL tables for all the AUTH tables, so sometime in the future you will get the historical view of who has been granting/revoking, but these are not available yet.

Steen Rasmussen

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Thursday, October 19, 2017 6:24 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Logs

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Dear Roy

Thanks for replying , what about logs that display admin activity like (Audit Grant / Revoke) ?

-----End Original Message-----

Mohamed Esmael

RE: DB2 Logs
(in response to Roy Boxwell)

i bit confusing about active logs and auditing logs that come from smf files 

what the content / information on Active logs?

if i want to audit Admins Activity or audit administrative authorities i must enable trace so start audit   

Mohamed Esmael

RE: DB2 Logs
(in response to Steen Rasmussen)

hello steen 

we using RACF Access control , so can i from racf log to display administrative authorities 

Steen Rasmussen

DB2 Logs
(in response to Mohamed Esmael)
You can display authorisations / who can do what but you can’t tell who executed what – for that you need to traverse the DB2-log or run the appropriate SMF reports with the necessary IFCID’s etc. being active.

Steen

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Thursday, October 19, 2017 7:50 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Logs

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

hello steen

we using RACF Access control , so can i from racf log to display administrative authorities

-----End Original Message-----

Mohamed Esmael

RE: DB2 Logs
(in response to Steen Rasmussen)

so if i want to know who execute what , use smf report after activating appropriate smf type with necessary ifcid

Roy Boxwell

DB2 Logs
(in response to Mohamed Esmael)
Basically yes, but the devil is in the details...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Thursday, October 19, 2017 2:30 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Logs


so if i want to know who execute what , use smf report after activating appropriate smf type with necessary ifcid

-----End Original Message-----

Phil Grainger

DB2 Logs
(in response to Roy Boxwell)
Not completely hopeless – remember Roy, we used to not HAVE any tools

Highlighting pens were useful though ☺

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Boxwell, Roy [mailto:[login to unmask email]
Sent: 19 October 2017 11:17
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Logs

Without tooling it is very nasty... You must just trawl the log but it is *not* trivial! You must find out the DBID, OBID of the table you are interested in (which might have been dropped by now...) and you must work out which RBA/LRSN you are interested in and then the hard bit starts as you must post-process the output of the LOGP utility... I would rate this as a “hopeless task with no chance of success” without 3rd Party Tools...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de_&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=4CTlgCE8Fs45C9bPIs82xxwhuopm9jeylNtnxmAnD9o&s=Hhrl14PmN9Wcf8B1Yc4KLekRDx30vpCaNJaiTFpJQ6s&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Thursday, October 19, 2017 11:15 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 Logs


Hello All,
I am trying to identify which user deleted some records or which user violate security
Does anyone know of a way to tell from the logs what user has updated a table?

We are on version DB2 V10 for z/os v2.1. Any help on this will be greatly appreciated.

-----End Original Message-----

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)

David Baldon

DB2 Logs
(in response to Mohamed Esmael)
It may be doable but the standard answer of “it depends” comes into play here. As several have already pointed out the basic utilities will get you there unless of course the data on the table in question is compressed. If that’s the case, it’s whole nother ballgame as we say here in America. There are multiple presentations “out there” that will get you started but be prepared to spend a lot of time on this. It isn’t trivial and therefore not for the faint of heart (another Americanism?).

…David

From: Mohamed Esmael [mailto:[login to unmask email]
Sent: Thursday, October 19, 2017 4:15 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Logs


Hello All,
I am trying to identify which user deleted some records or which user violate security
Does anyone know of a way to tell from the logs what user has updated a table?

We are on version DB2 V10 for z/os v2.1. Any help on this will be greatly appreciated.

-----End Original Message-----

Rob Barbour

RE: DB2 Logs
(in response to Mohamed Esmael)

Hi Esmael,

You may want to look at ULT4DB2 a log analyzer & tracker.   See web page ULT4DB2 for more info.

--
Best Regards,
Rob Barbour
Enterprise Systems Associates, Inc ("ESAi")
UCF Research Park
3259 Progress Drive
Orlando,  Florida  32826    USA  
Toll Free: 1-866-GO-4-ESAI (1-866-464-3724)
http://www.ESAIGroup.com/products

Visit us at the next IDUG & SHARE Conferences twitter.com/ESAiSoftware
z Performance... Productivity for DB2... Services...
BCV4 -  DB2, SAP, PeopleSoft Clones/Refreshes in Minutes vs Days
BCV5/BCV6 -  On-Demand DB2 Refresh / Migrate in 1/10th Time & Effort
XDM - Test Data Mgmt & Masking for DB2,Oracle, SQL Server et.al.
ULT4DB2 - Faster, Better Value in DB2 Log Analyzers w/ PROP, & Audit
BPA4DB2 - Save $, Improve DB2 Performance with Buffer Pool Analyzer
XM4DB2 - Proactive Approach for DB2,Performance, & Dynamic SQL
SQLQC - Find, Analyze, Improve SQL Quality Control and Performance

COST Optimization - Lower Mainframe MLC Software Costs
INSPECT-CPU - Improve CICS Application Performance with ICPU


In Reply to Mohamed Esmael:

Hello All,
I am trying to identify which user deleted some records or which user violate security 
Does anyone know of a way to tell from the logs what user has updated a table?

We are on version DB2 V10  for z/os v2.1. Any help on this will be greatly appreciated.

Rob Barbour

RE: DB2 Logs
(in response to Mohamed Esmael)

RESEND w/CORRECTION to link.

Hi Esmael,

You may want to look at ULT4DB2 a log analyzer & tracker.   See web page ULT4DB2 for more info.

--
Best Regards,
Rob Barbour
Enterprise Systems Associates, Inc ("ESAi")
UCF Research Park
3259 Progress Drive
Orlando,  Florida  32826    USA  
Toll Free: 1-866-GO-4-ESAI (1-866-464-3724)
http://www.ESAIGroup.com/products

Visit us at the next IDUG & SHARE Conferences twitter.com/ESAiSoftware
z Performance... Productivity for DB2... Services...
BCV4 -  DB2, SAP, PeopleSoft Clones/Refreshes in Minutes vs Days
BCV5/BCV6 -  On-Demand DB2 Refresh / Migrate in 1/10th Time & Effort
XDM - Test Data Mgmt & Masking for DB2,Oracle, SQL Server et.al.
ULT4DB2 - Faster, Better Value in DB2 Log Analyzers w/ PROP, & Audit
BPA4DB2 - Save $, Improve DB2 Performance with Buffer Pool Analyzer
XM4DB2 - Proactive Approach for DB2,Performance, & Dynamic SQL
SQLQC - Find, Analyze, Improve SQL Quality Control and Performance

COST Optimization - Lower Mainframe MLC Software Costs
INSPECT-CPU - Improve CICS Application Performance with ICPU



In Reply to Mohamed Esmael:

Hello All,
I am trying to identify which user deleted some records or which user violate security 
Does anyone know of a way to tell from the logs what user has updated a table?

We are on version DB2 V10  for z/os v2.1. Any help on this will be greatly appreciated.

Roy Boxwell

DB2 Logs
(in response to Phil Grainger)
Do not remind me... trying to guess an LRSN to be „about right“... pretty traumatic in production!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 19 Oct 2017, at 15:19, Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Not completely hopeless – remember Roy, we used to not HAVE any tools

Highlighting pens were useful though ☺

-----End Original Message-----
-----End Original Message-----
________________________________
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
-----End Original Message-----

Mick Graley

DB2 Logs
(in response to Roy Boxwell)
And of course "back then" we didn't have RRF to throw into the mix!!!
Cheers,
Mick.


On 19 October 2017 at 16:25, Boxwell, Roy <[login to unmask email]> wrote:

> Do not remind me... trying to guess an LRSN to be „about right“... pretty
> traumatic in production!
>
> Roy Boxwell
> SOFTWARE ENGINEERING GmbH and SEGUS Inc.
> -Product Development-
> Heinrichstrasse 83
> https://maps.google.com/?q=Heinrichstrasse+83&entry=gmail&source=g -85
> 40239 Düsseldorf/Germany
> Tel. +49 (0)211 96149-675 <+49%20211%2096149675>
> Fax +49 (0)211 96149-32 <+49%20211%209614932>
> Email: [login to unmask email]
> http://www.seg.de
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Bettina Schubert
>
> On 19 Oct 2017, at 15:19, Grainger, Phil <[login to unmask email]> wrote:
>
> Not completely hopeless – remember Roy, we used to not HAVE any tools
>
>
>
> Highlighting pens were useful though J
>
>
> -----End Original Message-----
> -----End Original Message-----
> -----End Original Message-----
>
>
> -----End Original Message-----
>

Phil Grainger

DB2 Logs
(in response to Mick Graley)
Or compression – at least I could read what I was looking at!

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Mick Graley [mailto:[login to unmask email]
Sent: 19 October 2017 16:45
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Logs

And of course "back then" we didn't have RRF to throw into the mix!!!
Cheers,
Mick.


On 19 October 2017 at 16:25, Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Do not remind me... trying to guess an LRSN to be „about right“... pretty traumatic in production!
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83 https://urldefense.proofpoint.com/v2/url?u=https-3A__maps.google.com_-3Fq-3DHeinrichstrasse-2B83-26entry-3Dgmail-26source-3Dg&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=NSX9wcXwtqZ7K52aiNRxfWEjYJBTNVH8_ij_OM03Dro&s=0GGu4x6-UQJH180ZFxjl5Y--WuWLLCybYCVmgiuGlgI&e= -85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675<tel:+49%20211%2096149675>
Fax +49 (0)211 96149-32<tel:+49%20211%209614932>
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de https://urldefense.proofpoint.com/v2/url?u=http-3A__www.seg.de&d=DwMFaQ&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=NSX9wcXwtqZ7K52aiNRxfWEjYJBTNVH8_ij_OM03Dro&s=IiHhrlR31WkMpY0RVVkuXSAEfa9GMIa7ulp3tFV0IMc&e=

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

On 19 Oct 2017, at 15:19, Grainger, Phil <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Not completely hopeless – remember Roy, we used to not HAVE any tools

Highlighting pens were useful though ☺

-----End Original Message-----
-----End Original Message-----
-----End Original Message-----


-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)