Tomcat MySQL realm

Leave a reply

Let’s get started with another Tomcat chapter in this blog. Tomcat comes by default with the UserDatabaseRealm which is really crappy because it reads user info from tomcat-users.xml file and a Tomcat restart is needed for the changes to take effect. This is not appropriate for a production environment so we are going to authenticate against a MySQL database instead of the tomcat-users.xml file.

We should already have a MySQL database running. Now we create the authority database.

CREATE DATABASE authority;
USE authority;
CREATE TABLE users (
	user_name varchar(20) NOT NULL PRIMARY KEY,
	password varchar(32) NOT NULL
);
CREATE TABLE roles (
	role_name varchar(20) NOT NULL PRIMARY KEY
);
CREATE TABLE 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 users (user_name),
	CONSTRAINT tomcat_users_roles_foreign_key_2 FOREIGN KEY (role_name) REFERENCES roles (role_name)
);
INSERT INTO users (user_name, password) VALUES ('manager', 'manager');
INSERT INTO users (user_name, password) VALUES ('tomcat', 'tomcat');
INSERT INTO roles (role_name) VALUES ('role1');
INSERT INTO roles (role_name) VALUES ('manager');
INSERT INTO users_roles (user_name, role_name) VALUES ('tomcat', 'tomcat');
INSERT INTO users_roles (user_name, role_name) VALUES ('manager', 'manager');
COMMIT;

Copy previous SQL code to a file named tomcat.sql. Now we create the authority database running the following.

[17:55:42] xavi@NewYork:/tmp $ mysql -u root -p < tomcat.sql 
Enter password: 
[17:59:23] xavi@NewYork:/tmp $

Now we check the newly created database. Log in to database and check the fields we just entered.

mysql> use authority;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_authority |
+---------------------+
| roles               |
| users               |
| users_roles         |
+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM roles;
+-----------+
| role_name |
+-----------+
| manager   |
| role1     |
+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users;
+-----------+----------+
| user_name | password |
+-----------+----------+
| manager   | manager  |
| tomcat    | tomcat   |
+-----------+----------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM users_roles;
+-----------+-----------+
| user_name | role_name |
+-----------+-----------+
| manager   | manager   |
| tomcat    | tomcat    |
+-----------+-----------+
2 rows in set (0.00 sec)

mysql>

So now we have to create a user to run queries on the authority database.

 [18:26:08] xavi@NewYork:/tmp $ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.1.49-1 (Debian)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE USER 'realm_user'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON authority.* TO realm_user@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>

Lets download the driver that will connect Tomcat to the MySQL database. Download the driver from the following URL. Unzip the driver and drop the .jar file in the $CATALINA_HOME/common/lib directory.
Now we need to modify server.xml to authenticate against the database and not against tomcat-users.xml file. Comment tomcat-users.xml entry and add the following.

<Realm className="org.apache.catalina.realm.JDBCRealm" debug="99"
      driverName="org.gjt.mm.mysql.Driver"
   connectionURL="jdbc:mysql://localhost/authority?user=realm_user&amp;password=password"
       userTable="users" userNameCol="user_name" userCredCol="user_pass"
   userRoleTable="user_roles" roleNameCol="role_name"/>

Now restart tomcat and you should be good to go.

Leave a Reply