Setting Microsoft SQL's Context_Info

In this recipe, we will show you how to use Microsoft SQL Server's Context_Info to provide the current username to your audit triggers.

Getting ready

  • Complete the previous recipe, Generating trigger-based auditing
  • Download Ninject.dll and CommonServiceLocator.NinjectAdapter.dll from the Ninject project at http://ninject.org
  • Download Microsoft.Practices.ServiceLocation.dll from the Microsoft patterns and practices team available at http://commonservicelocator.codeplex.com/

How to do it…

  1. Add a reference to Ninject.dll, CommonServiceLocator.NinjectAdapter.dll and Microsoft.Practices.ServiceLocation.dll.
  2. Add the following IAuditColumnSource implementation:
    public class CtxAuditColumnSource : IAuditColumnSource 
{


  public IEnumerable<AuditColumn> 
    GetAuditColumns(Table dataTable)
  {
    var userStamp = new AuditColumn()
    {
      Name = "AuditUser",
      Value = new SimpleValue()
      {
        TypeName = NHibernateUtil.String.Name
      },
      Length = 127,
      IsNullable = false,
      IncludeInPrimaryKey = true,
      ValueFunction = delegate(TriggerActions action)
      {
        return "dbo.fnGetContextData()";
      }
    };

    var timeStamp = new AuditColumn()
    {
      Name = "AuditTimestamp",
      Value = new SimpleValue()
      {
        TypeName = NHibernateUtil.DateTime.Name
      },
      IsNullable = false,
      IncludeInPrimaryKey = true,
      ValueFunction = delegate(TriggerActions action)
      {
        return "getdate()";
      }
    };

    var operation = new AuditColumn()
    {
      Name = "AuditOperation",
      Value = new SimpleValue()
      {
        TypeName = NHibernateUtil.AnsiChar.Name
      },
      Length = 1,
      IsNullable = false,
      IncludeInPrimaryKey = false,
      ValueFunction = delegate(TriggerActions action)
      {
        switch (action)
        {
          case TriggerActions.INSERT:
            return "'I'";
          case TriggerActions.UPDATE:
            return "'U'";
          case TriggerActions.DELETE:
            return "'D'";
          default:
            throw new ArgumentOutOfRangeException("action");
        }
      }
    };

    return new AuditColumn[] {
      userStamp, timeStamp, operation 
    };

  }

}
  3. Add the following IContextDataProvider interface:
    public interface IContextDataProvider
{

  string GetData();
  string GetEmptyData();

}
  4. Add the following implementation:
    public class UsernameContextDataProvider : 
  IContextDataProvider 
{

  public string GetData()
  {
    return WindowsIdentity.GetCurrent().Name;
  }

  public string GetEmptyData()
  {
    return string.Empty;
  }

}
  5. Add the following ContextConnectionDriver:
    public class ContextInfoConnectionDriver : 
  DriverConnectionProvider 
{

  private const string COMMAND_TEXT = 
    "declare @length tinyint
" +
    "declare @ctx varbinary(128)
" +
    "select @length = len(@data)
" +
    "select @ctx = convert(binary(1), @length) + " +
    "convert(binary(127), @data)
" +
    "set context_info @ctx";


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

  public override void CloseConnection(IDbConnection conn)
  {
    EraseContext(conn);
    base.CloseConnection(conn);
  }

  private void SetContext(IDbConnection conn)
  {
    var sl = ServiceLocator.Current;
    var dataProvider = sl.GetInstance<IContextDataProvider>();
    var data = dataProvider.GetData();
    SetContext(conn, data);
  }

  private void EraseContext(IDbConnection conn)
  {
    var sl = ServiceLocator.Current;
    var dataProvider = sl.GetInstance<IContextDataProvider>();
    var data = dataProvider.GetEmptyData();
    SetContext(conn, data);
  }

  private void SetContext(IDbConnection conn, string data)
  {
    var cmd = conn.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = COMMAND_TEXT;

    var param = cmd.CreateParameter();
    param.ParameterName = "@data";
    param.DbType = DbType.AnsiString;
    param.Size = 127;
    param.Value = data;
    cmd.Parameters.Add(param);

    cmd.ExecuteNonQuery();
  }

}
  6. Add the following mapping document:
    <?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <database-object>
    <create>
      CREATE FUNCTION dbo.fnGetContextData()
      RETURNS varchar(127)
      AS
      BEGIN
        declare @data varchar(127)
        declare @length tinyint
        declare @ctx varbinary(128)
        select @ctx = CONTEXT_INFO()
        select @length = convert(tinyint, 
            substring(@ctx, 1, 1))
        select @data = convert(varchar(127), 
            substring(@ctx, 2, 1 + @length))
        return @data
      END
    </create>
    <drop>DROP FUNCTION dbo.fnGetContextData</drop>
  </database-object>
