JasonDentler.com

This is a continuation of my posts about auditing with triggers.

The Problem

My previous examples have used system_user as the source of user information in the audit log, which required you to impersonate users down to the database level, that is to open the connection in the context of the application user, not some shared generic account. There are several reasons why this is not a good idea:

1. You either don’t have per-user credentials appropriate to create SQL server accounts (typical in public-facing web applications), or are unable to do so for various reasons ranging from security concerns to account management workload.
2. When each user authenticates with their own credentials, this effectively disables connection pooling. Especially in high-volume web scenarios, this will kill performance and put a strain on server resources.
3. I believe the loss of connection pooling will also burn through your license pool with per-user licensed SQL servers. I may be mistaken on this point, as I haven’t worked with a per-user licensed SQL server for the better part of a decade.
4. If you are unwilling to use SQL server authentication, you will most likely have to set up Kerberos constrained delegation so that your database server trusts your web server to authenticate users. This is a royal PITA to implement.

The Trouble With Triggers

As far as input inside a trigger, you only have access to the data being manipulated, system functions, and very little else. You can’t use SELECT statements in triggers either, at least not against normal tables and views.

SQL Context Info

Starting with MS SQL Server 2000, you can associate up to 128 bytes of data with the current connection using the SET CONTEXT_INFO command. Starting with SQL 2005, you can use the CONTEXT_INFO function to retrieve this data. This data is available everywhere, including triggers.

If you use connection pooling (and you probably are), this data is not reset when the connection is reused.

To set your context info data, use the SET CONTEXT_INFO command like so:

DECLARE @Ctx varbinary(128)
SELECT @Ctx = CONVERT(varbinary(128), 'My context data goes here.')
SET CONTEXT_INFO @Ctx

SET CONTEXT_INFO can only be used with variables and constants, meaning you can’t CONVERT data types or concatenate strings or any other calculation in the same command.

To retrieve the context data, use the CONTEXT_INFO function:

DECLARE @CtxData varchar(128)
SELECT @CtxData = CONVERT(VarChar(128), CONTEXT_INFO())
PRINT @CtxData

This will output ‘My context data goes here.’ etc. Note – The is ASCII character 0 (NULL). Why? We’re converting the string to fixed-length binary and then converting it back.

Know Your Roots

There was a time when programmers weren’t protected from the fact that strings are just chunks of memory. Just like programmers in the old days, you have two options: Pascal strings or C strings. You can stuff a length byte at the beginning of your context as in Pascal, or you can search for the null termination of your string as in the C language.

Also, because nchar and nvarchar are UNICODE UCS-2 strings, they use two bytes (of your 128 maximum) per character. char and varchar only use one byte per character, but the character set is limited. It’s a trade-off.

How does this help me?

We can use context info data to get information from our application in to our audit triggers. For example, we can get the current user name from our application or evem some identifying information about the location in our application, such as the name of the controller and action that triggered the event. Just be conscious of the 128 byte limitation. It's probably better to store an id for more information such as the id of the user account, or an id that references some lookup table with application locations.

The Code, Sir.

We will override the GetConntion() method of DriverConnectionProvider so that every NHibernate connection will have the current username stored in the context.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate.Connection;
using System.Data;

namespace NHibernate.Connection
{
    public class ContextDriverConnectionProvider : DriverConnectionProvider 
    {

        public override System.Data.IDbConnection GetConnection()
        {
            var conn = base.GetConnection();
            SetContext(conn);
            return conn;
        }

        private const string CONTEXT_SQL = "declare @Length tinyintndeclare @Ctx varbinary(128)nselect @Length = len(@username)nselect @Ctx = convert(binary(1), @Length) + convert(varbinary(127), @username)nset context_info @Ctx";

        protected virtual void SetContext(IDbConnection conn)
        {
            IDbCommand cmd = conn.CreateCommand();
            IDbDataParameter param = cmd.CreateParameter();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = CONTEXT_SQL;

            param.ParameterName = "@username";
            param.DbType = DbType.AnsiString;
            param.Size = 127;
            param.Value = System.Environmnt.UserName;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
        }

    }
}

This will run the following SQL code when NHibernate opens a SQL connection. Note that @username is a variable defined as a parameter on our IDbCommand. Also, I chose to use Pascal strings with the length stored in the first byte.

declare @Length tinyint
declare @Ctx varbinary(128)
select @Length = len(@username)
select @Ctx = convert(binary(1), @Length) + convert(varbinary(127), @username)
set context_info @Ctx

We'll alter our trigger code to get the username from the context instead of system_user.

set nocount on
declare @Username varchar(127)
declare @Length tinyint
declare @Ctx varbinary(128)
select @Ctx = CONTEXT_INFO()
select @Length = convert(tinyint, substring(@Ctx, 1, 1))
select @Username = convert(varchar(127), substring(@Ctx, 2, 1 + @Length))
if (@Username is null) select @Username = SYSTEM_USER

Security Implications

I am not a security expert. I am certainly not a SQL security expert. However, I believe this method is secure for most scenarios. In my opinion, this is at least as secure as auditing through NHibernate interceptors and events.

Here’s a few ways to circumvent the system, and how you can prevent that from happening:

• Manipulate the audit logs directly. Obviously, in any audit scenario, you should set permission on your audit tables to make them select and insert only.
• Alter the context_info data directly. A user overwrites their own context_info with that of another user, effectively blaming their actions on someone else. This can be done through a SQL injection attack some other security breach. If you go on vacation with the front door open, don’t be surprised when your house is robbed.
• Fail to set context_info. Again, this requires some other security breach. You may also change your trigger code to ROLLBACK TRANSACTION and RAISERROR when context_info is not set. This will force a rollback of the transaction (or in the case of no explicit transaction, the SQL statement that caused the trigger), abort the remainder of the SQL batch, and report an error. The approach requires everyone, including your DBA, to set an appropriate context_info before altering any audited data.

This is certainly my worst blog post this year, but don’t worry. I can do worse.

- Jason