The Ceptor Identity Management database is a full-featured database supporting users, custom user information, user attributes, custom user status, revision- and transaction logs, groups, profiles, ACL’s, multiple challenge types per user and user organizations allowing for de-centralized user administration by partners or customers themselves.

The database also features full history support making it easy to track all changes made to the database as well as logging tables for nonrep- and revision logging.

Currently undocumented parts:

How to set up user an Identity Management server
Performance numbers
Error tracking

Database Layout

These are the main tables of the database in the user administration database:

Logging tables

The database contains tables for revision logging, webservice audit logging and nonrep logs as well.

History Tables

The database can optionally contain a set of history tables which will enable the database administrator to keep track of changes made in the database.

The history tables are without foreign keys and other constraints.

Update of the history tables takes place through a series of database triggers for each table mentioned here. Triggers are defined for deleting, updating and inserting elements into the original database. The triggers can be discarded if they are not needed.

Setting up Connection to the Database

The database is set up through the useradmin server configuration entries in the portalprotect-configuration.xml. For details please refer to the configuration section..

Currently these databases are supported:

DB2/z
DB/2 UDB
Apache Derby (default demo database provided)
Oracle
Microsoft SQL Server
JavaDB
Postgresql
Postgresql Amazon RDB
MySQL stand alone
MySQL Amazon RDB

Each database has slightly different SQL flavor/syntax – so for performance reasons the configuration needs to reflect this. These are configuration samples that can be used as starting points for each of the above databases.

<!-- MySQL example -->
<property name="db.username" value="root" description="Username to use when connecting to the database"/>
<property name="db.drivername" value="org.gjt.mm.mysql.Driver" description="Name of database JDB Driver"/>
<property name="db.connectionurl" value="jdbc:mysql://10.1.1.1/useradmin" description="JDBC URL to access database"/>
<property name="db.credentials" value="password" description="Password to use for connecting with the database"/>
<property name="db.dbtype" value="mysql" description="Type of DB"/>

<!-- MySQL Amazon RDB example -->
<property name="db.username" value="admin" description="Username to use when connecting to the database"/>
<property name="db.drivername" value="org.gjt.mm.mysql.Driver" description="Name of database JDB Driver"/>
<property name="db.connectionurl" value="jdbc:mysql://ppuseradmin.clqcegvd8jku.eu-west-1.rds.amazonaws.com/test" description="JDBC URL to access database"/>
<property name="db.credentials" value="password" description="Password to use for connecting with the database"/>
<property name="db.dbtype" value="mysql" description="Type of DB"/>

<!-- Cloudscape example -->
<property name="db.username" value="" description="Username to use when connecting to the database"/>
<property name="db.drivername" value="com.ihost.cs.jdbc.CloudscapeDriver" description="Name of database JDB Driver"/>
<property name="db.connectionurl" value="jdbc:cloudscape:${portalprotect.home}/testdatabase/ppdata" description="JDBC URL to access database"/>
<property name="db.credentials" value="" description="Password to use for connecting with the database"/>

<!-- Oracle example -->
<property name="db.username" value="testuser" description="Username to use when connecting to the database"/>
<property name="db.drivername" value="oracle.jdbc.OracleDriver" description="Name of database JDB Driver"/>
<property name="db.connectionurl" value="jdbc:oracle:thin:@10.1.1.1:1521:XE" description="JDBC URL to access database"/>
<property name="db.credentials" value="password" description="Password to use for connecting with the database"/>
<property name="db.dbtype" value="oracle10" description="Type of DB"/>

<!-- DB2 example -->
<property name="db.username" value="admin" description="Username to use when connecting to the database"/>
<property name="db.drivername" value="COM.ibm.db2.jdbc.app.DB2Driver" description="Name of database JDB Driver"/>
<property name="db.connectionurl" value="jdbc:DB2:PP" description="JDBC URL to access database"/>
<property name="db.credentials" value="password" description="Password to use for connecting with the database"/>
<property name="db.dbtype" value="db2" description="Type of DB"/>

<!-- SQLServer example -->
<property name="db.username" value="dbuser" description="Username to use when connecting to the database"/>
<property name="db.drivername" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" description="Name of database JDB Driver"/>
<property name="db.connectionurl" value="jdbc:sqlserver://10.1.1.1;databaseName=useradmin;integratedSecurity=false;" description="JDBC URL to access database"/>
<property name="db.credentials" value="password" description="Password to use for connecting with the database"/>
<property name="db.dbtype" value="sqlserver" description="Type of DB"/>

<!-- JavaDB example -->
<property name="db.username" value="dbuser" description="Username to use when connecting to the database"/>
<property name="db.drivername" value="org.apache.derby.jdbc.ClientDriver" description="Name of database JDB Driver"/>
<property name="db.connectionurl" value="jdbc:derby:ppdata" description="JDBC URL to access database"/>
<property name="db.credentials" value="" description="Password to use for connecting with the database"/>
<property name="db.dbtype" value="" description="Type of DB"/>


<!-- Postgresql example -->
<property name="db.username" value="dbuser" description="Username to use when connecting to the database"/>
<property name="db.drivername" value="org.postgresql.Driver" description="Name of database JDB Driver"/>
<property name="db.connectionurl" value="jdbc:postgresql://10.1.1.1:5432/ppdata" description="JDBC URL to access database"/>
<property name="db.credentials" value="password" description="Password to use for connecting with the database"/>
<property name="db.dbtype" value="postgres" description="Type of DB"/>

Creating Tables and Inserting Basic Data

The Ceptor distribution has DDLs for all supported databases in the samples/database directory.