From: "Soefara Redzuan" <[EMAIL PROTECTED]>
Sent: Tuesday, July 23, 2002 11:52 PM
Subject: Re: More flexible JDBCRealm implementation ? (for ASP-style webapp)
> Yes, but this adds complexity due to different table names for different
> clients. I like to keep things simple. So, I can do what you suggest
except
> with different databases (running on the same MySQL instance) for
> different customers and webapps. That's my current setup.
Yeah, that's basically what I was talking about.
> >If you'd rather share the tables (for whatever reason), perhaps you could
> >make views on a master table that's limited by the client id.
>
> I do prefer to share the tables. The reason is that you can easily set
> up new clients and customers without restarting Tomcat. Better yet, users
> can register and sign-up themselves, something that's not possible if I
> have to set up a different database for them, with its own JNDI resource
> configuration in server.xml
>
> >These views would be placed in the appropriate client schemas and shadow
> >the
> >master table in a central schema. Something like CREATE VIEW
> >CLIENT1.USERS(user_name, user_pass, user_goupid) AS SELECT user_name,
> >user_pass, user-groupid FROM MASTER.USERS WHERE CLIENTID = 'CLIENT1'.
>
> I'll have to investigate this use of views. It's something I'd never
> considered before and looks very useful.
Many DBMS (again, I don't know about MySQL) will allow you to not only read
using these kinds of views, but also update the data through the view.
> >Finally, if you look at
> >$CATALINA_HOME/src/share/org/apache/cataline/realm/JDBCRealm.java, it
looks
> >pretty darn simple to tweak that to do whatever you want, or, better, to
> >subclass and change the relevant methods (not many from the looks of it).
> >The only fear here is that the TC team can change JDBCRealm behind your
> >back
> >in a later release.
>
> I'd much rather avoid something this drastic. It would be better for me
> to write a filter and use custom authentication I believe.
>
> >Stick the pertinent webapp specific entries into ENV-ENTRY, and you can
do
> >all sorts of scary things I would think.
I'd really reconsider. If you look at the code for a JDBCRealm, it's APPEARS
really trivial.
Here you go. Inspired by the JDBCRealm class, it allows you to specify the
actual SQL you wish to use in the Database. Credential will return the
password, and roles will return the roles for the user. The single argument
for both SQLs will be the username.
ConfigurableJDBCRealm.java
-=-=-=-=-=-=-=-=-=-=-=-=-
package your.co.package;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
public class CongigurableJDBCRealm
extends org.apache.catalina.realm.JDBCRealm {
/**
* 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
*/
private String credentialsSQL = null;
private String rolesSQL = null;
protected PreparedStatement credentials(Connection dbConnection,
String username)
throws SQLException {
if (credentialsSQL == null) {
try {
InitialContext ctx = new InitialContext();
credentialsSQL =
(String)ctx.lookup("java:comp/env/realm.credentials");
}
catch(NamingException ne) {
// This string will come as a rude shock to the SQL
database, and
// throw and nasty SQLException.
credentialsSQL = "Could not find realm.credentials";
}
}
if (preparedCredentials == null) {
preparedCredentials =
dbConnection.prepareStatement(credentialsSQL);
}
preparedCredentials.setString(1, username);
return (preparedCredentials);
}
/**
* 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 (rolesSQL == null) {
try {
InitialContext ctx = new InitialContext();
rolesSQL = (String)ctx.lookup("java:comp/env/realm.roles");
}
catch(NamingException ne) {
// This string will come as a rude shock to the SQL
database, and
// throw a nasty SQLException.
rolesSQL = "Could not find realm.roles";
}
}
if (preparedRoles == null) {
preparedRoles =
dbConnection.prepareStatement(rolesSQL);
}
preparedRoles.setString(1, username);
return (preparedRoles);
}
}
-=-=-=-=-=-=-=-=-=-=-=-=-
In you server XML, you tweak the Realm tag as follows:
<Realm className="your.co.package.ConfigurableJDBCRealm" debug="99"
driverName="org.gjt.mm.mysql.Driver"
connectionURL="jdbc:mysql://localhost/authority?user=dbuser&password=dbpass"
userTable="users" userNameCol="user_name" userCredCol="user_pass"
userRoleTable="user_roles" roleNameCol="role_name"/>
The userTable, NameCol, CredCol, and RoleTable parameters will be ignored.
(This does, however bring up a question for...is the Realm tag (or perhaps
all of the tags) in the server.xml reflective? i.e Are the parameters in the
tag based upon the fields in the bean? That's what it appears to be. If that
is the case, this could be a bit more elegant.)
Finally, in this case, you need to add ENV-ENTRYs to your webapp, or your
Context's in the server.xml.
<env-entry>
<env-entry-name>realm.credentials</env-entry-name>
<env-entry-value>SELECT password FROM users WHERE userName =
?</env-entry-value>
<env-entry-type>java.lang.String</env-entry-type>
</env-entry>
<env-entry>
<env-entry-name>realm.roles</env-entry-name>
<env-entry-value>SELECT role FROM roles WHERE userName =
?</env-entry-value>
<env-entry-type>java.lang.String</env-entry-type>
</env-entry>
With those tweaks, you can pretty much do anything you want, as long as it's
keyed by the username.
I haven't test any of this, I haven't even compiled it. It's just a simple
tweak I gleaned from the 4.0.4 source code. It is, in the most basic way, a
Hack. However, it could easily be cleaned up if you use JDBCRealm as
inspiration, particularly if the Realm tag get its field names from the bean
itself, that would skip the whole ENV-ENTRY hack.
The only dark side is that it is this bit is Tomcat Specific, however, you
webapp is still Servlet 2.3 compliant, as it doesn't know the difference if
you need to port to another container.
Good Luck,
Will Hartung
([EMAIL PROTECTED])
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
