SA03: IBM Audit SupportPac for WebSphere Service Registry and Repository

The SupportPac consists of the following:

• Audit J2EE Application
• Service Registry Audit Notifier plug-in
• Audit Database and Tables
• Sample reports

Table of contents

• Pre-requisite software
• Installation instructions
  • Audit Database and Tables
    • DB2 Database
    • Derby Database
    • Oracle Database
    • DB2 on z/OS Database
  • WebSphere Data Source and JDBC Provider
    • DB2
    • Derby
    • Oracle
    • All Databases
    • DB2 on z/OS specific properties
    • Test the datasource
  • Audit J2EE Application
  • Service Registry Audit Notifier plugin
• Auditing Overview
• Using Tivoli Common Reporting
  • Installing
  • Configuring
  • Deploying the sample reports
  • Changing the Data Source settings on a report
  • Derby Network Server
  • Running a report
• Using the command line report tool
  • Sample Report Queries
• Archiving and purging audit data
• Database schema
  • Audit event table
  • Object table
• Troubleshooting

Pre-requisite software

The database server minimum requirements are as for WebSphere Service Registry and Repository version 6.1 Fix Pack 1.

Installation instructions

Audit Database and Tables

DB2 Database

1. Create the database. If you want to use an existing database skip this step.
   1. Edit $SQLPATH/createdb.sql
      1. Replace all instances of __DB2TSDIR__ with the directory where the tablespaces are stored (the value of $DB2TSDIR).
      2. Replace all instances of __DBNAME__ with the value of $DB_NAME
      3. Replace all instances of __DBUSER__ with the value of $DB_USER
   2. Make sure your DB2 instance is configured for TCP/IP access, for example as $DB2INSTID, and replacing $DB2INST with your DB2 instance name, run:

      db2set DB2COMM=tcpip
      db2 update dbm cfg using SVCENAME db2c_$DB2INST

      Ensure that the db2c_$DB2INST service is defined in /etc/services.

      db2stop
      db2start

   3. On Windows, run:

      db2set DB2_CREATE_DB_ON_PATH=YES
      db2stop
      db2start

   4. As $DB2INSTID run:

      db2 -tf $SQLPATH/createdb.sql
      db2 connect reset 
      db2 terminate

2. Create the database tables.
   1. As $DB2INSTID run:

      db2 connect to $DB_NAME user $DB_USER using $DB_PASSWORD

      db2 set current schema $DB_USER

      db2 -tf $SQLPATH/createtables.sql

Derby Database

1. Create the database. If you want to use an existing database skip this step.
   1. Edit $SQLPATH/createdb.sql
      1. Replace all instances of __DBNAME__ with the value of $DB_NAME
      2. Replace all instances of __DBUSER__ with the value of $DB_USER
      3. Replace all instances of __DBPASSWORD__ with the value of $DB_PASSWORD
   2. From $WAS_HOME\derby\bin\embedded Run:

      ij -p $WAS_HOME\derby\derby.properties createdb.sql

2. Create the database tables.
   1. Edit $SQLPATH/createtables.sql
      1. Replace all instances of __DBNAME__ with the value of $DB_NAME
      2. Replace all instances of __DBUSER__ with the value of $DB_USER
      3. Replace all instances of __DBPASSWORD__ with the value of $DB_PASSWORD
   2. From $WAS_HOME\derby\bin\embedded Run:

      ij -p $WAS_HOME\derby\derby.properties createtables.sql

3. Configure the DB2UNIVERSAL_JDBC_DRIVER_PATH. You must now configure the DB2UNIVERSAL_JDBC_DRIVER_PATH WebSphere^® variable in WebSphere Application Server.

   If using a remote database, install the DB2 client to the WebSphere Application Server first, and then set DB2UNIVERSAL_JDBC_DRIVER_PATH to the directory in the client which contains db2jcc.jar, db2jcc_license_cu.jar and db2jcc_license_cisuz.jar.

   If using a cluster, configure DB2UNIVERSAL_JDBC_DRIVER_PATH at the node level for every node the Audit Application is installed on, otherwise at node level scope.

   Set DB2UNIVERSAL_JDBC_DRIVER_PATH to the directory containing db2jcc.jar, db2jcc_license_cu.jar and db2jcc_license_cisuz.jar, for example C:/Program Files/IBM/SQLLIB/java. From the WebSphere Application Server Integrated Solutions Console:

   1. Expand Environment -> WebSphere Variables.
   2. Set the scope appropriately.
   3. Click DB2UNIVERSAL_JDBC_DRIVER_PATH or, if the variable does not exist at the selected scope, Click New and enter DB2UNIVERSAL_JDBC_DRIVER_PATH in the Name field.
   4. In the Value field, enter the path.
   5. Click OK.
   6. On the message Changes have been made to your local configuration click Save.

