Tomcat JDBC Realm Security

Tomcat JDBC Realm Security Example

In previous article we already had a look on that, how to use the UserDatabase realm security and the web.xml configurations. In this article you able to learn, how to configure the JDBC realm in tomcat server and how to use the realm security in the web application. 

The main benefit of the JDBC realm is that, its possible to update or maintain the users and their relevant roles dynamically than server start up. In the UserDatabase Realm, the users are configured in 'tomcat-users.xml', which is loading when the server start up

The main objective of the tutorial is that, using the tomcat container set JDBC realm for the access the secure resources. In order to achieve the objective mentioned above, we are going to develop an web application with using eclipse and set the security access in application web.xml file. In addition to that, we are going to do small configuration in tomcat 'server.xml' file, in order to support the JDBC realm.

Requirements : 

• Eclipse Java IEE IDE
• JDK 1.6
• Appache tomcat - 6.0.26
• MySQL server 5.5

Step 1 : Create a table 'users' and 'users_roles' table in MySQL test database with using the following script.

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

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

Insert the test data to created table with using the below scrip. 

INSERT INTO users VALUES (
'test', 'test123'
);

INSERT INTO user_roles VALUES (
'test', 'VIP'
);

INSERT INTO users VALUES (
'john', 'john123'
);

INSERT INTO user_roles VALUES (
'john', 'Admin'
);

INSERT INTO users VALUES (
'ruki', 'ruki123'
);

INSERT INTO user_roles VALUES (
'ruki', 'Member'
);

INSERT INTO users VALUES (
'james', 'james123'
);

INSERT INTO user_roles VALUES (
'james', 'Member'
);

INSERT INTO users VALUES (
'mark', 'mark123'
);

INSERT INTO user_roles VALUES (
'mark', 'Member'
);


INSERT INTO users VALUES (
'manager', 'manager123'
);

INSERT INTO user_roles VALUES (
'manager', 'manager'
);

So far we completed the data setup. The next step we are going to configure the tomcat server for in order to support the JDBC realm security.

Step 2 : In this step we are going to look at the tomcat configuration for support JDBC Realm. In order to do that, first comment the following lines in the server.xml file located in apache-tomcat-6.0.26\conf directory. 

1 2	<Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/>

Secondly add the following lines to the 'server.xml' file in which was located in apache-tomcat-6.0.26\conf directory.

1 2 3 4 5

<Realm className="org.apache.catalina.realm.JDBCRealm" debug="99" driverName="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/test?user=root&amp;password=123" userTable="users" userNameCol="user_name" userCredCol="user_pass" userRoleTable="user_roles" roleNameCol="role_name"/>

In the above configuration we mentioned that My SQL driver, connection URL with username and password. In addition to that, we configured the users and user_roles table with the columns names. That's all for the tomcat configuration.

Step 3 : Create a Dynamic web project in Eclipse and name it as 'JDBCRealmSecurity'. The way of creating the dynamic web project as below. 
File > New > Dynamic Web Project

Step 4 : Create a 'index.jsp' file under the WebContent folder. In this jsp file contains the link to access secure jsp file name it as 'confidential.jsp'. 

Step 5 : Create the 'confidential.jsp' file under the WebContent\confidential directory. In this 'confidential.jsp' file contains 'This is confidential resource.' message. 

Step 6 : In this example we will use the 'web.xml' security configuration which we defined in the UserDatabase Realm Security article in step 5.

Step 7 : If run the 'JDBCRealmSecurity' project and access the 'confidential.jsp' page with click the URL defined in the 'index.jsp' the browser will ask the username and password. Until specify the VIP role user's username and password you unable to access the secure page.