DROP DATABASE DEMO;
GO
CREATE DATABASE DEMO;
GO
USE DEMO;
GO
--the database master key is always encrypted (using triple_DES) with user-supplier password
--stored in the sys.symmetric_keys; and at the same time using service master key to encrypt
--the database master key and stored in sys.databases table
CREATE MASTER KEY
ENCRYPTION BY PASSWORD='23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO
CREATE TABLE EMPLOYEE
(
EmployeeID INT PRIMARY KEY ,
FULLNAME VARCHAR(100),
SALARY INT,
ENCRYPTSALARY VARBINARY(4000)
)
USE MASTER;
GO
CREATE LOGIN DAVID01 WITH PASSWORD='DAVID01'
CREATE LOGIN DAVID02 WITH PASSWORD='DAVID02'
USE DEMO;
GO
CREATE USER DAVID01 FOR LOGIN DAVID01
GRANT SELECT,INSERT,UPDATE ON EMPLOYEE TO DAVID01
CREATE USER DAVID02 FOR LOGIN DAVID02
GRANT SELECT,INSERT,UPDATE ON EMPLOYEE TO DAVID02
--EXECUTE AS LOGIN='DAVID01'
--SELECT * FROM EMPLOYEE
--
--REVERT
CREATE CERTIFICATE CER_SK_David01
AUTHORIZATION David01
WITH SUBJECT ='CERTIFICATE FOR ACCESS SYMMETRIC KEYS -FOR USE BY David01'
CREATE CERTIFICATE CER_SK_David02
AUTHORIZATION David02
WITH SUBJECT ='CERTIFICATE FOR ACCESS SYMMETRIC KEYS -FOR USE BY David02'
CREATE SYMMETRIC KEY SK_EMPLOYEES_David01
AUTHORIZATION David01
WITH ALGORITHM=AES_192
ENCRYPTION BY CERTIFICATE CER_SK_David01
CREATE SYMMETRIC KEY SK_EMPLOYEES_David02
AUTHORIZATION David02
WITH ALGORITHM=AES_192
ENCRYPTION BY CERTIFICATE CER_SK_David02
SELECT * FROM SYS.OPENKEYS
--
--ALTER SYMMETRIC KEY SK_EMPLOYEES
--ADD ENCRYPTION BY CERTIFICATE CERT_SK_DAVID
SELECT *-- C.NAME
FROM SYS.KEY_ENCRYPTIONS KE,SYS.CERTIFICATES C,SYS.SYMMETRIC_KEYS SK
WHERE KE.THUMBPRINT=C.THUMBPRINT AND SK.NAME='SK_EMPLOYEES_David01' AND KE.KEY_ID=SK.SYMMETRIC_KEY_ID
exec as login='David01'
OPEN SYMMETRIC KEY SK_EMPLOYEES_DAVID01 DECRYPTION BY CERTIFICATE CER_SK_DAVID01
INSERT INTO EMPLOYEE
VALUES(1,'DAVIDDONG','10000',ENCRYPTBYKEY(KEY_GUID('SK_EMPLOYEES_David01'),'10000'));
INSERT INTO EMPLOYEE
VALUES(2,'DAVIDDONG','12000',ENCRYPTBYKEY(KEY_GUID('SK_EMPLOYEES_David01'),'12000'));
INSERT INTO EMPLOYEE
VALUES(3,'DAVIDDONG','15000',ENCRYPTBYKEY(KEY_GUID('SK_EMPLOYEES_David01'),'15000'));
SELECT EMPLOYEEID,SALARY,CONVERT(VARCHAR(1000),DECRYPTBYKEY(ENCRYPTSALARY)) AS SALARY1 FROM EMPLOYEE
CLOSE ALL SYMMETRIC KEYS
REVERT
SELECT EMPLOYEEID,SALARY,CONVERT(VARCHAR(1000),DECRYPTBYKEY(ENCRYPTSALARY)) AS SALARY1 FROM EMPLOYEE
EXECUTE AS LOGIN='DAVID02'
OPEN SYMMETRIC KEY SK_EMPLOYEES_DAVID02 DECRYPTION BY CERTIFICATE CER_SK_DAVID02
-----
INSERT INTO EMPLOYEE
VALUES(4,'DAVIDDONG','10000',ENCRYPTBYKEY(KEY_GUID('SK_EMPLOYEES_David02'),'10000'));
INSERT INTO EMPLOYEE
VALUES(5,'DAVIDDONG','12000',ENCRYPTBYKEY(KEY_GUID('SK_EMPLOYEES_David02'),'12000'));
INSERT INTO EMPLOYEE
VALUES(6,'DAVIDDONG','15000',ENCRYPTBYKEY(KEY_GUID('SK_EMPLOYEES_David02'),'15000'));
SELECT EMPLOYEEID,SALARY,CONVERT(VARCHAR(1000),DECRYPTBYKEY(ENCRYPTSALARY)) AS SALARY1 FROM EMPLOYEE
CLOSE ALL SYMMETRIC KEYS
REVERT
SELECT EMPLOYEEID,SALARY,CONVERT(VARCHAR(1000),DECRYPTBYKEY(ENCRYPTSALARY)) AS SALARY1 FROM EMPLOYEE
