查看数据库加密数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

--1 创建数据库主密钥 use test GO CREATE MASTER KEY ENCRYPTION BY PASSWORD='password123' GO --2创建存放加密数据的表 CREATE TABLE dbo.SecTable (    ID INT IDENTITY PRIMARY KEY,    DATA NVARCHAR(100) ) GO --3 创建数字证书，数字证书的私钥被数据库主密钥加密 CREATE CERTIFICATE TomCert WITH   SUBJECT = 'Tom Certificate' ,   START_DATE = '2010-04-01' ,   EXPIRY_DATE = '2010-12-01' GO --4 创建对称密钥，使用证书对其进行加密 CREATE SYMMETRIC KEY Sym_Tom WITH ALGORITHM=DESX ENCRYPTION BY CERTIFICATE TomCert --5使用证书解开对称密钥，将数据使用对称密钥后存储于表中，然后关闭对称密钥 OPEN SYMMETRIC KEY Sym_Tom DECRYPTION BY CERTIFICATE TomCert INSERT INTO SecTable(DATA) VALUES(EncryptByKey(Key_GUID('Sym_Tom'),N'TOM1')) INSERT INTO SecTable(DATA) VALUES(EncryptByKey(Key_GUID('Sym_Tom'),N'TOM2')) INSERT INTO SecTable(DATA) VALUES(EncryptByKey(Key_GUID('Sym_Tom'),N'TOM3')) CLOSE SYMMETRIC KEY Sym_Tom --6 查看被加密的数据内容 select * from dbo.SecTable --7 使用证书解开对称密钥，解密数据表中的数据，完成后关闭对称密钥 OPEN SYMMETRIC KEY Sym_Tom DECRYPTION BY CERTIFICATE TomCert SELECT ID,CAST(DecryptByKey(DATA) AS NVARCHAR ) FROM dbo.SecTable   DROP SYMMETRIC KEY Sym_Tom DROP CERTIFICATE TomCert