{"id":882,"date":"2011-07-31T14:21:28","date_gmt":"2011-07-31T08:21:28","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=882"},"modified":"2016-01-07T11:31:06","modified_gmt":"2016-01-07T05:31:06","slug":"a-glance-at-db2-audit","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=882","title":{"rendered":"A glance at DB2 Audit"},"content":{"rendered":"

The buzz word for IT security is now audit. \u00c2\u00a0They want to audit activities in database for various compliance reason. \u00c2\u00a0HIPPA, PCI, FDA, SOX , SAS70 and so on requires various levels of audit. \u00c2\u00a0Most of the database software vendors provide certain level of audit facilities within their database engine. \u00c2\u00a0The big three : IBM DB2, Oracle and SQL Server comes with audit features as part of database. \u00c2\u00a0DB2 has DB2 Audit, Oracle has fine grain audit , SQL Server 2008 has SQL audit. \u00c2\u00a0They all do one thing in common – generate i\/o and lots of them ! \u00c2\u00a0So be careful on what all you audit and how you audit it.<\/p>\n

In here, I am going to write about DB2 Audit. \u00c2\u00a0IBM has been developing the audit service over the years and have made it pretty robust and detailed. \u00c2\u00a0They have made the setup and process more easy and intrusive. Starting with DB2 V9.5, IBM has changed the security aspect around db2audit . They have the old db2audit process that runs at the instance level and now have one at the database level as well.<\/p>\n

The db2audit at the instance level audits what is happening under the instance and db2audit at the database level is more focused on activities at the particular database. The security requirements are different for instance and database level audits. \u00c2\u00a0Instance level audits can be run by a SYSADM and database level audit can be run by a SECADM.
\nTo configure instance level audit use the db2audit command<\/a>\u00c2\u00a0. \u00c2\u00a0All the audit administration requests are done using the db2audit command. \u00c2\u00a0If you want to audit database level activities you need to get SECADM authority and then create audit policy which defines what type of events you need to monitor. Then you associates what objects or users you want to audit.<\/p>\n

Lets say for SOX or PCI, you been asked to monitor your credit card ,password table and monitor the activities of your DBAs. Here is what I you would need to do :<\/p>\n

1. For best practices, \u00c2\u00a0I suggest you create a separate mount or directory called \u00c2\u00a0auditfiles, then create three subdirectories underneath it : \u00c2\u00a0auditdata, auditarchives, auditextracts. \u00c2\u00a0The reason why I suggest this is because by default the audit files go to the instance directory and if you are not careful then you will end up filling your instance’s home directory.<\/p>\n

2. Now lets configure db2 audit to send the files to the directory we created. Using a sysadm user id issue :
\ndb2audit configure datapath \/auditfiles\/auditdata<\/span><\/em>
\n db2audit configure archivepath \/auditfiles\/auditarchives<\/span><\/em><\/p>\n

Verify your configurations using \u00c2\u00a0db2audit describe<\/em><\/span><\/p>\n

3. \u00c2\u00a0Connect to the database you want to audit and create the audit tables :
\ndb2 -svtf \/home\/db2inst1\/sqllib\/misc\/db2audit.ddl<\/span><\/em><\/p>\n

4. \u00c2\u00a0Connect to the database you want to audit using a SECADM userid \u00c2\u00a0and create audit policies and then associate the policies to objects.
\ncreate audit policy sensitivedatapolicy<\/span><\/em>
\n categories execute status both error type audit ;<\/span><\/em>
\n audit table \u00c2\u00a0creditcards using policy sensitivedatapolicy;<\/span><\/em>
\n audit table \u00c2\u00a0userid_password using policy sensitivedatapolicy;<\/span><\/em>
\n create audit policy dbaspolicy<\/span><\/em>
\n categories<\/span><\/em>
\n audit status both,<\/span><\/em>
\n sysadmin status both,<\/span><\/em>
\n secmaint status failure,<\/span><\/em>
\n objmaint status both,<\/span><\/em>
\n checking status failure,<\/span><\/em>
\n execute status both,<\/span><\/em>
\n validate status failure<\/span><\/em>
\n error type normal;<\/span><\/em>
\n audit sysadm,dbadm,secadm,group dbadmins using policy dbaspolicy;<\/span><\/em><\/p>\n

5. Its time to start the audit :
\n\u00c2\u00a0db2audit start<\/span><\/em><\/p>\n

Now the database is audited, you can verify by going to \/auditfiles\/auditdata\/ . You should start seeing a file grow as database activities grow.<\/p>\n

6. Schedule a process to periodically flush and archive the auditfiles :
\ndb2audit flush<\/span><\/em>
\ndb2audit archive database rajudb<\/span><\/em><\/p>\n

The db2audit archive database command moves the files to the \/auditfiles\/auditarchives directory you configured in step2.<\/p>\n

7. According to your requirements schedule another process to extract the files and load the data into the audit tables:
\ndb2audit extract delasc to \/auditfiles\/auditextracts from files \/auditfiles\/auditarchives\/db2audit.db.rajudb.log.0.*<\/span><\/em><\/p>\n

This will create all the \u00c2\u00a0delimited files on the auditextracts directory that is need to load the data to the audit tables.<\/p>\n

8. Load the delimited files to the audit tables :
\nload from \/auditfiles\/auditextracts\/execute.del of del modified by delprioritychar lobsinfile insert into execute;<\/span><\/em>
\nload from \/auditfiles\/auditextracts\/audit.del of del modified by delprioritychar lobsinfile insert into audit;<\/span><\/em>
\nload from \/auditfiles\/auditextracts\/sysadmin.del of del modified by delprioritychar lobsinfile insert into sysadmin;<\/span><\/em>
\nload from \/auditfiles\/auditextracts\/secmaint.del of del modified by delprioritychar lobsinfile insert into secmaint;<\/span><\/em>
\nload from \/auditfiles\/auditextracts\/objmaint.del of del modified by delprioritychar lobsinfile insert into objmaint;<\/span><\/em>
\nload from \/auditfiles\/auditextracts\/checking.del of del modified by delprioritychar lobsinfile insert into checking;<\/span><\/em>
\nload from \/auditfiles\/auditextracts\/validate.del of del modified by delprioritychar lobsinfile insert into validate;<\/span><\/em><\/p>\n

Once you can remove the files on the archive and extract directory. \u00c2\u00a0You can change or drop audit policy by using the DROP POLICY or ALTER POLICY command. \u00c2\u00a0Detailed information regarding db2audit can be found at IBM DB2 Information Center<\/a>. \u00c2\u00a0To stop the audit process use the command : \u00c2\u00a0db2audit stop<\/span><\/em><\/p>\n

Even though DB2 Audit can be setup relatively easily, be careful on what you monitor as it can generate lot of data. Be very selective on what you want to monitor.
\n <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

The buzz word for IT security is now audit. \u00c2\u00a0They want to audit activities in database for various compliance reason. \u00c2\u00a0HIPPA, PCI, FDA, SOX , SAS70 and so on requires various levels of audit. \u00c2\u00a0Most of the database software vendors provide certain level of audit facilities within their database engine. \u00c2\u00a0The big three : IBM […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,3,358],"tags":[476,655,650,654,657,943,474,652,653,651,73,475,477,656],"class_list":["post-882","post","type-post","status-publish","format-standard","hentry","category-database","category-db2","category-how-to","tag-audit-database","tag-audit-policies","tag-audit-policy","tag-auditing-in-db2","tag-configuring-db2-audit","tag-db2","tag-db2-audit","tag-db2-pci","tag-db2-sox-audit","tag-db2-track-changes","tag-db2audit","tag-security","tag-setting-up-db2-audit","tag-starting-db2-audit"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/882","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=882"}],"version-history":[{"count":9,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/882\/revisions"}],"predecessor-version":[{"id":1500,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/882\/revisions\/1500"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=882"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=882"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=882"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}