UPDATE: IBM DB2 Data Warehouse Edition Password Maze

A new finding on the Alphablox wsadmin.password parameter in /server/AlphabloxAnalytics/server.properties is that after you replaced the wsadmin.password.protected with wsadmin.password to change the password, you will need to make some (dummy) changes to the Administration page in Alphablox Console for the plain text password to be automatically encrypted by Alphablox. Restart of applications or server is not necessary.

DB2 Audit Facility for Dummy

IT security auditors came and approached me, asking about DB2 user query activities, security events and blah blah. Let me see, o yeah, we got application specific audit trails. HR system got their own, ERP too and not forgetting that small little ETL program that IS people wrote. Of course, having domain specific audit facility is not a sin and they are one of the standard practice. However, DB2 provides an audit facility which is generic enough to capture almost any events happened in your databases.

Check out this DB2 program, located at <DB2_INSTALL_PATH>\bin

db2audit

Before you start to use db2audit, you should configure the AUDIT_BUF_SZ parameter in DBM configuration file. By the way, db2audit is controlled at Instance level. Setting AUDIT_BUF_SZ to non-Zero value indicates a multiple of 4KB. This is the buffer size before the audit records written to disk.

Then, you can check the current settings of db2audit, by using this command

db2audit describe

You will see something like below:

DB2 AUDIT SETTINGS:

Audit active: "FALSE "
Log errors: "TRUE "
Log success: "TRUE "
Log audit events: "TRUE "
Log checking events: "TRUE "
Log object maintenance events: "TRUE "
Log security maintenance events: "TRUE "
Log system administrator events: "TRUE "
Log validate events: "TRUE "
Log context events: "TRUE "
Return SQLCA on audit error: "TRUE "

AUD0000I Operation succeeded.

For example, if I just want to log audit events for authentication and authorization, both when success and failed, and rollback application if the system unable to generate the audit logs, perhaps due to system failure. Then I use this command:

db2audit configure scope checking,validate status both errortype audit

I will get the results below, when I submit "db2audit describe" again

DB2 AUDIT SETTINGS:

Audit active: "FALSE "
Log errors: "TRUE "
Log success: "TRUE "
Log audit events: "FALSE "
Log checking events: "TRUE "
Log object maintenance events: "FALSE "
Log security maintenance events: "FALSE "
Log system administrator events: "FALSE "
Log validate events: "TRUE "
Log context events: "FALSE "
Return SQLCA on audit error: "TRUE "

AUD0000I Operation succeeded.

You can enumerate the list of possible values for SCOPE, STATUS and ERRORTYPE by just typing "db2audit" and submit.

After configuring the audit facility, to start the facility

db2audit start

To stop the facility

db2audit stop

And, whenever you set AUDIT_BUF_SZ to non-ZERO, you should also use

db2audit flush

for writing the buffer to disk.

2 output file formats are supported by the native extraction: Flat and Delimited.

Use "db2audit extract" command to extract the logs. For example, if I need the audit records for authentication and authorization events, in comma delimiter format for database PROD, then I use the command below:

db2audit extract delasc DELIMITER , category checking, validation database

This will generates a list of files with .DEL extention in <INSTANCE_DIRECTORY>\security folder. You can load the files into database by using LOAD or IMPORT utility.

Audit file will grows over time and you need to perform house keeping on it occasionally. To remove all audit records, use

db2audit prune all

Or more likely you will want to remove records prior to certain date.

db2audit prune date YYYYMMDDHH

E.g. db2audit prune date 2007050100 will delete all records where date prior to 1-May-2007.


Lastly, additional information you might need to aware of

Only SYSADM group members can perform auditing actions

Audit Configuration File located at <INSTANCE_DIR>\security\db2audit.cfg, and it is in binary format

Audit Log File located at <INSTANCE_DIR>\security\db2audit.log, and it is in binary format

By setting DB2INSTANCE environment variable, you can configure audit for different DB2 instances

DB2 Restore DB Command

Personally, I prefer to use Restore DB DB2 Command, instead of that Restore Database Wizard in Control Center. Don't know why, maybe I easily get confused by buttons and drop downs. ;-)

Here I will run down a simulation of backing up and restoring the database under different name in separate DB2 instance resides within same physical machine. Similar scenario might be to replicate databases for development, testing and production.

First, Fire up the Command Window (db2cmd)

Create the Development instance:

db2icrt DEV

Start the DEV instance

SET DB2INSTANCE=DEV
db2start

Create the sample database

CREATE DB DEVDB

Check out the list of tablespaces in the database, this is important when later you need to redirect the creation of tablespace containers.

db2 connect to DEVDB
db2 list tablespaces

Backup the sample DEVDB database. This will create a folder DEVDB.0 under C:

db2 BACKUP DB DEVDB TO C:

Create the Production instance:

db2icrt PROD

Start the PROD instance

SET DB2INSTANCE=PROD
db2start

Restore the backup DEVDB under C: to PRODDB in PROD instance.

db2 restore db DEVDB FROM C: INTO PRODDB redirect
db2 set tablespace containers for 0 using (path 'C:\container\tspace00c1')
db2 set tablespace containers for 1 using (path 'C:\container\tspace01c1')
db2 set tablespace containers for 2 using (path 'C:\container\tspace02c1')
db2 restore db DEVDB continue

Try to connect to the new PRODDB restored from DEVDB

db2 connect to PRODDB

The trick here is to specify a separate set of container paths for Restore DB command. This will depends on number of tablespaces used by your DB. Default is only 3 tablespaces: SYS, TEMP and USER.


To clean up the simulation, do the following:

set DB2INSTANCE=DEV
db2stop force
set DB2INSTANCE=PROD
db2stop force

db2idrop DEV
db2idrop PROD

Manually remove C:\DEV, C:\PROD, C:\DEVDB.0 and C:\container