Oracle Database

1. Create the database. If you want to use an existing database skip this step.
   1. Edit $SQLPATH/dbca.rsp
      1. Replace all instances of __VARFILEPATH__ with the directory where variables.txt can be found ($SQLPATH).
      2. Replace all instances of __DBNAME__ with $DB_NAME
   2. As $ORAUSER run:

      $ORACLEPATH/bin/dbca -silent -responseFile $SQLPATH/dbca.rsp

   3. Set the password for the SYSTEM and SYS database users to be $DB_PASSWORD, or some other appropriate value, if you are going to be creating a new database user.

      You can, optionally, use the following script to do this. Edit install/database/sql/oracle/dbaccess.sql

      1. Replace all instances of __DBNAME__ with $DB_NAME.
      2. Replace all instances of __DBPASSWORD__ with $DB_PASSWORD.
      3. As $ORAUSER run:

         $ORACLEPATH/bin/sqlplus /nolog @$SQLPATH/dbaccess.sql

2. Create a database user. If you want a user ID other than SYSTEM for $DB_USER, create that user ID now using the normal Oracle process. The user ID will have privileges similar to those detailed in the following (this might not be an exhaustive list):

   SELECT ANY DICTIONARY, UNLIMITED TABLESPACE, "AQ_ADMINISTRATOR_ROLE", "AQ_USER_ROLE", "CONNECT", "DELETE_CATALOG_ROLE", "EJBCLIENT", "EXECUTE_CATALOG_ROLE", "EXP_FULL_DATABASE", "GATHER_SYSTEM_STATISTICS", "MGMT_USER", CREATE TABLE, CREATE DATABASE LINK, CREATE SESSION, CREATE SEQUENCE, CREATE TABLESPACE, ALTER TABLESPACE, MANAGE TABLESPACE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, CREATE VIEW

3. Create the database tables. As $ORAUSER run:

   $ORACLEPATH/bin/sqlplus $DB_USER/$DB_PASSWORD@$DB_NAME @$SQLPATH/createtables.sql

4. Configure the ORACLE_JDBC_DRIVER_PATH. You must now configure the ORACLE_JDBC_DRIVER_PATH WebSphere^® variable in WebSphere Application Server.

   If using a remote database, install the Oracle client to the WebSphere Application Server first, and then set ORACLE_JDBC_DRIVER_PATH to the directory in the client which contains ojdbc14.jar.

   If using a cluster, configure ORACLE_JDBC_DRIVER_PATH at the node level for every node the Audit Application is installed on, otherwise at node level scope.

   Set ORACLE_JDBC_DRIVER_PATH to the directory containing ojdbc14.jar, for example /home/oracle/product/10.2.0/Db_1/jdbc/lib

   From the WebSphere Application Server Integrated Solutions Console:
   1. Expand Environment -> WebSphere Variables.
   2. Set the scope appropriately.
   3. Click ORACLE_JDBC_DRIVER_PATH or, if the variable does not exist at the selected scope, Click New and enter ORACLE_JDBC_DRIVER_PATH in the Name field.
   4. In the Value field, enter the path.
   5. Click OK.
   6. On the message Changes have been made to your local configuration click Save.

5. Configure the Oracle nls_timestamp_format. You must now configure the initialization parameter nls_timestamp_format in the database Initialization Parameter File. This is typically located:

   $ORACLEPATH\database\init$DB_NAME.ora

   This should be set to:

   nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FFF'

   Refer to your operating system specific Oracle documentation for the default location of the initialization parameter file.

DB2 on z/OS Database

