Sample User Database

Cams does not impose a specific user directory server or schema on you, rather it uses login modules and associated services to map authentication services to your existing Active Directory, LDAP server and SQL database schema. For testing convenience and as an example, this document describes how to configure a sample relational database for use with the Cams authentication service. The default values supplied in the system security domain's JDBC database connection pooling service (found in security-domain.xml) and JDBC login module (found in login-config.xml) are configured to use the sample schema and values with the HSQLDB relational database. Alternatively, the schema, values and SQL script supplied below should work with any SQL database that you want to configure that has a JDBC driver.

Configure HSQLDB for use with Cams

The default configuration values for the JDBC connection pool service found in security-domain.xml are defined for HSQLDB, an open source SQL relational database engine written in Java. HSQLDB includes a JDBC driver and supports a rich subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003 enhancements. If you are configuring a different relational database, you should skip to the Schema and SQL Script section.

NOTE: The default system security domain configuration files define localhost connections to HSQLDB. Because the Cams policy server requires Java, you should already have Java installed on your system with the JAVA_HOME environment variable correctly defined. If you downloaded the Cams Policy Server for Windows, you may need to set the JAVA_HOME environment variable to the fully-qualifed path for Java, CAMS_HOME/jre/.

You can download HSQLDB free of charge at http://hsqldb.sourceforge.net/. The following instructions are tested using HSQLDB 1.8.0 but will likely work with the 1.7.x releases too.

After you've downloaded HSQLDB:

1. Unzip HSQLDB to a directory of your choice
2. Open a command or terminal window and change directories to HSQLDB_HOME/demo
3. Run runServer.bat/runServer.sh to launch HSQLDB server
4. Open a command or terminal window and change directories to HSQLDB_HOME/demo
5. Run runManager.bat/runServer.sh to launch the HSQLDB SQL client
6. Using the HSQLDB SQL client graphical user interface:
   1. select Type: HSQL Database Engine Server and click OK to connect to the HSQLDB server
   2. Copy and paste the SQL script below into the input field and click Execute

You should see the ROLES, USERS and USER_ROLES tables displayed in a tree menu in the left panel. You can enter and try any query against the tables. For example, you might test with the queries from Example 2.

You now need to configure the Cams policy server:

1. Copy HSQLDB_HOME/lib/hsqldb.jar to CAMS_HOME/lib/
2. Enable the JDBC connection pooling service found at the bottom of the system security domain's security-domain.xml file:
   
   <service id="jdbc-pool" enabled="true">
   
   
3. Open the system security domain's login-config.xml file, comment out the XmlLoginModule and uncomment the JdbcLoginModule (XML file values that are between enclosing <!-- --> tags are commented out).

You should now be able to test the configuration by starting the Cams policy server and the Jetty test web server. The default user accounts and associated roles created by the SQL script are show in Table 1.

Table 1 - Default user accounts and associated roles created by the SQL script in Example 1

NOTE: The guest account uses a SSHA message digest for the password value. The database value will not look like the clear text value password shown in Table 1, but the Cams JdbcLoginModule knows how to decipher and use it as such.

Schema and SQL Script

The SQL script shown in Example 1 creates tables and inserts data that defines sample users, roles and the roles users have. The table schema is shown in Tables 2, 3 and 4. These three tables imply the following relational joins:

• one USERS.USER_ID to many USER_ROLES.USER_ID_FK
• one ROLES.ROLE_ID to many USER_ROLES.ROLE_ID_FK

Tables 2, 3 and 4 - Sample SQL database table schema

The SQL script in Example 1 should work universally with any relational database and has been tested against HSQLDB 8.0 and MySQL 4.1. No attempt has been made to optimize the schema for a specific relational database. If you use this schema in a production environment, your database administrator should review the SQL script and add indices to improve performance and any other fields you might require.

DROP TABLE IF EXISTS USERS;

CREATE TABLE USERS(
 USER_ID INTEGER PRIMARY KEY,
 USER_NAME VARCHAR (75),
 PASSWORD VARCHAR (75));

DROP TABLE IF EXISTS ROLES;

CREATE TABLE ROLES(
 ROLE_ID INTEGER PRIMARY KEY,
 ROLE_NAME VARCHAR (20));

DROP TABLE IF EXISTS USER_ROLES;

CREATE TABLE USER_ROLES(
 USER_ID_FK INTEGER,
 ROLE_ID_FK INTEGER);

INSERT INTO USERS
 VALUES(1,'admin','password');
INSERT INTO USERS
 VALUES(2,'csr','password');
INSERT INTO USERS
 VALUES(3,'guest',
 '{SSHA}zEWG/X8AzSdkHEFXE8pyCt0ddA321ktZz6bx1to9bFikZBS5wlAw3g==');

INSERT INTO ROLES
 VALUES(1,'everyone');
INSERT INTO ROLES
 VALUES(2,'csr');
INSERT INTO ROLES
 VALUES(3,'administrator');

INSERT INTO USER_ROLES
 VALUES(1,1);
INSERT INTO USER_ROLES
 VALUES(1,2);
INSERT INTO USER_ROLES
 VALUES(1,3);
INSERT INTO USER_ROLES
 VALUES(2,1);
INSERT INTO USER_ROLES
 VALUES(2,2);
INSERT INTO USER_ROLES
 VALUES(3,1);

Example 1 - SQL script to create sample user database table schema and to populate sample values

Example 2 shows SQL queries that can be used to return a password and roles for a specific user. These sample queries are hard coded for the admin user. You'll want to replace the specific user value with a question mark when configuring the Cams JDBC login module (as shown in the default login-config.xml file). The Cams login module will substitute the question mark for the user name before executing the query. The sample role query does not use SQL joins for maximum cross-database compatibility.

SELECT PASSWORD FROM USERS WHERE USER_NAME = 'admin';

SELECT ROLES.ROLE_NAME FROM USERS, ROLES, USER_ROLES
WHERE USERS.USER_NAME = 'admin'
AND USERS.USER_ID = USER_ROLES.USER_ID_FK
AND USER_ROLES.ROLE_ID_FK = ROLES.ROLE_ID;

Example 2 - SQL queries to fetch the user password and roles for the sample admin user

Back | Next | Contents

© Copyright 1996-2008 Cafésoft LLC. All rights reserved.