-2

Bonjour friends,

Does anyone have commands or steps to setup Db2 level auditing so we can look for which user(s) is using the database, when (timestamp) it was used and what that user did (eg which queries, tables, schema was accessed)?

Merci Geraldine

mao
  • 11,321
  • 2
  • 13
  • 29
Geraldine86
  • 1
  • 5
  • Is the documentation (in the online Db2 Knowledge Centre) not good enough? You should specify your Db2-server platform (Z/OS, i-series, or Linux/Unix/Windows) because the audit tooling can differ between them. – mao Feb 05 '21 at 08:54
  • thank you mao. Its Db2 11.1.4.5, Linux 64-bit,x86-64. Please share exactly how to do these things Thanks.. – Geraldine86 Feb 05 '21 at 13:09
  • https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0005483.html – mao Feb 05 '21 at 13:11
  • thank you mao, we saw that link but we are unclear on exactly how to achieve the goals if you see my original posts we have specific requirements. Not if we that can be even done by db2 audit, assuming yes, what options/commands would be needed? – Geraldine86 Feb 05 '21 at 13:18
  • Start reading from [The EXECUTE category for auditing SQL statements](https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0050529.html). Briefly: 1. Create Audit Policy with EXECUTE category. 2. Enable database audit with this Policy. 3. Periodically archive the audit log and extract the archived log content to CSV files. All the information you need is in these CSV files, which you may, let's say, load to tables for better further processing. – Mark Barinstein Feb 05 '21 at 15:34

1 Answers1

0

Here is one of good article:
[Db2] Simple test case shell script for db2audit instance and database level auditing
https://www.ibm.com/support/pages/node/1075779

  • Excerpt of the SQLs in the article *
    db2 -v "drop db $DBNAME"
    db2 -v "create db $DBNAME"
    db2 -v "connect to $DBNAME"
    db2 -v "connect to $DBNAME user hoge using hoge"

  • Excerpt of the resutl of instance level auditing from that article *
    STATUS DATABASE USERID
    ----------- -------- ----------
    0 DB1 db2inst1
    0 DB1 db2inst1
    0 DB1 db2inst1
    0 DB1 db2inst1
    -30082 DB1 hoge
    -----

Much more information on it! So, it might be recommended to review this article.

Dharman
  • 30,962
  • 25
  • 85
  • 135
hidehy
  • 179
  • 5