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
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
Application Testing.
Application Name is the "ContextRoot" of the application here. so you can test the application at http://localhost:8080/TestSecApp
Screen1 : User Form, Enter the "sarav" account credentials to test
Screen2: Sucessfull login
Screen3: logout screen
Screen4: Test it with some incorrect credentials and you will not be able to login
