解决因SQL Server数据库引用程序集而不得不开启trustworthy方法

​前文中提到有提到,当SQL Server 数据库开启trustworthy时,存在着用户提升权限的风险。那么,我们是不是直接查出开启trustworthy的数据库,将其关闭就解决风险了呢?如下脚本可以直接关闭实例下所有开启trustworthy数据库的trustworthy属性:

use master
go
declare @sql varchar(max)     
set @sql=''
select @sql=@sql+'alter database '+ QUOTENAME(name,'[')+' set trustworthy ' +' off '
from sys.databases
where is_trustworthy_on=1
print @sql
--exec(@sql)

有心的朋友可能已经注意到,上面脚本的最后一句是注释掉的,我们是不能直接执行这个脚本的(重要的事情说三篇,不能直接执行!不能直接执行!不能直接执行!,我们必须确认数据库上是否有需要开启trustworthy的内容,如程序集。

    带有EXTERNAL_ACCESS 或 UNSAFE的程序集能正常执行的方案有两种:

  1. 开启数据库trustworthy,保证数据库拥有者有程序集 [EXTERNAL ACCESS] 或者 [UNSAFE] 权限。

  2.  程序集已使用其对应登录名具有 EXTERNAL ACCESS ASSEMBLY 权限的证书或非对称密钥加以签名。

考虑到前文中提到的安全隐患,如果数据库中存在着带有EXTERNAL_ACCESS 或 UNSAFE的程序集,并且数据库的trustworthy为开启状态,我们就需要采用方案2来代替方案1,消除安全隐患,下面我将分享方案2的处理步骤。

 

为ddl加强名称签名

强名称签名的私钥公钥生成

本文中是使用Windows强名称签名工具sn生成私钥和公钥的,首先我们需要到目录 :

C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools 下查找是否有sn工具,没有的话,就要另外安装,如果存在,以管理员身份打开CMD,切换到上面对应目录下:

使用强名称工具sn生成私钥、公钥的具体脚本及步骤如下:

最终将生成的私钥pri.snk 拷贝到项目文件中

在VS解决方案资源管理器中,右击项目名称→属性

在弹出的属性列表中选择“签名”→勾选为程序集签名,找到前面生成的私钥文件,这里是pri.snk:

最后重新生成dll,到此完成程序集的强名称签名。

将dll 部署到SQL Server数据库

下面我将给出将强名称签名的程序集部署到数据库上的脚本,并进行测试,如下:

use DB1
go
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'clr enabled',1
reconfigure with override
exec sp_configure 'show advanced options',0
go
reconfigure
go
​
USE master  --这个数据库一定是master
--创建非对称密钥
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'D:\TgSQLPlus\Skew.dll'  
--创建登录名
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
--把权限授予给该登录名 
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin;
USE DB1
--创建程序集
create ASSEMBLY DescriptiveStatistics from
'D:\TgSQLPlus\Skew.dll'
with PERMISSION_SET=EXTERNAL_ACCESS;
--创建用户定义函数(UDF)
CREATE AGGREGATE dbo.Skew(@s float)
returns float
external name DescriptiveStatistics.Skew;
create table test (name varchar(60),id bigint)
--测试
insert into test
select top 100 name,object_id
from sys.objects
go 10
with CustomerSalesCTE as
(
select
      name
       ,SUM(id) as TotalAmount
from test 
group by name
)
select
       ROUND(avg(TotalAmount),2) as Average
       ,ROUND(STDEV(TotalAmount),2) as StandardDeviation
       ,ROUND(dbo.Skew(cast(TotalAmount as decimal(30,2))),6) as Skewness
     
from CustomerSalesCTE
--确认trustworthy是否启用
select name,is_trustworthy_on from sys.databases
where name='DB1'

测试发现trustworthy关闭状态,自定义函数正常运行。

注意:

  • 如果两个程序集使用同一个私钥进行签名,在master库创建非对称秘钥时,会报如下错误:

消息 15396,级别 16,状态 1,第 103 行 名为 '' 的非对称密钥已存在,或已将此非对称密钥添加到该数据库中。

  • 在trustworthy关闭状态下,要先在master库创建非对称秘钥,使用非对称秘钥创建登陆名,并赋予[EXTERNAL ACCESS]权限,然后再在指定数据库中创建程序集,顺序不能颠倒,否则会报如下错误:

消息 10327,级别 14,状态 1,第 111 行

针对程序集 'DatabaseKurt' 的 CREATE ASSEMBLY 失败,因为程序集 'DatabaseKurt' 未获授权,不满足 PERMISSION_SET = EXTERNAL_ACCESS。满足以下两个条件之一时将给程序集授权: 数据库所有者(DBO)拥有 EXTERNAL ACCESS ASSEMBLY 权限,且数据库具有 TRUSTWORTHY 数据库属性;或者,程序集已使用其对应登录名具有 EXTERNAL ACCESS ASSEMBLY 权限的证书或非对称密钥加以签名。

  • 登陆账户的权限如果和创建程序集PERMISSION_SET选项不一致,即赋予账户[EXTERNAL ACCESS],PERMISSION_SET=UNSAFE,或者相反时,创建程序集亦会出现如下错误:

消息 10327,级别 14,状态 1,第 111 行

针对程序集 'DatabaseKurt' 的 CREATE ASSEMBLY 失败,因为程序集 'DatabaseKurt' 未获授权,不满足 PERMISSION_SET = UNSAFE。满足以下两个条件之一时将给程序集授权: 数据库所有者(DBO)拥有 UNSAFE ASSEMBLY 权限,且数据库具有 TRUSTWORTHY 数据库属性;或者,程序集已使用其对应登录名具有 UNSAFE ASSEMBLY 权限的证书或非对称密钥加以签名。

最后如果测试发现报如下错误:

消息 10314,级别 16,状态 4,第 74 行

在尝试加载程序集 ID 65544 时 Microsoft .NET Framework 出错。服务器可能资源不足,或者不信任该程序集,因为它的 PERMISSION_SET 设置为 EXTERNAL_ACCESS 或 UNSAFE。请重新运行查询,或检查有关的文档了解如何解决程序集信任问题。有关此错误的详细信息:

System.IO.FileLoadException: 未能加载文件或程序集“databasekurt, Version=0.0.0.0, Culture=neutral, PublicKeyToken=5db4a667503bfd56”或它的某一个依赖项。发生与安全有关的错误。 (异常来自 HRESULT:0x8013150A)

System.IO.FileLoadException:

   在 System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   在 System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

   在 System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)

   在 System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   在 System.Reflection.Assembly.Load(String assemblyString)

我们需要从头检查各个步骤是否完成。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值