6,129
社区成员
发帖
与我相关
我的任务
分享
if object_id('t_encryption','u') is not null drop table t_encryption
go
create table t_Encryption
(
DCNO int identity primary key,
DCNAME varchar(50) ,
SalaryInfo varbinary(max)
)
go
---------------------------------------------------创建加密证书
create certificate cert_SalaryInfo
Encryption by password='P@ssw0rd'
with
subject=N'Salary Information Encryption',
expiry_date='9999-12-31'
-----删除证书
----- drop certificate cert_SalaryInfo
go
---------------------------------------------------创建对称加密
create symmetric key sym_SalaryInfo
with
algorithm=DES --使用DES 加密算法(*不知道原理)
encryption by certificate cert_SalaryInfo
----删除对称加密
----drop symmetric key sym_SalaryInfo
go
---------------------------------------------------打开对称加密(插入数据前要打开对称加密,否则将无效)
open symmetric key sym_SalaryInfo
decryption by certificate cert_SalaryInfo
with password =N'P@ssw0rd'
go
---------------------------------------------------插入加密数据
insert into t_Encryption(DCNAME,SalaryInfo)
select 'andy',ENCRYPTBYKEY(KEY_GUID('sym_SalaryInfo'),N'1000.00') union all
select 'job',ENCRYPTBYKEY(KEY_GUID('sym_SalaryInfo'),N'2000.03')
go
---------------------------------------------------关闭密钥
close symmetric key sym_SalaryInfo
go
---------------------------------------------------查询表数据
select * from t_Encryption
/*
DCNO DCNAME SalaryInfo
----------- -------------------------------------------------- -----------------------------------------------------------------------------------------------------------
1 andy 0x00A4EFE20ABB16458517D289B626ECB20100000060F12ABBD792B7C542DC8AAEC496A3796A9C0B7D454F56CAC6AA60763953B631
2 job 0x00A4EFE20ABB16458517D289B626ECB2010000003CAAC262CE95588561CA30139608AD380608F12F70E7271006702FA037D5AE1A
(2 row(s) affected)
*/
go
---------------------------------------------------使用对称加密直接查看(不开启对称加密)
select
DCNO,
DCNAME,
convert(nvarchar(500),DECRYPTBYKEY(SalaryInfo)) as SalaryInfo
from t_Encryption
/*
DCNO DCNAME
----------- -------------------------------------------------- -------------------
1 andy NULL
2 job NULL
(2 row(s) affected)
*/
go
--------------------------------------------------查询数据前要打开加密
open symmetric key sym_SalaryInfo
decryption by certificate cert_SalaryInfo
with password =N'P@ssw0rd'
go
select
DCNO,
DCNAME,
convert(nvarchar(500),DECRYPTBYKEY(SalaryInfo)) as SalaryInfo
from t_Encryption
/*
DCNO DCNAME SalaryInfo
----------- -------------------------------------------------- ------------------
1 andy 1000.00
2 job 2000.03
(2 row(s) affected)
*/
go
--------------------------------------------------更新DCNO=1的salaryinfo 变为1500
update t_Encryption set SalaryInfo=ENCRYPTBYKEY(KEY_GUID('sym_SalaryInfo'),N'1500')
where DCNO=1
go
--------------------------------------------------再次插入一条记录
insert into t_Encryption(DCNAME,SalaryInfo)
select 'Ivan',ENCRYPTBYKEY(KEY_GUID('sym_SalaryInfo'),N'3000')
go
select
DCNO,
DCNAME,
convert(nvarchar(500),DECRYPTBYKEY(SalaryInfo)) as SalaryInfo
from t_Encryption
/*
DCNO DCNAME SalaryInfo
----------- -------------------------------------------------- ---------------------
1 andy 1500
2 job 2000.03
3 Ivan 3000
(3 row(s) affected)
*/
go
--------------------------------------------------使用完成后一定要记住关闭掉
close symmetric key sym_SalaryInfo
go
--------------------------------------------------关闭后查询(在没有开锁的情况下)
select
DCNO,
DCNAME,
SalaryInfo as SalaryInfo_,
convert(nvarchar(500),DECRYPTBYKEY(SalaryInfo)) as SalaryInfo
from t_Encryption
/*
DCNO DCNAME SalaryInfo_ SalaryInfo
1 andy 0x00A4EFE20ABB16458517D289B626ECB201000000DF23BA3059585CF3C6F0D3C3CA7EB685B201DAEAEF4351570FEE080D5927DD46 NULL
2 job 0x00A4EFE20ABB16458517D289B626ECB2010000003CAAC262CE95588561CA30139608AD380608F12F70E7271006702FA037D5AE1A NULL
4 Ivan 0x00A4EFE20ABB16458517D289B626ECB201000000D39FA6B1F9090FFC9488DD8947BD068D9B995776FC1A6E018DED081EA3150D5C NULL
*/
CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
GO
INSERT tb(data)
SELECT EncryptByPassPhrase(N'这是密码',N'这是要加密的内容2');
--解密
select * from tb
SELECT CONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'这是密码,用来加密的',data)) ,
convert(nvarchar(50),DECRYPTBYPASSPHRASE(N'这是密码',data))
FROM tb
GO
DROP TABLE tb