I would like that the users (which are allowed to log on to my site) are loaded from a MySQL database. To do that I want to set up a JDBCRealm for my Apache Tomcat 7 application server.
I've read through the documentation and created a database connection using a JNDI resource (jdbc/foo4). This resource works (I use it already in my application to retrieve data). Which does not seem to work, is the linking of a realm with this resource.
My configuration file looks like this:
src\main\webapp\META-INF\context.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/my-webapp">
<!-- works! -->
<Resource name="jdbc/foo4"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/foo4"
username="root"
password="root"
maxActive="8"
maxIdle="4"
maxWait="10000"
auth="Container"
/>
<!-- Does not seem to work?! -->
<Realm className="org.apache.catalina.realm.DataSourceRealm"
dataSourceName="jdbc/foo4"
userTable="users"
userNameCol="user_name"
userCredCol="user_pass"
userRoleTable="user_roles"
roleNameCol="role_name"/>
</Context>
In my Standard Deployment Descriptor I entered the following:
src\main\webapp\WEB-INF\web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
<security-constraint>
<display-name>General Restriction</display-name>
<web-resource-collection>
<web-resource-name>Entire Application</web-resource-name>
<description>All resources in this application are protected.</description>
<url-pattern>/*</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>admin</role-name>
</auth-constraint>
</security-constraint>
<login-config>
<auth-method>FORM</auth-method>
<realm-name>Administration Area</realm-name>
<form-login-config>
<form-login-page>/login.jsp</form-login-page>
<form-error-page>/error.jsp</form-error-page>
</form-login-config>
</login-config>
<security-role>
<role-name>admin</role-name>
</security-role>
<resource-ref>
<res-ref-name>jdbc/foo4</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
And I used this SQL script to create a sample user:
security.sql
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 INTO users(user_name,user_pass)VALUES('benny','test'); INSERT INTO user_roles(user_name,role_name)VALUES('benny','admin');
But I'm not able to login with user "benny" and password "test". I can login with user "tomcat" and password "tomcat" (because it is defined in %CATALINA_HOME%\conf\tomcat-users.xml) but I can not login with my own user from my MySQL database.
Of course, my Tomcat server is using a MySQL driver (I put "mysql-connector-java-5.1.9.jar" to %CATALINA_HOME%\lib).
I get the following error message when I try to log in:
Sep 08, 2012 7:38:55 PM org.apache.catalina.realm.DataSourceRealm open
Exception performing authentication
javax.naming.NameNotFoundException: Name [jdbc/foo4] is not bound in this Context. Unable to find [jdbc].
Does someone sees the error?
The realm will by default always look for a global datasource (configured in Tomcat side in /conf/server.xml
), however you've definied a local datasource (configured in webapp side in the very same /META-INF/context.xml
), so the realm can't find the datasource. You need to set the localDataSource
attribute to true
.
<Realm ... localDataSource="true" />
See also the Tomcat 7.0 Realm documentation:
localDataSource
When the realm is nested inside a Context element, this this allows the realm to use a DataSource defined for the Context rather than a global DataSource. If not specified, the default is
false
: use a global DataSource.