Grails – Using a database user for security login

Sometimes, legacy applications don’t use a users table, but instead, each user is a database user.

With this approach you can define security access to your database objects though permissions. In Oracle you can do this with roles.

By default you configure a single username and password for your Grails applications, and use (or not) a pool of connections. So, it’s possible to change this behavior and force to use the database user as login? The answer is yes!

Delaying the Connection

The first step is to open a connection only after the login. By default, Grails and Hibernate will open connections during startup. In this stage we don’t know witch user will be used for the connection and if we don’t want to define a default user we need somehow to change this.

Fortunately, there’s a Burt’s post that explains how to delay this connections.

My example here is based on an Oracle database (for more info you can see the post liked above):

Changing the dataSource type

So, we define the configuration to postpone our database connection, but we need to define the username and password for our connection during the login. For that Spring already have the UserCredentialsDataSourceAdapter class. You can set the credentials for your current thread, and all getConnection() calls will use this credentials.

To use this, we need to define extra Spring Beans

By now we can define the credentials for our process, and all subsequent getConnection() will use the username and password that we defined. This can be exemplified in a Controller:

Spring Security Core Integration

To not reinvent the wheel, I want to use the Spring Security Core plugin, that already have the login / logout and many other functionalities. I remembered that the login process works with Authentication Providers like the “remember me”, for example.

So the goal here is to register a custom authentication provider that will check for a valid database connection and set the credentials for the thread. The documentation says that you need to implement the AuthenticationProvider interface, but we can use an abstract class to make things easy. Spring Security have the AbstractUserDetailsAuthenticationProvider that works with UserDetails and a UsernamePasswordAuthenticationToken from where we can get the password.

Provided with the username and password, we can validate trying to open a connection, so my class looks like:

To use this provider, remember to set the grails.plugins.springsecurity.providerNames configuration option and to add the bean definition in the resources.groovy

Time to Test!

So, after all this, it’s time to test the login proccess. Run s2-quickstart to create the Spring Security Core controllers and views and try to inform your database user in the login action.


Java, Groovy & Grails developer

Tagged with: , , , ,
Posted in Grails, Groovy, Oracle, Spring Security
12 comments on “Grails – Using a database user for security login
  1. jzwolak says:

    Great! This is just what I needed for our legacy Oracle based application, which we are converting to REST. Currently the database also handles authorization, so that only certain rows are visible depending on the current user. Any suggestions how to get a database connection with the current user _after_ the user has logged in? For instance, the user logs in then visits a page that has some data that he/she owns. The Oracle database will filter the table to only return data owned by this user, but I need Grails to retrieve or establish a connection as this user. Since the user logged in in a previous request, I no long have his/her password.

  2. sergiomichels says:

    Hi jzwolak! You can retrieve the user password from the Spring Security Context. Here’s a filter that I made to always set the credentials for the current thread:

  3. My family every time say that I am killing my time here at web, but I
    know I am getting know-how every day by reading such nice posts.

  4. Jason Zwolak says:

    The connection needs to be closed after the user credentials are verified.

    After the line:
    Connection connection = dataSource.getConnection(username, password)


    This is necessary to avoid using up all the databases free connections. The connection resources must be freed or they hang around until they’re cleaned up automatically.

    I ran into this problem when hundreds of user authentications were made per minute. Some would just be unauthorized for no apparent reason.

    • sergiomichels says:

      Thanks for the comment Jason. Since we’re working with JDBC directly, the connection needs to be closed indeed.

  5. Jason Zwolak says:

    Oh… and so google can find this solution… the error that I got was “no appropriate service handler found”. This is the error Oracle returns when it has no resources for a new connection.

  6. Jason Zwolak says:

    Here’s my final code solution for the connection closing:

    Connection connection = null;
    try {
    connection = dataSource.getConnection(username, password)
    dataSource.setCredentialsForCurrentThread(username, password)
    }catch(SQLException sql) {
    throw new BadCredentialsException(“Invalid username or password!”);
    } finally {
    try {
    if ( connection != null ) connection.close();
    } catch ( SQLException sqlEx ) {
    log.severe(“Exception when closing DB connection.”,sqlEx)

    Sergio, thank you so much for this, it saved my life 🙂 So I’m really glad to contribute back these small polishing touches.

  7. Anatoliy says:

    Can you place example for this title ‘Changing the dataSource type’ like you do for
    ‘Delaying the Connection’
    I can’t getting it working 😦

  8. Bostele says:

    I’ve been scratching my head for weeks with this and still haven’t cleared all the gotchas.
    For instance: where do you configure dataSource.url? I don’t see it anywere in your code examples.
    It would be great if you could provide a FUNCTIONAL example, that is, code in gitHub or whatever. I feel there are some important missing pieces here.
    I got it to work somehow with a proper url, username and password in dataSource definition (in application.yml). It makes an initial connection, then it seems I’m able to change connection credentials. But without prior initialization it’s impossible for me to get a delayed connection.
    My database is postgreSQL, by the way.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: