Configure JDBCRealm in Tomcat for Container Managed Security

Every time I install a new version of Tomcat I change the file conf/tomcat-users.xml to add a administrator user with the correct roles for accessing the manager application. In this post we see how we can create an administrator with the correct roles in a MySQL database and re-use this administrator user in several Tomcat version installations. So now when we install a new version of Tomcat we don't have to change conf/tomcat-users.xml, but change the conf/server.xml to point to our database.

First we must create a new database in MySQL to store the username, password and user roles. In our example we create a database with the name tomcat and create two tables: users and user_roles. The users table has two columns to store the username and password. The user_roles table has two columns to store the username and role name for a user. Finally we create a new MySQL user account, tomcat, to access the tables.

CREATE DATABASE IF NOT EXISTS tomcat;

USE tomcat;

CREATE TABLE users(
    username VARCHAR(15) NOT NULL PRIMARY KEY,
    password VARCHAR(32) NOT NULL
);

CREATE TABLE user_roles(
    username VARCHAR(15) NOT NULL,
    rolename VARCHAR(20) NOT NULL,
    PRIMARY KEY(username, rolename)
);


CREATE USER 'tomcat'@'localhost' IDENTIFIED BY 'tomcat';
GRANT SELECT ON tomcat.* TO 'tomcat'@'localhost';
FLUSH PRIVILEGES;

Our database is setup so we can create a new administrator user with the roles manager-gui and manager-script to access the Tomcat manager web application:

USE tomcat;

INSERT INTO users VALUES('system', MD5('s3cr3t'));
INSERT INTO user_roles VALUES('system', 'manager-gui');
INSERT INTO user_roles VALUES('system', 'manager-script');

Notice we have created the MySQL user account tomcat with only SELECT privileges. So to add users to the tomcat.users table we must do so with the MySQL root account.

Now we only have to configure Tomcat so our database is used as extra realm to check the users. We open conf/server.xml and look for the lines <Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"> and add the following code after it:

<Realm className="org.apache.catalina.realm.JDBCRealm"
    driverName="org.gjt.mm.mysql.Driver"
    connectionURL="jdbc:mysql://localhost/tomcat"
    connectionName="tomcat" connectionPassword="tomcat"
    userTable="users" userNameCol="username" userCredCol="password"
    userRoleTable="user_roles" roleNameCol="rolename"
    digest="md5"/>

Notice we define the digest attribute with the value md5. In our SQL script we saved the password using MD5 encryption, so we must define the digest attribute with the same encryption name value.

Because we are accessing MySQL from the Tomcat Java code we must add the MySQL JDBC driver to Tomcat's classpath. We copy the mysql-connector-java-5.1.14-bin.jar (or other suitable MySQL JDBC driver) to $CATALINA_HOME/lib. This is the lib directory of the directory Tomcat is installed in.

We can start Tomcat and access the manager application with the username system and password s3cr3t. For each new or other Tomcat version we only have to add the <Realm ... /> element to conf/server.xml and the MySQL JDBC driver to the lib directory and we can re-use the same user credentials.