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

IBM Data Warehouse Edition DWH Password Maze

Stringent user account security policy in the network domain can cause damaging maintenance headache in deployed IBM DWH multiservers environment. User account information are all around the places, in your DB2 services, WAS server, Alphablox and so on. The day when the user passwords expired or required account disabled, it will be the day DWE solutions face total outrage. Well, may be I'm just exaggerating.

Where do you update the user credentials in DWE environment when such a need arise?

Briefly speaking, at least the following locations:

1. DB2 Windows services, assuming Windows environment

Log On As for each DB services need to be updated.


2. Websphere Global Security Setting, assuming using LocalOS repository

This can be tricky. The easiest is to update the password before you shut down the WAS server. If the server already shut down and you didn't manage to update the password, then you wouldn't be able to start the server again because of authentication error. If this is the case, you got to manually disable the WAS global security by changing the "enabled" attribute of security:Security xml element to false in security.xml file located in /config/cells/Cell. Then start the server, update the password in LocalOS setting and turn on Global Security again by checking on the option in WAS Admin Console.

3. Data Sources defined in DWE Admin Console

Data Sources used by DWH application processes, which are not attached to WAS data source, must be updated.

Before you can perform this, you need to update the J2C user password in WAS for Admin Console to be able to connect to its repository. (Item 6)

4. Data Sources defined in Alphablox Admin Console

Usually this will be data sources for Alphablox cubes to retrieve IBM Cube Views meta data.

5. WAS account used by Alphablox for management

Alphablox uses a WAS user credential for connecting to WAS and managing Alphablx applications in WAS. This piece of information is located in Alphablox repository, /servers/AlphabloxAnalytics/server.properties.

Replace the line ws.admin.password.protected with ws.admin.password=<your_password_in_plaintext>

The issue here is that the new password will be in clear text. I read across some materials that say the password is encoded again the next time the server restarted. However, I don't see that happens in my environment.

6. WAS J2C Authentication entries

7. WAS JNDI Data Sources, assuming not using J2C authentication

8. Optionally, WAS Windows Services


Hope this is helpful to you.

Generalization and Specialization

At some stages in life, you will suddenly have a desire to do a turn-around in your career, whether to continue specializing something or generalizing to handle more tasks. Face it, people who are specialized in an area for ages will have difficulties in adapting to the idea of multi-area + multi-process + multi-tasking. The same happened for generalized worker, they might have phobia of scaring entering a job dead-end or ceasing of learning curve. Anyway, ignore what I had said, these are just crappy murmuring.

Here's the meat. There are reasons why you need certified and well trained personnel to deploy your applications into production environments. One of it is that they always got some well-kept secrets that make them different from typical persons who try to be hero or force to be. For the sake of goodness of your enterprise, pay whatever that is necessary to get the job done properly. Cheapskate is not the way to survive.

Like thousands of others outside, me as a generic person, sometimes need to do stuff i'm not good at (or at least not at the current moment). Last week, I setup a Websphere Application Server in a machine which is Windows domain member. Naively, I just do whatever I did in the company test environment, thought it will turns on and run flawlessly. Well, most of the features do.

I had this problem of obtaining list of Windows groups and users from the LocalOS registry when try to map security roles in the deployed applications. WAS smartly returned me "*null" message on the screen and some "User not found" or "Not authorized" or "Password something" in the logs.

Stratching my nearly bald head and suspecting something to do with Windows domain, I look up the WAS 6 information center, and search for LocalOS registry. The fact is that WAS has different setting requirements for standalone machine, domain member and domain controller if you are using LocalOS.

A quick fix will be to add "com.ibm.websphere.registry.UseRegistry" custom property with the value "local" to the LocalOS custom property sheet. This will explicitly stop the WAS from querying domain registry for list of groups and users (That's my requirement, your's might be different). If you want it to get the list from both domain and local registry, then read on the documentation, there are a list of things to set up for the user who starts WAS process.

This is just one issue that I encountered so far, however just cross my finger and hopes there are no others.


The risks of being not specialized.

Alphablox Cube Security

I really headache when customer want to impose security constraints on Alphablox solution. Here is a simple security problem that I need to solve.

A local bank in Malaysia has 14 main branches throughout the country where each state got one main branch. There is at least 2 groups of users with different level of data access. One group is country wide users who can view the data for all the states. Another group being state manager which can only sees their own cake.

My first attempt is defining a user property called BelongingState and assign it state code that user belongs to, or "ALL" if they are country wide users. Then I would use Java codes to dynamically construct the MDX for the DataBlox. Thought this will solve my problem, but some user actions would causes rewrite of the MDX automatically (Seems like the default behavior). For example, Show Siblings action will display all members on the same level. This totally defeats my aim of controlling view by Malaysia state. Another one is the Drill Up action. I also found Member Filter dialog and Drill Down (There are 5 types of Drill Down available) action can causes exploitation on data.

Due to lack of time to spend on digging deep into Alphablox object model, I revert to a quick resolution: use removeAction attribute to disable Drill Up, Member Filter and Show Siblings for state level users. At the same time, I wrote a filter on Drilldown event to check the drill down option and prevent the drill down to inappropriate data area. This cut off a lot of interactivity from user, but for the sake of security, some trade offs gotta be made.

Giving some faith to developers who wrote Alphablox framework, I believe there should be some methods to disable MDX rewrite due to user activity, thus preventing the underlying data set from changing. One thing to note if that if you are using MSAS or Essbase as cube source, then you can rely on the native security control features of these cube engines for security. Particularly you can use MemberSecurity tag for this purpose. For Cube View based cubes, may be it could be possible to control the view at database level.

Back to square one, my point of bringing this topic up is that it is important to plan for security requirements to match the out of the box security features provided by Alphablox. For my simple security scenario, I personally think that it is more usable to create 14 identical cubes, each for different state. By controlling which cube that users use in their report, you can be sure that the data view doesn't violate the security expectation, yet retaining powerful interactivity actions such as Member Filter and Drill Up. Things can get pretty ugly and complicated when the security requirements are not just on one dimension. In this case, creating separate cube for different data view might not be practical.

Lastly, localization is another aspect of global business intelligence application that can be as tricky as security factor to implement in Alphablox solution. I really need to appraise Microsoft Analysis Services (MSAS) for incorporating security and localization so well in their cubes. Maybe this is what differentiates market leader and players.