Hi all. I'd like to propose a change to JDBCRealm to allow a little more flexibility in the layout of one's users and roles tables. The current implementation (1.20) assumes the roles table will have a column with the same name and meaning as the username column in the users table. In general, I like to avoid using a column with domain meaning as a primary or foreign key. In particular, doing so makes it difficult to allow a user to change his or her username because that change would then ripple through the db to any other place that uses username as a key, such as the roles table. Instead, my change requires the specification of two additional attributes in server.xml's Realm element when using a JDBCRealm, userPrimaryKeyCol and userForeignKeyCol, but frees one from having to track and propagate username changes. userPrimaryKeyCol is whatever column contains the unique (at least within the user table) identifier of a user; userForeignKeyCol is the column in the roles table that joins to that column. Applications built on the existing implementation of JDBCRealm can continue without a database reorganization. They simply have to specify userPrimaryKeyCol and userForeignKeyCol as having the same value as userNameCol.
I tested my changes using Tomcat 4.0.1 and PostgreSQL 7.1.x. I first changed my server.xml to explicitly state that the username column was to be used to join the users and roles tables. Things worked as expected. Then I changed the layout of my db to use different join columns. Again, things worked as expected. Though Tomcat 4.0.1 comes with JDBCRealm 1.19, I didn't have any trouble compiling and running my changes based on 1.20. It appears the only difference between 1.19 and 1.20 is the addition of accessors in the newer version. I generated the patchfile with "diff -u <version 1.20> <my version based on 1.20>." thanks john gregg TechArch Consulting Group Minneapolis, MN
--- JDBCRealm.java.orig Mon Mar 4 17:26:27 2002 +++ JDBCRealm.java Mon Mar 4 16:25:22 2002 @@ -179,6 +179,18 @@ /** + * The column in userRoleTable that joins it to userTable. + */ + protected String userForeignKeyCol = null; + + + /** + * The column in userTable that joins it to userRoleTable. + */ + protected String userPrimaryKeyCol = null; + + + /** * The string manager for this package. */ protected static final StringManager sm = @@ -297,6 +309,40 @@ } /** + * Returns the column in the user table that joins to the user role table. + * + */ + public String getUserPrimaryKeyCol() { + return userPrimaryKeyCol; + } + + /** + * Sets the column in the user table that joins to the user role table. + * + * @param userPrimaryKeyCol The column name + */ + public void setUserPrimaryKeyCol( String userPrimaryKeyCol ) { + this.userPrimaryKeyCol = userPrimaryKeyCol; + } + + /** + * Returns the column in the user role table that joins to the user table. + * + */ + public String getUserForeignKeyCol() { + return userForeignKeyCol; + } + + /** + * Sets the column in the user role table that joins to the user table. + * + * @param userForeignKeyCol The column name + */ + public void setUserForeignKeyCol( String userForeignKeyCol ) { + this.userForeignKeyCol = userForeignKeyCol; + } + + /** * Return the column in the user table that holds the user's credentials. * */ @@ -441,10 +487,12 @@ // Look up the user's credentials String dbCredentials = null; + String userPrimaryKey = null; PreparedStatement stmt = credentials(dbConnection, username); ResultSet rs = stmt.executeQuery(); while (rs.next()) { dbCredentials = rs.getString(1).trim(); + userPrimaryKey = rs.getString(2).trim(); } rs.close(); if (dbCredentials == null) { @@ -472,7 +520,7 @@ // Accumulate the user's roles ArrayList list = new ArrayList(); - stmt = roles(dbConnection, username); + stmt = roles(dbConnection, userPrimaryKey); rs = stmt.executeQuery(); while (rs.next()) { list.add(rs.getString(1).trim()); @@ -540,6 +588,8 @@ if (preparedCredentials == null) { StringBuffer sb = new StringBuffer("SELECT "); sb.append(userCredCol); + sb.append(", "); + sb.append(userPrimaryKeyCol); sb.append(" FROM "); sb.append(userTable); sb.append(" WHERE "); @@ -634,14 +684,14 @@ /** * Return a PreparedStatement configured to perform the SELECT required - * to retrieve user roles for the specified username. + * to retrieve user roles for the specified user primary key. * * @param dbConnection The database connection to be used - * @param username Username for which roles should be retrieved + * @param userPrimaryKey Primary key of user for whom roles should be retrieved * * @exception SQLException if a database error occurs */ - protected PreparedStatement roles(Connection dbConnection, String username) + protected PreparedStatement roles(Connection dbConnection, String userPrimaryKey) throws SQLException { if (preparedRoles == null) { @@ -649,14 +699,27 @@ sb.append(roleNameCol); sb.append(" FROM "); sb.append(userRoleTable); + sb.append(", "); + sb.append(userTable); sb.append(" WHERE "); - sb.append(userNameCol); + sb.append(userRoleTable); + sb.append("."); + sb.append(userForeignKeyCol); + sb.append(" = "); + sb.append(userTable); + sb.append("."); + sb.append(userPrimaryKeyCol); + sb.append(" AND "); + sb.append(userRoleTable); + sb.append("."); + sb.append(userForeignKeyCol); sb.append(" = ?"); + preparedRoles = dbConnection.prepareStatement(sb.toString()); } - preparedRoles.setString(1, username); + preparedRoles.setString(1, userPrimaryKey); return (preparedRoles); }
-- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>