Database authentication on JBoss EAP 5.0

Step 1 Create tables in the database and insert user, group and role information

CREATE TABLE USERS (
U_NAME VARCHAR(200) NOT NULL,
U_PASSWORD VARCHAR(50) NOT NULL,
U_DESCRIPTION VARCHAR(1000))
;

CREATE TABLE GROUPS (
G_NAME VARCHAR(200) NOT NULL,
G_DESCRIPTION VARCHAR(1000) NULL)
;

CREATE TABLE ROLES (
U_NAME VARCHAR(200) NOT NULL,
R_NAME VARCHAR(200) NOT NULL,
G_NAME VARCHAR(1000) NULL)
;

Insert into USERS values('faisal','faisal');
Insert into GROUPS values('Admin','Admin');
Insert into ROLES values('faisal','Administrators','Admin');

Step 2 Create a datasource pointing to that database. I am using postgres in this example.

<datasources>
  <local-tx-datasource>
    <jndi-name>jdbc/postgressds</jndi-name>
    <connection-url>jdbc:postgresql://localhost:5432/postgres</connection-url>
    <driver-class>org.postgresql.Driver</driver-class>
    <user-name>postgres</user-name>
    <password>postgres</password>

        <!-- sql to call when connection is created
        <new-connection-sql>some arbitrary sql</new-connection-sql>
        -->

        <!-- sql to call on an existing pooled connection when it is obtained from pool 
        <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
        -->

      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->

  </local-tx-datasource>

</datasources>

Step 3 Add the authentication policy in the login-config.xml file

	<application-policy name="databaseauth">
    <authentication>
      <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required">
			<module-option name="dsJndiName">java:jdbc/postgressds</module-option>
			<module-option name="principalsQuery">select U_PASSWORD from USERS where U_NAME=?</module-option>
			<module-option name="rolesQuery">select R_NAME,'Roles' from ROLES where U_NAME=?</module-option>
      </login-module>
    </authentication>
 </application-policy>

Step 4) Access protected page on the application and log in with faisal/faisal.
Note: The role should be Administrators.

Let us know if you face any issues. We’ll be happy to help.

Cheers!
Wonders Team