NHibernate and SetSessionAuth audit columns

Question

We have audit columns set by triggers. For obscure security reasons predating my tenure and out of my control, we log in with a generic user, and do a 'set session authorization' to change the user to the db user of the user who is logged in.

When we converted to NHibernate, it creates a whole new session and jacks everything up when we try to do a set session auth, so we turned the set session auth off...

Now we are trying to find out a way to get NHibernate to let us do 'set session authorization' without recycling the session on us, so we can use our existing trigger based audit column stuff with both legacy apps, and our new NHibernate apps.

It's not a ideal soloution, or the best way to do it even, but is it possible?

I was hoping there was a alternate interface that allowed this kind of access.

Does anyone know how to do it, or can you point me towards and good hints?

Thanks,

Eric-

Answer 1

You can inherit DriverConnectionProvider and do whatever you need when creating a connection.

Example:

public class MyConnectionProvider : DriverConnectionProvider
{
    public override IDbConnection GetConnection()
    {
        var connection = base.GetConnection();
        var sessionAuthCommand = connection.CreateCommand();
        sessionAuthCommand.CommandText = "set session authorization " + GetUser();
        sessionAuthCommand.ExecuteNonQuery();
        return connection;
    }
}

And then configure NHiberate to use that as the connection provider.

(GetUser is the method where you'll provide the correct user)