--------------------------------------------------------对称加密---------------------------
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
对称加密、证书加密
于 2022-04-21 19:29:27 首次发布