Tomcat 9 and Up: JDBC Realm For Basic HTTP Authentication

It is incredible how we can modify Tomcat to help hasten the development. This post will demonstrate how to use Tomcat JDBC Realm, which uses an RDBMS for Basic HTTP Authentication. This could cut down development time and effort for simple applications.

Tomcat and JDBC Realm Requirements

Java 11 (JDK11)
Tomcat 9.0.3
Hsqldb-2.6.0
Windows 10

Java, Tomcat, and HSQLDB Installation

First, we install JDK11. We can do this either using the installer or zip distribution. For this post, we opt for the former. Install the JDK and set JAVA_HOME to its home path (i.e., c:\program files\Java\jdk11). Then, restart Windows. On the other hand, using a zip distribution may be more straightforward because we do not need to install JDK11. Instead, we extract the binaries to some directory and set JAVA_HOME and PATH environment variables.

Second, we configure Tomcat 9.0.3. For Tomcat, we extract the downloaded zip file to some directory and go to <SOME-DIRECTORY>\apache-tomcat-9.0.3\bin. Then, start Tomcat by double-clicking “startup.bat.” Then, verify Tomcat is running by accessing http://localhost:8080. Later, we will configure Tomcat to allow for JDBC Realm Basic Authentication.

Third, we set up HSQLDB by extracting the downloaded zip file to some directory. Then, invoke the following in the command-line interpreter: <SOME-DIRECTORY>\hsqldb\bin\> runServer.bat --database.0 file:mydb --dbname.0 xdb. This will start the HSQLDB server and create an empty database with a “mydb” name.

C:\Users\karldev\Downloads\hsqldb-2.6.0\hsqldb\bin>runServer.bat --database.0 file:mydb --dbname.0 xdb

C:\Users\karldev\Downloads\hsqldb-2.6.0\hsqldb\bin>cd ..\data
[Server@30b8a058]: Startup sequence initiated from main() method
[Server@30b8a058]: Could not load properties from file
[Server@30b8a058]: Using cli/default properties only
[Server@30b8a058]: Initiating startup sequence...
[Server@30b8a058]: Server socket opened successfully in 7 ms.
[Server@30b8a058]: Database [index=0, id=0, db=file:mydb, alias=xdb] opened successfully in 360 ms.
[Server@30b8a058]: Startup sequence completed in 400 ms.
[Server@30b8a058]: 2021-07-16 06:00:51.720 HSQLDB server 2.6.0 is online on port 9001
[Server@30b8a058]: To close normally, connect and execute SHUTDOWN SQL
[Server@30b8a058]: From command line, use [Ctrl]+[C] to abort abruptly

C:\Users\karldev\Downloads\hsqldb-2.6.0\hsqldb\bin>runServer.bat --database.0 file:mydb --dbname.0 xdb

C:\Users\karldev\Downloads\hsqldb-2.6.0\hsqldb\bin>cd ..\data

[Server@30b8a058]: Startup sequence initiated from main() method

[Server@30b8a058]: Could not load properties from file

[Server@30b8a058]: Using cli/default properties only

[Server@30b8a058]: Initiating startup sequence...

[Server@30b8a058]: Server socket opened successfully in 7 ms.

[Server@30b8a058]: Database [index=0, id=0, db=file:mydb, alias=xdb] opened successfully in 360 ms.

[Server@30b8a058]: Startup sequence completed in 400 ms.

[Server@30b8a058]: 2021-07-16 06:00:51.720 HSQLDB server 2.6.0 is online on port 9001

[Server@30b8a058]: To close normally, connect and execute SHUTDOWN SQL

[Server@30b8a058]: From command line, use [Ctrl]+[C] to abort abruptly

Then, create tables and insert some rows into them.

Create HSQLDB Tables and Insert Data

Double-click the file runManagerSwing.bat in <SOME-DIRECTORY>\hsqldb\bin\, and the HSQL Database Manager will startup as shown below.

CREATE TABLE TC_USERS (user_name varchar(25) not null primary key, user_pwd varchar(25) not null);
CREATE TABLE TC_ROLES (user_name varchar(25) not null primary key, role_name varchar(20) not null);
INSERT INTO TC_USERS (USER_NAME, USER_PWD) VALUES ('ksangabriel', 'password');
INSERT INTO TC_USERS (USER_NAME, USER_PWD) VALUES ('admin', 'admin123');
INSERT INTO TC_USERS (USER_NAME, USER_PWD) VALUES ('tomcat', 'tomcat123');
INSERT INTO TC_ROLES(USER_NAME, ROLE_NAME) VALUES ('ksangabriel', 'admin');
INSERT INTO TC_ROLES(USER_NAME, ROLE_NAME) VALUES ('tomcat', 'tomcat');
INSERT INTO TC_ROLES(USER_NAME, ROLE_NAME) VALUES ('admin', 'admin');

CREATE TABLE TC_USERS (user_name varchar(25) not null primary key, user_pwd varchar(25) not null);

CREATE TABLE TC_ROLES (user_name varchar(25) not null primary key, role_name varchar(20) not null);

INSERT INTO TC_USERS (USER_NAME, USER_PWD) VALUES ('ksangabriel', 'password');

INSERT INTO TC_USERS (USER_NAME, USER_PWD) VALUES ('admin', 'admin123');

INSERT INTO TC_USERS (USER_NAME, USER_PWD) VALUES ('tomcat', 'tomcat123');

