I am using SQL Server and database triggers to keep a data-level audit of all changes to the system. This audit includes the userID / name of whomever initiated a change. Ideally I'd like to do something like this in my AppHost.Configure method:
SqlServerDialect.Provider.UseUnicode = true;
var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider)
{
ConnectionFilter = (db =>
{
IAuthSession session = this.Request.GetSession();
if (session != null && !session.UserName.IsNullOrEmpty())
{
System.Data.IDbCommand cmd = db.CreateCommand();
cmd.CommandText = "declare @ci varbinary(128); select @ci = CAST(@Username as varbinary(128)); set context_info @ci";
System.Data.IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = "Username";
param.DbType = System.Data.DbType.String;
//param.Value = session.UserName;
param.Value = session.UserAuthId;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
return new ProfiledDbConnection(db, Profiler.Current);
}),
AutoDisposeConnection = true
};
container.Register<IDbConnectionFactory>(dbFactory);
Of course, this doesn't work because this.Request doesn't exist. Is there any way to access the current session from the ConnectionFilter or ExecFilter on an OrmLite connection?
The other approach I had started, doing an override of the Db property of Service, doesn't work any more because I've abstracted some activities into their own interfaced implementations to allow for mocks during testing. Each of these is passed a function that is expected to return the a DB connection. Example:
// Transaction processor
container.Register<ITransactionProcessor>(new MockTransactionProcessor(() => dbFactory.OpenDbConnection()));
So, how can I ensure that any DML executed has the (admittedly database-specific) context information needed for my database audit triggers?
The earlier multi tenant ServiceStack example shows how you can use the Request Context to store per-request items, e.g. you can populate the Request Context from a Global Request filter:
GlobalRequestFilters.Add((req, res, dto) =>
{
var session = req.GetSession();
if (session != null)
RequestContext.Instance.Items.Add(
"UserName", session.UserName);
});
And access it within your Connection Filter:
ConnectionFilter = (db =>
{
var userName = RequestContext.Instance.Items["UserName"] as string;
if (!userName.IsNullOrEmpty()) {
//...
}
}),
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments