Grails Per-User Database Authentication

Question

First off, I know the best-practice is to use a single database user account in your web app to take advantage of connection pooling to keep the app nice and responsive. However, due to the REQUIREMENTS (as in no changing this under any circumstances), I must authenticate each user with his or her database account.

The context is a warehouse management application that runs on Android, but gets its data from web services that I'm probably going to write in Grails unless a suggestion here shows me a tech more suitable for my requirements. Due to the nature of the application, the users would likely only need to authenticate once or twice a day, so I was thinking I could simply persist the Connections in a HashMap keyed by the hash code of the username concatenated with the password. That should allow the application to maintain the same or similar performance level as the best practice.

Now, my issue is in using the persisted Connection objects. I know that I will not be able to use them with GORM without a significant amount of customization, so I was planning on using them with groovy.sql.Sql, which works out well because most of the business logic is in PL/SQL packages anyway.

My question is how does the groovy.sql.Sql class deal with its Connection object? Will I run into issues of Connections being closed by it, or can I safely use my HashMap to persist the Connections?

Answer 1

groovy.sql.Sql will not close your connection. In the class spec you can find:

If this SQL object was created with a Connection then this method closes the connection.

So SQL class is really if you want to do things by yourself, not trusting entirely on Hibernate. Although, I think you can use Spring's UserCredentialsDataSourceAdapter for your solution. It uses ThreadLocal to set credentials for each thread, so the call to: UserCredentialsDataSourceAdapter.setCredentialsForCurrentThread(String username, String password)

would solve. There are other aproaches you could try here.

Answer 2

I actually just found something that future visitors to this question may find useful. While digging into the Spring Framework's documentation, I discovered that their JDBC Extensions actually implements proxy authentication (where a proxy account is used to establish the connection, but an actual account is provided for the context of SQL execution). Unfortunately, the implementation as of 8/17/2012 does not support using passwords for users over the proxy connection, so it won't be usable for me currently, but anyone finding this question should check to see if that is still the case. Here are the links:

JDBC Extensions Docs v1.0.0.RC1

JDBC Extensions Docs Base