SQLServer 2008中的代碼安全(七) 證書(shū)加密_Mssql數(shù)據(jù)庫(kù)教程

      編輯Tag賺U幣

      推薦:SQL Server 2008中的代碼安全(八)透明加密(TDE)
      SQL Server 2008引入透明數(shù)據(jù)加密(Transparent Data Encryption),即TDE,它允許你完全無(wú)需修改應(yīng)用程序代碼而對(duì)整個(gè)數(shù)據(jù)庫(kù)加密。

      證書(shū)同時(shí)包含公鑰和密鑰,前者用來(lái)加密,后者解密。SQL Server可以生成它自己的證書(shū),也可以從外部文件或程序集載入。因?yàn)榭梢詡浞萑缓髲奈募休d入它們,證書(shū)比非對(duì)稱(chēng)密鑰更易于移植,而非對(duì)稱(chēng)密鑰卻做不到。這意味著可以在數(shù)據(jù)庫(kù)中方便地重用同一個(gè)證書(shū)。

      注意:證書(shū)和非對(duì)稱(chēng)密鑰同樣的消耗資源。

      我們看一組例子:

      示例一、創(chuàng)建數(shù)據(jù)庫(kù)證書(shū)

      創(chuàng)建數(shù)據(jù)庫(kù)證書(shū):CREATE SYMMETRIC KEY ()

      復(fù)制代碼 代碼如下:www.wf0088.com

      USE DB_Encrypt_Demo
      GO
      --創(chuàng)建證書(shū)
      CREATE CERTIFICATE cert_Demo --證書(shū)名稱(chēng)
      ENCRYPTION BY PASSWORD = 'asdfG!!!' --加密證書(shū)的密碼
      WITH SUBJECT = 'DB_Encrypt_Demo Database Encryption Certificate',--證書(shū)主題
      START_DATE = '3/14/2011', EXPIRY_DATE = '10/20/2012'--起止日期
      GO

      示例二、查看數(shù)據(jù)庫(kù)中的證書(shū)

      使用目錄視圖sys.certificates()來(lái)查看。

      復(fù)制代碼 代碼如下:www.wf0088.com

      --查看當(dāng)前數(shù)據(jù)庫(kù)中的證書(shū)
      use DB_Encrypt_Demo
      go

      --查看證書(shū)
      SELECT name, pvt_key_encryption_type_desc, issuer_name
      FROM sys.certificates

      ----結(jié)果返回
      /*
      name pvt_key_encryption_type_desc issuer_name
      cert_Demo ENCRYPTED_BY_PASSWORD DB_Encrypt_Demo Database Encryption Certificate
      */


      示例三、備份和還原證書(shū)

      創(chuàng)建證書(shū)后,也可以使用BACKUP CERTIFICATE()命令備份到文件,為了安全地保存或在其他數(shù)據(jù)庫(kù)中還原它。

      復(fù)制代碼 代碼如下:www.wf0088.com

      --備份證書(shū)
      BACKUP CERTIFICATE cert_Demo
      TO FILE = 'H:\SqlBackup\certDemo.BAK'--證書(shū)備份路徑,用來(lái)加密
      WITH PRIVATE KEY (FILE='H:\SqlBackup\certDemoPK.BAK',--證書(shū)私鑰文件路徑,用來(lái)解密
      ENCRYPTION BY PASSWORD = '1234GH!!!',--加密私鑰密碼
      DECRYPTION BY PASSWORD = 'asdfG!!!' )--解密私鑰密碼

      --備份后,可以在其他數(shù)據(jù)庫(kù)中使用這個(gè)證書(shū),或使用DROP CERTIFICATE命令刪除它。
      DROP CERTIFICATE cert_Demo
      GO
      --從備份文件中還原證書(shū)到數(shù)據(jù)庫(kù)中
      CREATE CERTIFICATE cert_Demo
      FROM FILE = 'H:\SqlBackup\certDemo.BAK'
      WITH PRIVATE KEY (FILE = 'H:\SqlBackup\certDemoPK.BAK',
      DECRYPTION BY PASSWORD = '1234GH!!!' ,--解密私鑰密碼
      ENCRYPTION BY PASSWORD = 'asdfG!!!')--加密私鑰密碼

      示例四、管理證書(shū)的私鑰

      使用ALTER CERTIFICATE( )命令為證書(shū)增加或刪除私鑰。這個(gè)命令允許刪除私鑰(默認(rèn)通過(guò)數(shù)據(jù)庫(kù)主密鑰時(shí)行加密)、增加私鑰或修改私鑰的密碼。

      復(fù)制代碼 代碼如下:www.wf0088.com

      --從證書(shū)中刪除私鑰
      ALTER CERTIFICATE cert_Demo
      REMOVE PRIVATE KEY

      --從備份文件為既有證書(shū)重新增加私鑰
      ALTER CERTIFICATE cert_Demo
      WITH PRIVATE KEY
      (FILE = 'H:\SqlBackup\certDemoPK.BAK',
      DECRYPTION BY PASSWORD = '1234GH!!!' ,--解密私鑰密碼
      ENCRYPTION BY PASSWORD = 'asdfG!!!')--加密私鑰密碼

      --修改既有私鑰的密碼
      ALTER CERTIFICATE cert_Demo
      WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'asdfG!!!',
      ENCRYPTION BY PASSWORD = 'mynewpassword!!!13E')

      示例五、使用證書(shū)加密和解密

      使用函數(shù)EncryptByCert加密數(shù)據(jù)。()

      復(fù)制代碼 代碼如下:www.wf0088.com

      --從證書(shū)中刪除私鑰
      ALTER CERTIFICATE cert_Demo
      REMOVE PRIVATE KEY

      --從備份文件為既有證書(shū)重新增加私鑰
      ALTER CERTIFICATE cert_Demo
      WITH PRIVATE KEY
      (FILE = 'H:\SqlBackup\certDemoPK.BAK',
      DECRYPTION BY PASSWORD = '1234GH!!!' ,--解密私鑰密碼
      ENCRYPTION BY PASSWORD = 'asdfG!!!')--加密私鑰密碼

      --修改既有私鑰的密碼
      ALTER CERTIFICATE cert_Demo
      WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'asdfG!!!',
      ENCRYPTION BY PASSWORD = 'mynewpassword!!!13E')

      下面是一個(gè)例子:
      復(fù)制代碼 代碼如下:www.wf0088.com

      USE DB_Encrypt_Demo
      GO
      --插入測(cè)試數(shù)據(jù)
      INSERT dbo.PWDQuestion
      (CustomerID, PasswordHintQuestion, PasswordHintAnswer)
      VALUES
      (10, '您出生的醫(yī)院名稱(chēng)?',
      EncryptByCert(Cert_ID('cert_Demo'), '北京四合院家中'))

      --查看明文
      SELECT CAST(PasswordHintAnswer as varchar(200)) PasswordHintAnswer
      FROM dbo.PWDQuestion
      WHERE CustomerID = 10

      邀月工作室
      復(fù)制代碼 代碼如下:www.wf0088.com

      --查看原文 3w@live.cn
      SELECT PasswordHintQuestion,
      CAST(DecryptByCert(Cert_ID('cert_Demo'),PasswordHintAnswer,
      N'mynewpassword!!!13E')
      as varchar(200)) PasswordHintAnswer
      FROM dbo.PWDQuestion WHERE CustomerID = 10

      邀月工作室

      示例六、使用對(duì)稱(chēng)密鑰對(duì)數(shù)據(jù)進(jìn)行加密和解密

      在前面的文章中,你已經(jīng)看到打開(kāi)用非對(duì)稱(chēng)密鑰加密的對(duì)稱(chēng)密鑰的演示,它分兩個(gè)步驟,首先用OPEN SYMMETRIC KEY命令,然后是實(shí)際的DecryptByKey函數(shù)調(diào)用。SQL Server也提供了能夠?qū)⑦@兩個(gè)步驟合二為一的額外的解密函數(shù):DecryptByKeyAutoAsymKey()和DecryptByKeyAutoCert()

      復(fù)制代碼 代碼如下:www.wf0088.com

      USE DB_Encrypt_Demo
      GO

      --本例使用數(shù)據(jù)庫(kù)主密碼加密,因而不需要密碼。3w@live.cn
      ----Create master Key Encryption By password='123ASD!'
      ----go

      --創(chuàng)建非對(duì)稱(chēng)密鑰 3w@live.cn
      CREATE ASYMMETRIC KEY asymDemo_V2
      WITH ALGORITHM = RSA_512
      --創(chuàng)建對(duì)稱(chēng)密鑰 3w@live.cn
      CREATE SYMMETRIC KEY sym_Demo_V2
      WITH ALGORITHM = TRIPLE_DES
      ENCRYPTION BY ASYMMETRIC KEY asymDemo_V2

      --打開(kāi)對(duì)稱(chēng)密鑰,插入記錄
      OPEN SYMMETRIC KEY sym_Demo_V2
      DECRYPTION BY ASYMMETRIC KEY asymDemo_V2
      INSERT dbo.PWDQuestion
      (CustomerID, PasswordHintQuestion, PasswordHintAnswer)
      VALUES
      (22, '您出生的醫(yī)院名稱(chēng)?',
      EncryptByKey(Key_GUID('sym_Demo_V2'), '邵逸夫醫(yī)院'))
      CLOSE SYMMETRIC KEY sym_Demo_V2

      此時(shí),使用DecryptByKeyAutoAsymKey解密數(shù)據(jù),只需要一個(gè)操作
      復(fù)制代碼 代碼如下:www.wf0088.com

      SELECT CAST(DecryptByKeyAutoAsymKey(ASYMKEY_ID('asymDemo_V2'),NULL,
      PasswordHintAnswer) as varchar)
      FROM dbo.PWDQuestion
      WHERE CustomerID = 22

      邀月工作室

      小結(jié):

      1、本文主要介紹證書(shū)的創(chuàng)建、刪除、查看以及用它來(lái)修改加密方式、進(jìn)行數(shù)據(jù)的加密和解密。

      2、證書(shū)加密和非對(duì)稱(chēng)密鑰加密相對(duì)對(duì)稱(chēng)密鑰加密更為消耗資源。

      下文將主要介紹SQL Server中最為令人鼓舞的透明數(shù)據(jù)加密(TDE)

      分享:SQL Server2008 數(shù)據(jù)庫(kù)誤刪除數(shù)據(jù)的恢復(fù)方法分享
      今天有個(gè)朋友很著急地打電話(huà)給我,他用delete語(yǔ)句誤刪除了SQL Server 2008數(shù)據(jù)庫(kù)中兩個(gè)表中的所有記錄,而這個(gè)數(shù)據(jù)庫(kù)之前沒(méi)有任何備份。讓我?guī)退鉀Q一下,不然他要賠償客戶(hù)很多錢(qián)

      來(lái)源:模板無(wú)憂(yōu)//所屬分類(lèi):Mssql數(shù)據(jù)庫(kù)教程/更新時(shí)間:2013-04-23
      相關(guān)Mssql數(shù)據(jù)庫(kù)教程