Using the JDBCRealm

Tomcat supports Container Managed Security. You don’t need to package your Servlets or JSPs with ugly authentication code, let Tomcat do it for you!

A Realm is a simply a collection of usernames, passwords and roles. This Q/A deals with how to store this information in a database table and integrate it with Tomcat.

A user has a password and can have multiple roles. You can give access to a resource (or a set of resources, eg. *.jsp) by associating it with one or more roles.

For example: suppose you want all JSP’s under the subdirectory /protected to be accessible by only the users that have the (custom) role member. You can declaratively do this in web.xml:

<web-app>   
   <security-constraint>      
      <web-resource-collection>         
         <web-resource-name>admin</web-resource-name>         
         <url-pattern>/protected/*.jsp</url-pattern>      
      </web-resource-collection>      
      <auth-constraint>         
         <role-name>member</role-name>         
      </auth-constraint>   
   </security-constraint>   
</web-app>

So, where are the users/passwords that have the member role? That depends on the type of Realm you’re using. A MemoryRealm stores this information in a file (see TOMCAT-HOME/conf/tomcat-users.xml). A JDBCRealm uses a database.

So let’s create a web application with the protected directory protected. All members having either role silver or role gold are allowed to access the resources in that directory.

Here is how our new web.xml looks like:

<?xml version="1.0" encoding="ISO-8859-1"?>

<!DOCTYPE web-app
PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_3.dtd">

<web-app>   
   <security-constraint>      
      <web-resource-collection>         
         <web-resource-name>admin</web-resource-name>         
         <url-pattern>/protected/*</url-pattern>      
      </web-resource-collection>      
      <auth-constraint>         
         <role-name>Silver</role-name>         
         <role-name>Gold</role-name>         
      </auth-constraint>   
   </security-constraint>
   
   <login-config>
      <auth-method>BASIC</auth-method>      
      <realm-name>Log In</realm-name>
   </login-config>
</web-app>

Note the login-config tag. It specifies how a user should authenticate. We use BASIC authentication which will cause a popup to appear whenever an unauthenticated user tries to access one of the protected resources:

Authentication information will be stored in a database and accessed with a JDBC driver. In this example, we use mysql with the driver MySQL Connector/J (formerly mm.mysql).
Download the JAR file (mm.mysql-2.0.4-bin.jar) and store it in [TOMCAT-HOME]/common/lib, if it will be only visible by your web applications or [TOMCAT-HOME]/server/lib if it’s used by Tomcat 4 as well.

Modify [TOMCAT-HOME]/conf/server.xml to configure Tomcat to use the database as an authentication mechanism. You can host the following tag in the Engine, Host or Context element, depending on the scope you want (inside Engine = share across all web applications and all virtual hosts, inside Host = share accross all web applications of that particular host, inside Context = use this Realm only for this web application).

      <Realm className="org.apache.catalina.realm.JDBCRealm" debug="00"
             driverName="org.gjt.mm.mysql.Driver"
             connectionURL="jdbc:mysql://localhost/esusdb"
             connectionName=""
             connectionPassword=""
             userTable="members"
             userNameCol="username"
             userCredCol="password"
             userRoleTable="memberroles"
             roleNameCol="role" /> 
  • driverName specifies the JDBC driver to be used (added previously to the lib directory).
  • connectionURL specifies the database URL to connect to.
  • connectionName and connectionPassword are needed if your database is password protected.
  • userTable specifies the table in your database in which authentication information is stored. It should contain a user per row, with the username/password stored in the columns specified in userNameCol and userCredCol.
  • userRoleTable specifies the table in your database that contains an association between the users and their roles. It should contain at least the columns specified in userNameCol and roleNameCol.

Now create the necessary mysql database tables:

C:mysqlbin>mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5 to server version: 3.23.47-nt
 
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
 
mysql> create database esusdb;
Query OK, 1 row affected (0.00 sec)
 
mysql> use esusdb;
Database changed
mysql> create table members (uid int(10) not null primary key, username varchar(
20) not null, password varchar(20) not null);
Query OK, 0 rows affected (0.00 sec)
 
mysql> create table memberroles (username varchar(20) not null, role varchar(10)
 not null, primary key (username, role));
Query OK, 0 rows affected (0.01 sec)
 
mysql> desc members;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| uid      | int(10)     |      | PRI | 0       |       |
| username | varchar(20) |      |     |         |       |
| password | varchar(20) |      |     |         |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
mysql> desc memberroles;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) |      | PRI |         |       |
| role     | varchar(10) |      | PRI |         |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
 
mysql>

…and insert some data:

 
mysql> insert into members values (1, 'John', '123456');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into members values (2, 'Lisa', '654321');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into memberroles values ('John', 'Gold');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into memberroles values ('Lisa', 'Silver');
Query OK, 1 row affected (0.00 sec)
 
mysql>

We have created two users: John with role Gold and Lisa with role Silver.

Our web application will use the JDBCRealm to connect to this database and authenticate users.
To create the example web application, create a directory structure as follows:

loginexample2
   |
   +- index.jsp
   |
   +--- protected
   |       |
   |       +- confidential.jsp
   |
   +--- WEB-INF
           |
           +- web.xml

Use the deployment descriptor web.xml that is shown above. The rest of the example JSP’s can be cut’n’pasted here:

loginexample2/index.jsp:

<html>
<body>
Access <a href="./protected/confidential.jsp">protected resource</a>
</body>
</html>

loginexample2/protected/confidential.jsp:

<html>
<body>
<%
   if (request.isUserInRole("Gold")) {
%>
   You have the GOLD role<br>
<% 
   }
   if (request.isUserInRole("Silver")) {
%>
   You have the SILVER role<br>
<%
   }
%>
<br> 
You have successfully accessed the protected resource!
</body>
</html>

Go to http://localhost:8080/loginexample2/index.jsp and try to access the protected resource. A popup window will show up. Notice that, once you’re logged in, you cannot log out, except by closing the browser window.