MSSQL加密数据表(sql2005)

--创建数据库主秘钥 USE adb GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P8ssw0rd' GO

--创建存放加密数据的表 CREATE TABLE dbo.SecTable (ID INT IDENTITY PRIMARY KEY, DATA NVARCHAR(100)) GO

--创建数字证书，数字证书的私钥被数据库主秘钥加密 CREATE CERTIFICATE TomCert WITH     SUBJECT = 'TOM CERTIFICATE',     EXPIRY_DATE= '01/01/2009'

--创建堆成秘钥，被用证书对其进行加密 CREATE SYMMETRIC KEY Sym_Tom WITH ALGORITHM = DESX ENCRYPTION BY CERTIFICATE TomCert

--使用证书解开对称秘钥，将数据使用对称秘钥加密后存储与表中，完成后关闭对称秘钥 OPEN SYMMETRIC KEY Sym_Tom DECRYPTION BY CERTIFICATE TomCert

INSERT INTO SecTable (DATA) VALUES(EncryptByKey(Key_GUID('Sym_Tom'), N'TOM1')) CLOSE SYMMETRIC KEY Sym_Tom

--察看被加密的数据内容 SELECT * FROM dbo.SecTable 显示乱码

--使用证书解开堆成秘钥，解密数据表中的数据，完成后关闭对称秘钥 OPEN SYMMETRIC KEY Sym_Tom DECRYPTION BY CERTIFICATE TomCert SELECT ID, CAST(DecryptByKey(Data) AS NVARCHAR) FROM dbo.SecTable CLOSE SYMMETRIC KEY Sym_Tom

转载于:https://www.cnblogs.com/bennylam/archive/2010/01/20/1652422.html