How do I use a JDBC Realm with Tomcat and MySQL?
Author: Deron Eriksson
Description: This Tomcat tutorial describes how to configure Tomcat to use a JDBC Realm with MySQL for container-managed security.
Tutorial created using: Windows XP || JDK 1.5.0_09 || Eclipse Web Tools Platform 2.0 (Eclipse 3.3.0) || Tomcat 5.5.20


Page:    1 2 3 >

Authentication can be controlled by a web application or by the container (such as TomcatSW) that the web application runs in. Tomcat's container-managed security is based on realms. A realm contains the names of users, their passwords, and roles.

My Tomcat (version 5.5.20) comes configured with a UserDatabase realm as an active realm. The UserDatabase realm uses the tomcat-users.xml file in Tomcat's conf directory as the location for the name, password, and role data. Data from this file gets loaded when Tomcat starts up and not at other times. Typically you need to modify this file manually to update it, so it's probably most useful during development but not in an actual production system.

Tomcat can be configured for other more robust realm alternatives. One such alternative is a JDBCW realm. Benefits of a JDBC realm over a UserDatabase realm include being able to dynamically update the JDBC realm data at runtime rather than only at startup. In Tomcat's server.xml file, we can see that the UserDatabase realm is uncommented while a sample JDBC realm for MySQLW is commented out:

section of Tomcat's server.xml file

...
      <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
             resourceName="UserDatabase"/>
...
      <!--
      <Realm  className="org.apache.catalina.realm.JDBCRealm"
             driverName="org.gjt.mm.mysql.Driver"
          connectionURL="jdbc:mysql://localhost/authority"
         connectionName="test" connectionPassword="test"
              userTable="users" userNameCol="user_name" userCredCol="user_pass"
          userRoleTable="user_roles" roleNameCol="role_name" />
      -->
...

Now, let's create a MySQL databaseW to store the realm user names, passwords, and roles. I already have MySQL installed on my machine. I'll run the following create_tomcat_realm_database.sql script to create a 'tomcat_realm' database:

create_tomcat_realm_database.sql

DROP DATABASE IF EXISTS tomcat_realm;
CREATE DATABASE tomcat_realm;
USE tomcat_realm;
CREATE TABLE tomcat_users (
	user_name varchar(20) NOT NULL PRIMARY KEY,
	password varchar(32) NOT NULL
);
CREATE TABLE tomcat_roles (
	role_name varchar(20) NOT NULL PRIMARY KEY
);
CREATE TABLE tomcat_users_roles (
	user_name varchar(20) NOT NULL,
	role_name varchar(20) NOT NULL,
	PRIMARY KEY (user_name, role_name),
	CONSTRAINT tomcat_users_roles_foreign_key_1 FOREIGN KEY (user_name) REFERENCES tomcat_users (user_name),
	CONSTRAINT tomcat_users_roles_foreign_key_2 FOREIGN KEY (role_name) REFERENCES tomcat_roles (role_name)
);
INSERT INTO tomcat_users (user_name, password) VALUES ('deron', 'deronpass');
INSERT INTO tomcat_users (user_name, password) VALUES ('larry', 'buythecompetition');
INSERT INTO tomcat_roles (role_name) VALUES ('dude');
INSERT INTO tomcat_roles (role_name) VALUES ('manager');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('deron', 'dude');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('deron', 'manager');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('larry', 'dude');
COMMIT;

For a JDBC realm, you need a database with basically two tables. One table needs to have a column for user names and another column for passwords. The second table needs to have a column for user names and a column for roles. The database, tables, and columns can be named anything you want them to be named, since all of them can be specified in the JDBC realm entry in server.xml. However, the user name column of the first table and the user name column of the second table need to have the same name.

The create_tomcat_realm_database.sql does a bit more than this for the sake of 'correctness'. It creates a 'tomcat_users' table for the user names (the user_name column) and passwords (the password column), and it sets the user_name column as the primary key. It creates a 'tomcat_roles' table to store the role names in a role_name column, which is set as the primary key. It creates a 'tomcat_users_roles' table which contains user names (in the user_name column) and their associated roles (in the role_name column). The combination of the user_name column and the role_name column is set to the primary key of tomcat_users_roles. Foreign key constraints are added to tomcat_users_roles for referential integrity to ensure that the user_name specified in tomcat_users_roles exists in tomcat_users and that the role_name specified in tomcat_users_roles exists in tomcat_roles. The script also inserts some user names, passwords, and roles into the database tables.

I placed the script at C:\. I log on to mysqlW as my root user (the login data is in my my.ini file) and execute the script via:

SOURCE C:\create_tomcat_realm_database.sql

The execution of the script is shown here:

SOURCE C:\create_tomcat_realm_database.sql

I'll verify that the tables exist via "use tomcat_realm;" and "show tables;"

use tomcat_realm;
show tables;
verifying tables exist

Next, I'll create a user that Tomcat can use to access the tomcat_realm database. I'll call the user 'realm_access'. I'll grant the user the ability to select from all of the tomcat_realm tables.

USE mysql;
CREATE USER 'realm_access'@'localhost' IDENTIFIED BY 'realmpass';
GRANT SELECT ON tomcat_realm.* TO realm_access@localhost;
creating 'realm_access' user

(Continued on page 2)

Page:    1 2 3 >