T-SQL/CLR函数用于确定性加密 [英] T-SQL/CLR function for deterministic encryption

问题描述

我有一个带有用户代理字符串表的表，其结构如下:

  UserAgentStringID INTUserAgentStringValue VARBINARY(8000) 

[UserAgentStringValue] 字段已使用对称密钥加密.表结构的先前版本是:

  UserAgentStringID INTUserAgentStringValue NVARCHAR(4000)UserAgentStringHASH BINARY(32) 

，为了优化搜索者，我在 [UserAgentStringHASH] 列上建立了索引.

使用新格式时，该索引效率不高，因为

在我们的环境中，我们正在使用Database Mater Key->证书->对称密钥层次结构.只有DBA 知道 DMK密码，才能访问证书和对称密钥.有些开发人员可以使用普通的T-SQL加密/解密数据，而其他开发人员则不能.

请注意，使用始终加密，您可以进行角色分离-使用数据的人员无法访问密钥，而有权访问密钥的人员无法访问数据.就我们而言，我们想保护我们的数据不受外界的侵害，并拥有其他内部授予/记录数据访问权限的技术.

有权访问加密数据的开发人员

可以访问受保护数据的开发人员可以对其进行加密和解密.他们无权访问对称密钥值.如果可以访问对称密钥值，则ze能够解密没有用于保护对称密钥的证书的数据事件.基本上，只有sys.admins和db_owners可以访问对称密钥值.

如何哈希

我们需要一个哈希来进行快速搜索，但是我们不能使用未经加密的盐.从安全的角度来看，没有盐的哈希就像纯文本.因此，我们决定使用对称密钥值作为盐.它是这样的:

  SELECT @SymmetricKeyValue = CONVERT(VARCHAR(128)，DECRYPTBYCERT(C. [certificate_id]，KE.[crypt_property])，1)从[sys].[symmetric_keys] SKINNER JOIN [sys].[key_encryptions] KE开启SK.[symmetric_key_id] = KE.[key_id]内部联接[sys].[证书] CON KE.[缩略图] = C. [缩略图]哪里SK.[name] = @SymmetricKeyName; 

然后将值连接到您的电子邮件地址，然后计算哈希值.这对我们有好处，因为我们将哈希绑定到安全性层次结构.每条记录的盐都不相同，它是相同的-但是，如果知道对称密钥值，则ze可以直接解密数据.

注意事项

您需要使用 EXECUTE AS OWNER 子句创建通过哈希值或计算哈希搜索的例程(存储过程，触发器).否则，开发人员将无法执行它们，因为只有sys.admins和db_owners可以访问对称密钥值.

I have a table with User Agents Strings table with the following structure:

UserAgentStringID INT
UserAgentStringValue VARBINARY(8000)

The [UserAgentStringValue] field is encrypted with symmetric key. The previous version of the table structure was:

UserAgentStringID INT
UserAgentStringValue NVARCHAR(4000)
UserAgentStringHASH BINARY(32)

and I have index on the [UserAgentStringHASH] column in order to optimized searchers.

With the new format, such index is not efficient as the ENCRYPTION function uses InitializationVector in order to generate random values each time the encryption function is called with the same input:

Initialization vectors are used to initialize the block algorithm. It is not intended to be a secret, but must be unique for every call to the encryption function in order to avoid revealing patterns.

So, I can create index on my encrypted field, but if I try to search by encrypted value, I will not be able to find anything.

I do not want to use HASH because using hash function is not secure technique. If someone have my table data and table with all or huge amount of user agents, he/she will be able to perform an join by hash and reveal my data.

In SQL Server 2016 SP standard edition we have Always Encrypted which allows using Deterministic Encryption for column value - this means equal comparisons are working and indexes can be created.

I am looking for a way to optimize the search by other technique or a way to implement deterministic encryption using CLR for example?

Knowing there is no work around is OK for me, too. I guess I will pay the data protection with performance.

解决方案

I am posting a workaround of this - it's not the ideal solution, but it is compromise between speed and security.

The details

• a columns must be encrypted (lets say an email address)
• fast search must be implemented (let say the email is used for login and we need to locate the record as fast as possible)
• we are not able to use Always Encrypted deterministic encryption (due to various reasons)
• we don't want to use hash function with salt - if one has the salt for each user, ze might be able to read the hashes using large sample database

The security hierarchy

There are various ways of implementing the security hierarchy. The following schema from the MSDN describes it very well.

In our environment we are using the Database Mater Key -> Certificate -> Symmetric Key hierarchy. Only DBAs know the DMK password, have access to certificate and symmetric keys. Some developers can do encrypt/decrypt data (using plain T-SQL) and other do not.

Note, using Always Encrypted you can have role separation - the people who works with the data have not access to the keys, and the people who have access to the keys, do not have access to the data. In our case, we want to protect our data from outsiders and have other techniques for granting/logging data access internally.

Developers with access to encrypted data

The developers who can access the protected data are able to encrypt and decrypt it. They have not access to the symmetric key values. If one have access to the symmetric key values, ze is able to decrypt the data event not having the certifications used for protecting the symmetric keys. Basically, only sys.admins and db_owners have access to the symmetric keys values.

How to hash

We need a hash to get fast searches, but we cannot use a salt which is not encrypted. And hash without a salt is like plain text from security perspective. So, we've decided to use use the symmetric key value as salt. It is get like this:

SELECT @SymmetricKeyValue = CONVERT(VARCHAR(128), DECRYPTBYCERT(C.[certificate_id], KE.[crypt_property]), 1)
FROM [sys].[symmetric_keys] SK
INNER JOIN [sys].[key_encryptions] KE
    ON SK.[symmetric_key_id] = KE.[key_id] 
INNER JOIN [sys].[certificates] C
    ON KE.[thumbprint] = C.[thumbprint]
WHERE SK.[name] = @SymmetricKeyName;

And the value is concatenated to your email address and then the hash is calculated. It is good for us, because we are binding the hash to the security hierarchy. And it is not a different salt for each record, it is the same - but if one knows the symmetric key value, ze is able to decrypt the data directly.

Considerations

You need to create the routines (stored procedures, triggers) which are searching by hash values or computing hashes using the EXECUTE AS OWNER clause. Otherwise, developers will not be able to execute them as only sys.admins and db_owners have access to the symmetric key value.

这篇关于T-SQL/CLR函数用于确定性加密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！