How to configure JBoss DatabaseServerLoginModule f

Question

In a sentence, I want to configure JBoss 4.2.2 to use DatabaseServerLoginModule as the login-module for a Web application that is secured via Digest Authentication. The problem I am having is that the passwords fail to validate. I suspect the issue is either in how I've defined the application policy or in how the passwords are stored in the database.

Below are all the relevant files. I have a MySQL database with users and roles defined using the following schema:

CREATE TABLE SR_USER (
  ID BIGINT(19) NOT NULL AUTO_INCREMENT,
  USERNAME VARCHAR(20) NOT NULL,
  PASSWORD VARCHAR(255) NOT NULL,
  PRIMARY KEY (ID)
)
CHARACTER SET utf8;

CREATE TABLE SR_ROLE (
  ID BIGINT(19) NOT NULL AUTO_INCREMENT,
  ROLE_NAME VARCHAR(20) NOT NULL,
  PRIMARY KEY (ID)
)
CHARACTER SET utf8;

CREATE TABLE SR_USER_ROLE (
  FK_USER_ID BIGINT(19) NOT NULL,
  FK_ROLE_ID BIGINT(19) NOT NULL,
  FOREIGN KEY (FK_USER_ID) REFERENCES SR_USER (ID),
  FOREIGN KEY (FK_ROLE_ID) REFERENCES SR_ROLE (ID)
)
CHARACTER SET utf8;

for the application policy in the login-config.xml file I have the following defined:

<application-policy name="secrest">
    <authentication>
        <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule"
            flag="required">
            <module-option name="dsJndiName">java:/SecRestDS</module-option>
            <module-option name="principalsQuery">
                SELECT PASSWORD FROM SR_USER WHERE USERNAME=?
                </module-option>
            <module-option name="rolesQuery">
                SELECT r.ROLE_NAME FROM SR_ROLE r, SR_USER_ROLE ur, SR_USER u WHERE
                u.USERNAME=? AND u.ID=ur.FK_USER_ID AND ur.FK_ROLE_ID=r.ID 
                </module-option>
            <module-option name="hashAlgorithm">MD5</module-option>
            <module-option name="hashEncoding">hex</module-option>
        </login-module>
    </authentication>
</application-policy>

here is the web.xml file for my web application:

