Configuring JDBC-based Authentication Using an Oracle RDBMS

Security

The standard distribution of JSPWiki supports JDBC-based authentication using Hypersonic SQL or Postrgres SQL out of the box. As mentioned elsewhere in the documentation, this feature can be used with any JDBC-compliant RDBMS. The trick, of course, is figuring out all of the details for doing so.

This page details the process involved with extending this support to an Oracle RDBMS. The processes detailed in this page were implemented on an Oracle 9.2 installation, but as the requirements are very modest, this same process should work on older (i.e. 8i) or newer (9 or 10) versions as well.

This particular installation was also performed on a less-common servlet container: the Sun Java System Web Server version 7.2. With that said, these instructions are not targetted to any particular server - they should work as well in Tomcat, JBoss, WebSphere, or any other container you might choose to make use of.

Lastly, this process was performed on a JSPWiki 2.6.2 installation, but would presumably work on any version that includes the generic JDBC support.

OK, with those disclaimers out of the way, let's get started.

Prerequisites

In order to make this process as painless as possible, you will want to take the following steps that are detailed elsewhere in this documentation before attempting to add Oracle support:

1. Deploy JSPWiki with a default install, configured to use the XML user and group databases.
2. Do the setup pieces on the Install.jsp page as documented in the installation instructions, opting to leave your installation in the XML user & group database mode for the time being.
3. Download the source code for the version of JSPWiki that you installed and unzip the source in a working directory.
4. Download and install a supported version of the Apache Ant build tool. Version 1.6.2 worked with the build.xml included with JSPWiki, but version 1.4.1 did not. I'm not sure what the lowest supported version number is as I could not find it anywhere.
5. Download and install a Java Development Kit (not a JRE, but a full-blown JDK). You should get version 5 or better as support for Java 4 is going away.
6. Download and install an Oracle JDBC (thin) driver.

OK, now that you have all those out of the way, we can get started on actually tying your JSPWiki installation to your Oracle database.

Create a Database Schema

The first thing you will need is a database schema in which to store your JSPWiki tables (for example, named "jspwiki"). The other supported databases include the user creation as part of their DDL, but I prefer to pre-create the user and then run the DDL as that user to minimize the work done as the DBA. Of course, you will need your DBA to grant the create table privilege to your new database user if you go this route.

Generate the DDL

There is a file named build.properties at the base of the source tree you previously unzipped. In this file is a block of properties with the comment heading "JDBC testing properties". Update these settings to include the appropriate information for your Oracle installation. Your settings should look something like this:

#
#  JDBC testing properties
#
jdbc.driver.id=oracle
jdbc.driver.jar=/opt/oracle/jdbc/oracle-jdbc-10.1.0.5.jar
jdbc.driver.class=oracle.jdbc.driver.OracleDriver
jdbc.driver.url=jdbc:oracle:thin:@oracleServer.mydomain.com:1521:MYDB
jdbc.admin.id=system
jdbc.admin.password=
jdbc.user.id=jspwiki
jdbc.user.password=password

Next add a new section of properties that defines the table and column names for your installation. These settings should look something like this:

#
#  JDBCUserDatabase & JDBCGroupDatabase setup information:
#
jspwiki.userdatabase.table=wiki_users
jspwiki.userdatabase.email=email
jspwiki.userdatabase.fullName=full_name
jspwiki.userdatabase.loginName=login_name
jspwiki.userdatabase.password=password
jspwiki.userdatabase.wikiName=wiki_name
jspwiki.userdatabase.created=created
jspwiki.userdatabase.modified=modified
jspwiki.userdatabase.roleTable=wiki_role
jspwiki.userdatabase.role=role
jspwiki.groupdatabase.table=wiki_groups
jspwiki.groupdatabase.membertable=wiki_members
jspwiki.groupdatabase.created=created
jspwiki.groupdatabase.creator=creator
jspwiki.groupdatabase.name=name
jspwiki.groupdatabase.member=member
jspwiki.groupdatabase.modified=modified
jspwiki.groupdatabase.modifier=modifier

