Apache Tomcat JDBC Realm with mysql and Form Based Web Application.

In my earlier post, we have designed and tested the Web Application in Apache Tomcat with Default Authentication Provider . this time we will use JDBC realm as the Authentication provider and we will use mysql as the Database.

Let's get started

Note*: I presume that you have basic knowledge of Linux user setup and Tomcat installation and administration. So I am skipping few basic steps here, I believe you should be able to understand. in case of any doubts of further support please comment.

Technology Stack

Operating System: RHEL6

MySQL Version: 5.7.12

Tomcat Version: 7.0.69

MYSQL SETUP

Download the product here

Install mysql community server using RPM and YUM

[root@localhost mysql]# yum install mysql-community-{server,client,common,libs}-* mysql-5.*
Loaded plugins: product-id, refresh-packagekit, security, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Setting up Install Process
Examining mysql-community-server-5.7.12-1.el6.x86_64.rpm: mysql-community-server-5.7.12-1.el6.x86_64
Marking mysql-community-server-5.7.12-1.el6.x86_64.rpm to be installed
Examining mysql-community-client-5.7.12-1.el6.x86_64.rpm: mysql-community-client-5.7.12-1.el6.x86_64
Marking mysql-community-client-5.7.12-1.el6.x86_64.rpm to be installed
Examining mysql-community-common-5.7.12-1.el6.x86_64.rpm: mysql-community-common-5.7.12-1.el6.x86_64
Marking mysql-community-common-5.7.12-1.el6.x86_64.rpm to be installed
Examining mysql-community-libs-5.7.12-1.el6.x86_64.rpm: mysql-community-libs-5.7.12-1.el6.x86_64
Marking mysql-community-libs-5.7.12-1.el6.x86_64.rpm to be installed
Examining mysql-community-libs-compat-5.7.12-1.el6.x86_64.rpm: mysql-community-libs-compat-5.7.12-1.el6.x86_64
Marking mysql-community-libs-compat-5.7.12-1.el6.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.12-1.el6 will be installed
---> Package mysql-community-common.x86_64 0:5.7.12-1.el6 will be installed
---> Package mysql-community-libs.x86_64 0:5.7.12-1.el6 will be obsoleting
---> Package mysql-community-libs-compat.x86_64 0:5.7.12-1.el6 will be obsoleting
---> Package mysql-community-server.x86_64 0:5.7.12-1.el6 will be installed
---> Package mysql-libs.x86_64 0:5.1.73-3.el6_5 will be obsoleted
--> Finished Dependency Resolution

Dependencies Resolved

Install 5 Package(s)

Total size: 843 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
.....
.....

Installed:
mysql-community-client.x86_64 0:5.7.12-1.el6 mysql-community-common.x86_64 0:5.7.12-1.el6 mysql-community-libs.x86_64 0:5.7.12-1.el6
mysql-community-libs-compat.x86_64 0:5.7.12-1.el6 mysql-community-server.x86_64 0:5.7.12-1.el6

Complete!

[root@localhost mysql]#

Start the mysql Server

[root@localhost mysql]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]

Check the version of mysql server

[root@localhost mysql]# mysqladmin --version
mysqladmin Ver 8.42 Distrib 5.7.12, for Linux on x86_64

grep 'temporary password' /var/log/mysqld.log

