How to integrate Apache DerbyDB and Tomcat using JDBCRealm Configuration

How to integrate Apache DerbyDB and Tomcat using JDBCRealm Configuration

tomcat_derbydb_setup

Here in this article we will try to setup a simple Tomcat JDBCRealm configuration using the Apache Derby DB database.

Test Environment

  • Tomcat 8
  • DerbyDB 10.8

Procedure

Step1: Create a new database tomcatdb in Apache DerbyDB

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

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

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

Hope you enjoyed reading this article. Thank you..