If the z/OS tar was untarred into the deployment directory of WSRR, the scripts will be in the subdirectory Admin/scripts/sql/db2_ZOS. Otherwise the scripts need to be copied onto the z/OS system to the deployment directory of WSRR, for example /u/wsrr/WebSphereServiceRegistry/Admin/scripts/sql/db2_ZOS

1. Change directory to Admin/scripts/sql/db2_ZOS and run script copyddl.sh to copy the DB2 SQL and JCL to PDSs:

   copyddl.sh -sqldsn <SQL_DSN> -jcldsn <JCL_DSN>

   Where:

   Parameter	Description
   <JCL_DSN>	The fully qualified name of the PDS to contain JCL. It must be LRECL=80 RECFM=FB and will be created by the script if it does not exist.
   <SQL_DSN>	The fully qualified name of the PDS to contain SQL. It must be LRECL=80 RECFM=FB and will be created by the script if it does not exist.

2. Edit the two SQL members to conform with your site's DB2 naming standards. Change all occurrences of the following in each member of the SQL data set:

   Parameter	Description
   <SQLID>	The required DB2 SQLID, for example WSRRUSR. It is recommended that this should be a unique SQLID for WSRR.
   <AUDIT-DATABASE>	The name for the Audit database, for example AUDITDB.
   <AUDIT-TABLESPACE>	The name for the Audit tablespace in the Audit database, for example AUDITTS.

3. Edit the two JCL members to conform with your site's DB2 standards. Change all occurrences of the following in each member of the JCL data set:

   Parameter	Description
   <SDSNLOAD>	The name of the target DB2 SDSNLOAD library.
   <RUNLIB>	The name of the target DB2 RUNLIB library.
   <SSID>	The name of the target DB2 Subsystem ID.
   <PLAN-NAME>	The name of the target DSNTIAD DB2 plan name.

4. Create the DB2 databases and tables using the modified SQL. Either:
   • Submit the modified jobs in the JCL library, or
   • Use the SQL in SPUFI or other DB2 tool.

If using a remote database, install the DB2 client for z/OS to the WebSphere Application Server first, and then set DB2UNIVERSAL_JDBC_DRIVER_PATH to the directory in the client which contains db2jcc.jar, db2jcc_license_cu.jar and db2jcc_license_cisuz.jar.

If using a cluster, configure DB2UNIVERSAL_JDBC_DRIVER_PATH at the node level for every node the Audit Application is installed on, otherwise at node level scope.

Set DB2UNIVERSAL_JDBC_DRIVER_PATH to the directory containing db2jcc.jar, , db2jcc_license_cu.jar and db2jcc_license_cisuz.jar, for example C:/Program Files/IBM/SQLLIB/java. From the WebSphere Application Server Integrated Solutions Console:

1. Expand Environment -> WebSphere Variables.
2. Set the scope appropriately.
3. Click DB2UNIVERSAL_JDBC_DRIVER_PATH or, if the variable does not exist at the selected scope, Click New and enter DB2UNIVERSAL_JDBC_DRIVER_PATH in the Name field.
4. In the Value field, enter the path.
5. Click OK.
6. On the message Changes have been made to your local configuration click Save.

WebSphere Data Source and JDBC Provider

Following are the settings for the JDBC Provider for your particular Database System.

Database System	Database type	Provider type	Implementation type
DB2 Distributed	DB2	DB2 Universal JDBC Driver Provider	XA data source
Derby	Derby	Derby JDBC Provider	XA data source
Oracle	Oracle	Oracle JDBC Provider	XA data source
DB2 z/OS	DB2	DB2 Universal JDBC Driver Provider	XA data source

Name	Value
$JDBC_PROVIDER_NAME	WSRR Audit JDBC Provider
$J2C_ALIAS_NAME	WSRR Audit Alias
$DATASOURCE_NAME	WSRR Audit DataSource
$JNDI_NAME	jdbc/WSRRAUDIT

To create the JDBC Provider:

1. From the WebSphere Application Server Integrated Solutions Console, expand Resources->JDBC->JDBC Providers.
2. If in a cluster, set the scope to cluster level, if not, set to the node level.
3. Click the New button.
4. For the fields Database type, Provider type and Implementation type enter the values described above.
5. Enter a descriptive name such as $JDBC_PROVIDER_NAME.
6. Click Next.
   • For Derby the Summary is shown, click Finish.
   • For DB2 and Oracle, leave the field "directory location for JARs" with the default value, then click Next and then click Finish.

