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

EncryptData  

創作者介紹
MIS

MISTECH 技術手抄本

MIS 發表在 痞客邦 PIXNET 留言(0) 人氣()