Bug
"Dan Payne" <[email protected]> Wed, 21 May 2003 00:20:23 -0500
| Newsgroups | gmane.comp.java.securityfilter.user |
|---|---|
| Message-ID | <[email protected]> |
I've got one heck of an issue, and I'm hoping ya'll can help me out.
Basicaly what's happening is when a user attempts to log in but supplies the
wrong username/password combo, a dbConnection.commit() is never sent to the
db and thus the table with the usernames/passwords has a lock on it such
that once an insert command is sent to it it locks even all read access to
the table. This is causing the db queu to fill up to the point my server
keeps crashing due to out of memory errors. Argh!
I've done a fair amount of investigating and do believe I've figured out
what's wrong. I just need to run it by someone to see if I'm thinking
clearly.
At first I suspected the SecurityFilter software (my apologies) but of
course it's not. I'm using the
org.securityfilter.realm.catalina.CatalinaRealmAdapter to wrap an
org.apache.catalina.realm.JDBCRealm. (btw I'm using Tomcat 4.1.18 and BDB
tables with MySQL 3.23.49a-Max) After taking a look at the MySQL logs you
can see where the JDBCRealm does the following:
1 Init DB sotx
1 Query SHOW VARIABLES
1 Query SET autocommit=0
1 Query SELECT password FROM users WHERE username = 'wrongUsername'
3 Init DB sotx
3 Query SHOW VARIABLES
3 Query SET autocommit=0
3 Query INSERT INTO users (username,password) VALUES
('myUsername','myPassword');
now since thread one hasn't issued a commit thread three can't complete the
insert and gets queued up. Now if the user successfully authenticates we get
a different situation:
1 Init DB sotx
1 Query SHOW VARIABLES
1 Query SET autocommit=0
1 Query SELECT password FROM users WHERE username = 'correctUsername'
1 Query SELECT role FROM user_role_xref WHERE username =
'correctUsername'
1 Query commit
3 Init DB sotx
3 Query SHOW VARIABLES
3 Query SET autocommit=0
3 Query INSERT INTO users (username,password) VALUES
('destro','myPassword');
3 Query commit
now thread one appropriately issued the commit statement and thread three
was free to execute the insert.
Looking at the authenticate method in the source code for
org.apache.catalina.realm.JDBCRealm we see that (the class source is
included at the end of this email in its entirety as well as my config
files):
/**
* Return the Principal associated with the specified username and
* credentials, if there is one; otherwise return <code>null</code>.
*
* If there are any errors with the JDBC connection, executing
* the query or anything we return null (don't authenticate). This
* event is also logged, and the connection will be closed so that
* a subsequent request will automatically re-open it.
*
* @param username Username of the Principal to look up
* @param credentials Password or other credentials to use in
* authenticating this username
*/
public Principal authenticate(String username, String credentials) {
Connection dbConnection = null;
try {
// Ensure that we have an open database connection
dbConnection = open();
// Acquire a Principal object for this user
Principal principal = authenticate(dbConnection,
username, credentials);
// Release the database connection we just used
release(dbConnection);
// Return the Principal (if any)
return (principal);
} catch (SQLException e) {
// Log the problem for posterity
log(sm.getString("jdbcRealm.exception"), e);
// Close the connection so that it gets reopened next time
if (dbConnection != null)
close(dbConnection);
// Return "not authenticated" for this request
return (null);
}
}
----------
In the comments to this method we see that the dbConnection is intended to
be closed but no commit is ever issued. (The release method is empty and is
intended for future inclusion of connection pooling). However, in this
method there is a call to an additional authenticate method: Principal
principal = authenticate(dbConnection, username, credentials). This method
involves the following:
public synchronized Principal authenticate(Connection dbConnection,
String username,
String credentials)
throws SQLException {
// Look up the user's credentials
String dbCredentials = null;
PreparedStatement stmt = credentials(dbConnection, username);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
dbCredentials = rs.getString(1).trim();
}
rs.close();
if (dbCredentials == null) {
return (null);
}
<snip/>
// Accumulate the user's roles
ArrayList list = new ArrayList();
stmt = roles(dbConnection, username);
rs = stmt.executeQuery();
while (rs.next()) {
list.add(rs.getString(1).trim());
}
rs.close();
dbConnection.commit();
// Create and return a suitable Principal for this user
return (new GenericPrincipal(this, username, credentials, list));
}
-------------
You can see where the dbConnection.commit(); lies: just after looking up the
user's role which is consistent with the behaviour I've seen. However, prior
to that when we check to see if (dbCredentials == null) { return (null); }
if indeed the user did not enter the right username/password dbCredentials
would equal null and return null and dbConnection.commit() would never have
been called.
Am I looking at this correctly? It appears to be a bug to me unless perhaps
I need to recongfigure my db to be a little looser on the locks. What do
ya'll think?
I appreciate your help.
-Dan
=======================================================
Contents of: org.apache.catalina.realm.JDBCRealmAdapter
-------------------------------------------------------
/*
* $Header:
/home/cvs/jakarta-tomcat-4.0/catalina/src/share/org/apache/catalina/realm/JD
BCRealm.java,v 1.21 2002/06/09 02:19:43 remm Exp $
* $Revision: 1.21 $
* $Date: 2002/06/09 02:19:43 $
*
* ====================================================================
* The Apache Software License, Version 1.1
*
* Copyright (c) 1999 The Apache Software Foundation. All rights
* reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in
* the documentation and/or other materials provided with the
* distribution.
*
* 3. The end-user documentation included with the redistribution, if
* any, must include the following acknowlegement:
* "This product includes software developed by the
* Apache Software Foundation (http://www.apache.org/)."
* Alternately, this acknowlegement may appear in the software itself,
* if and wherever such third-party acknowlegements normally appear.
*
* 4. The names "The Jakarta Project", "Tomcat", and "Apache Software
* Foundation" must not be used to endorse or promote products derived
* from this software without prior written permission. For written
* permission, please contact [email protected]
*
* 5. Products derived from this software may not be called "Apache"
* nor may "Apache" appear in their names without prior written
* permission of the Apache Group.
*
* THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
* WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
* OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
* ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
* USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
* OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
* OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
* SUCH DAMAGE.
* ====================================================================
*
* This software consists of voluntary contributions made by many
* individuals on behalf of the Apache Software Foundation. For more
* information on the Apache Software Foundation, please see
* <http://www.apache.org/>.
*
* [Additional notices, if required by prior licensing conditions]
*
*/
package org.apache.catalina.realm;
import java.io.File;
import java.security.MessageDigest;
import java.security.Principal;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;
import org.apache.catalina.Container;
import org.apache.catalina.Lifecycle;
import org.apache.catalina.LifecycleEvent;
import org.apache.catalina.LifecycleException;
import org.apache.catalina.LifecycleListener;
import org.apache.catalina.Logger;
import org.apache.catalina.Realm;
import org.apache.catalina.util.HexUtils;
import org.apache.catalina.util.LifecycleSupport;
import org.apache.catalina.util.StringManager;
import org.apache.catalina.util.Base64;
/**
*
* Implmentation of <b>Realm</b> that works with any JDBC supported database.
* See the JDBCRealm.howto for more details on how to set up the database and
* for configuration options.
*
* <p><strong>TODO</strong> - Support connection pooling (including message
* format objects) so that <code>authenticate()</code> does not have to be
* synchronized.</p>
*
* @author Craig R. McClanahan
* @author Carson McDonald
* @author Ignacio Ortega
* @version $Revision: 1.21 $ $Date: 2002/06/09 02:19:43 $
*/
public class JDBCRealm
extends RealmBase {
// ----------------------------------------------------- Instance
Variables
/**
* The connection username to use when trying to connect to the
database.
*/
protected String connectionName = null;
/**
* The connection URL to use when trying to connect to the database.
*/
protected String connectionPassword = null;
/**
* The connection URL to use when trying to connect to the database.
*/
protected String connectionURL = null;
/**
* The connection to the database.
*/
protected Connection dbConnection = null;
/**
* Instance of the JDBC Driver class we use as a connection factory.
*/
protected Driver driver = null;
/**
* The JDBC driver to use.
*/
protected String driverName = null;
/**
* Descriptive information about this Realm implementation.
*/
protected static final String info =
"org.apache.catalina.realm.JDBCRealm/1.0";
/**
* Descriptive information about this Realm implementation.
*/
protected static final String name = "JDBCRealm";
/**
* The PreparedStatement to use for authenticating users.
*/
protected PreparedStatement preparedCredentials = null;
/**
* The PreparedStatement to use for identifying the roles for
* a specified user.
*/
protected PreparedStatement preparedRoles = null;
/**
* The column in the user role table that names a role
*/
protected String roleNameCol = null;
/**
* The string manager for this package.
*/
protected static final StringManager sm =
StringManager.getManager(Constants.Package);
/**
* The column in the user table that holds the user's credintials
*/
protected String userCredCol = null;
/**
* The column in the user table that holds the user's name
*/
protected String userNameCol = null;
/**
* The table that holds the relation between user's and roles
*/
protected String userRoleTable = null;
/**
* The table that holds user data.
*/
protected String userTable = null;
// -------------------------------------------------------------
Properties
/**
* Return the username to use to connect to the database.
*
*/
public String getConnectionName() {
return connectionName;
}
/**
* Set the username to use to connect to the database.
*
* @param connectionName Username
*/
public void setConnectionName(String connectionName) {
this.connectionName = connectionName;
}
/**
* Return the password to use to connect to the database.
*
*/
public String getConnectionPassword() {
return connectionPassword;
}
/**
* Set the password to use to connect to the database.
*
* @param connectionPassword User password
*/
public void setConnectionPassword(String connectionPassword) {
this.connectionPassword = connectionPassword;
}
/**
* Return the URL to use to connect to the database.
*
*/
public String getConnectionURL() {
return connectionURL;
}
/**
* Set the URL to use to connect to the database.
*
* @param connectionURL The new connection URL
*/
public void setConnectionURL( String connectionURL ) {
this.connectionURL = connectionURL;
}
/**
* Return the JDBC driver that will be used.
*
*/
public String getDriverName() {
return driverName;
}
/**
* Set the JDBC driver that will be used.
*
* @param driverName The driver name
*/
public void setDriverName( String driverName ) {
this.driverName = driverName;
}
/**
* Return the column in the user role table that names a role.
*
*/
public String getRoleNameCol() {
return roleNameCol;
}
/**
* Set the column in the user role table that names a role.
*
* @param roleNameCol The column name
*/
public void setRoleNameCol( String roleNameCol ) {
this.roleNameCol = roleNameCol;
}
/**
* Return the column in the user table that holds the user's
credentials.
*
*/
public String getUserCredCol() {
return userCredCol;
}
/**
* Set the column in the user table that holds the user's credentials.
*
* @param userCredCol The column name
*/
public void setUserCredCol( String userCredCol ) {
this.userCredCol = userCredCol;
}
/**
* Return the column in the user table that holds the user's name.
*
*/
public String getUserNameCol() {
return userNameCol;
}
/**
* Set the column in the user table that holds the user's name.
*
* @param userNameCol The column name
*/
public void setUserNameCol( String userNameCol ) {
this.userNameCol = userNameCol;
}
/**
* Return the table that holds the relation between user's and roles.
*
*/
public String getUserRoleTable() {
return userRoleTable;
}
/**
* Set the table that holds the relation between user's and roles.
*
* @param userRoleTable The table name
*/
public void setUserRoleTable( String userRoleTable ) {
this.userRoleTable = userRoleTable;
}
/**
* Return the table that holds user data..
*
*/
public String getUserTable() {
return userTable;
}
/**
* Set the table that holds user data.
*
* @param userTable The table name
*/
public void setUserTable( String userTable ) {
this.userTable = userTable;
}
// --------------------------------------------------------- Public
Methods
/**
* Return the Principal associated with the specified username and
* credentials, if there is one; otherwise return <code>null</code>.
*
* If there are any errors with the JDBC connection, executing
* the query or anything we return null (don't authenticate). This
* event is also logged, and the connection will be closed so that
* a subsequent request will automatically re-open it.
*
* @param username Username of the Principal to look up
* @param credentials Password or other credentials to use in
* authenticating this username
*/
public Principal authenticate(String username, String credentials) {
Connection dbConnection = null;
try {
// Ensure that we have an open database connection
dbConnection = open();
// Acquire a Principal object for this user
Principal principal = authenticate(dbConnection,
username, credentials);
// Release the database connection we just used
release(dbConnection);
// Return the Principal (if any)
return (principal);
} catch (SQLException e) {
// Log the problem for posterity
log(sm.getString("jdbcRealm.exception"), e);
// Close the connection so that it gets reopened next time
if (dbConnection != null)
close(dbConnection);
// Return "not authenticated" for this request
return (null);
}
}
// -------------------------------------------------------- Package
Methods
// ------------------------------------------------------ Protected
Methods
/**
* Return the Principal associated with the specified username and
* credentials, if there is one; otherwise return <code>null</code>.
*
* @param dbConnection The database connection to be used
* @param username Username of the Principal to look up
* @param credentials Password or other credentials to use in
* authenticating this username
*
* @exception SQLException if a database error occurs
*/
public synchronized Principal authenticate(Connection dbConnection,
String username,
String credentials)
throws SQLException {
// Look up the user's credentials
String dbCredentials = null;
PreparedStatement stmt = credentials(dbConnection, username);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
dbCredentials = rs.getString(1).trim();
}
rs.close();
if (dbCredentials == null) {
return (null);
}
// Validate the user's credentials
boolean validated = false;
if (hasMessageDigest()) {
// Hex hashes should be compared case-insensitive
validated =
(digest(credentials).equalsIgnoreCase(dbCredentials));
} else
validated = (digest(credentials).equals(dbCredentials));
if (validated) {
if (debug >= 2)
log(sm.getString("jdbcRealm.authenticateSuccess",
username));
} else {
if (debug >= 2)
log(sm.getString("jdbcRealm.authenticateFailure",
username));
return (null);
}
// Accumulate the user's roles
ArrayList list = new ArrayList();
stmt = roles(dbConnection, username);
rs = stmt.executeQuery();
while (rs.next()) {
list.add(rs.getString(1).trim());
}
rs.close();
dbConnection.commit();
// Create and return a suitable Principal for this user
return (new GenericPrincipal(this, username, credentials, list));
}
/**
* Close the specified database connection.
*
* @param dbConnection The connection to be closed
*/
protected void close(Connection dbConnection) {
// Do nothing if the database connection is already closed
if (dbConnection == null)
return;
// Close our prepared statements (if any)
try {
preparedCredentials.close();
} catch (Throwable f) {
;
}
try {
preparedRoles.close();
} catch (Throwable f) {
;
}
// Close this database connection, and log any errors
try {
dbConnection.close();
} catch (SQLException e) {
log(sm.getString("jdbcRealm.close"), e); // Just log it here
}
// Release resources associated with the closed connection
this.dbConnection = null;
this.preparedCredentials = null;
this.preparedRoles = null;
}
/**
* Return a PreparedStatement configured to perform the SELECT required
* to retrieve user credentials for the specified username.
*
* @param dbConnection The database connection to be used
* @param username Username for which credentials should be retrieved
*
* @exception SQLException if a database error occurs
*/
protected PreparedStatement credentials(Connection dbConnection,
String username)
throws SQLException {
if (preparedCredentials == null) {
StringBuffer sb = new StringBuffer("SELECT ");
sb.append(userCredCol);
sb.append(" FROM ");
sb.append(userTable);
sb.append(" WHERE ");
sb.append(userNameCol);
sb.append(" = ?");
preparedCredentials =
dbConnection.prepareStatement(sb.toString());
}
preparedCredentials.setString(1, username);
return (preparedCredentials);
}
/**
* Return a short name for this Realm implementation.
*/
protected String getName() {
return (this.name);
}
/**
* Return the password associated with the given principal's user name.
*/
protected String getPassword(String username) {
return (null);
}
/**
* Return the Principal associated with the given user name.
*/
protected Principal getPrincipal(String username) {
return (null);
}
/**
* Open (if necessary) and return a database connection for use by
* this Realm.
*
* @exception SQLException if a database error occurs
*/
protected Connection open() throws SQLException {
// Do nothing if there is a database connection already open
if (dbConnection != null)
return (dbConnection);
// Instantiate our database driver if necessary
if (driver == null) {
try {
Class clazz = Class.forName(driverName);
driver = (Driver) clazz.newInstance();
} catch (Throwable e) {
throw new SQLException(e.getMessage());
}
}
// Open a new connection
Properties props = new Properties();
if (connectionName != null)
props.put("user", connectionName);
if (connectionPassword != null)
props.put("password", connectionPassword);
dbConnection = driver.connect(connectionURL, props);
dbConnection.setAutoCommit(false);
return (dbConnection);
}
/**
* Release our use of this connection so that it can be recycled.
*
* @param dbConnnection The connection to be released
*/
protected void release(Connection dbConnection) {
; // NO-OP since we are not pooling anything
}
/**
* Return a PreparedStatement configured to perform the SELECT required
* to retrieve user roles for the specified username.
*
* @param dbConnection The database connection to be used
* @param username Username for which roles should be retrieved
*
* @exception SQLException if a database error occurs
*/
protected PreparedStatement roles(Connection dbConnection, String
username)
throws SQLException {
if (preparedRoles == null) {
StringBuffer sb = new StringBuffer("SELECT ");
sb.append(roleNameCol);
sb.append(" FROM ");
sb.append(userRoleTable);
sb.append(" WHERE ");
sb.append(userNameCol);
sb.append(" = ?");
preparedRoles =
dbConnection.prepareStatement(sb.toString());
}
preparedRoles.setString(1, username);
return (preparedRoles);
}
// ------------------------------------------------------ Lifecycle
Methods
/**
*
* Prepare for active use of the public methods of this Component.
*
* @exception LifecycleException if this component detects a fatal error
* that prevents it from being started
*/
public void start() throws LifecycleException {
// Validate that we can open our connection
try {
open();
} catch (SQLException e) {
throw new LifecycleException(sm.getString("jdbcRealm.open"), e);
}
// Perform normal superclass initialization
super.start();
}
/**
* Gracefully shut down active use of the public methods of this
Component.
*
* @exception LifecycleException if this component detects a fatal error
* that needs to be reported
*/
public void stop() throws LifecycleException {
// Perform normal superclass finalization
super.stop();
// Close any open DB connection
close(this.dbConnection);
}
}
===================================================================
Contents of: securityfilter-config.xml (it's been snipped up a bit)
-------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE securityfilter-config PUBLIC
"-//SecurityFilter.org//DTD Security Filter Configuration 1.0//EN"
"http://www.securityfilter.org/dtd/securityfilter-config_1_0.dtd">
<securityfilter-config>
<security-constraint>
<display-name>Security Constraints for Registered Users</display-name>
<web-resource-collection>
<web-resource-name>User Account Manager</web-resource-name>
<url-pattern>/users/manager/*</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>user</role-name>
</auth-constraint>
</security-constraint>
<!-- LOGIN CONFIGURATIONS -->
<login-config>
<auth-method>FORM</auth-method>
<form-login-config>
<form-login-page>/users/login/index.jsp</form-login-page>
<form-error-page>/users/login/error.jsp</form-error-page>
<form-default-page>/home.do</form-default-page>
</form-login-config>
</login-config>
<!-- start with a Catalina realm adapter to wrap the Catalina realm
definied below -->
<realm className="org.securityfilter.realm.catalina.CatalinaRealmAdapter"/>
<realm className="org.apache.catalina.realm.JDBCRealm">
<realm-param name="driverName" value="org.gjt.mm.mysql.Driver"/>
<realm-param name="debug" value="99"/>
<realm-param name="connectionURL"
value="jdbc:mysql://localhost:3306/myDb?user=myUser&password=myPassword"
/>
<realm-param name="userTable" value="users"/>
<realm-param name="userNameCol" value="username"/>
<realm-param name="userCredCol" value="password"/>
<realm-param name="userRoleTable" value="user_role_xref"/>
<realm-param name="roleNameCol" value="role"/>
</realm>
</securityfilter-config>
EOF
-------------------------------------------------------
This SF.net email is sponsored by: ObjectStore.
If flattening out C++ or Java code to make your application fit in a
relational database is painful, don't do it! Check out ObjectStore.
Now part of Progress Software. http://www.objectstore.net/sourceforge