INSERT INTO TC_ROLES(USER_NAME, ROLE_NAME) VALUES ('ksangabriel', 'admin');

INSERT INTO TC_ROLES(USER_NAME, ROLE_NAME) VALUES ('tomcat', 'tomcat');

INSERT INTO TC_ROLES(USER_NAME, ROLE_NAME) VALUES ('admin', 'admin');

Tomcat will use these roles and users for the JDBC Realm Basic Authentication. Use the following configuration details to access the HSQLDB from an SQL IDE, e.g., Data Grip.

Configure Tomcat For JDBC Realm

Modify <TOMCAT_HOME>/conf/server.xml by replacing the following line numbers 144-145.

...
<!-- Use the LockOutRealm to prevent attempts to guess user passwords
     via a brute-force attack -->
<Realm className="org.apache.catalina.realm.LockOutRealm">
  <!-- This Realm uses the UserDatabase configured in the global JNDI
       resources under the key "UserDatabase".  Any edits
       that are performed against this UserDatabase are immediately
        available for use by the Realm.  -->
  <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
         resourceName="UserDatabase"/>
</Realm>
...

...

<!-- Use the LockOutRealm to prevent attempts to guess user passwords

via a brute-force attack -->

<!-- This Realm uses the UserDatabase configured in the global JNDI

resources under the key "UserDatabase". Any edits

that are performed against this UserDatabase are immediately

available for use by the Realm. -->

<Realm className="org.apache.catalina.realm.UserDatabaseRealm"

resourceName="UserDatabase"/>

</Realm>

...

with this:

<Realm className="org.apache.catalina.realm.JDBCRealm" 
        debug="0?" 
        driverName="org.hsqldb.jdbc.JDBCDriver" 
        connectionURL="jdbc:hsqldb:hsql://localhost:9001/xdb" 
        connectionName="sa" 
        connectionPassword="" 
        userTable="tc_users" 
        userNameCol="user_name" 
        userCredCol="user_pwd" 
        userRoleTable="tc_roles" 
        roleNameCol="role_name" />

<Realm className="org.apache.catalina.realm.JDBCRealm"

debug="0?"

driverName="org.hsqldb.jdbc.JDBCDriver"

connectionURL="jdbc:hsqldb:hsql://localhost:9001/xdb"

connectionName="sa"

connectionPassword=""

userTable="tc_users"

userNameCol="user_name"

userCredCol="user_pwd"

userRoleTable="tc_roles"

roleNameCol="role_name" />

Deploy and configure your Application

Before deploying our Application, let us modify the web.xml file a bit. Note we added one role under the auth-constraint element, which is also available in our database.

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

<web-app>
    <display-name>Archetype Created Web Application</display-name>

    <security-constraint>
        <web-resource-collection>
            <web-resource-name>JDBCRealmTest</web-resource-name>
            <description>accessible by authenticated users of the tomcat role</description>
            <url-pattern>/*</url-pattern>
            <http-method>GET</http-method>
            <http-method>POST</http-method>
            <http-method>PUT</http-method>
            <http-method>DELETE</http-method>
        </web-resource-collection>
        <auth-constraint>
            <description>These roles are allowed access</description>
            <role-name>tomcat</role-name>
            <role-name>admin</role-name>
        </auth-constraint>
    </security-constraint>
    <login-config>
        <auth-method>BASIC</auth-method>
        <realm-name>MyFirst Protected Area</realm-name>
    </login-config>
    <security-role>
        <description>Only ‘tomcat’ role is allowed to access this web application</description>
        <role-name>tomcat</role-name>
    </security-role>
</web-app>

<!DOCTYPE web-app PUBLIC

"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"

"http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>

<display-name>Archetype Created Web Application</display-name>

<security-constraint>

<web-resource-collection>

<web-resource-name>JDBCRealmTest</web-resource-name>

<description>accessible by authenticated users of the tomcat role</description>

<url-pattern>/*</url-pattern>

<http-method>GET</http-method>

<http-method>POST</http-method>

<http-method>PUT</http-method>

<http-method>DELETE</http-method>

</web-resource-collection>

<auth-constraint>

<description>These roles are allowed access</description>

<role-name>tomcat</role-name>

<role-name>admin</role-name>

</auth-constraint>

</security-constraint>

<login-config>

<auth-method>BASIC</auth-method>

<realm-name>MyFirst Protected Area</realm-name>

</login-config>

<security-role>

<description>Only ‘tomcat’ role is allowed to access this web application</description>

<role-name>tomcat</role-name>

</security-role>

</web-app>

Therefore, only users of the tomcat role can sign in to Tomcat using JDBC Realm via Basic HTTP Authentication.

Test JDBC Realm Basic HTTP Authentication

From the web.xml, only users with tomcat roles can get in to access the resources of Tomcat. Access the sample web application via http://localhost:8080/java_tomcat_jdbc_realm_war/index.jsp and use tomcat/tomcat123 username and password. Make sure HSQLDB is up and running.

You should be able to view the content of the index.jsp.

That is how we can use Tomcat JDBC Realm for HTTP Basic Authentication!

BASIC Authentications Without JDBC (Optional)

Supposed we want a BASIC authentication without JDBC; we could configure Tomcat using XML configuration files.

Tomcat requires the HSQLDb connector in its lib directory. Without it, Tomcat will not be able to connect to the HSQLDb. To create an old-school JSP/Servlet Web application like the one we have on this post, please create a Maven project using maven-archetype-webapp. See https://maven.apache.org/archetypes/maven-archetype-webapp/