encryption_hr.sql

< SQL Server2005程序设计>

CREATE LOGIN HR_Login WITH PASSWORD='SomeComplexPassword'
GO
CREATE DATABASE ExampleDB
GO
use ExampleDB
GO
CREATE USER HR_User FOR LOGIN HR_Login
GO

--Create the database master key for the ExampleDB database
CREATE MASTER KEY ENCRYPTION BY PASSWORD='AComplexPassword'
GO

--Create the table that will store sensitive information
--Notice we use a varbinary for our salary information
--This is because the ciphertext (encrypted data) is binary
CREATE TABLE SalaryInfo
(employee nvarchar(50),
department nvarchar(50),
salary varbinary(60))
GO

--Give access to this table to HR_User so they can add data
GRANT SELECT,INSERT TO HR_User
GO

--Create a Symmetric Key
--Encrypt the key with a password 
--Give access to the key to HR_User
CREATE SYMMETRIC KEY HR_User_Key
AUTHORIZATION HR_User
WITH ALGORITHM=TRIPLE_DES
ENCRYPTION BY PASSWORD='CompensationPlansRule'
GO

--Now, let's login as HR_User and encrypt some data
EXECUTE AS LOGIN='HR_Login'
GO

--First, we need to open the key that will be used to encrypt data
--Notice we have to pass the password for the key
OPEN SYMMETRIC KEY HR_User_Key DECRYPTION BY PASSWORD='CompensationPlansRule'
GO

--This system view shows open keys that can be used for encryption
select * from sys.openkeys

--Insert sensitive data into the table
--encryptByKey takes the GUID of the key and the text of the data
--Since remembering GUIDs is not easy, Key_GUID is a function
--that does the lookup for us
INSERT INTO SalaryInfo VALUES
('Bryan','Sales',encryptByKey(Key_GUID('HR_User_Key'),'125000'))
INSERT INTO SalaryInfo VALUES
('Tammie','Sales',encryptByKey(Key_GUID('HR_User_Key'),'122000'))
INSERT INTO SalaryInfo VALUES
('Frank','Development',encryptByKey(Key_GUID('HR_User_Key'),'97500'))
INSERT INTO SalaryInfo VALUES
('Fran','Marketing',encryptByKey(Key_GUID('HR_User_Key'),'99500'))

--When we are done, always close all keys
CLOSE ALL SYMMETRIC KEYS
GO

--View the table as it lives in the database, notice the binary
select * from SalaryInfo

--Now, let's decrypt and view the contents
--We use decryptByKey and pass the column name
--We don't have to specify a key GUID because SQL will look
--at all your open keys and use the appropriate one automatically
OPEN SYMMETRIC KEY HR_User_Key DECRYPTION BY PASSWORD='CompensationPlansRule'
GO

SELECT employee,department,
CONVERT(varchar,decryptByKey(salary)) 
FROM SalaryInfo
GO

CLOSE ALL SYMMETRIC KEYS
GO

--Revert back to sysadmin
REVERT
GO

--When encrypting by password, need to know the password 
--and pass it everytime you encrypt something.
--Alternatively you can create a certificate and give access to 
--the HR User.  With this, the user doesn't have to provide a password
--and you can easily revoke access to that encrypted data by simply
--removing the certificate for that user
CREATE CERTIFICATE HRCert1
AUTHORIZATION HR_User
WITH SUBJECT='Certificate used by the Human Resources person'

--Open the key so we can modify it
OPEN SYMMETRIC KEY HR_User_Key DECRYPTION BY PASSWORD='CompensationPlansRule'
GO

--We can not remove the password because we would leave the key
--exposed without encryption so we need to add the certificate first
ALTER SYMMETRIC KEY HR_User_Key
ADD ENCRYPTION BY CERTIFICATE HRCert1
GO

--Now we can remove the password encryption from the key 
ALTER SYMMETRIC KEY HR_User_Key
DROP ENCRYPTION BY PASSWORD= 'CompensationPlansRule' 
GO
CLOSE ALL SYMMETRIC KEYS
GO

--Now change context to HR_Login to test our changes
EXECUTE AS LOGIN='HR_Login'
GO

--Notice, we opened the key without a password!
--This is because we created the certificate and gave authorization
--on it explicitly to HR_User
OPEN SYMMETRIC KEY HR_User_Key DECRYPTION BY CERTIFICATE HRCert1
GO

SELECT employee,department,
CONVERT(varchar,decryptByKey(salary)) 
FROM SalaryInfo
GO