Symmetric encryption, certificate encryption

-------------------------------------------------------- Symmetric encryption ---------------------------
create table Employee_encryption
(
  Eid char(5) primary key not null,
  Did char(4) not null,
  Ename char(10) not null,
  Esex char(2) not null,
  Ebirth date check(Ebirth>='1956-1-1' and Ebirth<='2050-1-1'),
  salary varbinary(1000) not null,
  foreign key(Did) references Department(Did)
)


-- Create a database master key 
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='1209QQ-1'

-- Create a symmetric key using the database master key 
CREATE SYMMETRIC KEY salaryKey
WITH ALGORITHM =AES_256
ENCRYPTION BY PASSWORD = '1209QQ-1'

-- Open symmetric key PWDKEY
OPEN SYMMETRIC KEY salaryKey
DECRYPTION BY PASSWORD = '1209QQ-1'

-- insert data 
insert into Employee_encryption(Eid,Did,Ename,Esex,Ebirth,salary) values
('60301','1001','xxx',' Woman ','2000-05-01',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'3000')),
('60403','2002','xxx',' Woman ','2000-08-23',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'2500')),
('30124','3002',' Song Zhihao ',' male ','1998-09-11',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'3600')),
('21004','3003',' Jialin ',' male ','1993-07-09',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'1369')),
('02305','5003',' Shijia ',' Woman ','2000-01-03',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'2596')),
('04506','4001',' Du Bang Bang ',' male ','1992-08-25',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'3654')),
('05607','1001',' Xiao Lin ',' Woman ','1997-04-29',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'1515')),
('00228','4002',' Song Jia ',' male ','1978-09-30',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'9600')),
('40109','2003',' beautiful ',' Woman ','1999-10-17',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'6800')),
('40518','5002',' Beautiful ',' Woman ','1999-03-08',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'50000'))





SELECT Eid,Did,Ename,Esex,Ebirth,CONVERT(VARCHAR,DECRYPTBYKEY(salary)) as salary  FROM Employee_encryption




CLOSE SYMMETRIC KEY salaryKey






------------------------- Encrypt the database with a certificate --------------------------------

-- Add column 
ALTER TABLE Employee_encryption ADD marriage varbinary(1000)
-- Delete column 
ALTER TABLE Employee_encryption DROP COLUMN marriage


CREATE CERTIFICATE CER_MARRIAGE
WITH SUBJECT='encrypt balance',  -- The subject of the certificate 
START_DATE='01/01/2022',       -- Certificate activation date 
EXPIRY_DATE= '06/12/2022'       -- Certificate expiration date 
--DROP CERTIFICATE CER_BALANCE 

INSERT INTO Employee_encryption values
('90000','5001',' certificate 1',' male ','1988-08-08',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'1000'),ENCRYPTBYCERT(CERT_ID('CER_MARRIAGE'),'1')),
('90001','5001',' certificate 2',' Woman ','2013-01-05',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'5300'),ENCRYPTBYCERT(CERT_ID('CER_MARRIAGE'),'0')),
('90002','5001',' certificate 3',' male ','1976-03-04',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'6300'),ENCRYPTBYCERT(CERT_ID('CER_MARRIAGE'),'1'))

--delete from Employee_encryption where eid='90000'


SELECT Eid,Did,Ename,Esex,Ebirth,
CONVERT(VARCHAR,DECRYPTBYKEY(salary)) as salary , 
CONVERT(VARCHAR,DECRYPTBYCERT(CERT_ID('CER_MARRIAGE'), marriage))
FROM Employee_encryption