You can use this exact set of properties unless you have a need to assign other table or column names (e.g., you're sharing a schema that already has tables with the above names).

The source tree includes directories under etc/db for each supported database. The standard distribution includes directories for hsql and postgresql. Create a new directory within etc/db named "oracle". Create two new files (templates) within that directory: one for creating the user and group tables (named "userdb-setup.ddl") and one for deleting them (named "userdb-teardown.ddl").

The contents of the userdb-teardown.ddl file should be as follows:

drop table @jspwiki.userdatabase.table@;
drop table @jspwiki.userdatabase.roleTable@;
drop table @jspwiki.groupdatabase.table@;
drop table @jspwiki.groupdatabase.membertable@;

The contents of the userdb-setup.ddl file should be as follows:

drop table @jspwiki.userdatabase.table@;
drop table @jspwiki.userdatabase.roleTable@;
drop table @jspwiki.groupdatabase.table@;
drop table @jspwiki.groupdatabase.membertable@;

create table @jspwiki.userdatabase.table@ (
  @jspwiki.userdatabase.email@ varchar(100),
  @jspwiki.userdatabase.fullName@ varchar(100),
  @jspwiki.userdatabase.loginName@ varchar(100) not null primary key,
  @jspwiki.userdatabase.password@ varchar(100),
  @jspwiki.userdatabase.wikiName@ varchar(100),
  @jspwiki.userdatabase.created@ timestamp,
  @jspwiki.userdatabase.modified@ timestamp
);

create table @jspwiki.userdatabase.roleTable@ (
  @jspwiki.userdatabase.loginName@ varchar(100) not null,
  @jspwiki.userdatabase.role@ varchar(100) not null
);

create table @jspwiki.groupdatabase.table@ (
  @jspwiki.groupdatabase.name@ varchar(100) not null primary key,
  @jspwiki.groupdatabase.creator@ varchar(100),
  @jspwiki.groupdatabase.created@ timestamp,
  @jspwiki.groupdatabase.modifier@ varchar(100),
  @jspwiki.groupdatabase.modified@ timestamp
);

create table @jspwiki.groupdatabase.membertable@ (
  @jspwiki.groupdatabase.name@ varchar(100) not null,
  @jspwiki.groupdatabase.member@ varchar(100) not null,
  constraint @jspwiki.groupdatabase.membertable@_pk
    primary key (@jspwiki.groupdatabase.name@,@jspwiki.groupdatabase.member@)
);

insert into @jspwiki.userdatabase.table@ (
  @jspwiki.userdatabase.email@,
  @jspwiki.userdatabase.fullName@,
  @jspwiki.userdatabase.loginName@,
  @jspwiki.userdatabase.password@,
  @jspwiki.userdatabase.wikiName@
) values (
  'me@myDomain.com',
  'Administrator',
  'admin',
  'yourSHAencryptedPasswordGoesHere',
  'Administrator'
);

insert into @jspwiki.userdatabase.roleTable@ (
  @jspwiki.userdatabase.loginName@,
  @jspwiki.userdatabase.role@
) values (
  'admin',
  'Authenticated'
);

insert into @jspwiki.groupdatabase.table@ (
  @jspwiki.groupdatabase.name@,
  @jspwiki.groupdatabase.created@,
  @jspwiki.groupdatabase.modified@
) values (
  'Admin',
  SYSDATE,
  SYSDATE
);

insert into @jspwiki.groupdatabase.membertable@ (
  @jspwiki.groupdatabase.name@,
  @jspwiki.groupdatabase.member@
) values (
  'Admin',
  'Administrator'
);

Note that the text yourSHAencryptedPasswordGoesHere should be the encrypted password that was created for your admin user that was created when you first submitted your changes on the Install.jsp page. Assuming you used the default settings, you will find the password for your admin user in your WEB-INF/userdatabase.xml file in the password attribute. The setting in the XML file will be prefixed with "{SHA}" to signify that it has been encrypted using the Secure Hash Algorithm - remove this prefix from the value that you insert into the users table.

Now run Ant to generate the DDL for constructing your tables:

ant -Dbuild.properties=build.properties db-properties

Note: you should be in the directory at the base of your working copy of the source tree when you run this command.

You will now find versions of your setup and teardown files in the tests/etc/db/oracle directory. The values you specified in the build.properties file will have been inserted into the templates. Connect to your database using SQLPlus or your tool of choice, log in as your jspwiki user, and run the tests/etc/db/oracle/userdb-setup.ddl file to create your tables and populate them with information for your admin user.

If you had other users and groups that had already been created in your JSPWiki, you will need to use the contents of the userdatabase.xml and groupdatabase.xml files to create SQL to populate your users and groups tables for these pre-existing users and groups. An XSLT would be a nice way to do this, but is beyond the scope of this document.

Add a JNDI Resource to Your Web Server

This process varies from web server to web server. Refer to the instructions for your web server and create a JDBC resource that will be used by JSPWiki (e.g., "jdbc/jspwikiDataSource"). This requires that your JDBC driver be available in your server's CLASSPATH. Again, refer to your web server documentation for details on how this is done.

Reconfigure Your Installation to Use the JDBC User and Group Databases

Before you can use the JDBC authentication piece you will need to have already configured the JAAS stuff for your web server to include the needed settings for JSPWiki.

The settings that direct JSPWiki to use JDBC to manage user and group data are included in the "Security, authentication and authorization" section of the jspwiki.properties file (which will by default be in your WEB-INF directory). Specifically, you need to make sure your settings are as follows:

jspwiki.groupdatabase =com.ecyrd.jspwiki.auth.authorize.JDBCGroupDatabase
jspwiki.userdatabase = com.ecyrd.jspwiki.auth.user.JDBCUserDatabase

If your web container is configured to use your JDBC database for authentication, then be sure to also include this setting:

jspwiki.userdatabase.isSharedWithContainer = true

Lastly, the "JDBC Configuration" section of the jspwiki.properties file needs to include the datasource, table, and column names used by your configuration. Your settings will look something like this:

#  JDBC Configuration. Tells JSPWiki which tables and columns to map
#  to for the JDBCUserDatabase and JDBCGroupDatabase. For more info, see the
#  JavaDoc for classes com.ecyrd.jspwiki.auth.user.JDBCUserDatabase and
#  com.ecyrd.jspwiki.auth.authorize.JDBCGroupDatabase.
#
jspwiki.userdatabase.datasource=jdbc/jspwikiDataSource
jspwiki.userdatabase.table=wiki_users
jspwiki.userdatabase.email=email
jspwiki.userdatabase.fullName=full_name
jspwiki.userdatabase.loginName=login_name
jspwiki.userdatabase.password=password
jspwiki.userdatabase.wikiName=wiki_name
jspwiki.userdatabase.created=created
jspwiki.userdatabase.modified=modified
jspwiki.userdatabase.roleTable=wiki_role
jspwiki.userdatabase.role=role
jspwiki.groupdatabase.datasource=jdbc/jspwikiDataSource
jspwiki.groupdatabase.table=wiki_groups
jspwiki.groupdatabase.membertable=wiki_members
jspwiki.groupdatabase.created=created
jspwiki.groupdatabase.creator=creator
jspwiki.groupdatabase.name=name
jspwiki.groupdatabase.member=member
jspwiki.groupdatabase.modified=modified
jspwiki.groupdatabase.modifier=modifier

Note that these table and column names need to match those that you added to the build.properties file when generating the DDL.

Testing Your Configuration

To put all of the previous preparations into production you must now restart your JSPWiki (and most likely your web server, especially if you made CLASSPATH changes). Do so at this time and take note of the information logged by the JSPWiki when it starts. If everything was done right, your log entries should include something similar to this:

2008-04-01 15:49:04,173 [main] INFO com.ecyrd.jspwiki.auth.UserManager  - Attempting to load user database class com.ecyrd.jspwiki.auth.user.JDBCUserDatabase
2008-04-01 15:49:04,355 [main] INFO com.ecyrd.jspwiki.auth.user.AbstractUserDatabase  - JDBCUserDatabase initialized from JNDI DataSource: jdbc/jspwikiDataSource
2008-04-01 15:49:04,355 [main] INFO com.ecyrd.jspwiki.auth.user.AbstractUserDatabase  - JDBCUserDatabase supports transactions. Good; we will use them.
2008-04-01 15:49:04,357 [main] INFO com.ecyrd.jspwiki.auth.UserManager  - UserDatabase initialized.

This indicates that JSPWiki was able to load your JDBC configuration and connect to the database using the JNDI resource you configured in the container.

At this point you should be able to create a new user or group and see them appear in the appropriate table. If that happens, you've successfully configured your JDBC connection to your JSPWiki, and you're done!