学习简记：SQL Server Encryption and Certificate

使用证书加密:

-- Create table

CREATE TABLE TestTable_two(ID int IDENTITY(1,1),Data varbinary(8000));

GO

 

SELECT * FROM TestTable_two

 

-- Create Database Master Key     

CREATE MASTER KEY ENCRYPTION BY 

PASSWORD = 'SQLAuthority' 

 

-- Create first Certificate "Cert_Demo1"

CREATE CERTIFICATE Cert_Demo1 

WITH 

SUBJECT=N'cert1 encryption by database master key',

START_DATE='2009-01-01',

EXPIRY_DATE='2009-12-31'

GO

-- Create second Certificate "Cert_Demo2"

CREATE CERTIFICATE Cert_Demo2

ENCRYPTION BY PASSWORD='SQLAuthority'

WITH 

SUBJECT=N'cert1 encrption by password',

START_DATE='2009-01-01',

EXPIRY_DATE='2009-12-31'

GO

 

INSERT TestTable_two(data)

  SELECT ENCRYPTBYCERT(CERT_ID('Cert_Demo1'), 'Certificate_Cert_Demo1_Content');

 

INSERT TestTable_two(data)

  SELECT ENCRYPTBYCERT(CERT_ID('Cert_Demo2'), 'Certificate_Cert_Demo2_Content');

 

 

SELECT * FROM TestTable_two

 

 

ID          Data
----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           0xFCCE968CE94C0DA647E1C06137C48A7AC59C3E1CFBDE623CD7276F7021AEE04B2D6D8A96B431EE889712C07100DC80D17AFFE5E43C35066F69212AF9A2338DA561AF5F5CA5B2AE1DE53EC18090E87149B87DCB26675C35941C9DAAC46D2A3EACDBA5818EDE89C1F0DE4FFB392F7D969129380AEED0FF6CA4BBFBA0082F1A5D00
2           0x76CCE6AEA6C941E02C7638471B1FF8255F77AB359823A887D833BF0AF2D0CD7431AD0DD0BE0B1C209A44AB65029D1654E9B59A94E7FC5D837423888045B6CED961AC0E9425754F12C2A46789C63CAF41406CD43937D90DF4C14611F1AED64694EBE1525FAA77F1A9B31099449E59C6C6CB0117E8835C72FD8F06028FE0808552

(2 row(s) affected)

 

SELECT CONVERT(VARCHAR(50),DECRYPTBYCERT(CERT_ID('Cert_Demo1'),data))

FROM TestTable_two

 

SELECT CONVERT(VARCHAR(50),DECRYPTBYCERT(CERT_ID('Cert_Demo2'),data,N'SQLAuthority'))

FROM TestTable_two

 

 

--------------------------------------------------
Certificate_Cert_Demo1_Content
NULL

(2 row(s) affected)

--------------------------------------------------
NULL
Certificate_Cert_Demo2_Content

(2 row(s) affected)

 

DROP CERTIFICATE Cert_Demo1

DROP CERTIFICATE Cert_Demo2

DROP MASTER KEY

DROP TABLE TestTable_two

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12476590/viewspace-536431/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12476590/viewspace-536431/