Access ServiceStack session from ConnectionFilter

Question

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?

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

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()) {
       //...
    }
}),

edited May 23 '17 at 12:12

Community

1
1

answered Jan 29 '15 at 18:43

mythz

141,670
29
246
390

I saw `RequestContext` and it sparked something, but it was empty in debugging. I of course didn't think of populating it! – jklemmack Jan 29 '15 at 19:45

score 1 · Answer 2 · answered Jan 29 '15 at 21:55

Another approach is to use a factory pattern, similar to how ServiceStack creates OrmLite db connections in the first place. Since all user-associated calls are made via the ServiceRunner, I piggy-back off of the session that's managed by ServiceStack.

public class TransactionProcessorFactory : ITransactionProcessorFactory
{
    public ITransactionProcessor CreateTransactionProcessor(IDbConnection Db)
    {
        return new TransactionProcessor(Db);
    }
}

public abstract MyBaseService : Service
{
    private IDbConnection db;

    public override System.Data.IDbConnection Db
    {
        get
        {
            if (this.db != null) return db;

            this.db = this.TryResolve<IDbConnectionFactory>().OpenDbConnection();

            IAuthSession session = this.Request.GetSession();

            if (session != null && !session.UserName.IsNullOrEmpty())
            {
                IDbCommand cmd = db.CreateCommand();
                cmd.CommandText = "declare @ci varbinary(128); select @ci = CAST(@Username as varbinary(128)); set context_info @ci";
                IDbDataParameter param = cmd.CreateParameter();
                param.ParameterName = "Username";
                param.DbType = DbType.String;
                //param.Value = session.UserName;
                param.Value = session.UserAuthId;
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
            }
            return db;
        }
    }

    private ITransactionProcessor tp = null;
    public virtual ITransactionProcessor TransactionProcessor
    {
        get
        {
            if (this.tp != null) return tp;
            var factory = this.TryResolve<ITransactionProcessorFactory>();
            this.tp = factory.CreateTransactionProcessor(this.Db);

            return tp;
        }
    }
}

Not good. What if you need to implement `Db` property in your repository -- you will need repositories the moment your service starts fleshing out. Wrap the whole db connection context initialization in a custom db factory and use DI to auto-wire a public `IDbConnectionFactory` property so you can reuse it in your repos later. — wqw, Jan 30 '15 at 15:40
@jklemmack: Wat? I meant a `public class MySqlServerFactory : IDbConnectionFactory` implementation which gets injected. — wqw, Feb 09 '15 at 15:33

score 1 · Answer 3 · edited May 23 '17 at 11:43

For the sake of potential future ServiceStack users, another approach would be to use OrmLite's Global Insert/Update filters combined with Mythz's approach above to inject the necessary SQL only when DML actions are made. It isn't 100%, since there may be stored procs or manual SQL, but that's potentially handled via an IDbConnection extension method to manually set desired auditing information.

Access ServiceStack session from ConnectionFilter

3 Answers3