SQL CLR 触发器，由于透明代码调用关键代码，如何使程序集可信? [英] SQL CLR Trigger, How to make an assembly trusted due to transparent code call critical code?

问题描述

我已经深入研究了 SQL CLR.不幸的是，我的第一个示例存在对安全代码的透明代码调用的问题.

I have dived into researching the SQL CLR. Unfortunately, my first example has problem with transparent code call to security code.

重点是我的 SQL CLR 触发器被视为透明代码.在触发器中，我使用 Quartz 调用 Quartz Windows 服务:

The point is my SQL CLR Trigger is treated as transparent code. And in trigger, I use Quartz to call to Quartz Windows service :

var properties = new NameValueCollection();
properties["quartz.scheduler.instanceName"] = "ServerScheduler";

properties["quartz.scheduler.proxy"] = "true";
properties["quartz.scheduler.proxy.address"] = string.Format("tcp://{0}:{1}/{2}", "localhost", "555",
                "QuartzScheduler");

var schedulerFactory = new StdSchedulerFactory(properties);

IScheduler scheduler = schedulerFactory.GetScheduler();

错误:

(135,1):SQL72014:.Net SqlClient 数据提供程序:消息 6522，级别 16，状态 1，过程 AfterMarketSessionInserted，第 1 行 A .NET Framework执行用户定义的例程或聚合期间发生错误AfterMarketSessionInserted":System.MethodAccessException:尝试通过安全透明的方法'.Database.Triggers.MarketSessionTriggers.AfterMarketSessionInserted()'访问安全关键方法 'Quartz.Impl.StdSchedulerFactory..ctor(System.Collections.Specialized.NameValueCollection)'失败.

(135,1): SQL72014: .Net SqlClient Data Provider: Msg 6522, Level 16, State 1, Procedure AfterMarketSessionInserted, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "AfterMarketSessionInserted": System.MethodAccessException: Attempt by security transparent method '.Database.Triggers.MarketSessionTriggers.AfterMarketSessionInserted()' to access security critical method 'Quartz.Impl.StdSchedulerFactory..ctor(System.Collections.Specialized.NameValueCollection)' failed.

程序集'数据库，版本=1.0.5275.15169，文化=中性，PublicKeyToken=null' 是部分信任的，这会导致 CLR无论任何透明度如何，都使其完全安全透明程序集本身中的注释.为了访问安全关键代码，必须完全信任此程序集.System.MethodAccessException: 在Database.Triggers.FinancialMarketSessionTriggers.AfterFinancialMarketSessionInserted()

Assembly 'Database, Version=1.0.5275.15169, Culture=neutral, PublicKeyToken=null' is partially trusted, which causes the CLR to make it entirely security transparent regardless of any transparency annotations in the assembly itself. In order to access security critical code, this assembly must be fully trusted. System.MethodAccessException: at Database.Triggers.FinancialMarketSessionTriggers.AfterFinancialMarketSessionInserted()

为什么 SQL CLR Trigger 代码被认为是透明代码并且部分可信?

Why the SQL CLR Trigger code is considered as transparent code and trusted partially?

如何使 SQL CLR 触发器代码不是透明代码或使其完全受信任?

How to make SQL CLR Trigger code not a transparent code or make it trusted fully?

我愿意接受建议.

推荐答案

为什么仅部分信任 SQLCLR 代码?

Why is SQLCLR code only trusted partially?

默认情况下，SQL Server 内部运行的 CLR 代码(即SQLCLR")受到严格限制，以免降低 SQL Server 的安全性或稳定性.

By default, CLR code running inside of SQL Server (i.e. "SQLCLR") is highly restricted so as to not degrade security or stability of SQL Server.

如何让 SQLCLR 完全受信任?

How do you make SQLCLR fully trusted?

Assembly 中的 CLR 代码可以做什么(主要)由每个 Assembly 的 PERMISSION_SET 属性控制.如果您在通过 CREATE ASSEMBLY 加载程序集时未指定 PERMISSION_SET，则默认为 SAFE，这是最受限制的 不完全信任.为了使 CLR 代码能够访问 SQL Server 外部(网络、文件系统、操作系统等)，您需要将程序集设置为至少 EXTERNAL_ACCESS，但这是仍然不完全信任.为了被视为完全信任，您需要将程序集设置为UNSAFE.

What the CLR code within an Assembly can do is controlled (mostly) by the PERMISSION_SET property of each Assembly. If you do not specify a PERMISSION_SET when loading the Assembly via CREATE ASSEMBLY, the default will be SAFE which is the most restricted and not fully trusted. In order for the CLR code to be able to reach outside of SQL Server (to the network, file system, OS, etc.) you would need to set the Assembly to at least EXTERNAL_ACCESS, but this is still not fully trusted. In order to be considered fully trusted you need to set the Assembly to UNSAFE.

要将任何程序集设置为 EXTERNAL_ACCESS 或 UNSAFE，您需要执行以下操作之一:

In order to set any Assembly to either EXTERNAL_ACCESS or UNSAFE, you need to do one of the following:

• 使用密码对程序集进行签名，从程序集创建非对称密钥，从非对称密钥创建登录名，授予登录名 UNSAFE ASSEMBLY 权限.这是首选方法.
• 将包含程序集的数据库设置为 TRUSTWORTHY = ON.这假定数据库的所有者具有 UNSAFE ASSEMBLY 服务器级权限(通常是这种情况).虽然此选项更快/更容易，但它不是首选，因为 TRUSTWORTHY = ON 是 相当大的安全漏洞.

• Sign the Assembly with a password, create an Asymmetric Key from the Assembly, create a Login from the Asymmetric Key, grant the Login the UNSAFE ASSEMBLY permission. This is the preferred method.
• Set the database containing the Assembly to TRUSTWORTHY = ON. This assumes that the owner of the database has the UNSAFE ASSEMBLY server-level permission (which is typically the case). While this option is quicker / easier, it is not preferred due to TRUSTWORTHY = ON being a fairly wide-open security hole.

如果您想更详细地了解 SQLCLR 安全性，尤其是关于SAFE 程序集的限制程度，请查看此文章，我在 SQL Server Central 上写的.

If you want a more detailed look at SQLCLR security, especially in relation to how restricted SAFE Assemblies are, check out this article that I wrote on SQL Server Central.

这篇关于SQL CLR 触发器，由于透明代码调用关键代码，如何使程序集可信?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！