CREATE SYMMETRIC KEY DB_KEY1 WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = 'rainmaker'
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Employee]') AND type IN (N'U'))
BEGIN
DROP TABLE [Employee]
END
GO
CREATE TABLE [Employee](
[EmployeeID] [int] NOT NULL PRIMARY KEY,
[FirstName] VARCHAR(250) NOT NULL,
[LastName] VARCHAR(250) NOT NULL,
[BirthDay] DATE,
[Salary] DECIMAL,
[EN_BirthDay] VARBINARY(8000), --放BirthDay加密的資料
[EN_Salary] VARBINARY(8000) --放Salary加密的資料
)
GO
CREATE TRIGGER TR_Employee
ON [Employee]
FOR INSERT, UPDATE
AS
BEGIN
--取得金鑰名稱
DECLARE @KeyGUID AS UNIQUEIDENTIFIER
SELECT @KeyGUID = KEY_GUID('DB_KEY1')
--將資料放到加密欄位之中,並更新原本的欄位為其他值
UPDATE [Employee]
SET
[EN_BirthDay]= ENCRYPTBYKEY(@KeyGUID, CAST(i.BirthDay AS varbinary) )
, BirthDay = '1970/10/10'
, [EN_Salary]= ENCRYPTBYKEY(@KeyGUID, CAST(i.Salary AS varbinary) )
, Salary = 0
FROM inserted i
JOIN [Employee] ON(i.EmployeeID=[Employee].EmployeeID)
END;
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[vwEN_Employee]'))
DROP VIEW [vwEN_Employee]
GO
CREATE VIEW [vwEN_Employee]
AS
SELECT [EmployeeID], [FirstName], [LastName]
, [BirthDay] = CAST(DECRYPTBYKEY([EN_BirthDay]) AS DATE) --將資料解密轉成DATE
, [Salary] = CAST(DECRYPTBYKEY([EN_Salary]) AS DECIMAL) --將資料解密轉成DECIMAL
FROM [Employee];
OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker';
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [BirthDay], [Salary])
VALUES (1, N'Eric', N'Lin', '1965/4/3', 47000 );
SELECT * FROM Employee;
SELECT * FROM vwEN_Employee;
CLOSE SYMMETRIC KEY DB_KEY1;