Chapter 4. Configuring a Security Domain to use a Database

The Database login module is a Java Database Connectivity-based (JDBC) login module that supports authentication and role mapping. This login module is used if username, password and role information are stored in a relational database.

This works by providing a reference to logical tables containing Principals and Roles in the expected format. For example:

Table Principals(PrincipalID text, Password text) Table Roles(PrincipalID text, Role text, RoleGroup text)

Table Principals(PrincipalID text, Password text) Table Roles(PrincipalID text, Role text, RoleGroup text)

The Principals table associates the user PrincipalID with the valid password and the Roles table associates the user PrincipalID with its role sets. The roles used for user permissions must be contained in rows with a RoleGroup column value of Roles.

The tables are logical in that users can specify the SQL query that the login module uses. The only requirement is that the java.sql.ResultSet has the same logical structure as the Principals and Roles tables described previously. The actual names of the tables and columns are not relevant as the results are accessed based on the column index.

To clarify this notion, consider a database with two tables, Principals and Roles, as already declared. The following statements populate the tables with the following data:

For a full list of configuration options for the Database login module, please see the Database login module section Red Hat JBoss Enterprise Application Platform Login Module Reference.

CREATE TABLE Users(username VARCHAR(64) PRIMARY KEY, passwd VARCHAR(64))
CREATE TABLE UserRoles(username VARCHAR(64), role VARCHAR(32))

CREATE TABLE Users(username VARCHAR(64) PRIMARY KEY, passwd VARCHAR(64))
CREATE TABLE UserRoles(username VARCHAR(64), role VARCHAR(32))

/subsystem=security/security-domain=testDB:add

/subsystem=security/security-domain=testDB/authentication=classic:add

/subsystem=security/security-domain=testDB/authentication=classic/login-module=Database:add(code=Database,flag=required,module-options=[("dsJndiName"=>"java:/MyDatabaseDS"),("principalsQuery"=>"select passwd from Users where username=?"),("rolesQuery"=>"select role, 'Roles' from UserRoles where username=?")])

reload

/subsystem=security/security-domain=testDB:add

/subsystem=security/security-domain=testDB/authentication=classic:add

/subsystem=security/security-domain=testDB/authentication=classic/login-module=Database:add(code=Database,flag=required,module-options=[("dsJndiName"=>"java:/MyDatabaseDS"),("principalsQuery"=>"select passwd from Users where username=?"),("rolesQuery"=>"select role, 'Roles' from UserRoles where username=?")])

reload