</hibernate-mapping>
  7. In the Main method of Program.cs, use the following code:
    var kernel = new StandardKernel();
kernel.Bind<IContextDataProvider>()
  .To<UsernameContextDataProvider>();
var sl = new NinjectServiceLocator(kernel);
ServiceLocator.SetLocatorProvider(() => sl);

var namingStrategy = new NamingStrategy();
var auditColumnSource = new CtxAuditColumnSource();
var cfg = new Configuration().Configure();
new TriggerAuditing(cfg, namingStrategy,
  auditColumnSource).Configure();

var sessionFaculty = cfg.BuildSessionFactory();

var se = new NHibernate.Tool.hbm2ddl.SchemaExport(cfg);
se.Execute(true, true, false);
  8. Set the NHibernate property connection.provider to <namespace>.ContextInfoConnectionDriver, <assembly>, to set the namespace and assembly according to the name of your project.
  9. Add a mapping element for this assembly so that the fnGetContextData mapping document is loaded.
  10. Build and run the program.

How it works…

Starting with Microsoft SQL Server 2000, SQL Server provides 128 bytes of context data for each database connection. This data is set using the SQL statement SET CONTEXT_INFO @ContextData where @ContextData may be a binary(128) variable or constant. It can be read using the CONTEXT_INFO() SQL function, which returns binary(128) data.

In this recipe, we store the current username in the CONTEXT_INFO. It's important to note that the CONTEXT_INFO is a fixed-length binary array, not a variable-length varbinary. When placing data into CONTEXT_INFO, any leftover bytes may contain trash.

Similar to storing strings in memory, when storing variable-length data in this fixed-length field, we must have some way to determine where the real data ends. The two possible ways to do this are as follows:

  • Taking the Pascal strings approach, we can use the first byte to determine the length of the data. This limits the amount of data that can be stored to 255 characters. This is fine, because SQL Server only allows half that amount.
  • Using the C string approach, we place a null terminator (zero byte) at the end of the string. The data can be any length, but we have to search for the null terminator to find the end.

In this recipe, we use the Pascal string approach. The fnGetContextData SQL function uses the first byte to determine the correct substring parameters to get our username string from the CONTEXT_INFO().

Because the Context_Info is tied to the database connection, we need to set it every time we open a database connection. Additionally, because our application will most likely use connection pooling, we should also clear the Context_Info when the application releases the connection back to the pool.

NHibernate's DriverConnectionProvider is responsible for providing a database connection as needed, and for closing those connections when they're no longer needed. This is the perfect place to set our Context_Info. The custom connection provider will set the Context_Info after the connection is opened, but before it's passed back to NHibernate. It also clears the Context_Info just before calling conn.Close() to return the connection to the connection pool.

The AuditUser column has been changed from our previous recipe so that our triggers call fnGetContextData() instead of using system_user.

Finally, we've added fnGetContextData as an auxiliary database object with our database-object mapping. This mapping provides the drop- and- create scripts used by hbm2ddl.

All of this allows us to use the application's current username in our audit logs. We can use any SQL credentials we like, including plain old SQL accounts. Of course, just as with the Creation and change stamping of entities recipe, you will likely need to replace WindowsIdentity.GetCurrent() with the correct implementation for your application.

See also

  • Generating trigger-based auditing
  • Using dynamic connection strings
  • Creation and change stamping of entities
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.148.102.166