1:建立数据库主密钥:
USE ADVENTUREWORKS
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@SSW0RD';
GO
2:其次创建存放加密数据的表:
CREATE TABLE TABLENAME(DATA VARCHAR(20))
GO
3:创建数字证书,数字证书的私钥被数据库主密钥加密:
CREATE CERTIFICATE TESTCERTIFICATE
WITH SUBJECT = 'ADVENTUREWORKS TEST CERTIFICATE',
EXPIRY_DATE = '10/31/2012';
4:创建对称密钥,使用证书对其进行加密:
CREATE SYMMETRIC KEY TESTSYMMETRICKEY WITH ALGORITHM = DESX ENCRYPTION BY CERTIFICATE TESTCERTIFICATE;
5:插入数据:
OPEN SYMMETRIC KEY TESTSYMMETRICKEY DECRYPTION BY CERTIFICATE TESTCERTIFICATE;
INSERT INTO TABLENAME VALUES (ENCRYPTBYKEY(KEY_GUID('TESTSYMMETRICKEY'),'WISON' )
CLOSE SYMMETRIC KEY TESTSYMMETRICKEY;
6:查询测试:
直接查询SELECT * FROM TABLENAME;发现数据是加密的,无法查看.
使用下面的SQL语句查看数据:
OPEN SYMMETRIC KEY TESTSYMMETRICKEY DECRYPTION BY CERTIFICATE TESTCERTIFICATE;
SELECT CAST(DECRYPTBYKEY(DATA) AS NVARCHAR) FROM TABLENAME;
CLOSE SYMMETRIC KEY TESTSYMMETRICKEY;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16754036/viewspace-691647/ ，如需转载，请注明出处，否则将追究法律责任。
