Working with JDBCRealm

What is JDBCRealm?

Is an implementation of a tomcat 3.X Realm that use a set of configurable tables inside a RDMS to store user's data, this tables are accessed by means of standard JDBC drivers.
The passwords can be stored as digested ( using standard Java's MessageDigest ) or in plain form.
All the parameters, drivers, tables, and columns are user configurable.

Example Config for JDBCRealm

1. Create a database.

I made the database named "authority"

2. Create needed tables.

1. The user table.

This table needs the user's name and a password field. In the example I use "users" for the table name, "user_name" for the column that holds the user's name, and "user_pass" for the user's password.

2. The role table.

This table needs the role's set up that will be in any deployment descriptor that is managed under the container this Realm is in. In the example I use "roles" as the table name and "role_name" as the role's name. NB: This table doesn't get used at all by tomcat.

3. The role to user table.

This table joins a set of roles to a single user. In the example the table name is "user_roles", the role's name is "role_name" , and the user's name is assumed to have the same column name as in the user's table ("user_name" in this example.

Here is the SQL I used to create the tables:

create table users
(
  user_name varchar(15) not null primary key,
  user_pass varchar(15) not null
);


create table roles
(
  role_name varchar(15) not null primary key
);

create table user_roles
(
  user_name varchar(15) not null,
  role_name varchar(15) not null,
  primary key( user_name, role_name )
);

Here is sample output from the tables:

mysql> select * from users;
+-----------+-----------+
| user_name | user_pass |
+-----------+-----------+
| tomcat    | tomcat    |
| user1     | tomcat    |
| user2     | tomcat    |
| user3     | tomcat    |
+-----------+-----------+
4 rows in set (0.00 sec)

mysql> 
mysql> select * from roles;
+------------+
| role_name  |
+------------+
| tomcat     |
| role1      |
+------------+
2 rows in set (0.02 sec)

mysql> 


mysql> select * from user_roles;
+------------+-----------+
| role_name  | user_name |
+------------+-----------+
| tomcat     | user1     |
| role1      | user2     |
| tomcat     | tomcat    |
| role1      | tomcat    |
+------------+-----------+
4 rows in set (0.00 sec)

mysql> 

3. Configure Tomcat

Add the information to the server.xml file. For this example I used this entry inside:

The meaning of the attributes is as follow:

Done!!

Using digested passwords

To use digested password you need to store them digested. To achieve this, you will need to use the same digest strategies that JDBCrealm uses to store the passwords. Iinside JDBCRealm there is a static method with signature final public static String digest(String password,String algorithm). This method is provided as a tool to be used outside JDBCRealm by an application that wants to generate digested passwords readable by JDBCRealm.

The class JDBCRealm contains a main method, so it can be used as an application to generate digests and print them to stdout. Usage is:
java org.apache.tomcat.modules.aaa.JDBCRealm -a <algorithm> <password> [<password> ...]
where <algorithm> is a supported message digest algorithm, e.g. MD5, and <password> is a plaintext password to be digested.

Hints

- Make sure that the JDBC driver is in the lib/container directory.
- If you have problem connecting you can specify connectionName and connectionPassword