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]>
