JDBC Realm on Tomcat

In this exaple we will authenticate users in database. First we need to prepare database with some users. In this example Apache Derby DB was used

Start Derby server

$ java -jar $DERBY_HOME/lib/derbyrun.jar server start

Start ij tool:

$ $DERBY_HOME/bin/ij

Connect to Derby, create database and create table:

ij> CONNECT 'jdbc:derby://localhost:1527/TESTDB;create=true';
ij> CREATE TABLE USERS (username VARCHAR(20), password VARCHAR(20));

Insert some users:

ij> INSERT INTO USERS VALUES ('matjaz', 'mypass');
ij> INSERT INTO USERS VALUES ('test', 'test');
ij> INSERT INTO USERS VALUES ('tomcat', 'tomcat');

Show contents of USERS table:

ij> SELECT * FROM USERS;

USERNAME            |PASSWORD            
-----------------------------------------
matjaz              |mypass              
test                |test                
tomcat              |tomcat

Create new web project named HelloWorld and create index.jsp file. The user will have to authenticate himself before entering index.jsp page.

index.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>Hello</title>
</head>
<body>
<h3>HelloWorld</h3>

</body>
</html>

Add the following lines to web.xml. It is almost the same as in previous example except <auth-method> element which declares the BASIC type of authentication.

<security-constraint>
<web-resource-collection>
<web-resource-name>MyRealm</web-resource-name>
<url-pattern>/*</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>manager</role-name>
</auth-constraint>
</security-constraint>
<login-config>
<auth-method>BASIC</auth-method>
<realm-name>MyRealm</realm-name>
</login-config>
<security-role>
<role-name>manager</role-name>
</security-role>

Now we need to implement MyJDBCRealm class. Implementation of realm should be built separatly of HelloWorld web application and it must be added to Tomcat classpath (ie. put jar inside $CATALINA_HOME/lib).

Create new project containing single class MyJDBCRealm and extend org.apache.catalina.realm.JDBCRealm class. The benefit of JDCBRealm class is that it provides authenticate(..) method, with a reference to java.sql.Connection object. All you need to do is override authenticate(connection, username, credentials) method and implement your authentication procedure.

package si.matjaz.jdbcrealm;

import java.security.Principal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.catalina.realm.GenericPrincipal;
import org.apache.catalina.realm.JDBCRealm;

public class MyJDBCRealm extends JDBCRealm {

private String username;
private String password;
@Override
public synchronized Principal authenticate(Connection dbConnection, String username, String credentials) {

if (username == null || username.equals("")
|| credentials == null || credentials.equals("")) {
return null;
}

this.username = username;
this.password = credentials;

try {

String tableName = getUserTable(); // see <Realm>
String usernameCol = getUserNameCol(); // see <Realm>
String passwordCol = getUserCredCol(); // see <Realm>

PreparedStatement statement = dbConnection.prepareStatement("SELECT * FROM " + tableName + " WHERE " + usernameCol + " = ?");

statement.setString(1, username);

ResultSet results = statement.executeQuery();

while (results.next()) {
String user = results.getString(usernameCol);
String pass = results.getString(passwordCol);

if (user.equals(username) && pass.equals(credentials)) {
return getPrincipal(username);
}
}

return null;

} catch (SQLException ex) {
Logger.getLogger(MyJDBCRealm.class.getName()).log(Level.SEVERE, null, ex);
return null;
}
}

@Override
protected synchronized Principal getPrincipal(String username) {
List<String> roles = new ArrayList<String>();
roles.add("manager");
return new GenericPrincipal(this, username, password, roles);
}

}

If authenticate(..) method returns null, the user is not authenticated and cannot proceed. The Principal object should return a list of roles. Tomcat is based on roles: for every user who successfully authenticates himself you should assign a role. Tomcat treats all users with specified role in the same way and also should your application.

Declare realm implementation in server.xml file. In case of JDBCRealm, you need to provide the following parameters which are mandatory to establish connection to DB and retrieve some entries:
driverName - driver that handles the connection with database
connectionURL - url to access database
userTable - name of table with users
userNameCol - exact name of column with usernames
userCredCol - exact name of column with passwords (credentials)
userRoleTable - name of table with user roles *
roleNameCol - exact name of column with user roles *

* - We don’t have any table with user roles, so we will use in this case the username as role parameter. We will set user roles within MyJDBCRealm implementation (see getPrincipal(...) method).

<Realm className="si.matjaz.jdbcrealm.MyJDBCRealm"
driverName="org.apache.derby.jdbc.ClientDriver"
connectionURL="jdbc:derby://localhost:1527/TESTDB"
userTable="USERS" userNameCol="username" userCredCol="password"
userRoleTable="USERS" roleNameCol="users"/>

The last thing to do is to put MyJDBCRealm.jar and derbyclient.jar to $CATALINA_HOME/lib. Because you specified that org.apache.derby.jdbc.ClientDriver will be used with Realm implementation, Tomcat will search for corresponding Driver class inside $CATALINA_HOME/lib directory (the same as MyJDBCRealm.jar).

Deploy HelloWorld application and see if it works:

http://localhost:8080/HelloWorld

If you are using the database from different vendor, then you just need to modify server.xml, without recompiling your code.