1. From the WebSphere Application Server Integrated Solutions Console, expand Security->Secure administration, applications, and infrastructure.
2. Expand Java Authentication and Authorization Service.
3. Click J2C authentication data.
4. Click New.
5. Enter a descriptive name for the Alias field, such as $J2C_ALIAS_NAME.
6. Enter $DB_USER in the User ID field and $DB_PASSWORD in the Password field. For DB2 on z/OS enter the database user and password.
7. Click OK.

1. From the WebSphere Application Server Integrated Solutions Console, expand Resources->JDBC->JDBC Providers.
2. Click the JDBC Provider created in the previous step $JDBC_PROVIDER_NAME.
3. Under Additional Properties click Data sources.
4. Click New.
5. In the Data source name field enter $DATASOURCE_NAME.
6. In the JNDI name field, enter $JNDI_NAME.
7. In the Component-managed authentication alias and XA recovery authentication alias field select $J2C_ALIAS_NAME.
8. Click Next.

DB2

1. In the Database name field, enter $DB_NAME. For DB2 on z/OS enter the Database Location, which can be acquired from DB2 on z/OS.
2. Leave Driver Type as 4.
3. In the Server name field, enter the hostname of the Database Server host.
4. In the Port number enter the TCP/IP port number where the Database server resides.
5. Uncheck the Use this data source in container managed persistence (CMP) checkbox.
6. Click Next.

Derby

1. In the Database name field, enter $DB_NAME.
2. Uncheck the Use this data source in container managed persistence (CMP) checkbox.
3. Click Next.

Oracle

1. In the URL field, enter:

   jdbc:oracle:thin:@$ORASERVER:$ORAPORT:$DB_NAME

   for example:

   jdbc:oracle:thin:@localhost:1521:WSRRAUDI

2. In the Data store helper class name field select Oracle10g data store helper.
3. Uncheck the Use this data source in container managed persistence (CMP) checkbox.
4. Click Next.

All databases

1. Click Finish.
2. On the message Changes have been made to your local configuration click Save.

DB2 on z/OS specific properties

1. From the WebSphere Application Server Integrated Solutions Console, expand Resources->JDBC->JDBC Providers.
2. Click the JDBC Provider created in the previous step $JDBC_PROVIDER_NAME.
3. Under Additional Properties click Data sources.
4. Click the Data Source created in the previous step $DATASOURCE_NAME.
5. Click Custom properties.
6. Find the custom property currentSchema. Click currentSchema to edit its value.
7. On the General Properties screen, set the value to <SQLID>.
8. Click OK.
9. On the message Changes have been made to your local configuration click Save.

All databases

1. Restart the WebSphere Application Server. This binds the Data Source defined into JNDI.

Test the datasource

1. From the WebSphere Application Server Integrated Solutions Console, expand Resources->JDBC->JDBC Providers.
2. Click the JDBC Provider created in the previous step $JDBC_PROVIDER_NAME.
3. Under Additional Properties click Data sources.
4. Select the checkbox next to the Data source $DATASOURCE_NAME.
5. Click the Test connection button.

Audit J2EE Application

• Deploy the <WSRRHOME>/Audit/ServiceRegistryAudit.ear J2EE application to the same WebSphere Application Server that is running WebSphere Service Registry and Repository. Use the WebSphere Application Server Integrated Solutions Console to do this. For a cluster, deploy on the Deployment Manager and ensure the application will be deployed across the cluster.
• Start the ServiceRegistryAudit application. Use the WebSphere Application Server Integrated Solutions Console to do this.

Service Registry Audit Notifier plugin

