sql server 2012中设置trustworthy=on的安全风险 [英] 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

设置trustworthy = on 是正确的解决方案吗?这有什么安全问题?

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

推荐答案

数据库的 TRUSTWORTHY 属性(当设置为 ON 时)本质上向 SQL Server 声明代码包含在该数据库中并在模拟上下文中执行时，应允许访问该数据库之外，同时维护该模拟安全上下文.它还允许将该数据库中的所有 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 中使用可信数据库设置的指南
• 使用 EXECUTE AS 扩展数据库模拟
  这是一份内容非常丰富的文档，涵盖了该主题的大部分方面，并且在上面的链接页面中也有引用.
• 通往 SQLCLR 的阶梯级别 4:安全性(外部和不安全的程序集)
  这是我作为 SQLCLR 系列文章的一部分写的一篇文章，其中的示例说明了 TRUSTWORTHY 方法和基于签名程序集的登录方法之间的区别；需要免费注册.

您应该尽可能避免将数据库设置为 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	omyassembly.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中设置trustworthy=on的安全风险的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！