Fact

PhenixID Identity Manager

Situation

This document describes the steps needed to perform IM auditing to database.

Steps involved:
1. Create or select a database
2. Create a database table
3. Configure DSEditor.properties
4. Set policy for audit level
5. Other

Solution

1. Create or select a database

In your choice of database engine, please create a new database or select one already created.
The database account needs write (insert) and read access to database and table.
(In Windows environment, make sure SQL authentication is selected and not Windows authentication)

2. Create a database table

Create the sql table to which Identity Manager should send auditing messages.
Examples of database commands to create table SQL command:

SQL:

create table imaudit (auditDate DATETIME not null, userDN VARCHAR(200), objectDN VARCHAR(200), message VARCHAR(500), filter VARCHAR(100)) 

ORACLE:

create table imaudit (auditDate DATE not null, userDN VARCHAR(200), objectDN VARCHAR(200), message VARCHAR(500), filter VARCHAR(100))

Database query may have to be modified depending on the SQL syntax of the chosen database engine. Check with your database administrator for assistance.

3. Configure DSEditor.properties

In DSEditor.properties find the section for audit logging and add the following parameters, if they are not already present:

Enable_Audit_Log=true
Enable_Audit_TO_DB=true
Enable_Internal_DB=false

In DSEditor.properties add one of the lines below for the audit table, depending on the database engine used:

log4j.appender.audit.table=tablename     (default imaudit)
log4j.appender.audit.table=database.tablename     (some SQL Express installations)

Add the line at the bottom of section called “#Log4j Audit”.

If the database is configured as an ODBC source set the following:

log4j.appender.audit.URL=jdbc:odbc:datasourceName
log4j.appender.audit.DBUser=myDBUser
log4j.appender.audit.DBPassword=myDBUserPassword

If the database is not an ODBC source and the database engine is Microsoft SQL 2005/2008:

log4j.appender.audit.URL=jdbc:sqlserver:// dbhostname;databaseName=database;user=userName;password=password
log4j.appender.audit.Class=com.microsoft.sqlserver.jdbc.SQLServerDriver

Use sqljdbc4.jar as ODBC driver. Put driver in tomcat lib folder.

If the database is not an ODBC source and the database engine is Microsoft SQL 2005 Express:

log4j.appender.audit.URL=jdbc:sqlserver:// dbhostname;user=userName;password=password
log4j.appender.audit.Class=com.microsoft.sqlserver.jdbc.SQLServerDriver

Use sqljdbc4.jar as ODBC driver. Put driver in tomcat lib folder.

If the database is not an ODBC source and the database engine is Microsoft SQL 2000:

log4j.appender.audit.URL=jdbc:sqlserver:// dbhostname;databaseName=datebase;user=userName;password=password
log4j.appender.audit.Class= com.microsoft.jdbc.sqlserver.SQLServerDriver

Use sqljdbc4.jar as ODBC driver. Put driver in tomcat lib folder.

If the database is not an ODBC source and the database engine is MySQL:

log4j.appender.audit.URL=jdbc:mysql://dbhostname:port/databaseName
log4j.appender.audit.DBUser=root
log4j.appender.audit.DBPassword=connected
log4j.appender.audit.Class=com.mysql.jdbc.Driver

Use latest JDBC driver for MySQL. Put driver in tomcat lib folder.

If the database is not an ODBC source and the database engine is Microsoft SQL using jTDS and Windows integrated authentication:

log4j.appender.audit.table=imaudit
log4j.appender.audit.URL=jdbc:jtds:sqlserver://server:1433/<databaseName>;useNTLMv2=true;domain=XX;
log4j.appender.audit.DBUser=<UserName>
log4j.appender.audit.DBPassword=<Password>
log4j.appender.audit.Class=net.sourceforge.jtds.jdbc.Driver

Use latest JDBC driver for jTDS. Put driver in tomcat lib folder.

After the changes please restart PhenixID Identity Manager service.

4. Set policy for audit level

The policy for audit level is set in DSEditor.properties. So open DSEditor.properties and add the parameter:

Audit_Level=Detailed-History

somewhere in the file, for example in the audit section.

After the changes please restart PhenixID Identity Manager service.

5. Other

Ensure the database user has “insert” and “read” access rights to the audit table.

When not using ODBC as a connection method, copy the appropriate .jar files to the /lib directory. For the standalone client this is: %imroot%/lib. For the web application this is: %webapplicationroot%/WEB-INF/lib.