title: Authentication failure: JDBC Realm in JBoss EAP 6.1
body: <p>I've set up a JDBC realm in JBoss to handle user authentication/authorisation in a web app. I'm not receiving any error messages but authorisation fails with both of the test users and I'm struggling to understand why.</p>
  
  <p>The project has been the result of pulling together forum posts on JDBC Security Realms on both JBoss and Tomcat so its entirely possible I've missed something but I can't see the wood for the trees anymore. </p>
  
  <p>Relevant sections from Standalone.xml (JBoss management console shows DB connection &amp; pool is OK so I know the driver is installed correctly):</p>
  
  <pre><code>&lt;datasource jndi-name="java:jboss/datasources/MySqlDS" pool-name="MySqlDS"&gt;
      &lt;connection-url&gt;jdbc:mysql://localhost:3306/securitytest&lt;/connection-url&gt;
      &lt;driver-class&gt;com.mysql.jdbc.Driver&lt;/driver-class&gt;
      &lt;driver&gt;com.mysql&lt;/driver&gt;
      &lt;transaction-isolation&gt;TRANSACTION_READ_COMMITTED&lt;/transaction-isolation&gt;
      &lt;pool&gt;
          &lt;min-pool-size&gt;10&lt;/min-pool-size&gt;
          &lt;max-pool-size&gt;100&lt;/max-pool-size&gt;
          &lt;prefill&gt;true&lt;/prefill&gt;
      &lt;/pool&gt;
      &lt;security&gt;
          &lt;user-name&gt;jboss_as&lt;/user-name&gt;
          &lt;password&gt;test&lt;/password&gt;
      &lt;/security&gt;
      &lt;statement&gt;
          &lt;prepared-statement-cache-size&gt;32&lt;/prepared-statement-cache-size&gt;
          &lt;share-prepared-statements&gt;true&lt;/share-prepared-statements&gt;
      &lt;/statement&gt;
  &lt;/datasource&gt;
  &lt;drivers&gt;
      &lt;driver name="h2" module="com.h2database.h2"&gt;
          &lt;xa-datasource-class&gt;org.h2.jdbcx.JdbcDataSource&lt;/xa-datasource-class&gt;
      &lt;/driver&gt;
      &lt;driver name="com.mysql" module="com.mysql"&gt;
          &lt;xa-datasource-class&gt;com.mysql.jdbc.jdbc2.optional.MysqlXADataSource&lt;/xa-datasource-class&gt;
      &lt;/driver&gt;
  &lt;/drivers&gt;
  ....
  &lt;security-domain name="JDBCRealm" cache-type="default"&gt;
      &lt;authentication&gt;
          &lt;login-module code="Database" flag="required"&gt;
              &lt;module-option name="dsJndiName" value="java:jboss/datasources/MysqlDS"/&gt;
              &lt;module-option name="principalsQuery" value="select user_password from USERS where user_name=?"/&gt;
              &lt;module-option name="rolesQuery" value="select role_name, 'Roles' from  ROLES where user_name=?"/&gt;
          &lt;/login-module&gt;
      &lt;/authentication&gt;
  &lt;/security-domain&gt;
  </code></pre>
  
  <p>MySQL test database (securitytest) contains two tables (users - three columns ID, user_name, password and roles - three columns: ID, user_name, role_name, group_name) and I have populated these with "user" and "admin" both with "password" as the password. Role_name and group_name are set to the name of the respective users. Entries are cleartext (currently)</p>
  
  <p>web.xml:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
  http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
  id="WebApp_ID" version="2.5"&gt;
  
  &lt;!-- Roles --&gt;
  &lt;security-role&gt;
      &lt;description&gt;Administrators&lt;/description&gt;
      &lt;role-name&gt;admin&lt;/role-name&gt;
  &lt;/security-role&gt;
  &lt;security-role&gt;
      &lt;description&gt;Management&lt;/description&gt;
      &lt;role-name&gt;mgmt&lt;/role-name&gt;
  &lt;/security-role&gt;
  
  &lt;!--  Resource/role mapping --&gt;
  &lt;security-constraint&gt;
      &lt;display-name&gt;Admin Pages&lt;/display-name&gt;
      &lt;web-resource-collection&gt;
          &lt;web-resource-name&gt;Protected Admin Area&lt;/web-resource-name&gt;
          &lt;description /&gt;
          &lt;url-pattern&gt;/admin/*&lt;/url-pattern&gt;
          &lt;http-method&gt;GET&lt;/http-method&gt;
          &lt;http-method&gt;POST&lt;/http-method&gt;
          &lt;http-method&gt;HEAD&lt;/http-method&gt;
          &lt;http-method&gt;PUT&lt;/http-method&gt;
          &lt;http-method&gt;OPTIONS&lt;/http-method&gt;
          &lt;http-method&gt;TRACE&lt;/http-method&gt;
          &lt;http-method&gt;DELETE&lt;/http-method&gt;
      &lt;/web-resource-collection&gt;
      &lt;auth-constraint&gt;
          &lt;description&gt;For administrators only&lt;/description&gt;
          &lt;role-name&gt;admin&lt;/role-name&gt;
      &lt;/auth-constraint&gt;
      &lt;user-data-constraint&gt;
          &lt;transport-guarantee&gt;NONE&lt;/transport-guarantee&gt;
      &lt;/user-data-constraint&gt;
  &lt;/security-constraint&gt;
  &lt;security-constraint&gt;
      &lt;display-name&gt;All Access&lt;/display-name&gt;
      &lt;web-resource-collection&gt;
          &lt;web-resource-name&gt;Unprotected User Area&lt;/web-resource-name&gt;
          &lt;description&gt;Open access for all users&lt;/description&gt;
          &lt;url-pattern&gt;/users/*&lt;/url-pattern&gt;
          &lt;http-method&gt;GET&lt;/http-method&gt;
          &lt;http-method&gt;POST&lt;/http-method&gt;
          &lt;http-method&gt;HEAD&lt;/http-method&gt;
          &lt;http-method&gt;PUT&lt;/http-method&gt;
          &lt;http-method&gt;OPTIONS&lt;/http-method&gt;
          &lt;http-method&gt;TRACE&lt;/http-method&gt;
          &lt;http-method&gt;DELETE&lt;/http-method&gt;
      &lt;/web-resource-collection&gt;
      &lt;user-data-constraint&gt;
          &lt;transport-guarantee&gt;NONE&lt;/transport-guarantee&gt;
      &lt;/user-data-constraint&gt;
  &lt;/security-constraint&gt;
  
  &lt;!-- Login prompt --&gt;
  &lt;login-config&gt;
      &lt;auth-method&gt;FORM&lt;/auth-method&gt;
      &lt;realm-name&gt;JDBCRealm&lt;/realm-name&gt;
      &lt;form-login-config&gt;
          &lt;form-login-page&gt;/login.xhtml&lt;/form-login-page&gt;
          &lt;form-error-page&gt;/loginerror.xhtml&lt;/form-error-page&gt;
      &lt;/form-login-config&gt;
  &lt;/login-config&gt;
  &lt;/web-app&gt;
  </code></pre>
  
  <p>jboss.xml (in WEB-INF directory):</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;jboss-web&gt;
      &lt;security-domain&gt;java:/jaas/JDBCRealm&lt;/security-domain&gt;
  &lt;/jboss-web&gt;
  </code></pre>
  
  <p>Finally, login.xhtml:</p>
  
  <pre><code>&lt;?xml version='1.0' encoding='UTF-8' ?&gt;
  &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
  &lt;html xmlns="http://www.w3.org/1999/xhtml"
  xmlns:p="http://primefaces.prime.com.tr/ui"
  xmlns:h="http://java.sun.com/jsf/html"&gt;
  &lt;h:head&gt;
  &lt;title&gt;Login Form&lt;/title&gt;
  &lt;/h:head&gt;
  &lt;h:body&gt;
  &lt;p:panel header="Login From"&gt;
  &lt;form method="post" action="j_security_check"&gt;
  Username: &lt;input type="text" name="j_username" /&gt;
  Password: &lt;input type="password" name="j_password" /&gt;
  &lt;br /&gt;
  &lt;input type="submit" value="Login" /&gt;
  &lt;input type="reset" value="Reset" /&gt;
  &lt;/form&gt; 
  
  &lt;/p:panel&gt;
  &lt;/h:body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>If anyone has any ideas I'd be really grateful as I'm tearing my hair out. I'm sure I've done something stupid, I just can't see it.</p>
  
  <p>Cheers!</p>