sql 2005数据库加密实战

转贴自teched讲师:     牛可      
   
  基本概念:  
   
  第一层   服务主密钥    
   
  备份服务主密钥  
  backup   service   master   key   to   file='c:\smk.bak'  
  encryption   by   password='P@ssw0rd'  
     
  restore   service   master   key   from   file='c:\smk.bak'  
  decryption   by   password='P@ssw0rd'  
     
  第二层   数据库主密钥  
  1)必须先在该数据库上创建数据库主密钥才能使用  
  create   master   key   encryption   by   password='P@ssw0rd'  
     
  2)使用数据库主密钥  
  －如果数据库主密钥使用服务密钥进行保护，则在使用时会自动打开  
  opren   master   key   decryption   by   password='P@ssw0rd'  
     
  3)查看数据库主密钥状态  
  sys.symmetric_keys  
     
  4)备份数据库主密钥  
  backup   master   key   to   file='c:\smk.bak'  
  encryption   by   password='P@ssw0rd'  
     
  restore   master   key   from   file='c:\smk.bak'  
  decryption   by   password='P@ssw0rd'  
     
     
  数字证书  
  创建自签名  
  create   certificate   cert_myCert  
  encryption   by   password='P@ssw0rd'  
  with   subject='Self   Signed   Cert',  
  start_date='1/31/2006'  
  expiry_date='1/31/2008'  
     
     
  非对称密钥  
  创建新的密钥对  
  create   asymmetric   key   asy_Key1  
  with   algorithm=RSA_2048  
  encryption   by   password='P@ssw0rd'  
     
     
  对称密钥  
  创建新的密钥对  
  create   symmetric   key   SymKeyMarketing3  
  with   algorithm=AES_2048  
  encryption   by   certificate   asy_Key1  
     
  使用对称密钥  
  使用前必须打开  
  open   symmetric   SymKeyMarketing3  
  decryption   by   certificate   asy_Key1  
     
  sys.open_keys  
     
     
  数据列加密  
  －使用对称密钥加密大量的列数据  
  －考虑使用证书，非对称密钥保护对称密钥  
     
  防止绕过加密数据列的攻击－使用验证器  
     
  注:  
  在加密列上的索引将变得无效  
  加密数据列的长度增长，建议使用varbinary（max）数据类型  
  修改已有的dml语句以支持加密的数据列  
     
  -----***********示例1   了解数据库加密体系结构*****-----  
     
  --************(1)   服务主密钥  
  --准备工作  
  --创建测试数据库TestDB  
   
  --1)备份服务主密钥  
  backup   service   master   key   to   file='g:\smk.bak'  
  encryption   by   password='p@ssw0rd'  
     
  --2)生成新的主密钥  
  Alter   service   master   key   regenerate  
     
  --3)从备份文件还原服务主密钥  
  Restore   service   master   key   from   file=   file='g:\smk.bak'  
  encryption   by   password='p@ssw0rd'  
     
  --*************(2)   数据库主密钥  
  --1)为数据库创建数据库主密钥  
  create   master   key   encryption   by   password='p@ssw0rd'  
  go  
   
  --2)查看数据库加密状态  
  select   [name],is_master_key_encrypted_by_server    
  from   sys.databases   where   name='TestDB'  
     
  --3)查看数据库主密钥的信息  
  select   *   from   sys.symmetric_keys    
     
  --4)备份数据库主密钥  
  backup   master   key  
  to   file='g:\testdbkey.bak'  
  encryption   by   password='p@ssw0rd'  
     
  --5)删除服务主密钥对数据库主密钥的保护  
  --创建非对称密钥成功,自动使用服务主密钥解密并使用该数据库主密钥  
  create   asymmetric   key   asy_Testkey1   with   algorithm=RSA_1024  
  go  
  --删除服务主密钥对数据库主密钥的保护  
  alter   master   key  
  drop   encryption   by   service   master   key  
  go  
     
  --查看数据库加密状态  
  select   [name],is_master_key_encrypted_by_server    
  from   sys.databases   where   name='TestDB'  
     
  --创建非对称密钥失败,因为数据库主密钥未打开  
  create   asymmetric   key   asy_Testkey2   with   algorithm=RSA_1024  
  go  
   
  --打开数据库主密钥  
  open   master   key   decryption   by   password='p@ssw0rd'  
  select   *   from   sys.openkeys  
  go  
   
  --创建非对称密钥成功  
  create   asymmetric   key   asy_Testkey2   with   algorithm=RSA_1024  
  go  
     
  --恢复服务主密钥对数据库主密钥的保护  
  alter   master   key  
  add   encryption   by   service   master   key  
  close   master   key  
  go  
     
  --*****(3)证书  
  --1)创建自签名证书  
  create   certificate   cert_Testcert  
  encryption   by   password='p@ssw0rd'  
  with   subject='TestCert1',  
  start_date='1/31/2006',  
  expiry_date='1/31/2008'  
  go  
   
  select   *   from   sys.certificates  
     
     
  --2)从文件导入证书  
  Create   certificate   cert_TestCert2  
  From   file=’g:\MSCert.cer’  
  Go  
     
  --   3)备份导出证书和密钥  
  backup   certificate   cert_Testcert  
  to   file='g:\Testcert.cer'  
  with   private   key  
  (decryption   by   password='p@ssw0rd',  
  file='g:\TestCert_pvt',--私密钥  
  encryption   by   password='p@ssw0rd'   )  
  go  
     
  --4)使用证书加解密数据  
  --加密:使用证书的公钥  
  declare   @cleartext   varbinary(200)  
  declare   @cipher   varbinary(200)  
  set   @cleartext=convert(   varbinary(200),'Test   text   string')  
  set   @cipher=EncryptByCert(Cert_ID('cert_TestCert'),@cleartext)  
  select   @cipher  
     
  --解密:使用证书的私钥  
  select   convert(varchar(200),DecryptByCert(Cert_ID('cert_TestCert'),@cipher,N'p@ssw0rd'))   as   [cleartext]  
     
     
  --5)   删除证书私钥  
  alter   certificate   cert_TestCert  
  remove   private   key  
  go  
     
  --加密成功  
  declare   @cleartext   varbinary(200)  
  declare   @cipher   varbinary(200)  
  set   @cleartext=convert(   varbinary(200),'Test   text   string')  
  set   @cipher=EncryptByCert(Cert_ID('cert_TestCert'),@cleartext)  
  select   @cipher  
     
  --解密失败:因为私钥被删除  
  select   convert(varchar(200),DecryptByCert(Cert_ID('cert_TestCert'),@cipher,N'p@ssw0rd'))   as   [cleartext]  
     
     
  --***(4)   非对称密钥  
  --1)使用sn.exe生成非对称密钥,安装vs2005后有sn.exe,   在命令行方式下执行  
  sn   -k   g:\asy_Test.key  
     
  --2)从文件创建非对称密钥  
  create   asymmetric   key   asm_Test  
  from   file='g:\asy_Test.key'  
  encryption   by   password='p@ssw0rd'  
  go  
     
  select   *   from   sys.asymmetric_keys

