创建程序集Creating an Assembly

适用于: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: YesSQL Server NoAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

托管数据库对象(如存储过程或触发器)先经过编译,然后部署到称为程序集的单元中。Managed database objects, such as stored procedures or triggers, are compiled and then deployed in units called an assembly. 托管 DLL 程序集必须在中MicrosoftMicrosoft SQL ServerSQL Server注册,然后才能使用程序集提供的功能。Managed DLL assemblies must be registered in MicrosoftMicrosoft SQL ServerSQL Server before the functionality the assembly provides can be used. 若要在 SQL ServerSQL Server 数据库中注册程序集,请使用 CREATE ASSEMBLY 语句。To register an assembly in a SQL ServerSQL Server database, use the CREATE ASSEMBLY statement. 本主题讨论如何使用 CREATE ASSEMBLY 语句在数据库中注册程序集,以及如何为程序集指定安全设置。This topic discusses how to register an assembly in a database using the CREATE ASSEMBLY statement, and how to specify the security settings for the assembly.

CREATE ASSEMBLY 语句The CREATE ASSEMBLY Statement

CREATE ASSEMBLY 语句用于在数据库中创建程序集。The CREATE ASSEMBLY statement is used to create an assembly in a database. 以下是示例:Here is an example:

CREATE ASSEMBLY SQLCLRTest  
FROM 'C:\MyDBApp\SQLCLRTest.dll';  

FROM 子句指定要创建的程序集的路径名。The FROM clause specifies the pathname of the assembly to create. 此路径既可以是通用命名约定 (UNC) 路径,也可以是计算机本地的物理文件路径。This path can either be a Universal Naming Convention (UNC) path or a physical file path that is local to the machine.

SQL ServerSQL Server 不允许使用相同的名称、区域性和公钥来注册程序集的不同版本。does not allow registering different versions of an assembly with the same name, culture and public key.

可以创建引用其他程序集的程序集。It is possible to create assemblies that reference other assemblies. 当在 SQL ServerSQL Server 中创建程序集时,SQL ServerSQL Server 还将创建由根级别程序集引用的程序集(如果尚未在数据库中创建被引用程序集)。When an assembly is created in SQL ServerSQL Server, SQL ServerSQL Server also creates the assemblies referenced by the root-level assembly, if the referenced assemblies are not already created into the database.

将向数据库用户或用户角色授予在数据库中创建进而拥有程序集的权限。Database users or user roles are given permissions to create, and thereby own, assemblies in a database. 为了创建程序集,数据库用户或角色应具有 CREATE ASSEMBLY 权限。In order to create assemblies, the database user or role should have the CREATE ASSEMBLY permission.

仅当满足以下条件时,程序集才能成功地引用其他程序集:An assembly can only succeed in referencing other assemblies if:

  • 所调用或被引用的程序集由同一个用户或角色所有。The assembly that is called or referenced is owned by the same user or role.

  • 所调用或被引用的程序集是在同一个数据库中创建的。The assembly that is called or referenced was created in the same database.

创建程序集时指定安全性Specifying Security When Creating Assemblies

SQL ServerSQL Server数据库中创建程序集时,可以指定三个不同的安全级别之一,你的代码可以在其中运行: SAFEEXTERNAL_ACCESSUNSAFEWhen creating an assembly into a SQL ServerSQL Server database, you can specify one of three different levels of security in which your code can run: SAFE, EXTERNAL_ACCESS, or UNSAFE. 当运行CREATE ASSEMBLY语句时,将对代码程序集执行某些检查,这可能会导致程序集无法在服务器上注册。When the CREATE ASSEMBLY statement is run, certain checks are performed on the code assembly which may cause the assembly to fail to register on the server. 有关详细信息,请参阅CodePlex上的模拟示例。For more information, see the Impersonation sample on CodePlex.

SAFE是默认权限集,适用于大多数方案。SAFE is the default permission set and works for the majority of scenarios. 若要指定给定的安全级别,您可以按如下所示修改 CREATE ASSEMBLY 语句的语法:To specify a given security level, you modify the syntax of the CREATE ASSEMBLY statement as follows:

CREATE ASSEMBLY SQLCLRTest  
FROM 'C:\MyDBApp\SQLCLRTest.dll'  
WITH PERMISSION_SET = SAFE;  

通过只需省略上述第三行代码,也可以通过安全权限集创建程序集:It is also possible to create an assembly with the SAFE permission set by simply omitting the third line of code above:

CREATE ASSEMBLY SQLCLRTest  
FROM 'C:\MyDBApp\SQLCLRTest.dll';  

当程序集中的代码在安全权限集下运行时,它只能通过进程内托管提供程序在服务器中执行计算和数据访问。When code in an assembly runs under the SAFE permission set, it can only do computation and data access within the server through the in-process managed provider.

创建 EXTERNAL_ACCESS 和 UNSAFE 程序集Creating EXTERNAL_ACCESS and UNSAFE Assemblies

EXTERNAL_ACCESS解决了代码需要访问服务器外部资源(如文件、网络、注册表和环境变量)的情况。EXTERNAL_ACCESS addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables. 只要服务器访问外部资源,它就会模拟调用托管代码的用户的安全上下文。Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code.

