防止开发人员获取到敏感数据(SQL Server的数据加密简介)

时间:2021-10-21 16:21:49

背景

有时候,我们还真的会碰到这样的需求:防止开发人员获取到敏感数据。也许你觉得很简单,把开发和运营分开不就可以了吗?是的,如果公司有专门的运营团队的话,但对于很多小公司来说,几个人的开发团队就兼顾了需求分析、设计、开发、测试、调试、部署和运营了,数据库密码知道,程序代码全有,怎么办?——必须对数据库里的数据进行加密,这是唯一的办法。

也许你还是不明白,什么东西需要瞒着我们了不起的程序员,好吧,我直说了:工资!假如你的公司让你做一个工资系统,你会不会有这方面的顾虑,一旦工资信息被公开,后果必定是很严重的,也许老板对你很信任,认为让你知道没什么问题,但其他开发人员呢?后来接手你的工作的人呢?所以必须考虑这个问题。而且,还外带一个需求:员工自己可以用自己的“薪资查看密码”来查看自己的工资(只能看自己的),每个人自己的“薪资查看密码”都不一样。另外不需要描述的隐藏需求还有:将来必定是要对薪资做统计做报表的。

相关代码

SQL Server(2005及之后的版本)提供了内置的加密机制,加密方式有两大类,一类是对称加密,另一类则是非对称加密。

SQL Server的对称加密示例代码:

--创建一个对称密钥,其实只需要创建一次,不用每次都创建,这个对称密钥密码为123456(嗯,大多数人认为的密码),密码是nvarchar类型的
CREATE SYMMETRIC KEY my_symetric_key WITH ALGORITHM = DESX ENCRYPTION BY PASSWORD = N''; --使用一个对称密钥前必须打开它,而且要提供创建它时所使用的密码,密码不对的话就会打开失败
OPEN SYMMETRIC KEY my_symetric_key DECRYPTION BY PASSWORD = N''; --只能加密字符串,如果要加密数字,就用CONVERT函数先把数字转为字符串
DECLARE @strClearText NVARCHAR(100);
SET @strClearText = N'3000.00'; --密文类型为VARBINARY,用
DECLARE @strCipherText VARBINARY(MAX);
SET @strCipherText = EncryptByKey(Key_GUID('my_symetric_key'), @strClearText); --显示密文(密文其实为二进制格式,你会看到其HEX文本)
SELECT @strCipherText AS [密文]; --解密不需要提供密钥名称,SQL Server会根据当前上下文去寻找打开的对称密钥
DECLARE @strDecrypted VARBINARY(MAX);
SET @strDecrypted = DecryptByKey(@strCipherText); --显示出解密后的明文
SELECT Convert(NVARCHAR(100), @strDecrypted) AS [解密后的明文] --关闭这个密钥
CLOSE SYMMETRIC KEY my_symetric_key; --以后还需要用这个密钥的话就不用删掉它
DROP SYMMETRIC KEY my_symetric_key;

SQL Server的非对称加密示例代码:

--创建一个非对称密钥(不用每次都创建),这个对称密钥密码为123456,使用RSA512算法,另外还有RSA1024和RSA2048,强度更高,可加密内容更长,密钥生成速度也会慢不少,RSA512这里足够用了
CREATE ASYMMETRIC KEY my_asymetric_key WITH ALGORITHM = RSA_512 ENCRYPTION BY PASSWORD = N''; DECLARE @strClearText NVARCHAR(100);
SET @strClearText = N'3000.00'; --加密,和对称加密不一样,不需要提供密码,也不需要打开密钥
DECLARE @strCipherText VARBINARY(MAX);
SET @strCipherText = EncryptByAsymKey(AsymKey_ID('my_asymetric_key'), @strClearText); --显示密文
SELECT @strCipherText AS [密文]; --解密,必须提供生成密钥时候的密码,密码不正确的话就会出错
--密钥选择不正确的话会得到NULL结果
DECLARE @strDecrypted VARBINARY(MAX);
SET @strDecrypted = DecryptByAsymKey(AsymKey_ID('my_asymetric_key'), @strCipherText, N''); --显示出解密后的明文
SELECT Convert(NVARCHAR(100), @strDecrypted) AS [解密后的明文] --以后还需要用这个密钥的话就不用删掉它
DROP ASYMMETRIC KEY my_Asymetric_key;

另外可能用得到的一些语句有:

--查看所有对称密钥
SELECT * FROM sys.symmetric_keys; --查看所有非对称密钥
SELECT * FROM sys.asymmetric_keys;

例子

可能你还想说:其实这些加密程序也能做,为什么要用DBMS的功能来做?——方便。前面也提到了,工资这个东西将来一定要做统计,做报表的,如果用DBMS的功能来做,一个报表也许也就是一个连表查询的SELECT语句,但用程序来做这种“连表查询”的功能恐怕就很麻烦了。

