MISTECH 技術手抄本

MSSQL Server 2012 對於 table  中的欄位作加密已有支援，作業如下:

-- Create EmployeeReviews table and grant permissions
USE AdventureWorks

IF EXISTS (SELECT * FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
           WHERE t.[Name] = 'EmployeeReview' AND s.[Name] = 'HumanResources')
 DROP TABLE HumanResources.EmployeeReview

CREATE TABLE HumanResources.EmployeeReview
(EmployeeID int NOT NULL,
 ReviewDate datetime DEFAULT GETDATE() NOT NULL,
 Comments varbinary(2000) NOT NULL)

-- Create database master key
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

-- Create certificate
CREATE CERTIFICATE HRAppCert
WITH SUBJECT = 'HR certificate'
GO

-- Create symmetric key
CREATE SYMMETRIC KEY HRKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE HRAppCert
GO

-- Insert encrypted data
    OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert

    INSERT INTO HumanResources.EmployeeReview
    VALUES
    (1, DEFAULT, EncryptByKey(Key_GUID('HRKey'),'Increasing salary to $35,000'))

    CLOSE ALL SYMMETRIC KEYS


-- Read the decrypted data as HRApp
    OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert
    SELECT CONVERT(varchar,DecryptByKey(Comments)) AS Comments FROM HumanResources.EmployeeReview
    CLOSE ALL SYMMETRIC KEYS