在SQL Server 2012中设置可信赖=的安全风险 [英] Security risks of setting trustworthy = on in sql server 2012

问题描述

我的开发数据库中出现以下错误:

I get the following errors in my development database:

A .NET Framework error occurred during execution of user-defined routine or aggregate "SpCreateTable": 
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All
The demanded resources were: Synchronization, ExternalThreading

设置值得信任=的正确解决方案是正确的吗?与此相关的安全问题是什么?

Is the correct solution to set trustworthy = on? What are security concerns with this?

推荐答案

数据库的TRUSTWORTHY属性(设置为ON时)实质上向SQL Server声明该数据库中包含的代码，并以模拟方式执行上下文，应该被允许到达该数据库之外，同时保持该模拟的安全上下文.它还允许将该数据库中的 all 个SQLCLR程序集设置为EXTERNAL_ACCESS和UNSAFE，无论该代码是否到达服务器外部(外部含义:网络访问，文件系统访问，注册表访问，环境访问等).

The TRUSTWORTHY property of a database (when set to ON) essentially declares to SQL Server that code contained within that database, and executing in an impersonated context, should be allowed to reach outside of that database while maintaining that impersonated security context. It also allows for all SQLCLR Assemblies in that Database to be set to EXTERNAL_ACCESS and UNSAFE, whether or not that code reaches outside of the server (outside meaning: network access, file system access, registry access, environment access, etc).

这是允许这样做的一种相当通用的方法，因为它涵盖了数据库中的所有代码.使用证书和/或非对称密钥对模块(过程和/或程序集)进行签名，可以更精确地控制哪些代码具有哪些权限.

It is a rather generic means of allowing for this as it covers all code within the database. Using Certificates and/or Asymmetric Keys to sign modules--procs and/or assemblies--allow for more granular control over what code has what permissions.

将数据库设置为TRUSTWORTHY还允许在此数据库中启动的任何进程达到服务器级别和/或跨其他数据库.通常，进程被限制/隔离到启动它的数据库中.如果数据库由"sa"登录名拥有，则在该数据库中启动并以"dbo"身份运行的任何进程将实际上具有"sa"特权(喜欢！).

Setting a Database to TRUSTWORTHY also allows any process starting in this Database to reach up to the Server-level and/or across to other Databases. Normally a process is confined / quarantined to the Database where it started. If the Database is owned by the "sa" Login, then any process initiated in that Database and running as "dbo" will effectively have "sa" privileges (yikes!).

我不想在这里描述，而是在充分传达有关模拟的细节，扩展所述模拟，签名模块等所需的详细信息中，我建议仔细阅读以下有关此主题的资源:

Rather than trying to describe here, in the amount of detail required to fully communicate the specifics about impersonation, extending said impersonation, signing modules, etc, I recommend perusing the following resources on this topic:

• 请，请停止使用模拟，信誉和跨数据库所有权链接
• 在SQL Server中使用TRUSTWORTHY数据库设置的指南
• SQLCLR的阶梯级别4:安全性(外部和不安全组件)
  这是我作为SQLCLR系列文章的一部分撰写的文章，其中的示例说明了TRUSTWORTHY方法和基于签名程序集的Login方法之间的区别.需要免费注册.

• PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining
• Guidelines for using the TRUSTWORTHY database setting in SQL Server
• Extending Database Impersonation by Using EXECUTE AS
  This is a very informative document that covers most aspects of this topic, and is also referenced in the linked page above.
• Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)
  This is an article I wrote as part of a series on SQLCLR that has examples which illustrate the differences between the TRUSTWORTHY method and the Signed Assembly-based Login method; Free registration is required.

您应该避免尽可能将数据库设置为TRUSTWORTHY.如果确实必须进行多线程/异步调用，并且如果您具有源代码并正在编译程序集，那么我想不出使用SET TRUSTWORTHY ON选项的原因.相反，您应该使用密码对程序集签名，并使用以下命令来设置允许EXTERNAL_ACCESS和UNSAFE程序集的首选方法:

You should avoid setting your database to TRUSTWORTHY as much as possible. If you really must have multithreading / async calls AND if you have the source code and are compiling the assembly, then I cannot think of a reason to use the SET TRUSTWORTHY ON option. Instead, you should sign the assembly with a password and use the following commands to set up the preferred method of allowing EXTERNAL_ACCESS and UNSAFE assemblies:

USE [master];
  CREATE ASYMMETRIC KEY [ClrPermissionsKey]
    AUTHORIZATION [dbo]
    FROM EXECUTABLE FILE = 'C:\path\to\my\assembly.dll';

CREATE LOGIN [ClrPermissionsLogin]
  FROM ASYMMETRIC KEY [ClrPermissionsKey];

GRANT UNSAFE ASSEMBLY TO [ClrPermissionsLogin];

就位后，您可以转到程序集已加载并运行的数据库:

Once that is in place, you can go to the database where your assembly has been loaded and run:

ALTER ASSEMBLY [MyAssembly] WITH PERMISSION_SET = UNSAFE;

或者您可以在CREATE ASSEMBLY命令的末尾包含WITH PERMISSION_SET = UNSAFE.

Or you could have included WITH PERMISSION_SET = UNSAFE at the end of the CREATE ASSEMBLY command.

这篇关于在SQL Server 2012中设置可信赖=的安全风险的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！