<web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
    version="2.5">

    <servlet>
        <servlet-name>JerseyServlet</servlet-name>
        <servlet-class>com.sun.jersey.spi.container.servlet.ServletContainer</servlet-class>
        <init-param>
            <param-name>javax.ws.rs.Application</param-name>
            <param-value>com.acme.samples.SecureRESTApplication</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>

    <servlet-mapping>
        <servlet-name>JerseyServlet</servlet-name>
        <url-pattern>/*</url-pattern>
    </servlet-mapping>

    <security-constraint>
        <web-resource-collection>
            <web-resource-name>secrest</web-resource-name>
            <url-pattern>/*</url-pattern>
        </web-resource-collection>
        <auth-constraint>
            <role-name>admin</role-name>
        </auth-constraint>
    </security-constraint>

    <login-config>
        <auth-method>DIGEST</auth-method>
    </login-config>
    <security-role>
        <role-name>admin</role-name>
    </security-role>

</web-app>

and finally, here is the jboss-web.xml:

<jboss-web>
 <security-domain>java:/jaas/secrest</security-domain>
</jboss-web>

I should also add that I populate the data base with the following content:

INSERT INTO SR_ROLE (ROLE_NAME) VALUES ('admin');
INSERT INTO SR_ROLE (ROLE_NAME) VALUES ('apiuser');

INSERT INTO SR_USER (USERNAME, PASSWORD) VALUES ('user1', PASSWORD('p455w0rd'));
INSERT INTO SR_USER (USERNAME, PASSWORD) VALUES ('user2', 'p455w0rd');
INSERT INTO SR_USER (USERNAME, PASSWORD) VALUES ('user3', 'a4fd8e6fa9fbf9a6f2c99e7b70aa9ef2');

INSERT INTO SR_USER_ROLE (FK_USER_ID, FK_ROLE_ID) VALUES (1, 1);
INSERT INTO SR_USER_ROLE (FK_USER_ID, FK_ROLE_ID) VALUES (1, 2);

INSERT INTO SR_USER_ROLE (FK_USER_ID, FK_ROLE_ID) VALUES (2, 1);
INSERT INTO SR_USER_ROLE (FK_USER_ID, FK_ROLE_ID) VALUES (2, 2);

INSERT INTO SR_USER_ROLE (FK_USER_ID, FK_ROLE_ID) VALUES (3, 1);
INSERT INTO SR_USER_ROLE (FK_USER_ID, FK_ROLE_ID) VALUES (3, 2);

As you can see above, all three users (e.g. user1, user2, user3) all have the same password; but in each case the password is encoded (or not) using MD5 hash. None of the above, however, work. This is at the core of the issue I think.

Answer 1

something like this be possible?

SELECT PASSWORD FROM SR_USER WHERE USERNAME=? AND IDCOMPANIA=?

SELECT r.ROLE_NAME, 'Roles'
FROM
  SR_ROLE r, SR_USER_ROLE ur, SR_USER u
WHERE
  u.USERNAME=? AND u.ID=ur.FK_USER_ID AND ur.FK_ROLE_ID=r.ID AND u.IDCOMPANIA?

Answer 2

In all examples I saw the role query looks a little different and always returns two columns:

select userRoles,'Roles' from UserRoles where username=? 

Your query returns only one column.

I made test in my application and after removing these one extra column authentication stop working.

Answer 3

Can you do something like this?

<application-policy name="secrest">
<authentication>
    <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required">
        <module-option name="dsJndiName">java:/SecRestDS</module-option>
        <module-option name="principalsQuery">
            SELECT PASSWORD FROM SR_USER WHERE USERNAME=? AND IDCOMPANIA=? 
        </module-option>
        <module-option name="rolesQuery">
            SELECT r.ROLE_NAME, 'Roles' FROM SR_ROLE r, SR_USER_ROLE ur, SR_USER u WHERE
            u.USERNAME=? AND  u.IDCOMPANIA=? AND u.ID=ur.FK_USER_ID AND ur.FK_ROLE_ID=r.ID
        </module-option>    
        <module-option name="hashAlgorithm">MD5</module-option>
        <module-option name="hashEncoding">rfc2617</module-option>
        <module-option name="ignorePasswordCase">false</module-option>
        <module-option name="hashStorePassword">true</module-option>
        <module-option name="hashUserPassword">false</module-option>
        <module-option name="storeDigestCallback">org.jboss.security.auth.spi.RFC2617Digest</module-option>
    </login-module>
</authentication>

Answer 4

So I finally figured this one out. The key was the following:

<application-policy name="secrest">
<authentication>
    <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required">
        <module-option name="dsJndiName">java:/SecRestDS</module-option>
        <module-option name="principalsQuery">
            SELECT PASSWORD FROM SR_USER WHERE USERNAME=?
        </module-option>
        <module-option name="rolesQuery">
            SELECT r.ROLE_NAME, 'Roles' FROM SR_ROLE r, SR_USER_ROLE ur, SR_USER u WHERE
            u.USERNAME=? AND u.ID=ur.FK_USER_ID AND ur.FK_ROLE_ID=r.ID
        </module-option>    
        <module-option name="hashAlgorithm">MD5</module-option>
        <module-option name="hashEncoding">rfc2617</module-option>
        <module-option name="ignorePasswordCase">false</module-option>
        <module-option name="hashStorePassword">true</module-option>
        <module-option name="hashUserPassword">false</module-option>
        <module-option name="storeDigestCallback">org.jboss.security.auth.spi.RFC2617Digest</module-option>
    </login-module>
</authentication>