[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
2016-05-13T18:33:49.054595Z 1 [Note] A temporary password is generated for root@localhost: >!ds6sfiEijp

Login with that temporary Password

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Update the password for "root" account

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'secure';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mwi@4321';
Query OK, 0 rows affected (0.02 sec)

Execute the SQL queries given below one by one (or) All at once.

DROP DATABASE IF EXISTS mwidb;
CREATE DATABASE mwidb;
USE mwidb;
CREATE TABLE tomcat_users (
user_name varchar(20) NOT NULL PRIMARY KEY,
password varchar(32) NOT NULL
);
CREATE TABLE tomcat_roles (
role_name varchar(20) NOT NULL PRIMARY KEY
);
CREATE TABLE tomcat_users_roles (
user_name varchar(20) NOT NULL,
role_name varchar(20) NOT NULL,
PRIMARY KEY (user_name, role_name),
CONSTRAINT tomcat_users_roles_foreign_key_1 FOREIGN KEY (user_name) REFERENCES tomcat_users (user_name),
CONSTRAINT tomcat_users_roles_foreign_key_2 FOREIGN KEY (role_name) REFERENCES tomcat_roles (role_name)
);
INSERT INTO tomcat_users (user_name, password) VALUES ('sarav', 'mwi@4321');
INSERT INTO tomcat_users (user_name, password) VALUES ('mwiuser', 'mwiuser');
INSERT INTO tomcat_roles (role_name) VALUES ('webuser');
INSERT INTO tomcat_roles (role_name) VALUES ('manager');
INSERT INTO tomcat_roles (role_name) VALUES ('manager-gui');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('mwiuser', 'webuser');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('sarav', 'webuser');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('sarav', 'manager-gui');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('sarav', 'manager');

COMMIT;

mysql> DROP DATABASE IF EXISTS mwidb;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE mwidb;

Query OK, 1 row affected (0.00 sec)

mysql> USE mwidb;

Database changed

mysql> CREATE TABLE tomcat_users (

-> user_name varchar(20) NOT NULL PRIMARY KEY,

-> password varchar(32) NOT NULL

-> );

Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE tomcat_roles (

-> role_name varchar(20) NOT NULL PRIMARY KEY

-> );

Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE tomcat_users_roles (

-> user_name varchar(20) NOT NULL,

-> role_name varchar(20) NOT NULL,

-> PRIMARY KEY (user_name, role_name),

-> CONSTRAINT tomcat_users_roles_foreign_key_1 FOREIGN KEY (user_name) REFERENCES tomcat_users (user_name),

-> CONSTRAINT tomcat_users_roles_foreign_key_2 FOREIGN KEY (role_name) REFERENCES tomcat_roles (role_name)

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tomcat_users (user_name, password) VALUES ('sarav', 'Sara@4321');

Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO tomcat_users (user_name, password) VALUES ('mwiuser', 'mwiuser');

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO tomcat_roles (role_name) VALUES ('webuser');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tomcat_roles (role_name) VALUES ('manager');

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO tomcat_roles (role_name) VALUES ('manager-gui');

Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('mwiuser', 'webuser');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('sarav', 'webuser');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('sarav', 'manager');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('sarav', 'manager-gui');

Query OK, 1 row affected (0.10 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

Verify the tables and its record set.

mysql> show tables;

+--------------------+

| Tables_in_mwidb |

+--------------------+

| tomcat_roles |

| tomcat_users |

| tomcat_users_roles |

+--------------------+

3 rows in set (0.01 sec)

mysql> select * from tomcat_roles;

+-----------+

| role_name |

+-----------+

| manager |

| webuser |

+-----------+

2 rows in set (0.00 sec)

mysql> select * from tomcat_users;

+-----------+-----------+

| user_name | password |

+-----------+-----------+

| mwiuser | mwiuser |

| sarav | mwi@4321 |

+-----------+-----------+

2 rows in set (0.00 sec)

mysql> select * from tomcat_users_roles;

+-----------+-------------+

| user_name | role_name |

+-----------+-------------+

| sarav | manager |

| sarav | manager-gui |

| mwiuser | webuser |

| sarav | webuser |

+-----------+-------------+

4 rows in set (0.00 sec)

TOMCAT SETUP

Download the Tomcat Application Server ZIP Distribution here and un compress it under /opt/tomcat directory

[tomcat@localhost tomcat]$ pwd

/opt/tomcat

[tomcat@localhost tomcat]$ ls -lrt

total 4

drwxr-xr-x. 9 tomcat tomcat 4096 Apr 11 10:59 apache-tomcat-7.0.69

Download the JConnecter JDBC Driver from here and get the jar file

[sarav@localhost Downloads]$ ls -rlt

total 138660

-rw-rw-r--. 1 sarav sarav 3899019 May 13 12:44 mysql-connector-java-5.1.39.tar.gz

[sarav@localhost Downloads]$ pwd

/home/sarav/Downloads

[sarav@localhost Downloads]$ gunzip mysql-connector-java-5.1.39.tar.gz

[sarav@localhost Downloads]$ ls -lrt

total 145824

-rw-rw-r--. 1 sarav sarav 138082565 May 1 22:07 jdk-7u79-linux-x64.rpm

-rw-rw-r--. 1 sarav sarav 11233280 May 13 12:44 mysql-connector-java-5.1.39.tar

[sarav@localhost Downloads]$ tar -xf mysql-connector-java-5.1.39.tar

[sarav@localhost Downloads]$ ls -lrt

total 145828

-rw-rw-r--. 1 sarav sarav 138082565 May 1 22:07 jdk-7u79-linux-x64.rpm

drwxr-xr-x. 4 sarav sarav 4096 May 4 04:11 mysql-connector-java-5.1.39

-rw-rw-r--. 1 sarav sarav 11233280 May 13 12:44 mysql-connector-java-5.1.39.tar

[sarav@localhost Downloads]$ ls -lrt mysql-connector-java-5.1.39/mysql-connector-java-5.1.39-bin.jar

-rw-r--r--. 1 sarav sarav 989497 May 4 04:11 mysql-connector-java-5.1.39/mysql-connector-java-5.1.39-bin.jar

Copy the jar file to Lib directory of Tomcat

[sarav@localhost Downloads]$ cp mysql-connector-java-5.1.39/mysql-connector-java-5.1.39-bin.jar /opt/tomcat/apache-tomcat-7.0.69/lib/ls -lrt^C

[sarav@localhost Downloads]$ ls -lrt /opt/tomcat/apache-tomcat-7.0.69/lib/mysql-connector-java-5.1.39-bin.jar 

-rw-rw-r--. 1 tomcat tomcat 989497 May  4 04:11 /opt/tomcat/apache-tomcat-7.0.69/lib/mysql-connector-java-5.1.39-bin.jar

Server.xml changes

Find the section LockOutRealm and Comment  the below lines with <!--     -->

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

resourceName="UserDatabase"/>

and Add the JDBC Realm values

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

driverName="com.mysql.jdbc.Driver"

connectionURL="jdbc:mysql://localhost:3306/mwidb" connectionName="root" connectionPassword="mwi@4321" userTable="tomcat_users" userNameCol="user_name" userCredCol="password" userRoleTable="tomcat_users_roles" roleNameCol="role_name" />

Here,

1) jdbc:mysql://localhost:3306/mwidb is the JDBC URL and mwidb is the DataBase Name2) connectionName is the username I am using to access the Database3) connectionPassword is the password of the connectionName account mentioned.

Find the Below Resource and Comment this as well with

<Resource name="UserDatabase" auth="Container"

              type="org.apache.catalina.UserDatabase"

              description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" pathname="conf/tomcat-users.xml" />

Now restart the tomcat server with ./shutdown.sh & ./startup.sh scripts

Access the manager app at http://localhost:8080/manager/ and enter the credentials of account "sarav" and you should be able to get through without any issues as it is having "manager-gui" privilage.

Application Design and Deployment

Hope you have checked out my previous tutorial about web application designing and form based auth at http://www.mwinventory.in/2015/12/apache-tomcat-web-application.html

we can use the same Application here as well (without any code changes). you can download the war file from here.

Once you have downloaded, copy the war file to tomcat/webapps location to get it deployed. (Hot Deployment)

[tomcat@localhost webapps]$ ls -lrt

total 24

drwxr-xr-x. 3 tomcat tomcat 4096 Apr 11 10:58 ROOT

drwxr-xr-x. 5 tomcat tomcat 4096 Apr 11 10:58 manager

drwxr-xr-x. 5 tomcat tomcat 4096 Apr 11 10:58 host-manager

drwxr-xr-x. 7 tomcat tomcat 4096 Apr 11 10:58 examples

drwxr-xr-x. 14 tomcat tomcat 4096 Apr 11 10:58 docs

drwxrwxr-x. 3 tomcat tomcat 4096 May 13 14:30 TestSecApp

[tomcat@localhost webapps]$ pwd

/opt/tomcat/apache-tomcat-7.0.69/webapps

Restart the tomcat instance after application installation and make sure the app gets deployed by checking the logs. you should be able to see the app deploying message

INFO: Deploying web application directory /opt/tomcat/apache-tomcat-7.0.69/webapps/TestSecApp