One problem I’ve run across a lot in the past is that I’ve wanted to use the username of the current user for some function in the database. Some of the more common reasons I’ve run across include:
- Logging the username for auditing
- App-specific permissions checks at the query level
- Database-level per-user SLA restrictions
- Real-time monitoring of database activity
Unfortunately, as anyone who’s worked with a web site or other application with shared connections know, that information is never available from the database unless the application explicitly provides it. A database can only tell the name of the DATABASE user that is connected, and that’s the same one for all our application users:
$user = "myappuser";
$password = "mypass";
$server = "db.company.com";
$database = "myapp";
The application can explicitly pass this value for every action, but that can end up being a world of pain, especially if your application is already pretty evolved by the time you decide it’s time to start adding some auditing to it. You may have to go through every statement like this:
UPDATE customers SET password = '$newpass' WHERE id = $id;
with something like this:
UPDATE customers SET password = '$newpass', updated_by = $user_id WHERE id = $id;
This is the sort of thing that smells like an aspect to me. I want to add the “auditing” aspect to my already-existing code without having to rewrite everything. Fortunately, databases already provide a very simple mechanism to do that sort of thing: triggers. Unfortunately, to write one would require the database to know who my application user is without me telling it on every update…
Restating the problem, it would be great if my application could tell the database some global information about the current session, much like web apps store user session information, with every user connection. It turns out this isn’t an impossible request, at least with the two databases below!
User-defined Variables in MySQL
I spent a couple of hours recently trying to look up a solution to this kind of problem for my app which uses a MySQL database. I had a look at MySQL session variables, both static and dynamic, to see if there was something I could set in there that I could hijack for my purposes. The closest I came was the @@identity variable, which I could set to a numerical user id, but which would unfortunately be overridden on any INSERT (after all, that’s what it’s for).
It turns out the answer is much more simple, and much more powerful: user-defined variables.
SET @user_id = $user_id;
The best part about this solution, besides its simplicity, is its flexibility. With this mechanism, your application can report anything it wishes to the database that should affect its overall behavior. For example, when running batch synchronization processes, rather than have to manually switch off each and every trigger, I tell the database to run in “synchronization mode” when I open the connection:
SET @synchronization = 1;
I’m not really sure if the previous solution will work in Oracle (or in other databases) because I haven’t really tried. It turns out Oracle does provide some session variables that are actually suited to this purpose: client_id.
The downside of this approach is it isn’t nearly as flexible as the approach above. But it has one ENORMOUS advantage: it is visible from the Oracle Enterprise Manager (OEM) screens (at least from 10g on). What this means is that you can view real-time (or historic) performance stats, drill down to see the top queries, zoom in to the worst, and see the actual user that is executing those queries! (more on a particularly interesting use of this feature in another blog post)
How to plug it in
It’s great that these databases offer a way to provide this information, but it doesn’t come for free. Your application still needs to explicitly set these values in the connection some time before it needs them. But, assuming you are following the DRY Principle like a good boy or girl, this shouldn’t be too much of a problem. In apps that explicitly open the connections, like a simple PHP site or a Java app that does its own connection management, this command can be executed right after the connection itself is created.
In apps that use connection pooling, this can be a little more complicated. In Java apps, I have found that the connection pool libraries often provide some sort of event callback mechanism, so the user can be set in the “beforeGetConnection” method or whatever. In more extreme cases, it may be necessary to resort to a more explicit use of an AOP framework, or explicitly get connections from a single source (e.g. static method, horror of horrors). When a framework is involved, the framework itself may require a little bit of hacking, or, in the case of Ruby or Python, some sort of monkey-patching to the framework classes.
One way or another, it may be hard to get to, but shouldn’t require more than a couple of lines of code. Definitely better than rewriting your whole app!