安全代码权限适用于程序集不安全或需要对受限制资源(如MicrosoftMicrosoft Win32 API)的其他访问权限的情况。UNSAFE code permission is for those situations in which an assembly is not verifiably safe or requires additional access to restricted resources, such as the MicrosoftMicrosoft Win32 API.

若要在中SQL ServerSQL Server创建EXTERNAL_ACCESSUNSAFE程序集,必须满足以下两个条件之一:To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL ServerSQL Server, one of the following two conditions must be met:

  1. 程序集经过了强名称签名或使用证书进行了 Authenticode 签名。The assembly is strong name signed or Authenticode signed with a certificate. 此强名称(或证书)是在中SQL ServerSQL Server作为非对称密钥(或证书)创建的,并且具有具有外部访问程序集权限(对于外部访问程序集)或unsafe assembly权限(对于不安全的程序集)的相应登录名。This strong name (or certificate) is created inside SQL ServerSQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).

  2. 数据库所有者(DBO)具有外部访问程序集(对于外部访问程序集)或不安全程序集(对于不安全程序集),并且数据库的 "可信数据库" 属性设置为 "开"。The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON.

在加载程序集(包括执行)时,也将检查上面所列的两个条件。The two conditions listed above are also checked at assembly load time (which includes execution). 至少必须满足这些条件之一才能加载程序集。At least one of the conditions must be met in order to load the assembly.

建议不要将数据库上的 "可信数据库" 属性设置为 "仅在服务器进程中运行公共语言运行时(CLR)代码"。We recommend that the TRUSTWORTHY Database Property on a database not be set to ON only to run common language runtime (CLR) code in the server process. 而是建议在 master 数据库中通过程序集文件创建非对称密钥。Instead, we recommend that an asymmetric key be created from the assembly file in the master database. 然后,必须创建映射到此非对称密钥的登录名,并且必须向该登录名授予EXTERNAL ACCESS assemblyUNSAFE ASSEMBLY权限。A login mapped to this asymmetric key must then be created, and the login must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.

以下Transact-SQLTransact-SQL语句执行创建非对称密钥所需的步骤,将登录名映射到此密钥,然后将EXTERNAL_ACCESS权限授予该登录名。The following Transact-SQLTransact-SQL statements perform the steps that are required to create an asymmetric key, map a login to this key, and then grant EXTERNAL_ACCESS permission to the login. 必须运行以下 Transact-SQLTransact-SQL 语句,然后才能运行 CREATE ASSEMBLY 语句。You must run the following Transact-SQLTransact-SQL statements before running the CREATE ASSEMBLY statement.

USE master;   
GO    
  
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll'     
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey     
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin;   
GO   

备注

必须创建新的登录名以与非对称密钥关联。You must create a new login to associate with the asymmetric key. 此登录名仅用于授予权限;不必与用户关联或在应用程序中使用。This login is only used to grant permissions; it does not have to be associated with a user, or used within the application.

若要创建外部访问程序集,创建者需要具有外部访问权限。To create an EXTERNAL ACCESS assembly, the creator needs to have EXTERNAL ACCESS permission. 此权限在创建程序集时指定:This is specified when creating the assembly:

CREATE ASSEMBLY SQLCLRTest  
FROM 'C:\MyDBApp\SQLCLRTest.dll'  
WITH PERMISSION_SET = EXTERNAL_ACCESS;  

以下Transact-SQLTransact-SQL语句执行创建非对称密钥所需的步骤,将登录名映射到此密钥,然后将UNSAFE权限授予该登录名。The following Transact-SQLTransact-SQL statements perform the steps that are required to create an asymmetric key, map a login to this key, and then grant UNSAFE permission to the login. 必须运行以下 Transact-SQLTransact-SQL 语句,然后才能运行 CREATE ASSEMBLY 语句。You must run the following Transact-SQLTransact-SQL statements before running the CREATE ASSEMBLY statement.

USE master;   
GO    
  
CREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll';     
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey ;    
GRANT UNSAFE ASSEMBLY TO SQLCLRTestLogin ;  
GO  

若要指定使用UNSAFE权限加载程序集,请在将程序集加载到服务器时指定unsafe权限集:To specify that an assembly loads with UNSAFE permission, you specify the UNSAFE permission set when loading the assembly into the server:

CREATE ASSEMBLY SQLCLRTest  
FROM 'C:\MyDBApp\SQLCLRTest.dll'  
WITH PERMISSION_SET = UNSAFE;  

有关每个设置的权限的详细信息,请参阅CLR 集成安全性For more details about the permissions for each of the settings, see CLR Integration Security.

另请参阅See Also

管理 CLR 集成程序集 Managing CLR Integration Assemblies
更改程序集 Altering an Assembly
删除程序集 Dropping an Assembly
CLR 集成代码访问安全性 CLR Integration Code Access Security
TRUSTWORTHY 数据库属性 TRUSTWORTHY Database Property
允许部分可信任的调用方Allowing Partially Trusted Callers