MSSQL2012 对用户级别的单元格进行加密

Question

MSSQL2012 对用户级别的单元格进行加密

sql-serverencryptionsql-server-2012encryption-asymmetric

3

有没有一种方法可以根据用户帐户加密单元格值？这样：

只有有效的用户才能解密某些单元格的值？
或者，通过用户身份验证加密数据，只有正确的用户才能解密它？

目前我的解决方案是：我创建非对称密钥，并授予用户使用。但这并不好，因为：

我必须为每个用户或用户组创建一个密钥；
它不能是原子性的；
读取数据的查询必须始终检索密钥名称。

示例：

create database test_for_encrypt
Go
use test_for_encrypt
Go

-- Create Master key and certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPass1234';
CREATE CERTIFICATE MySelfSignedCert
WITH SUBJECT = 'MySelfSignedCert',
EXPIRY_DATE = '07/14/2020';

-- Create Asymmetric keys
CREATE ASYMMETRIC KEY Asym_user_1  WITH ALGORITHM = RSA_2048
CREATE ASYMMETRIC KEY Asym_user_2  WITH ALGORITHM = RSA_2048

-- create table with data and select data
create table tb_encrypt (word nvarchar(100), asymkey nvarchar(100), crypt varbinary(1000))
Go
insert tb_encrypt (word, asymkey) values (N'One', N'Asym_user_1'), (N'Two', N'Asym_user_2')
update tb_encrypt set crypt = ENCRYPTBYASYMKEY(ASYMKEY_ID(asymkey), word)
select word, crypt, convert(nvarchar, DECRYPTBYASYMKEY(ASYMKEY_ID(asymkey), crypt)) as decrypt
from tb_encrypt


-- create new user with grants
create login [user_asym] With password = N'password_1234'
CREATE USER [user_asym] FOR login [user_asym]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [user_asym]
GRANT CONTROL ON ASYMMETRIC KEY::[Asym_user_1] TO [user_asym]
ALTER ROLE [db_datareader] ADD MEMBER [user_asym]

-- After this, Logout and Login with [user_asym]
-- Select data with user [user_asym]
select word, crypt, convert(nvarchar, DECRYPTBYASYMKEY(ASYMKEY_ID(asymkey), crypt)) as decrypt
from tb_encrypt

- Radioleao

2

你使用的是哪个版本的SQL？如果你正在使用2016预览版，你应该看一下这个功能。http://blogs.microsoft.com/next/2015/05/27/always-encrypted-sql-server-2016-includes-new-advances-that-keeps-data-safer/#sm.000n5aecc10nrfkuxaz24umzkk4q0 - Igor

版本2012。我将更新帖子。谢谢。 - Radioleao

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Srinika Pinnaduwage · Answer 1

保留一个包含用户名称以及对应列是否加密的表格。使用一个以用户为参数的表值函数，并根据上述表格确定要加密的列。

类似于以下内容：

Select Case when UserSeeEncrypted = 1 then <EncryptedVal>
Else <Decrypted> ....

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

我添加了以下表格

Create Table #Users (UserName varchar(100), IsEncrypted bit )
Insert into #Users
Select 'PrasadPC\Prasad', 1 -- this is my username for my machine (i used to test)
Union
Select 'user2', 0
Union
Select 'user3', 1

select * from #Users

这段代码是从您的帖子中提取并进行了修改。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPass1234';
CREATE CERTIFICATE MySelfSignedCert
WITH SUBJECT = 'MySelfSignedCert',
EXPIRY_DATE = '07/14/2020';

ASYMMETRIC KEY是任何记录的一种

CREATE ASYMMETRIC KEY [Encrypt_Key_Radioleao]  WITH ALGORITHM = RSA_2048

创建带有数据的表并选择数据（我更改了您的表结构，只是为了保存这个单词，如果您需要保留加密数据，可以稍微更改我的逻辑）

create table #tb_encrypt (word nvarchar(100))
Go
insert #tb_encrypt (word) values (N'One'), (N'Two')

现在查询

select Word,  
Case when exists
(Select UserName from #Users 
where IsEncrypted = 0 and SUSER_SNAME() = UserName ) then Word
Else 
convert(nvarchar(max), ENCRYPTBYASYMKEY(ASYMKEY_ID('Encrypt_Key_Radioleao'),
 Word)) End as En_Decrypt
from #tb_encrypt e

-- change My user to see decrypted data, 
Update #Users set IsEncrypted = 0  where UserName = 'PrasadPC\Prasad'