有没有一种方法可以根据用户帐户加密单元格值?这样:
- 只有有效的用户才能解密某些单元格的值?
- 或者,通过用户身份验证加密数据,只有正确的用户才能解密它?
目前我的解决方案是:我创建非对称密钥,并授予用户使用。但这并不好,因为:
- 我必须为每个用户或用户组创建一个密钥;
- 它不能是原子性的;
- 读取数据的查询必须始终检索密钥名称。
示例:
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