SQL SERVER 2008 使用对称加密算法加解密列

需求：

　　1.对某系统的敏感数据列进行加密

　　2.能够在使用该数据时进行解密

以上两个基本需求就确定了加密算法的类型必须是“对称加密算法”。

分析--分解需求：

　　（1）对称加密算法的选择： Des Or Aes

　　（2）需求貌似很简单，无非是写和取该列数据时多进行一步加解密操作（应用程序中用到该列相关的地方都得做相应改动）

　　（3）对老的明文数据进行一次数据转换

　　（4）还得保证在数据转换的同时新产生的数据的加密同步问题

分析--分点的具体实现方式以及可能出现的难点：

　　　　（1）对称加密算法个人觉得只是个“防君子不防小人”的做法，Des or Aes 可以任选一个。<OK>

　　　　（2）对于老数据的“数据转换”，这个看起来很简单的事情在实际实施起来可能是漏洞百出的。<Why？>

　　　　下面我们分析下吧（数量级上点当量的可以往下考虑）：

 

　　　　　　　a.数据转换时对表的影响

　　　　　　　b.数据转换的对一些数据同步的影响

　　　　　　　c.数据转换时产生的事务日志对数据库服务器的影响

　　　　对于具体如何去实施数据转换：

　　　　（1）可能你会想写个小工具去批量转换并更新这些数据，如果你的系统也是朝九晚五的上班那没问题大半夜开启随它挥霍，但是你的系统如果是24小时待命的

　　　　　　那就非常抱歉了，此批量操作很可能会导致该表“锁表“，一旦出现”锁表“这个影响就不好估量了。

　　　　（2）尽量避免锁表，我先将转换的数据插入到临时表，待转换完毕再进行”联表更新“数据这个应该是万无一失吧。

　　　　　　该数据转换的方案貌似比上一个在锁表环节处理的更合理，但是细细一看还是有很多隐患：

　　　　　　a. 由于联表更新的速度非常快的，可能会瞬间使同步堵塞，造成数据中心的对应的表”锁表"

　　　　　　b. 由于短时间内的事务日志急剧增加，可能会导致数据库服务器的硬盘空间造成很大冲击，严重的话会使某些日常任务执行失败。

　　　　 （3）改应用程序中相关的代码，这个要是漏掉某个隐匿很久的接口或者函数没改，那就等着定时炸弹的爆炸来提醒你吧（好吧我可以全文搜索着改嘛。。。）<Maybe OK!>

方案一，照着需求和程序思维自然形成的思路：

　　　　程序猿当时没做过多考虑，一股脑儿把：数据转换小工具，加解密公共接口，相关使用该字段的逻辑一通翻天覆地的改啊，okay打完准备收工。但是提交升级时，评审没通过。。。

　　　　评语是： 不确定因素太多。。。

在程序猿　泪奔啊。。伤不起中，一个新的方案在高人指点下诞生。

方案二，在数据库中来做本次改造

　　乍一听该方案的可行性几乎可以忽略。但是在SQL SERVER 2008下通过测试对称加密算法采用证书实现跨数据服务加解密成功后就初步确定了实施该方案的信心。

　　然而进一步分析当前数据库架构时发现方案二的可行性和性能较方案一更好：

　　1.在各个数据库服务器上建立一套相同的Master Key 和 Certificate， Symmetric Key由Certificate来加密，然后可以通过调用DecryptByAutoCert来解密（这一套加解密方法是可以保证数据在各服务器间同步后也能采用证书去互相解密）

　　2.结合需求来实现一次对客户无感知的改造方案和升级步骤（前提是你的数据库物理层之上还有一个逻辑数据层）：

　　a.给该物理表增加一个新的字段，类型是VarBinary

      b. 给该物理表对应的逻辑对象新建两个触发器：更新 和 插入的触发器 -- 为了保证在数据转换时新产生的数据能够老字段和新字段的数据一致,即新数据来的时候也同步加密保存到新字段

　　c. 创建代理任务去执行数据转换，可以直接将老字段数据加密并Update到新字段，并且测试预估好各服务间的数据同步能力以及事务日志对服务器的压力，选择一个系统使用相对较少的时间段去执行数据转换的任务，如果数据量比较大还可以分成多个时段去执行该任务。

　　d.检查数据，待数据转换完成，可以进行下一步的逻辑对象中新老字段的正式切换

　　e.暂时保留老字段，观察一段时间后，稳定了可以将老字段Drop掉，需求完成。

对比分析方案一和方案二，很明显的看出方案二几乎是实现了"用户无感知的升级“，且无须改动当前的任何代码，另外在数据转换和实际升级操作过程中更为便捷，性能和效率也更高。

 

特此道别，庸散的2013。。。

第一次写博客，还有点找不到感觉，希望以后有长足的进步。