sql server 加密解密字段的解决方案步骤

1.创建数据库主密钥CREATE MASTER KEY ENCRYPTION BY PASSWORD = '********'

2.为用户创建证书和密钥

CREATE CERTIFICATE erpwebscert

AUTHORIZATION erpwebs WITH SUBJECT = 'Cert for erpwebs'

CREATE CERTIFICATE erpsyscert

AUTHORIZATION erpsys WITH SUBJECT = 'Cert for erpsys'

GO

--symmetric keys to be used for encryption

--they are faster than certificates

CREATE SYMMETRIC KEY Key1 AUTHORIZATION erpwebs

WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE erpwebscert

CREATE SYMMETRIC KEY Key2 AUTHORIZATION erpsys

WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE erpsyscert

GO

3.为表创建触发器，在插入时加密

create  trigger [dbo].[EncryptPassword]
on [dbo].[t_excutesql_password]
for insert
as
EXECUTE AS USER='erpsys'

OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE erpsyscert;

update t_excutesql_password set password= EncryptByKey(Key_GUID('Key2'),password)

CLOSE SYMMETRIC KEY Key2

4.创建存储过程在读取时解密

create proc [erpwebs].[SelPass]
@password as nvarchar(50)
as
begin
EXECUTE AS USER='erpsys'OPEN SYMMETRIC KEY Key2
DECRYPTION BY CERTIFICATE erpsyscert
select top 1 cast(decryptByKey(password) AS VARCHAR(256))
from t_excutesql_password
where cast(decryptByKey(password) AS VARCHAR(256))=@password
end
CLOSE SYMMETRIC KEY Key2