• Load the <WSRRHOME>/Audit/ServiceRegistryAuditProperties.properties file into the WebSphere Service Registry and Repository as a user configuration with the configuration item name ServiceRegistryAuditProperties. From the Configuration perspective in the WebSphere Service Registry & Repository user interface:
  1. Expand Active Configuration Profile -> Plug-ins.
  2. Click on User Configurations in the navigation tree.
  3. On the User Configurations view, click Load User Configuration.
  4. In the Preparing to Load the Configuration item page, click the Browse button.
  5. In the File Upload dialog, select the file <WSRRHOME>/Audit/ServiceRegistryAuditProperties.properties.
  6. In the Preparing to Load the Configuration item page, enter ServiceRegistryAuditProperties in the field Provide the User configuration item name. Note: the name must be as specified.
  7. Click OK. The ServiceRegistryAuditProperties configuration item is listed in the User Configurations view.

• Load the <WSRRHOME>/Audit/ServiceRegistryAuditPlugin.jar file into the WebSphere Service Registry and Repository as a Plug-in JAR. From the Configuration perspective in the WebSphere Service Registry & Repository user interface:
  1. Expand Active Configuration Profile -> Plug-ins.
  2. Click on JARs in the navigation tree.
  3. On the Plug-in JARs view, click Load JAR Plug-in.
  4. In the Preparing to Load the Configuration item page, click the Browse button.
  5. In the File Upload dialog, select the file <WSRRHOME>/Audit/ServiceRegistryAuditPlugin.jar.
  6. In the Preparing to Load the Configuration item page, enter ServiceRegistryAuditPlugin in the field Provide the User configuration item name.
  7. Click OK. The ServiceRegistryAuditPlugin configuration item is listed in the User Configurations view.

• Modify the Notification properties configuration in the WebSphere Service Registry and Repository. From the Configuration perspective in the WebSphere Service Registry & Repository user interface:
  1. Expand Active Configuration Profile -> Plug-ins.
  2. Click on Notification Properties in the navigation tree.
  3. On the Notification Properties view, click the entry Notification properties plug-in (NotificationProperties).
  4. Add the following to the end of the notifiers and governanceNotifiers lines:

     com.ibm.sr.audit.SRAuditNotifier

     For example the resultant lines will be:

     notifiers=com.ibm.sr.api.ServiceRegistryNotifierJMS,com.ibm.sr.audit.SRAuditNotifier
     governanceNotifiers=com.ibm.sr.api.ServiceRegistryNotifierJMS,com.ibm.sr.audit.SRAuditNotifier

  5. Click OK. The Notification properties plug-in (NotificationProperties) configuration item is updated.

Auditing Overview

1. the registry user who made the change
2. the registry on which the change was made (the websphere cell/node/server)
3. the date and time on which they were made
4. the type of change from: create, update, delete, govern, remove governance, transition, state change.
5. whether successful or not
6. the exception message if unsuccessful
7. detail of the event:
   1. for transition the URI of the transition which was done
   2. for govern the initial transition
   3. for change state the new state

1. create: new object
2. update: old object
3. delete: old object
4. govern: old object
5. remove governance: old object
6. transition: old object
7. state changed: old object

Using Tivoli Common Reporting

Installing

Configuring

<tcr_install_location>\tcr\lib\birt-runtime-2_2_1\ReportEngine\plugins\org.eclipse.birt.report.data.oda.jdbc_2.2.1.r22x_v20070904\drivers

Deploying the sample reports

1. Log onto the Tivoli Common Reporting console
2. From the navigation tree, expand Tivoli Common Reporting -> Work with Reports.
3. From the Navigation in the right hand pane, select the Report Sets node.
4. Right click and select Import Report Package from the pop up menu.
5. From the Import Report Package dialog, click the Browse button.
6. From the resulting File Upload dialog, navigate to <WSRRHOME>/Audit directory and select the ReportSamples.zip file.
7. Click the Import button.

A WebSphereServiceRegistryandRepository node will appear in the navigation tree. Expand WebSphereServiceRegistryandRepository and click on AuditReports. A list of the sample reports will be shown. These are as follows:

Report name	Description
audit_all	All audited events, in a table
audit_bsruri	All audited events for an entity with the provided BsrUri, in a table.
audit_userid	All audited events for the user with the provided user id, in a table. Pie chart showing numbers by type of event. Bar chart showing numbers by type of object.
audit_what	All audited events between two provided dates and times, in a table. Pie chart showing number of successful and unsuccessful actions. Bar chart showing numbers by user ID.

Changing the Data Source settings on a report

