Using Multi-User Database Pools in WildFly 28

July 20, 2023

Many applications are careful to use good user authentication and only permit authorized access to database resources. But often these applications do so entirely in the application layer and completely ignore built-in access controls at the database layer. This way of managing access fails to implement one of the most basic principles of good security: defense in depth. Let's look at how we can use the allow-multiple-users attribute of a WildFly data source to enable enterprise level security for data.

Typical Database Connection

As mentioned, often applications that use database resources manage access to those resources entirely at the application level and ignore the access controls the underlying database already offers. For example, a data source might be configured to always use a hard-coded username and password and the database is configured to give this hard-coded user complete access to all resources. Here is an example from a WildFly 28.0.1 test installation:

<datasource jndi-name="java:/SampleDB" pool-name="SampleDB">
    <connection-url>jdbc:postgresql://localhost/era</connection-url>
    <driver>postgresql-42.5.0.jar</driver>
    <security>
        <user-name>wildflyuser</user-name>
    </security>
</datasource>

The CLI command for setting up the above data source would look like this:

data-source add --name=SampleDB --jndi-name=java:/SampleDB --driver-name=postgresql-42.5.0.jar --connection-url=jdbc:postgresql://localhost/sample --user-name=wildflyuser

Some simple Java code to obtain this database connection might look like this:

package com.eaerich.test;

import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class WildflyTest {

   public Connection getConnection() throws NamingException, SQLException {
      InitialContext ctx = new InitialContext();
      DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB");
      return ds.getConnection();
   }
}

This way of obtaining database connections works fine, but notice that it always uses the same user to connect to the database, namely wildflyuser. This makes for a simple configuration and simple code, but requires that all database resources grant full access to user wildflyuser. This means that your application must absolutely never grant access to database resources that the application user is not authorized to. While perhaps this seems obvious, it has the downside that just one bug in our application security is all that is required to lead to a breach of data security. What we really need is a "belt and suspenders" approach, i.e. defense in depth.

Multi-User Database Connections

It is often preferable (and maybe required) to use built-in database access controls to make certain that a user cannot find a way to bypass the application and access data that is restricted. For example, the Accounts Payable department generally does not need to access Payroll information and employees in Shipping and Receiving need not have access to the General Ledger. Putting such access controls right in the database means that a clever employee cannot misuse credentials to access data they have no business seeing. Multi-user database connections allow these types of controls to be put in place and further safeguard company data.

WildFly enables multi-user database connections by setting the allow-multiple-users attribute on the connection. Doing so allows WildFly to maintain a pool of connections by user, such that a connection created by one user is not shared with a different user. The CLI command to set this attribute is:

/subsystem=datasources/data-source=SampleDB:write-attribute(name=allow-multiple-users, value=true)

Our example standalone.xml now looks like this:

<datasource jndi-name="java:/SampleDB" pool-name="SampleDB">
    <connection-url>jdbc:postgresql://localhost/era</connection-url>
    <driver>postgresql-42.5.0.jar</driver>
    <pool>
        <allow-multiple-users>true</allow-multiple-users>
    </pool>
    <security>
        <user-name>wildflyuser</user-name>
    </security>
</datasource>

The Java code to obtain such a connection could be modified as follows:

package com.eaerich.test;

import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class WildflyTest {

   public Connection getConnection(final String username, final String password)
   throws NamingException, SQLException {
      InitialContext ctx = new InitialContext();
      DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/SampleDB");
      return ds.getConnection(username, password);
   }
}

The application now needs to pass the current user information to the getConnection() method. A likely source for this information is the login credentials used to access the application. The important point is to use a distinct username and password for each application user and not use the same database credentials for all users across the entire application.

Managing Access

As with all security issues, an increase in secure practices usually includes an increase in complexity. We must consider how to keep authorized access as defined in the application in sync with authorized access as defined in the database. Because of the diversity of databases, this article cannot pretend to give a single answer. But thinking on this problem suggests two ideas:

• Use groups as much as possible. For example, assign all Accounts Payable users to an AP group, all Payroll users to a Payroll group, and so on. Many databases have a group concept which allows access to be given to members of a group without having to give access to each individual user and database resource.
• Make application code run appropriate GRANT and REVOKE database statements when application level authorities are given and removed. This ensures that database access authorities and application access authorities are never out of sync.

I hope that this helps you understand how to use multiple user database pools in WildFly and how they can benefit your application security.