如何在视图中屏蔽/加密数据但保持值的唯一性? [英] How do I mask/encrypt data in a view but maintain uniqueness of values?

问题描述

说我在SQL Server表中有一列，其中包含以下条目:

Say I have a column in a SQL Server table with the following entries:

+----+
|id's|
+----+
|489 |
|633 |
|251 |
|633 |
+----+

我想为同事提供一个视图，该视图屏蔽/加密此列，但仍然允许值的唯一性，即:

I would like to provide a view to colleagues that masks/encrypts this column but still allows for uniqueness in values, i.e. :

+----+
|id's|
+----+
|xx  |
|zz  |
|yy  |
|zz  |
+----+

达到此结果的最佳功能是什么?

What is the best function for achieving this result?

推荐答案

一种解决方法是使用哈希函数.我们想要一个无冲突的哈希，这当然是不存在的，但是出于所有实践的目的，大多数密码散列都是无冲突的.所以:

One way to approach this would be to use a hash function. We want a collision-free hash, which of course doesn't exist, but most cryptographic hashes are collision free for all practical purposes. So:

declare @t table (id varchar(30))
insert into @t(id) values
('489'),
('633'),
('251'),
('633')

select id,HASHBYTES('SHA2_256',id)
from @t

哪个会产生:

id    
----- --------------------------------------------------------------------
489   0xE4BE97CE765E6CFCD703884CC31DB7478FA7BEFCA7CF6DC15420BA20ED718ABE
633   0xB6B1B469EA43C90A602E7AE3BDEA001B11F66C17337DEC23DF0B0249542357EE
251   0xC75D3F1F5BCD6914D0331CE5EC17C0DB8F2070A2D4285F8E3FF11C6CA19168FF
633   0xB6B1B469EA43C90A602E7AE3BDEA001B11F66C17337DEC23DF0B0249542357EE

当然，有些人会抱怨说使用掩码值太长了-但是请注意，将这些值截短到更短的值会增加发生冲突的风险.碰撞是否是您唯一可以接受的决定.

Of course, some people are going to complain that these are overly long to use a mask values - but be aware that any truncation of these to shorter values increases the risk of collisions. Whether collisions are acceptable to you only you can decide.

这篇关于如何在视图中屏蔽/加密数据但保持值的唯一性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！