In this recipe, I'll show you how to use Microsoft SQL Server's Context_Info
to provide the current username to our audit triggers.
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/.
Ninject.dll
, CommonServiceLocator.NinjectAdapter.dll
, and Microsoft.Practices.ServiceLocation.dll
.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 }; } }
IContextDataProvider
interface:public interface IContextDataProvider { string GetData(); string GetEmptyData(); }
public class UsernameContextDataProvider : IContextDataProvider { public string GetData() { return WindowsIdentity.GetCurrent().Name; } public string GetEmptyData() { return string.Empty; } }
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(); } }
<?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>
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);
connection.provider
to <namespace>.ContextInfoConnectionDriver
, <assembly>
, to set the namespace and assembly according to the name of your project.fnGetContextData
mapping document is loaded.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:
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 Creating and changing stamping entities recipe, you will likely need to replace WindowsIdentity.GetCurrent()
with the correct implementation for your application.