在应付这次需求上面,我认为比较适合用非对称加密,即:谁都可以加密,但只有知道私钥的人才能解密。例如我是工资管理员,我要给员工007设置工资为3000,我就用007的公钥对“3000”进行加密好了,这样,007能够用自己的私钥解密出自己的工资了,每个员工都有不同的公私钥,都只能查看自己的工资,那问题来了,对于我这个管理员来说,要查看所有员工的工资,岂不是要知道他们全部的私钥才行?这样岂不是很麻烦?是的,我这次是用了一点“数据冗余”来解决这个麻烦,即:用两列来保存工资信息,其中一列是真正的工资加密信息(amount),另一列是给员工自己查看的工资信息(amount_view),amount_view是用amount生成的,amount的内容使用工资管理员的公钥进行加密,而amount_view的内容则使用员工的各自的公钥进行加密。

现在我们来实践一下:

--创建一个员工表
CREATE TABLE hr_emp(
emp_no nvarchar(20) PRIMARY KEY,
name_c nvarchar(20) NOT NULL,
has_salary_pwd bit NOT NULL DEFAULT(0),
); --创建一个工资表
CREATE TABLE hr_salary(
sal_id int PRIMARY key IDENTITY(1,1) NOT NULL,
emp_no nvarchar(20) NOT NULL,
type nvarchar(15) NOT NULL,
amount varbinary(max) NOT NULL,
amount_view varbinary(max) NOT NULL
); --增加一个外键约束
ALTER TABLE hr_salary ADD CONSTRAINT fk_salary_emp_ref_emp FOREIGN KEY (emp_no) REFERENCES hr_emp(emp_no); --创建一个非对称密钥
CREATE ASYMMETRIC KEY salary_mgr_key
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = N''; --初始化一些数据
insert into hr_emp (emp_no, name_c) values ('', '张三');
insert into hr_emp (emp_no, name_c) values ('', '李四');
insert into hr_emp (emp_no, name_c) values ('', '王五');
insert into hr_emp (emp_no, name_c) values ('', '赵六'); insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Cash', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),8000.00)), 0);
insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Cash', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),4000.00)), 0);
insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Cash', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),3000.00)), 0);
insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Cash', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),4500.00)), 0); insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Allowance', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),1234.00)), 0);
insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Allowance', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),800.00)), 0);
insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Allowance', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),765.00)), 0);
insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Allowance', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),980.00)), 0); insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Deduct', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),0.00)), 0);
insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Deduct', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),-440.00)), 0);
insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Deduct', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),0.00)), 0);
insert into hr_salary (emp_no, type, amount, amount_view) values('', 'Deduct', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),0.00)), 0);

现在来看hr_salary表中的内容的话,发现amount列是加密的,没有密码就没法知道其中的内容:

防止开发人员获取到敏感数据(SQL Server的数据加密简介)

OK,我们现在来解密:

select emp_no , type , Convert ( decimal( 16 ,2 ), Convert( nvarchar (100 ), DecryptByAsymKey (AsymKey_ID ( 'salary_mgr_key'), amount, N'')))as amount from hr_salary;

结果出来了,解密成功。

防止开发人员获取到敏感数据(SQL Server的数据加密简介)

统计各个员工工资总数,并把中文名带出来:

select e.emp_no, e.name_c, s.amount from hr_emp e left join
(select emp_no, sum(Convert(decimal(16,2),Convert(nvarchar(100), DecryptByAsymKey(AsymKey_ID('salary_mgr_key'), amount, N'')))) as amount from hr_salary group by emp_no) s on e.emp_no=s.emp_no;

没有压力,对吧。

防止开发人员获取到敏感数据(SQL Server的数据加密简介)

注意事项

至于amount_view这列的处理,大家想想也知道,方法其实前面都给出了,用CREATE ASYMMETRIC KEY语句来给每个用户创建密钥,密钥名称可以使用“ak+工号”这种规则,密码可以用随机生成,将密码告诉用户,让他们自己记住,这样就OK了。只是处理的时候必须记得,amount发生变化的时候,amount_view也要跟着发生变化。

用户提供的密码是否正确可以这样验证:

SELECT count(DECRYPTBYASYMKEY(AsymKey_ID('salary_mgr_key'), '', N'')) FROM sys.asymmetric_keys WHERE name='salary_mgr_key';

若结果为1则正确,若结果为0或出现异常则不正确。

修改密码是很麻烦的事情,相当于重新创建一对非对称密钥,你得把整个加密好的数据用旧的密钥解密好,再用新的密钥加密。

最后还必须强调一点:整个服务器程序都不要保存密码,否则前功尽弃,密码必须由用户在使用的时候提供,并且只暂存于Session中,Session丢失的话必须要求用户重新输入密码。