1 Reply Latest reply on Apr 21, 2005 7:49 PM by Michael Dinsmore

    DatabaseServerLoginModule SQL Failure

    Ian Mckay Newbie

      Hi,

      Im trying to set up database authentication following the startguide section 4.3.2. When accessing my web app i get forwarded to a login.jsp i enter my details and i get this error.

      ERROR [DatabaseServerLoginModule] SQL failure
      java.sql.SQLException: Table not found: ROLES in statement [select Role, RoleGroup from Roles where PrincipalID=?]
      at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)


      My web.xml is as follows

      <web-resource-collection>
      <web-resource-name>HtmlAdaptor</web-resource-name>
      Will only allow users of role admin to access the web server
      <url-pattern>/admin.jsp</url-pattern>
      <url-pattern>/device-command.jsp</url-pattern>
      <url-pattern>/device-info.jsp</url-pattern>
      <url-pattern>/device-viewer.jsp</url-pattern>
      <url-pattern>/macro-add.jsp</url-pattern>
      <url-pattern>/macro-addname.jsp</url-pattern>
      <url-pattern>/macro-admin.jsp</url-pattern>
      <url-pattern>/macro-failure.jsp</url-pattern>
      <url-pattern>/macro-process.jsp</url-pattern>
      <url-pattern>/macro-processcomplete.jsp</url-pattern>
      <url-pattern>/macro-processname.jsp</url-pattern>
      <url-pattern>/macro-refresh.jsp</url-pattern>
      <url-pattern>/macro-remove.jsp</url-pattern>
      <url-pattern>/macro-success.jsp</url-pattern>
      <url-pattern>/main.jsp</url-pattern>
      <http-method>GET</http-method>
      <http-method>POST</http-method>
      </web-resource-collection>
      <auth-constraint>
      <role-name>Admin</role-name>
      </auth-constraint>
      </security-constraint>
      <login-config>
      <auth-method>FORM</auth-method>
      <form-login-config>
      <form-login-page>/login.jsp</form-login-page>
      <form-error-page>/login-error.jsp</form-error-page>
      </form-login-config>
      </login-config>
      <security-role>
      <role-name>Admin</role-name>
      </security-role>

      My jboss-web.xml looks like:

      <security-domain>java:/jaas/UDKServer</security-domain>

      And my login-config.xml is:

      <!-- A Security Configuration for the UDK Server
      -->
      <application-policy name="UDKServer">

      <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule"
      flag="required">
      <module-option name="dsJndiName">java:/DefaultDS</module-option>
      <module-option name="principalsQuery">
      select passwd from Users where username=?
      </module-option>
      <module-option>
      select userRoles, 'Roles' from UserRoles where username=?
      </module-option>
      </login-module>

      </application-policy>

      Ive created the Users and userRoles tables all following the guide. But it seems to want to reference another table.

      Can someone help me, ive searched this forum and the guide and can't find what im doing wrong.

      Cheers
      Ian

        • 1. Re: DatabaseServerLoginModule SQL Failure
          Michael Dinsmore Newbie

          Are you sure you have the correct SQL for the select of the userRoles fro mthe table? As you can see from the error itself, the SQL exception is telling you that there is no table called ROLES whereas you have a table called userRoles. I think you might have cut and pasted the original SQL statement the example has which refers to the ROLES table.