Configuring JDBC-based Authentication Using an Oracle RDBMS#
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:
- Deploy JSPWiki with a default install, configured to use the XML user and group databases.
- 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.
- Download
the source code for the version of JSPWiki that you installed and unzip the source in a working directory.
- 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.
- 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.
- 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!