Using a Relational Database for Users | JBoss: A Developers Notebook

Nearly every J2EE application stores data in a relational database, so it is natural to want to store user data in the database alongside your existing data. It's likely that your domain models contain a user object that already captures the concept of a login name and password, if not full role data. We've chosen not to have a user object in the ToDo application to ensure maximum flexibility, but chances are you'll want to control user information dynamically using data in the database.

Note: If he J2EE specification defined a user management API, we wouldn't have to make these kinds of trade-offs.

How do I do that?

First you need to get user and role data into the database. Since we don't have a user domain object to work from, we'll need to create new tables in the database. We'll create one table for storing the user information, and a separate table for storing the role data. Open the Hypersonic database manager tool and create the following two tables:

     CREATE TABLE USERS(login VARCHAR(64) PRIMARY KEY, passwd VARCHAR(64))     CREATE TABLE USER_ROLES(login VARCHAR(64), role VARCHAR(32))

You can store user information in the database in many ways. You might have user ID and role ID columns, for example, for a more normalized database structure. As we'll see later, JBoss doesn't really care how you structure the data. As long as you can create efficient SQL queries to extract user and role information, you'll be able to work with the data easily within JBoss.

While you have the database manager open, you should also add the users to the database:

     INSERT into USERS values('pinky', 'duh')     INSERT into USERS values('brain', 'conquest')     INSERT into USER_ROLES values('pinky', 'User')     INSERT into USER_ROLES values('brain', 'User')

Now we will update the security domain in the login-config.xml file. DatabaseServerLoginModule provides the ability to load user and role data from a database. So, we'll use it:

     <application-policy name="todo">         <authentication>             <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 login=?                 </module-option>                 <module-option name="rolesQuery">                     select role, 'Roles' from USER_ROLES where login=?                 </module-option>             </login-module>        </authentication>     </application-policy>

Note: Don't forget to restart the server when you modify login-config.xml.

This login module needs three configuration parameters. The dsJndiName attribute is the JNDI name of the datasource to use. If you use a different database, it's a simple matter to change the name to bind the login module to your database of choice.

The principalsQuery and rolesQuery attributes provide the SQL queries to extract the user and role information from the database. You can use any SQL that makes sense for your database and user data, as long as the queries return result data in the form that the login module expects.

Note: Basically, you provide the schema and the queries.

The principalsQuery attribute should return a single row with a single column that contains the password for the user. The rolesQuery attribute is slightly more complicated. It should return rows that contain two columns. The first column should be the role name, and the second should be the name of the JAAS principal to use. To provide role data, this should simply be Roles. It's rare to need to delve into the depths of JAAS, so it's enough to hardcode the Roles value in the query and not worry about it.

What just happened?

You moved the user login information from the hardcoded properties files to the database using DatabaseServerLoginModule. All you needed to do was to add the user information to the database and configure the database login module to read it.

Moving user information into the database makes the data easy to inspect and manage. When user information in the database changes, that information can be used immediately at the application level. After adding a user to the database, for example, that user should be able to log in immediately.

Changes, on the other hand, are a bit trickier. The security manager caches authentication credentials so that it doesn't have to consult the database on every single access to the web page. This is great for performance, but it can sometimes leave you scratching your head, wondering why JBoss isn't recognizing changed passwords or new group information. If you need to force JBoss to see changes to data, invoke the flushAuthenticationCache operation on the JAASSecurityManager MBean after updating the data.