--***********示例2   使用加密保护列数据  
  -----*****(1)   准备工作  
  --1)   创建示例表  
  create   table   empsalary  
  (EmpID   int,  
    Title   nvarchar(50),  
    Salary   varbinary(500)  
  )  
  go  
     
  --2)   创建数据库主密钥  
  create   master   key   encryption   by   password='p@ssw0rd'  
  go  
     
  --3)   创建用于加密的对称密钥  
  create   symmetric   key   sym_Salary  
  with   algorithm=AES_192  
  encryption   by   password='p@ssw0rd'  
  go  
     
  select   *   from   sys.symmetric_keys   where   [name]='sym_Salary'  
     
  ------****(2)加密列数据  
  --1)打开对称密钥  
  open   symmetric   key   sym_Salary  
  decryption   by   password='p@ssw0rd'  
  go  
   
  select   *   from   sys.openkeys  
     
  --2)向表中插入数据，并对salary列加密  
  insert   into   empsalary   values   (1,'CEO',EncryptByKey(KEY_GUID('sym_Salary'),'20000'))  
  insert   into   empsalary   values   (2,'Manager',EncryptByKey(KEY_GUID('sym_Salary'),'10000'))  
  insert   into   empsalary   values   (3,'DB   Admin',EncryptByKey(KEY_GUID('sym_Salary'),'5000'))  
     
  --3)关闭打开的对称密钥  
  close   symmetric   key   sym_Salary  
  go  
   
  select   *   from   sys.openkeys  
     
     
  --4)   查看表中的数据  
  select   *   from   empsalary  
     
  --(3)解密并访问被加密的数据列  
  --1)打开对称密钥  
  open   symmetric   key   sym_Salary   decryption   by   password='p@ssw0rd'  
  go  
   
  --2)使用对称密钥解密并访问被加密的列  
  select   empid,title,cast(DecryptByKey(Salary)   as   varchar(20))   as   salary   from   empsalary  
     
  --3)   关闭对称密钥  
  close   symmetric   key   sym_Salary  
  go  
     
  --(4)绕过加密数据的攻击  
  --1)攻击者使用其他数据行的加密数据替换某一行的数据  
  update   empsalary    
  set   salary=(select   salary   from   empsalary   where   empid=1)  
  where   empid=3  
     
  --2)查看被攻击后解密的数据  
  open   symmetric   key   sym_Salary   decryption   by   password='p@ssw0rd'  
  select   empid,title,cast(DecryptByKey(Salary)   as   varchar(20))   as   salary   from   empsalary  
  close   symmetric   key   sym_Salary  
     
     
  --(5)使用验证器防止绕过加密数据的攻击  
  --1)删除前面添加的数据行  
  delete   empsalary  
     
  --2)向表插入数据，并对salary列的数据使用验证器进行加密  
  open   symmetric   key   sym_Salary   decryption   by   password='p@ssw0rd'  
  insert   into   empsalary   values   (1,'CEO',EncryptByKey(KEY_GUID('sym_Salary'),'20000',1,'1'))  
  insert   into   empsalary   values   (2,'Manager',EncryptByKey(KEY_GUID('sym_Salary'),'10000',1,'2'))  
  insert   into   empsalary   values   (3,'DB   Admin',EncryptByKey(KEY_GUID('sym_Salary'),'5000',1,'3'))  
     
  --3)解密并访问被加密的数据  
  select   empid,title,cast(DecryptByKey(Salary,1,cast(empid   as   varchar(3)))   as   varchar(20))   as   salary   from   empsalary  
     
  --4)用同样的方法篡改数据  
  update   empsalary    
  set   salary=(select   salary   from   empsalary   where   empid=1)  
  where   empid=3  
     
  --5)被篡改后的加密了的数据列变成无效  
  select   empid,title,cast(DecryptByKey(Salary,1,cast(empid   as   varchar(3)))   as   varchar(20))   as   salary   from   empsalary  
     
     
     
  --***********示例3   使用数字证书签署存储过程  
     
  --*****(1)准备  
  --1)创建数据库主密钥  
  create   master   key   encryption   by   password='p@ssw0rd'  
     
  --2)创建签署存储过程所需要的证书  
  create   certificate   cert_Product  
  with   subject='Products   Sign',  
  start_date='1/31/2006',  
  expiry_date='1/31/2008'  
  go  
     
  --3)创建SPDeveloper登录帐户和用户，该用户创建并访问Products表的存储过程  
  create   login   [SPDeveloper]   with   password='p@ssw0rd',default_database=[TestDB]  
  go  
  create   user   [SPDeveloper]   for   login   SPDeveloper   with   default_schema=[SPDeveloper]  
  go  
  create   schema   products   authorization   SPDeveloper  
  go  
  exec   sp_addrolemember   @rolename='db_owner',@membername='SPDeveloper'  
     
  --4)以SPDeveloper的身份创建存储过程products.usp_Products  
  execute   as   user='SPDeveloper'  
  go  
  create   procedure   products.usp_Products  
  as  
  select   *   from   dbo.Products  
  go  
     
  revert  
  select   user  
     
  --5)创建普通用户jerry  
  create   login   [jerry]   with   password='p@ssw0rd',default_database=[TestDB]  
  go  
  create   user   [jerry]   for   login   jerry    
  go  
     
  --*******(2)使用证书签署存储过程  
  --1)授予用户jerry执行存储过程的权限  
  grant   execute   on   products.usp_Products   to   jerry  
     
  --2)以jerry的身份执行存储过程失败，因为拥有权链的断裂的  
  execute   as   user='jerry'  
  select   user  
  go  
     
  execute   products.usp_Products  
  go  
  revert  
     
  --3)使用证书在当前数据库创建用户ProductsReader,并为该用户赋予读取products表的权限  
  create   user   ProductsReader   for   certificate   cert_Products  
  go  
  grant   select   on   products   To   ProductsReader  
     
  --4)使用证书签署当前存储过程  
  add   signature   to   products.usp_Products   by   certificate   cert_Products  
     
  --5)以jerry的身份重新执行存储过程，成功  
  --因为存储过程将以ProductsReader的权限上下文执行  
  execute   as   user='jerry'  
  select   user  
  go  
     
  execute   products.usp_Products  
  go  
  revert