对称加密、证书加密

--------------------------------------------------------对称加密---------------------------
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 MASTER KEY ENCRYPTION BY PASSWORD ='1209QQ-1'

--使用数据库主密钥创建一个对称密钥
CREATE SYMMETRIC KEY salaryKey
WITH ALGORITHM =AES_256
ENCRYPTION BY PASSWORD = '1209QQ-1'

--打开对称密钥PWDKEY
OPEN SYMMETRIC KEY salaryKey
DECRYPTION BY PASSWORD = '1209QQ-1'

--插入数据
insert into Employee_encryption(Eid,Did,Ename,Esex,Ebirth,salary) values
('60301','1001','xxx','女','2000-05-01',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'3000')),
('60403','2002','xxx','女','2000-08-23',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'2500')),
('30124','3002','宋志豪','男','1998-09-11',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'3600')),
('21004','3003','佳霖','男','1993-07-09',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'1369')),
('02305','5003','世佳','女','2000-01-03',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'2596')),
('04506','4001','度棒棒','男','1992-08-25',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'3654')),
('05607','1001','小霖','女','1997-04-29',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'1515')),
('00228','4002','宋佳','男','1978-09-30',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'9600')),
('40109','2003','美丽','女','1999-10-17',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'6800')),
('40518','5002','俊美','女','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






-------------------------使用证书对数据库加密--------------------------------

--增加列
ALTER TABLE Employee_encryption ADD marriage varbinary(1000)
--删除列
ALTER TABLE Employee_encryption DROP COLUMN marriage


CREATE CERTIFICATE CER_MARRIAGE
WITH SUBJECT='encrypt balance',  --证书的主题
START_DATE='01/01/2022',       --证书启用日期
EXPIRY_DATE= '06/12/2022'       --证书到期日期
--DROP CERTIFICATE CER_BALANCE 

INSERT INTO Employee_encryption values
('90000','5001','证书1','男','1988-08-08',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'1000'),ENCRYPTBYCERT(CERT_ID('CER_MARRIAGE'),'1')),
('90001','5001','证书2','女','2013-01-05',ENCRYPTBYKEY(KEY_GUID('salaryKey'),'5300'),ENCRYPTBYCERT(CERT_ID('CER_MARRIAGE'),'0')),
('90002','5001','证书3','男','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