ASP.NET MVC+EF6.0数据库对称密钥加密后的查询操作问询

这问题我之前在项目里也碰到过，手动每次开/关密钥太容易忘，而且代码冗余得不行，给你几个靠谱的优化方案，按场景选就行：

因为EF的DbContext通常和ASP.NET MVC的请求生命周期绑定（默认每个请求一个DbContext实例），而SQL Server的对称密钥是会话级的——同一个数据库连接里打开后一直有效，所以我们可以把密钥的开/关逻辑和DbContext的生命周期绑定，一劳永逸。

步骤：

• 创建一个基类EncryptedDbContext继承自DbContext
• 在基类里监听数据库连接的状态变化，确保连接打开时执行打开密钥命令，连接释放或DbContext销毁时关闭密钥
• 所有业务DbContext都继承这个基类

示例代码：

public abstract class EncryptedDbContext : DbContext
{
    private bool _isKeyOpened = false;

    public EncryptedDbContext(string nameOrConnectionString) : base(nameOrConnectionString)
    {
        // 监听连接状态变化
        this.Database.Connection.StateChange += Connection_StateChange;
    }

    private void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        if (e.CurrentState == ConnectionState.Open && !_isKeyOpened)
        {
            // 打开对称密钥
            this.Database.ExecuteSqlCommand(
                "OPEN SYMMETRIC KEY SymmetricKeyEncryptionTest DECRYPTION BY CERTIFICATE EncryptionTest;"
            );
            _isKeyOpened = true;
        }
        else if (e.CurrentState == ConnectionState.Closed && _isKeyOpened)
        {
            // 关闭对称密钥
            this.Database.ExecuteSqlCommand(
                "CLOSE SYMMETRIC KEY SymmetricKeyEncryptionTest;"
            );
            _isKeyOpened = false;
        }
    }

    protected override void Dispose(bool disposing)
    {
        // 兜底：即使连接没正常关闭，Dispose时也尝试关闭密钥
        if (_isKeyOpened && this.Database.Connection.State == ConnectionState.Open)
        {
            try
            {
                this.Database.ExecuteSqlCommand("CLOSE SYMMETRIC KEY SymmetricKeyEncryptionTest;");
            }
            catch { /* 忽略关闭时的异常，避免影响Dispose流程 */ }
        }
        this.Database.Connection.StateChange -= Connection_StateChange;
        base.Dispose(disposing);
    }
}

之后你的业务上下文只要继承EncryptedDbContext，就不用再手动处理密钥了，所有LINQ查询都会自动在可用的连接会话里使用已打开的密钥。

如果不想修改现有DbContext的继承关系，可以用EF的拦截器，自动在每个查询命令前后注入密钥的开/关逻辑。不过要注意：这个方案会给每个查询命令都添加OPEN语句，虽然SQL Server不会报错，但存在一定性能冗余，适合快速改造的场景。

示例代码：

public class SymmetricKeyInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        // 在查询前执行打开密钥命令
        var openKeyCmd = command.Connection.CreateCommand();
        openKeyCmd.CommandText = "OPEN SYMMETRIC KEY SymmetricKeyEncryptionTest DECRYPTION BY CERTIFICATE EncryptionTest;";
        openKeyCmd.ExecuteNonQuery();
        
        base.ReaderExecuting(command, interceptionContext);
    }

    public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        // 查询完成后关闭密钥
        try
        {
            var closeKeyCmd = command.Connection.CreateCommand();
            closeKeyCmd.CommandText = "CLOSE SYMMETRIC KEY SymmetricKeyEncryptionTest;";
            closeKeyCmd.ExecuteNonQuery();
        }
        catch { }
        
        base.ReaderExecuted(command, interceptionContext);
    }
}

然后在Global.asax的Application_Start里注册拦截器：

DbInterception.Add(new SymmetricKeyInterceptor());

如果你的项目已经在用Unit of Work模式，可以把密钥的开/关逻辑放到Unit of Work的初始化和销毁流程里，和业务事务绑定：

public class UnitOfWork : IDisposable
{
    private readonly YourDbContext _context;
    private bool _isKeyOpened = false;

    public UnitOfWork(YourDbContext context)
    {
        _context = context;
        OpenSymmetricKey();
    }

    private void OpenSymmetricKey()
    {
        if (_context.Database.Connection.State != ConnectionState.Open)
        {
            _context.Database.Connection.Open();
        }
        _context.Database.ExecuteSqlCommand(
            "OPEN SYMMETRIC KEY SymmetricKeyEncryptionTest DECRYPTION BY CERTIFICATE EncryptionTest;"
        );
        _isKeyOpened = true;
    }

    public void Complete()
    {
        _context.SaveChanges();
    }

    public void Dispose()
    {
        if (_isKeyOpened && _context.Database.Connection.State == ConnectionState.Open)
        {
            try
            {
                _context.Database.ExecuteSqlCommand("CLOSE SYMMETRIC KEY SymmetricKeyEncryptionTest;");
            }
            catch { }
        }
        _context.Dispose();
    }
}

使用时直接通过Unit of Work操作上下文：

using(var uow = new UnitOfWork(new YourDbContext()))
{
    var data = uow.Context.YourEntities.Where(...).ToList();
    uow.Complete();
}

• 异常安全：一定要用try-finally确保即使查询抛出异常，密钥也能被关闭——虽然数据库连接关闭后会自动关闭密钥，但手动兜底能避免会话残留问题
• 权限控制：确保EF使用的数据库用户拥有VIEW DEFINITION权限（针对证书）和CONTROL权限（针对对称密钥），否则打开密钥会失败
• 加密列性能：加密列上的索引会失效，如果查询频繁用到加密列，可考虑使用确定性加密（安全性稍低但支持索引），或者调整查询逻辑
• 避免重复打开：同一个连接会话里多次执行OPEN命令只会返回警告，不会报错，但还是尽量避免，所以绑定DbContext生命周期是最优选择

内容的提问来源于stack exchange，提问作者Stark