How to integrate Apache DerbyDB and Tomcat using JDBCRealm Configuration

Purpose – To setup a simple Tomcat JDBCRealm configuration using the Apache Derby DB database

Pre-requisities –
Tomcat 8
DerbyDB 10.8

Step1: Create a new database tomcatdb in Apache DerbyDB.

CONNECT ‘jdbc:derby://localhost:1527/tomcatdb;create=true;user=admin;password=admin1’;

Step2: Create users table and insert data into table.

create table users (
  user_name         varchar(15) not null primary key,
  user_pass         varchar(15) not null
);

insert into users values (‘tomcat’,’tomcat’);
insert into users values (‘tomadmin’,’tomadmin’);

Step3: Create user_roles table and insert data into table.

create table user_roles (
  user_name         varchar(15) not null,
  role_name         varchar(15) not null,
  primary key (user_name, role_name)
);

insert into user_roles values (‘tomcat’,’tomcat’);
insert into user_roles values (‘tomadmin’,’manager-gui’);

Step4: Edit the server.xml at to use the JDBCRealm rather than the UserDatabaseRealm for authentication and authorization purpose.

<Realm className=”org.apache.catalina.realm.JDBCRealm”
driverName=”org.apache.derby.jdbc.ClientDriver”
connectionURL=”jdbc:derby://localhost:1527/tomcatdb”
connectionName=”admin”
connectionPassword=”admin1″
userTable=”users”
userNameCol=”user_name”
userCredCol=”user_pass”
userRoleTable=”user_roles”
roleNameCol=”role_name”/>

Step5: Copy the derbyclient.jar file from Derby Lib directory into Tomcat lib directory and startup the Tomcat instance.

Step6: Test access the below protected url with the user ‘tomcat’.

http://localhost:8080/examples/jsp/security/protected/

Step7: Test access the below manager url with the user ‘tomadmin’.

http://localhost:8080/manager

Note – Apache derby needs to be started up in Client-Server mode. Use the below useful link to setup derbydb.

Hope you enjoyed reading this article. Thank you.