I thought I'd toss in another change to JDBC Realm, specifically the
ability to override the SQL query used for the password and the roles.
It let's you have a config like:
<!--
<Realm className="org.apache.catalina.realm.JDBCRealm" debug="99"
driverName="sun.jdbc.odbc.JdbcOdbcDriver"
connectionURL="jdbc:odbc:CATALINA"
<!-- for both the user and the roles queries only the first
column
in the result set is looked at for the password and
role -->
userQuery="SELECT user_pass FROM users WHERE user_name = ?"
userRoleQuery="SELECT role_name FROM roles WHERE user_name =
?"
/>
This gives people the ability to use SQL statements that may involve
joins, which is something I need to get the roles correctly.
Paul Lamb
>
> Hola David:
>
> Sorry for the delay, just catched your message in the archive.
>
> >
> > * Would it be acceptable for me to submit a patch for this code that
> > currently gets repeated in 2-3 different places ( in a few
> > different ways
> > for Tomcat 3.2 ), moving the act of reconnecting to the
> > database to it's own
> > seperate method that either returns void or returns a connection.
> >
>
> 3.3 JDBCRealm is now doing that in this way, but i use a boolean
> checkConnection(), I have planned backport this snippet soon ( this
> night ) but in the review i have found some things that can
> done better
> too, as translating the log methods from SimpleRealm and JDBCRealm to
> BaseRealm,
>
> What do you think Craig?
>
> > * Would this adversely affect the amount of logging that is
> > done in the
> > current code?
>
> I think JDBCRealm is doing too much logging now, so no problem.
>
> > * If I am interpreting the logic that checks for the presence of a
> > connection name and connection password correctly, in the
> > case that only
> > *one* of them is empty or null, the three parameter
> > getConnection method is
> > used. Is it possible that this could cause problems as well?
>
> i think you are right, i will change this in ALL tomcat versions ASAP,
> if nobody complaints.
>
> >
> > I apologize for not catching this with my original patch,
> and for not
>
> No problem, Thanks to point on this , i will try to do it ASAP,
>
>
> Saludos ,
> Ignacio J. Ortega
>
Index: JDBCRealm.java
===================================================================
RCS file:
/home/cvspublic/jakarta-tomcat-4.0/catalina/src/share/org/apache/catalina/realm/JDBCRealm.java,v
retrieving revision 1.7
diff -u -r1.7 JDBCRealm.java
--- JDBCRealm.java 2000/12/16 23:49:35 1.7
+++ JDBCRealm.java 2000/12/26 22:55:37
@@ -208,6 +208,16 @@
private String userTable = null;
/**
+ * The query to retrieve the user date.
+ */
+ private String userQuery = null;
+
+ /**
+ * The query to retrieve the roles
+ */
+ private String userRoleQuery = null;
+
+ /**
* The connection URL to use when trying to connect to the databse
*/
private String connectionName = null;
@@ -348,6 +358,22 @@
}
/**
+ * Set the query that will be used to retrieve the user data
+ * @param userQuery SQL Query string
+ */
+ public void setUserQuery(String userQuery ) {
+ this.userQuery = userQuery;
+ }
+
+ /**
+ * Set the query that will be used to retrieve the roles
+ * @param userRoleQuery SQL Query string
+ */
+ public void setUserRoleQuery(String userRoleQuery ) {
+ this.userRoleQuery = userRoleQuery;
+ }
+
+ /**
* Set the name to use to connect to the database.
*
* @param connectionName User name
@@ -446,9 +472,14 @@
}
// Create the authentication search prepared statement if necessary
+ String sql = "";
if (preparedAuthenticate == null) {
- String sql = "SELECT " + userCredCol + " FROM " + userTable +
- " WHERE " + userNameCol + " = ?";
+ if (userQuery == null) {
+ sql = "SELECT " + userCredCol + " FROM " + userTable +
+ " WHERE " + userNameCol + " = ?";
+ } else {
+ sql = userQuery;
+ }
if (debug >= 1)
log("JDBCRealm.authenticate: " + sql);
preparedAuthenticate = dbConnection.prepareStatement(sql);
@@ -456,13 +487,17 @@
// Create the roles search prepared statement if necessary
if (preparedRoles == null) {
- String sql = "SELECT " + roleNameCol + " FROM " +
- userRoleTable + " WHERE " + userNameCol + " = ?";
+ if (userRoleQuery == null ) {
+ sql = "SELECT " + roleNameCol + " FROM " +
+ userRoleTable + " WHERE " + userNameCol + " = ?";
+ } else {
+ sql = userRoleQuery;
+ }
if (debug >= 1)
log("JDBCRealm.roles: " + sql);
preparedRoles = dbConnection.prepareStatement(sql);
}
-
+
// Perform the authentication search
preparedAuthenticate.setString(1, username);
ResultSet rs1 = preparedAuthenticate.executeQuery();
Index: server.xml
===================================================================
RCS file: /home/cvspublic/jakarta-tomcat-4.0/catalina/src/conf/server.xml,v
retrieving revision 1.16
diff -u -r1.16 server.xml
--- server.xml 2000/12/17 01:03:29 1.16
+++ server.xml 2000/12/26 23:00:26
@@ -126,6 +126,16 @@
userRoleTable="user_roles" roleNameCol="role_name" />
-->
+ <!--
+ <Realm className="org.apache.catalina.realm.JDBCRealm" debug="99"
+ driverName="sun.jdbc.odbc.JdbcOdbcDriver"
+ connectionURL="jdbc:odbc:CATALINA"
+ <!-- for both the user and the roles queries only the first column
+ in the result set is looked at for the password and role -->
+ userQuery="SELECT user_pass FROM users WHERE user_name = ?"
+ userRoleQuery="SELECT role_name FROM roles WHERE user_name = ?"
+ />
+
<!-- Define the default virtual host -->
<Host name="localhost" debug="0" appBase="webapps">
