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

原文

背景

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

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

相关代码

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

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

复制代码

--创建一个对称密钥,其实只需要创建一次,不用每次都创建,这个对称密钥密码为123456(嗯,大多数人认为的密码),密码是nvarchar类型的CREATE SYMMETRIC KEY my_symetric_key WITH ALGORITHM = DESX ENCRYPTION BY PASSWORD = N'123456';--使用一个对称密钥前必须打开它,而且要提供创建它时所使用的密码,密码不对的话就会打开失败OPEN SYMMETRIC KEY my_symetric_key DECRYPTION BY PASSWORD = N'123456';--只能加密字符串,如果要加密数字,就用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'123456';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'123456');--显示出解密后的明文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_keyWITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = N'123456';--初始化一些数据insert into hr_emp (emp_no, name_c) values ('0008', '张三');insert into hr_emp (emp_no, name_c) values ('0053', '李四');insert into hr_emp (emp_no, name_c) values ('0055', '王五');insert into hr_emp (emp_no, name_c) values ('0058', '赵六');insert into hr_salary (emp_no, type, amount, amount_view) values('0008', 'Cash', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),8000.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0053', 'Cash', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),4000.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0055', 'Cash', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),3000.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0058', 'Cash', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),4500.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0008', 'Allowance', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),1234.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0053', 'Allowance', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),800.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0055', 'Allowance', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),765.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0058', 'Allowance', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),980.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0008', 'Deduct', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),0.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0053', 'Deduct', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),-440.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0055', 'Deduct', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),0.00)), 0);insert into hr_salary (emp_no, type, amount, amount_view) values('0058', 'Deduct', EncryptByAsymKey(AsymKey_ID('salary_mgr_key'), Convert(nvarchar(100),0.00)), 0);

复制代码

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

OK,我们现在来解密:

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

结果出来了,解密成功。

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

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'123456')))) as amount from hr_salary group by emp_no) s on e.emp_no=s.emp_no;

没有压力,对吧。

注意事项

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

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

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

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

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

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

标签: SQLServer

Leave a Reply

Your email address will not be published. Required fields are marked *