Tomcat + DataSourceRealm and custom sql query (disable a user flag)?

Question

DataSourceRealm does not support a customized select user, select role sql queries. I want to add enabled(boolean) sql column to enable and disable users. Is it only option to subclass and create MuchBetterDataSourceRealm implementation?

Answer 1

Check the startInternal method in DataSourceRealm class (at the bottom). https://github.com/apache/tomcat/blob/main/java/org/apache/catalina/realm/DataSourceRealm.java

There you can see how the sql is created.

Configuring the realm like this

<Realm className="org.apache.catalina.realm.DataSourceRealm"
       dataSourceName="jdbc/myDatabase"
       userTable="usertable u"
       userNameCol="u.enabled = TRUE AND u.username"
       userCredCol="u.password"
       userRoleTable="roletable r JOIN usertable u ON r.username = u.username"
       roleNameCol="r.rolename">
</Realm>

results in the following statements

SELECT
r.rolename
FROM
roletable r JOIN usertable u ON r.username = u.username
WHERE
u.enabled = TRUE AND u.username
= ?

SELECT
u.password
FROM
usertable u
WHERE
u.enabled = TRUE AND u.username
= ?