SQL Server 2016 始终加密 - 在视图中使用始终加密列的比较和计算表达式 [英] SQL Server 2016 always encrypted - comparison and calculated expression using always encrypted column in view

问题描述

我有一个列 "Amount" numeric(18,2) 我使用 SSMS v17 的加密列向导对其进行了加密.列数据现已加密.

I have a column "Amount" numeric(18,2) that I have made encrypted by using Encrypt Column wizard of SSMS v17. The column data is now encrypted.

但是，我有一个使用以下内容的视图:

However, I have a view that uses something like:

create SampleView 
as
    Select 
        *, Amount * Rate as TotalAmount 
    From 
        SampleTable 
    Where 
        Amount > 0
go

Rate 列的类型为 numeric(18,8).

我无法创建此视图.它给出了数据类型不兼容的错误，因为一列是加密的，另一边是明文.从我尝试过的各种排列中，我看到 > 0 的 Where 子句导致了问题，并且选择列表中的 Amount*Rate 也不起作用.

I am unable to create this view. It gives data type incompatible error as one column is encrypted and the other side is plaintext. From various permutation I have tried, I see that the Where clause with > 0 is causing problem and also Amount*Rate in Select list is not working.

与Amount*Rate相关的错误是(我注释了Where子句)

The error related to Amount*Rate is (I commented Where clause)

操作数类型冲突:numeric(18,2) 加密与 (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'SampleDB_CEK', column_encryption_key_database_name = 'incompatible with numeric_p>

Operand type clash: numeric(18,2) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'SampleDB_CEK', column_encryption_key_database_name = 'SampleDB') is incompatible with numeric

与Where Amount>0相关的错误是(我在Select子句中注释了Amount*Rate)

The error related to Where Amount>0 is (I commented Amount*Rate in Select clause)

使用(encryption_type = 'DETERMINISTIC'、encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256'、column_encryption_key_name = 'SampleDB_CEK'、column_encryption_key_database_name = 'in the tinyint operator.')加密的数据类型 numeric(18,2) 大于tinyint operator/p>

The data types numeric(18,2) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'SampleDB_CEK', column_encryption_key_database_name = 'SampleDB') and tinyint are incompatible in the greater than operator.

我尝试了这些，但效果不佳:

I tried these, but it didn't work as well:

Where Amount > cast(0 as numeric(18,2)
Select Amount * cast(Rate as numeric(18,2)

我们不能声明变量，因为它是视图.并且这个视图在许多存储过程中得到使用.

We cannot declare variables as it is view. And this view is getting used in many stored procedures.

任何想法表示赞赏.

推荐答案

加密列中不允许进行比较和数学运算.目前对加密列唯一可能的操作是相等.bastos 的答案不起作用，因为 SQL Server 没有密钥.

Comparison and mathematical operations are not allowed in encrypted columns. Currently the only operation possible on encrypted columns is equality. The answer by bastos would not work because SQL Server does not have the key.

您可能必须在客户端应用程序中实现此逻辑.

You might have to implement this logic in the client application.

来自官方文档

确定性加密始终为任何给定的纯文本值.使用确定性加密允许点查找、等式连接、分组和加密的索引列.但是，也可能让未经授权的用户猜到通过检查模式中的加密值的信息加密列，特别是如果有一小组可能的加密值，例如 True/False 或 North/South/East/West 区域.确定性加密必须使用带有 binary2 的列排序规则字符列的排序顺序.

Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.

随机加密使用一种方法以一种不太可预测的方式加密数据.随机加密更安全，但会阻止搜索、分组、索引和加入加密列.

Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.

这篇关于SQL Server 2016 始终加密 - 在视图中使用始终加密列的比较和计算表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！