在 SQL Server 中对存储过程签名Signing Stored Procedures in SQL Server

数字签名是用签名人的私钥加密的数据摘要。A digital signature is a data digest encrypted with the private key of the signer. 该私钥可确保数字签名对于其持有者或所有者是唯一的。The private key ensures that the digital signature is unique to its bearer or owner. 可以注册存储的过程、 (除内联表值函数) 的函数、 触发器和程序集。You can sign stored procedures, functions (except for inline table-valued functions), triggers, and assemblies.

您可以使用证书或非对称密钥为存储过程签名。You can sign a stored procedure with a certificate or an asymmetric key. 此设计适用于无法通过所属权链继承权限或所属权链中断的方案,如动态 SQL。This is designed for scenarios when permissions cannot be inherited through ownership chaining or when the ownership chain is broken, such as dynamic SQL. 然后可以创建映射到证书的用户授予针对存储的过程需要访问的对象的用户权限的证书。You can then create a user mapped to the certificate, granting the certificate user permissions on the objects the stored procedure needs to access.

您可以还创建登录名映射到同一个证书,并授予该登录名,任何所需的服务器级别权限,或将该登录名添加到一个或多个固定的服务器角色。You can also create a login mapped to the same certificate, and then grant any necessary server-level permissions to that login, or add the login to one or more of the fixed server roles. 这样设计是为了避免在TRUSTWORTHY数据库的方案需要更高级别的权限设置。This is designed to avoid enabling the TRUSTWORTHY database setting for scenarios in which higher level permissions are needed.

当执行存储的过程时,SQL Server 将与调用方的相结合证书用户和/或登录名的权限。When the stored procedure is executed, SQL Server combines the permissions of the certificate user and/or login with those of the caller. 与不同EXECUTE AS子句,它不会更改该过程的执行上下文。Unlike the EXECUTE AS clause, it does not change the execution context of the procedure. 返回登录名和用户名的内置函数会返回调用方的名称,而不是证书用户的名称。Built-in functions that return login and user names return the name of the caller, not the certificate user name.

创建证书Creating Certificates

当你注册的证书或非对称密钥的加密哈希的存储的过程代码,以及执行包含的数据摘要的存储的过程的以用户身份,使用创建的私钥。When you sign a stored procedure with a certificate or asymmetric key, a data digest consisting of the encrypted hash of the stored procedure code, along with the execute-as user, is created using the private key. 在运行时,数据摘要将使用公钥解密并与存储过程的哈希值进行比较。At run time, the data digest is decrypted with the public key and compared with the hash value of the stored procedure. 更改 execute-按用户使无效哈希值,因此,数字签名不再匹配。Changing the execute-as user invalidates the hash value so that the digital signature no longer matches. 修改存储的过程删除签名完全,即,禁止不具有私钥的访问权限更改存储的过程代码的人。Modifying the stored procedure drops the signature entirely, which prevents someone who does not have access to the private key from changing the stored procedure code. 在任一情况下,您必须重新签名过程每次更改代码或 execute-以用户身份。In either case, you must re-sign the procedure each time you change the code or the execute-as user.

有两个必需的步骤中为模块签名涉及:There are two required steps involved in signing a module:

  1. 使用 Transact-SQL CREATE CERTIFICATE [certificateName] 语句创建一个证书。Create a certificate using the Transact-SQL CREATE CERTIFICATE [certificateName] statement. 此语句具有多个用于设置开始日期、结束日期和密码的选项。This statement has several options for setting a start and end date and a password. 默认的到期日期为一年。The default expiration date is one year.

  2. 使用 Transact-SQL ADD SIGNATURE TO [procedureName] BY CERTIFICATE [certificateName] 语句通过证书为过程签名。Sign the procedure with the certificate using the Transact-SQL ADD SIGNATURE TO [procedureName] BY CERTIFICATE [certificateName] statement.

一旦已签名模块,一个或多个主体需要创建才能保存应与证书相关联的其他权限。Once the module has been signed, one or more principals needs to be created in order to hold the additional permissions that should be associated with the certificate.

如果模块所需的其他数据库级权限:If the module needs additional database-level permissions:

  1. 使用 Transact-SQL CREATE USER [userName] FROM CERTIFICATE [certificateName] 语句创建与该证书关联的数据库用户。Create a database user associated with that certificate using the Transact-SQL CREATE USER [userName] FROM CERTIFICATE [certificateName] statement. 此用户只在数据库中存在,除非也从该相同的证书创建一个登录名不与登录名相关联。This user exists in the database only and is not associated with a login unless a login has also been created from that same certificate.

  2. 向证书用户授予所需的数据库级权限。Grant the certificate user the required database-level permissions.

如果模块所需的其他服务器级权限:If the module needs additional server-level permissions:

  1. 将证书复制到master数据库。Copy the certificate to the master database.

  2. 创建与使用 Transact SQL 该证书关联的登录名CREATE LOGIN [userName] FROM CERTIFICATE [certificateName]语句。Create a login associated with that certificate using the Transact-SQL CREATE LOGIN [userName] FROM CERTIFICATE [certificateName] statement.

  3. 证书登录名授予所需的服务器级别权限。Grant the certificate login the required server-level permissions.

备注

证书不能向用户授予已经使用 DENY 语句撤消的权限。A certificate cannot grant permissions to a user that has had permissions revoked using the DENY statement. DENY 始终优先于 GRANT,以防止调用方继承授予给证书用户的权限。DENY always takes precedence over GRANT, preventing the caller from inheriting permissions granted to the certificate user.

外部资源External Resources

有关更多信息,请参见以下资源。For more information, see the following resources.

资源Resource 描述Description
模块签名SQL Server 联机丛书中Module Signing in SQL Server Books Online 说明模块签名,提供示例方案和到相关 Transact-SQL 主题的链接。Describes module signing, providing a sample scenario and links to the relevant Transact-SQL topics.
使用证书为存储的过程签名SQL Server 联机丛书中Signing Stored Procedures with a Certificate in SQL Server Books Online 提供有关使用证书为存储过程签名的教程。Provides a tutorial for signing a stored procedure with a certificate.

请参阅See also