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