How do I use a JDBC Realm with Tomcat and MySQL?
Authentication can be controlled by a web application or by the container (such
as
Tomcat) that the web application runs in. Tomcat's container-managed security
is
based on realms. A realm contains the names of users, their passwords, and
roles.
There are 4 types of authentication mechanisms provided by Tomcat
"out-of-the-box":
* Basic Authentication
*
Form-based Authentication
* Custom Authentication
* Digest Authentication
If you configure for Basic Authentication, you will get prompted with a login
dialog
box when you attempt to access a protected resource. If you use Form-based
authentication,
your users will be redirected to a HTML page that allows them to login, when
they attempt
to access a protected resource. Custom authentication is used when you require
additional
information from the user before you allow him to login, and Digest
authentication is used
when you need an added level of security using hashed passwords.
1) Tomcat can be configured for other more robust realm alternatives. One such
alternative
is a JDBC realm. Benefits of a JDBC realm over a UserDatabase realm include
being able to
dynamically update the JDBC realm data at runtime rather than only at startup.
In Tomcat's server.xml file, we can see that the
UserDatabase realm is uncommented
while a sample JDBC realm for MySQL is commented out:
section of Tomcat's server.xml file
.....
<Realm className="org.apache.catalina.realm.UserDatabaseRealm"
resourceName="UserDatabase"/>
....
<!--
<Realm className="org.apache.catalina.realm.JDBCRealm"
driverName="org.gjt.mm.mysql.Driver"
connectionURL="jdbc:mysql://localhost/authority"
connectionName="test" connectionPassword="test"
userTable="users" userNameCol="user_name" userCredCol="user_pass"
userRoleTable="user_roles" roleNameCol="role_name" />
-->
....
2) let's create a MySQL database to store the realm user names, passwords, and
roles.
I created a database named tomcat_realm.
# mysql -u root -p
mysql> CREATE DATABASE tomcat_realm;
USE
tomcat_realm;
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 ('tomcat', 'tomcatpass');
------------->User Name and Password
INSERT INTO tomcat_users (user_name, password) VALUES ('user1', 'userpass');
INSERT INTO tomcat_roles (role_name) VALUES ('tomcat'); ---------------->
Role name i.e tomcat and manager are the two role_name
INSERT INTO tomcat_roles
(role_name) VALUES ('manager');
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('tomcat',
'tomcat'); --
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('user1',
'manager'); | define username & role_name
INSERT INTO tomcat_users_roles (user_name, role_name) VALUES ('user1',
'tomcat'); --
COMMIT;
For a JDBC realm, you need a database with basically two tables.
One table needs to have a column for user names and another column for
passwords.
The second table needs to have a column for user names and a column for roles..
The database, tables, and columns can be named anything you want them to be
named,
since all of them can be specified in the JDBC realm entry in server.xml.
However,
the user name column of the first table and the user name column of the second
table need to have the same name.
Next...
mysql> use tomcat_realm;
mysql> show
tables;
+------------------------+
| Tables_in_tomcat_realm |
+------------------------+
| tomcat_roles |
| tomcat_users |
| tomcat_users_roles |
+------------------------+
Next, create a user that Tomcat can use to access the tomcat_realm database..
I'll call the user 'tom'. I'll grant the user the ability to select from all
of the tomcat_realm tables.
USE mysql;
CREATE USER 'tom'@'localhost' IDENTIFIED BY 'tompass';
GRANT SELECT ON tomcat_realm.* TO t...@localhost;
quit
3) Next, I will update my Tomcat server.xml file to connect to the
MySQL realm database that I just created. I'll comment out the UserDatabase
realm section.
I'll create a JDBC realm entry containing all of the correct information, as
shown here:
Section of Tomcat's server.xml file
....
<!--
<Realm
className="org.apache.catalina.realm.UserDatabaseRealm"
resourceName="UserDatabase"/>
-->
....
<Realm className="org.apache.catalina.realm.JDBCRealm"
driverName="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/tomcat_realm"
connectionName="tom" connectionPassword="tompass"
userTable="tomcat_users" userNameCol="user_name"
userCredCol="password"
userRoleTable="tomcat_users_roles" roleNameCol="role_name" />
....
4) After this, you need to put the MySQL jar file i.e jdbc driver to Tomcat's
classpath so that Tomcat can talk with the MySQL database.
If we don't do this, we will receive an error message when Tomcat starts up,
such as:
Download jdbc driver from mysql site or go through below link:
http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.0.8.tar.gz/from/pick?file=Downloads/Connector-J/mysql-connector-java-5.0.8.tar..gz&mirror=pick#mirrors
Im using jdbc driver version: mysql-connector-java-5.0.8-bin.jar
I added the mysql-connector-java-5.0.8-bin.jar file to my Tomcat's common/lib
directory
and /MyFirst/WB-INF/lib/. When Tomcat starts up, it will find the jar file in
common/lib.
5) I added a Content for my 'MyFirst' project to my Tomcat server.xml file.
<Context docBase="/opt/tomcat/webapss/MyFirst" path="/MyFirst"
reloadable="true"/>
6) Prepare the Necessary Login and Error HTML Files
I am using FORM-based authentication.
I created 2 HTML files i.e login.html and autherr.html under /MyFirst/ -- one
for displaying the login page
and one to show the user that he has keyed in the wrong password or
username.
Here is the login page for this exercise:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd";>
<html>
<head>
<title>Login Page</title>
</head>
<body>
<h1>Login to MyFirst</h1>
<p>
If you have been issued a username and password, key them in here now!
</p>
<form method="POST" action="j_security_check">
Username : <input type="text" size="15" maxlength="25"
name="j_username"><br><br>
Password : <input type="password" size="15" maxlength="25"
name="j_password"><br><br>
<input value="Login" type="submit"> <input value="Clear"
type="reset">
</form>
</body>
</html>
Here is the error page:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01
Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd";>
<html>
<head>
<title>Authentication Error!</title>
</head>
<body>
<h1>Authentication Error!</h1>
<p>
Oops! You either keyed in the wrong username or password.
</p>
<a href="javascript:history.back(1)">Try again ?</a>
</body>
</html>
7) Edit the Web Application's web.xml To Require Authentication
We need to configure the web application to require authentication as well.
At this stage, we determine which resources to protect, who has access, and
how we want to protect these resources. That is, we define the directories
and/or
the files to protect (html, jsp, image files or all files), which role has
access and
which type of authentication we want to use (Form-based, Basic, Custom or
Digest).
This is what we want to
protect:
* Everything in the MyFirst application, that is, all HTML files, image
files, JSP files, servlets, text files, everything!
* Any and all access methods, that is, HTTP GET, PUT, POST, DELETE, will
get the login prompt.
Who can access it:
* Only users with the 'tomcat' role are allowed to access the MyFirst web
application (IMP)
How we want to protect the resources:
* We will use Form-based authentication
* The login page is login.html
* The error page, if the user keys in the wrong username or password is
autherr.html
We also need to define the roles that will have access. For this exercise, we
only want users with the 'tomcat' role to have access.
To express these requirements, we key in the stanza below into the web
application's web.xml file,
after the <servlet-mapping> section and before the terminating </web-app>
tag.
<security-constraint>
<web-resource-collection>
<web-resource-name>MyFirst</web-resource-name>
<description> accessible by authenticated users of the tomcat
role</description>
<url-pattern>/*</url-pattern>
<http-method>GET</http-method>
<http-method>POST</http-method>
<http-method>PUT</http-method>
<http-method>DELETE</http-method>
</web-resource-collection>
<auth-constraint>
<description>These roles are allowed access</description>
<role-name>tomcat</role-name>
</auth-constraint>
</security-constraint>
<login-config>
<auth-method>FORM</auth-method>
<realm-name>MyFirst Protected Area</realm-name>
<form-login-config>
<form-login-page>/login.html</form-login-page>
<form-error-page>/autherr.html</form-error-page>
</form-login-config>
</login-config>
<security-role>
<description>Only 'tomcat' role is allowed to access this web
application</description>
<role-name>tomcat</role-name>
</security-role>
Start Tomcat and Test
http://localhost:8080/MyFirst/HelloWorld
****************************************************************************************************************************
NOTE:
* Key in the username and password of a user who does *not* have the tomcat
role. You should get the error page.
* Next, key in
the username and password of a user who has the 'tomcat' role, and
click the Login button. You should then see the familiar HelloWorld servlet
page..
* If you get any error please kindly read file
/opt/tomcat/logs/catalina_log.xxxx-xx-xx.txt.
Changing MySQL root user password using MySQL sql command see below details:
$ mysql -u root -p
2) Use mysql database (type command at mysql> prompt):
mysql> use mysql;
3) Change password for user shaikh:
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='shaikh';
4) Reload privileges:
mysql> flush privileges;
mysql>
mysql> quit
*************************************END*****************************************************
Regards,
Mohiddin
Yahoo! India has a new look. Take a sneak peek http://in.yahoo.com/trynew
[Non-text portions of this message have been removed]
