原文 http://www.cnblogs.com/guogangj/p/3365614.html
背景
有时候，我们还真的会碰到这样的需求：防止开发人员获取到敏感数据。也许你觉得很简单，把开发和运营分开不就可以了吗？是的，如果公司有专门的运营 团队的话，但对于很多小公司来说，几个人的开发团队就兼顾了需求分析、设计、开发、测试、调试、部署和运营了，数据库密码知道，程序代码全有，怎么办？ ——必须对数据库里的数据进行加密，这是唯一的办法。
也许你还是不明白，什么东西需要瞒着我们了不起的程序员，好吧，我直说了：工资！假如你的公司让你做一个工资系统，你会不会有这方面的顾虑，一旦工 资信息被公开，后果必定是很严重的，也许老板对你很信任，认为让你知道没什么问题，但其他开发人员呢？后来接手你的工作的人呢？所以必须考虑这个问题。而 且，还外带一个需求：员工自己可以用自己的“薪资查看密码”来查看自己的工资（只能看自己的），每个人自己的“薪资查看密码”都不一样。另外不需要描述的 隐藏需求还有：将来必定是要对薪资做统计做报表的。
相关代码
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_key WITH 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丢失的话必须要求用户重新输入密码。
