基于C#控制器参数限制SQL Server特定表的写入权限

针对你在.NET测试SQL工具里遇到的「根据控制器参数限制Accounts表写操作」的问题，我分享几个实际落地过的可行方案：

方案1：数据库角色+动态连接字符串（最推荐） #

这是最安全可靠的方案，依赖数据库原生的权限控制，完全避免SQL解析的坑：

• 先在SQL Server里创建只读角色，给它仅授予Accounts表的SELECT权限：

  -- 创建只读角色
  CREATE ROLE [ReadOnlyAccounts]
  -- 授予Accounts表查询权限
  GRANT SELECT ON dbo.Accounts TO [ReadOnlyAccounts]
  -- 其他需要限制的表同理，或者给角色授予全局只读权限（按需）
  

• 创建绑定该角色的SQL登录名和数据库用户，确保这个账号只能查询Accounts表，无法执行写操作。
• 在.NET配置文件里配置两个连接字符串：一个是正常业务用的读写连接串，另一个是测试工具专用的只读连接串（用上面创建的只读账号）。
• 在控制器里，根据InSupportTool参数动态选择连接串：当参数为true时，使用只读连接串初始化数据库上下文或SQL命令对象；正常模式则用读写串。

这个方案的优势是完全无漏洞，测试人员不管怎么写SQL都绕不开数据库权限，稳定性拉满。

方案2：.NET层SQL命令拦截（适合轻量场景） #

如果不想改数据库权限，可以在应用层做拦截校验，针对内部测试工具的场景足够好用：

• 封装一个统一的SQL执行入口方法，所有测试工具的SQL都通过这个方法执行。
• 当InSupportTool=true时，先校验SQL的操作类型和目标表：
  • 可以用SQL Server的系统函数sys.dm_exec_describe_first_result_set来解析SQL，比自己写正则可靠得多，示例C#代码：

    public void ExecuteSupportToolSql(string sql, bool inSupportTool)
    {
        if (inSupportTool)
        {
            using (var conn = new SqlConnection(YourConnectionString))
            {
                conn.Open();
                // 解析SQL的操作类型和目标表
                var parseCmd = new SqlCommand(@"
                    SELECT operation, object_name 
                    FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1)
                ", conn);
                parseCmd.Parameters.AddWithValue("@sql", sql);
                using (var reader = parseCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var op = reader["operation"]?.ToString();
                        var table = reader["object_name"]?.ToString();
                        // 检查是否是针对Accounts表的写操作
                        if (!string.IsNullOrEmpty(op) && !string.IsNullOrEmpty(table)
                            && (op.Equals("INSERT") || op.Equals("UPDATE") || op.Equals("DELETE"))
                            && table.Equals("Accounts", StringComparison.OrdinalIgnoreCase))
                        {
                            throw new InvalidOperationException("测试工具模式下禁止对Accounts表执行写操作");
                        }
                    }
                }
            }
        }
        // 执行SQL逻辑...
    }
    

• 额外可以加一层正则校验做前置过滤，比如匹配INSERT\s+.*Accounts这类模式，双重保障。

这个方案不需要修改数据库，灵活度高，适合内部测试人员不会刻意规避规则的场景。

方案3：动态切换CHECK约束（适配你之前的尝试） #

针对你提到的CHECK约束思路，其实可以通过.NET参数动态修改约束状态，而不是让约束依赖外部参数：

• 先创建一个默认不生效的CHECK约束：

  ALTER TABLE Accounts WITH NOCHECK ADD CONSTRAINT chk_read_only_accounts CHECK(1=1)
  

• 当InSupportTool=true时，执行SQL修改约束为禁止写操作：

  -- 先删除旧约束（如果存在）
  IF EXISTS(SELECT * FROM sys.constraints WHERE name='chk_read_only_accounts')
      ALTER TABLE Accounts DROP CONSTRAINT chk_read_only_accounts
  -- 添加新约束，1=0会阻止所有写操作
  ALTER TABLE Accounts WITH CHECK ADD CONSTRAINT chk_read_only_accounts CHECK(1=0)
  

• 当退出测试工具模式时，再把约束改回CHECK(1=1)或者直接删除。

注意：这个方案需要应用账号有修改表结构的权限，而且要考虑并发问题——如果多个测试人员同时操作，可能会出现约束修改冲突，适合低并发的测试场景。

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