对SQL Server数据库记录或插入/更新操作进行数字签名

Is there a way to somehow digitally sign the current state of a database record or a particular field with a particular user's certificate or AD credential, etc? I need to implement some kind of record level approval where the correctness and validity of a particular field or fields or a row is signed by a username/password combination or with some certificate. Data should be always visible in the record, all I need is to be able to tell: who and when approved the data. I do not need to prevent a change in state (lock down), only to verify that the current state was signed.

有没有办法以某种方式用特定用户的证书或AD凭证等以数字方式对数据库记录或特定字段的当前状态进行签名？我需要实现某种记录级别的批准，其中特定字段或字段或行的正确性和有效性由用户名/密码组合或某些证书签名。数据应始终在记录中可见，我所需要的只是能够告诉：谁和何时批准数据。我不需要阻止状态更改（锁定），只需要验证当前状态是否已签名。

2 个解决方案

#1

You can use the SIGNBYASYMKEY-function to create a signature using an asymmetric key:

您可以使用SIGNBYASYMKEY函数使用非对称密钥创建签名：

DECLARE @id = 2; -- The primary key of the row we need to sign
DECLARE @keyid = AsymKey_Id( 'KeyID_for_the_User' );
DECLARE @keypw = N'pGFD4bb925DGvbd2439587y'; 
                 -- Password the user entered to sign the values
DECLARE @clear_text_data nvarchar(max);
set @clear_text_data = function_that_returns_a_concatenation_of_the_columns(@id)

UPDATE table
SET signature = SignByAsymKey( @keyid, @clear_text_data, @keypw )   
WHERE id = @id;

#2

[Disclosure: I work for CoSign]

[披露：我为CoSign工作]

How standard a digital signature do you want? Do you want an X.509 signature, the "real thing?"

您想要多么标准的数字签名？你想要一个X.509签名，“真实的东西？”

If so:

如果是这样：

serialize (flatten) the data fields in a specific, documented and repeatable way into a buffer.
将数据字段以特定的，记录的和可重复的方式序列化（展平）到缓冲区中。
use a signing library to sign the buffer.
使用签名库对缓冲区进行签名。
The resulting digital signature should be a "detached digital signature." The recommended standard for detached signatures is the .p7b file type–-a PKCS#7 digital signature structure without the data.
产生的数字签名应该是“分离的数字签名”。分离签名的推荐标准是.p7b文件类型 - 没有数据的PKCS＃7数字签名结构。
Store the detached digital signature as an additional field in the dbms record.
将分离的数字签名存储为dbms记录中的附加字段。

You will also need to provide a signature verification service to assure the person (or machine) that the data was not changed since it was signed, and that the signer's identity is trusted:

您还需要提供签名验证服务，以确保人员（或机器）自签署以来数据未被更改，并且签名者的身份是可信的：

Using the same algorithm as above, serialize the data into a buffer
使用与上述相同的算法，将数据序列化为缓冲区
Call the signing library's verification method
调用签名库的验证方法
Present the results to the user.
将结果呈现给用户。

If you want to improve the audit capabilities of your system, then you should enable the serialized data and the external signature to be downloaded. This will enable an audit check: using third party software to duplicate the effect of your verification software.

如果要提高系统的审计功能，则应启用序列化数据和外部签名的下载。这将启用审核检查：使用第三方软件复制验证软件的效果。

Ideally, the serialized version of the data should be easily checked that it matches the plaintext data. Eg serialize into a json, csv, or xml utf-8 format rather than a binary format. Any bundle of bits can be signed, but a plain text format will be much easier to audit.

理想情况下，应轻松检查数据的序列化版本是否与明文数据匹配。例如，序列化为json，csv或xml utf-8格式而不是二进制格式。可以对任何位包进行签名，但是纯文本格式将更容易审计。

If you also want the signing library to handle all of the security issues of properly protecting signer's private keys, check out the CoSign SAPI APIs.

如果您还希望签名库处理正确保护签名者私钥的所有安全问题，请查看CoSign SAPI API。

Remember that storing signers' private keys in a file on the filesystem means that the overall application is not providing proof of signature. The only way to provide real proof of signature is to use a Secure Signature Creation Device (SSCD). An easy way to do that is to use a CoSign Central signing appliance or similar. The main alternative is smart cards which can be quite difficult to implement in a client/server/web browser environment.

请记住，将签名者的私钥存储在文件系统上的文件中意味着整个应用程序不提供签名证明。提供真实签名证明的唯一方法是使用安全签名创建设备（SSCD）。一种简单的方法是使用CoSign Central签名设备或类似设备。主要的替代方案是智能卡，在客户端/服务器/ Web浏览器环境中很难实现。

#1