1. Select a report.
2. Right click and select Data Sources... from the pop up menu.
3. In the Report Data Sources dialog, select the source with name Audit Data Source.
4. Click the Edit button.
5. In the Edit Data Source dialog, enter the database user, password and details for your specific database system, as follows:

6. Click Save.

Derby Network Server

1. From the WebSphere Application Server Integrated Solutions Console, expand Server -> Application Server-><Application Server Name> ->Java Process Management -> Process Definition -> Java Virtual Machine
2. In the classpath window add

   ${DERBY_JDBC_DRIVER_PATH}/derbynet.jar

3. In the Generic VM Arguments add

   -Dderby.drda.startNetworkServer=true

4. Click OK
5. On the message Changes have been made to your local configuration click Save.
6. Restart the WebSphere Application Server

If TCR runs on a different host than the WebSphere Application Server, the derby properties file should be changed to allow all hosts to connect to the Derby Network Server:

1. Open derby.properties, typically located in $WAS_HOME/derby/derby.properties
2. Uncomment the line

   #derby.drda.host=0.0.0.0

   It should read:

   derby.drda.host=0.0.0.0

3. Restart the WebSphere Application Server

To ensure the network server is running, perform the following:

1. From the WebSphere Application Server Integrated Solutions Console, expand Resources->JDBC->JDBC Providers.
2. Click the Derby JDBC Provider created previously $JDBC_PROVIDER_NAME.
3. Under Additional Properties click Data sources.
4. Select the checkbox next to the Data source $DATASOURCE_NAME.
5. Click the Test connection button.

Running a report

Using the command line report tool

To run the command line reporting tool, use the WebSphere Application Server launchClient script. The arguments for the reporting client are:

launchClient ServiceRegistryAudit.ear <Report Query> <CSV File Name>

1. Report Query. The SQL for the WHERE clause of the query.
2. CSV File Name. The name of the file where the CSV results will be saved.

launchClient ServiceRegistryAudit.ear "AUDITEVENT.REGISTRYUSER='admin'" output.csv

Sample Report Queries

AUDITEVENT.REGISTRYUSER = 'UNAUTHENTICATED' AND OBJECT.BSRURI = '319db131-69e7-4716.a39f.f614daf69f13'

Archiving and purging audit data

Database schema

Audit Event table

• Create
• Delete
• Update
• Govern
• RemoveGovernance
• Transition
• StateChange

Object table

Troubleshooting

Audit SupportPac messages

If an error occurs during Audit plug-in initialization, once the cause has been corrected, Replace the Audit plug-in JAR configuration item with ServiceRegistryAuditPlugin.jar. This will cause WebSphere Service Registry and Repository to re-initialize the plug-in.

If an error occurs during Audit Bean initialization, once the cause has been corrected, restart the ServiceRegistryAudit J2EE Application from the WebSphere Application Server Integrated Solutions Console.

Unable to find configuration

Unable to find JNDI name of Bean in configuration

Unable to get the Initial Context using defaults

Unable to look up the Bean JNDI name of Bean

The jndi object was not the Audit bean: Type of object in JNDI

Error occured adding the audit record to the database Exception message

Error not initialized

Unable to look up the Data Source jdbc/auditDatabase

The jndi object was not a Data Source: Type of object in JNDI

Error connecting to the database

Error creating the jdbc statement

Other messages

ReportDesign (id = 1): + Cannot open the connection for the driver: org.eclipse.birt.report.data.oda.jdbc Failed to get connection. SQL error #1: java.net.ConnectException : Error opening socket to server server name on port 1527 with message : Connection refused: connect

Server is ready to accept connections on port 1527.

If the TCR host is different to the WebSphere Application Server which runs the Derby Network Server and audit database, check the derby.properties file located in $WAS_HOME/derby/derby.properties, ensure the following line is uncommented:

derby.drda.host=0.0.0.0

ReportDesign (id = 1): + Cannot open the connection for the driver: org.eclipse.birt.report.data.oda.jdbc Failed to get connection. SQL error #1: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database $DB_NAME, see the next exception for details.::SQLSTATE: XSDB6Another instance of Derby may have already booted the database /opt/IBM/WebSphere/AppServer/derby/$DB_NAME