SQLServer数据库加密
参考⽂献：
SQL Server中的加密简介
  在SQL Server2000和以前的版本，是不⽀持加密的。所有的加密操作都需要在程序中完成。这导致⼀个问题，数据库中加密的数据仅仅是对某⼀特定程序有意义，⽽另外的程序如果没有对应的解密算法，则数据变得毫⽆意义。举个例⼦来说，我以前写过⼀个web应⽤程序(参考博客：)，想让⽤户信息中的密码字段⽤密⽂保存，我是在程序中将⽤户的明⽂密码加密以后保存到数据库中的。
  到了SQL Server2005，引⼊了列级加密。使得加密可以对特定列执⾏，这个过程涉及4对加密和解密的内置函数。
  SQL Server 2008时代，则引⼊的了透明数据加密（TDE），所谓的透明数据加密，就是加密在数据库中进⾏，但从程序的⾓度来看就好像没有加密⼀样，和列级加密不同的是，TDE加密的级别是整个数据库。使⽤TDE加密的数据库⽂件或备份在另⼀个没有证书的实例上是不能附加或恢复的。
对称加密与⾮对称加密
  通常来说，加密可以分为两⼤类，对称(Symmetric)加密和⾮对称(Asymmetric)加密。
  对称加密是那些加密和解密使⽤同⼀个密钥的加密算法，就是加密密钥=解密密钥。对称加密通常来说会⽐较羸弱，因为使⽤数据时不仅仅需要传输数据本⾝，还是要通过某种⽅式传输密钥，这很有可能使得密钥在传输的过程中被窃取。
  ⾮对称加密是那些加密和解密使⽤不同密钥的加密算法，就是加密密钥!=解密密钥。⽤于加密的密钥称之为公钥，⽤于解密的密钥称之为私钥。因此安全性相⽐对称加密来说会⼤⼤提⾼。当然有⼀长必有⼀短，⾮对称加密的⽅式通常算法会相⽐对称密钥来说复杂许多，因此会带来性能上的损失。
  因此，⼀种折中的办法是使⽤对称密钥来加密数据，⽽使⽤⾮对称密钥来加密对称密钥。这样既可以利⽤对称密钥的⾼性能，还可以利⽤⾮对称密钥的可靠性。
证书与密钥的创建
每个数据库有且只有⼀个数据库主密钥(master key)，这是⼀个数据库级别的密钥。可以⽤于为创建数据库级别的证书或⾮对称密钥提供加密。通过T-SQL语句创建，如代码1所⽰。
View Code
--创建证书，对称与⾮对称密钥需要master key
--创建master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='P@ssw0rd';
--删除master key
drop MASTER KEY ;
如果要查看数据库是否有master key，可以通过下⾯的T-SQL语句查询
View Code
--在SSMS中没有查看master key的地⽅，但是可以在sys.databases中查看到数据库是否有master key
select name,is_master_key_encrypted_by_server from sys.databases
我的查询结果如下图所⽰：
从上图中我们可以看到master和TESTDB4这两个数据库含有master key，⽽且数据库没有。所以说master并不是只在master数据库下⾯的，如果我们要在TESTDB4中创建证书与密钥，那么就必须为TESTDB4这个数据库创建master key。
下⾯给出创建证书、对称密钥、⾮对称密钥的T-SQL代码
View Code
use TESTDB3;
--创建证书
CREATE CERTIFICATE CertTest
with SUBJECT ='Test Certificate'
GO
--创建⾮对称密钥
CREATE ASYMMETRIC KEY TestAsymmetric
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD ='P@ssw0rd';
GO
--创建对称密钥
CREATE SYMMETRIC KEY TestSymmetric
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD ='P@ssw0rd';
GO
创建完以后我们可以在SSMS中到我们创建的证书与密钥，如下图所⽰：
上⾯创建的证书、对称密钥、⾮对称密钥都是由固定的对称密钥P@ssw0rd加密的。其实创建的证书、对称密钥、⾮对称密钥的时候，也可以使⽤证书、对称密钥、⾮对称密钥加密，T-SQL代码如下：
View Code
--由证书加密对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CertTest;
GO
--由对称密钥加密对称密钥
OPEN SYMMETRIC KEY TestSymmetric
DECRYPTION BY PASSWORD='P@ssw0rd'
--必须先open 了TestSymmetric以后才可以⽤TestSymmetric来加密
CREATE SYMMETRIC KEY SymmetricBySy
WITH ALGORITHM = AES_256
ENCRYPTION BY SYMMETRIC KEY TestSymmetric;
GO
--由⾮对称密钥加密对称密钥
CREATE SYMMETRIC KEY SymmetricByAsy
WITH ALGORITHM = AES_256
ENCRYPTION BY ASYMMETRIC KEY TestASymmetric;
GO
创建完以后我们在Symmetric Key中可以看到我们查询的结果：
列级加密
  在列级加密中我们将使⽤AdventureWorks2008R2这个样例数据库，在Sales.CreditCard这张表中我们可以看到CardNumber列是明⽂的，如下图所⽰：
sqlserver备份表语句在实际⽣产环境中，如果⽤户的信⽤卡号是明⽂的是⾮常不安全的，我们在这项想将这个CardNumber使⽤列级加密。需要注意的是，进⾏加密或者解密的列必须是Varbinary类型。
⾸先我们创建不带数据的CreditCard的表结构，并且CardNumber这个列是Varbinary类型，T-SQL代码如下：
View Code
--创建不带数据的表结构，有where 1<>1来控制
SELECT CreditCardID,
CardType,
CardNumber_encrypt =CONVERT(varbinary(500), CardNumber),
ExpMonth,
ExpYear,
ModifiedDate
INTO CreditCard_Encrypt
FROM AdventureWorks2008R2.Sales.CreditCard
WHERE1<>1
在创建表结构以后，我们将数据导⼊到这张表中去：
View Code
--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert DECRYPTION BY CERTIFICATE CertTest;
--利⽤这个密钥加密CardNumber这个数据列，插⼊新建的表中，使⽤了EncryptByKey这个函数
insert CreditCard_encrypt (
CardType,
CardNumber_encrypt,
ExpMonth,
ExpYear,
ModifiedDate
)
select top10
CardType,
CardNumber_encrypt = EncryptByKey(KEY_GUID('SymmetricByCert'), CardNumber),
ExpMonth,
ExpYear,
ModifiedDate
from AdventureWorks2008R2.Sales.CreditCard
查询CreditCard_encrypt这个表，我们可以发现CardNumber列是密⽂了，如下图所⽰：
但是我们可以通过对称密钥来解密CardNumber这个列
View Code
--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert DECRYPTION BY CERTIFICATE CertTest;
--查看CardNumber_encrypt
select convert(nvarchar(25), DecryptByKey(CardNumber_encrypt)) from CreditCard_Encrypt;
使⽤这条语句查询出来的结果就是CardNuber的明⽂了。注意这⾥需要OPEN SYMMETRIC KEY，如果不打开这个对称私钥的话查询结果是null。如果之前打开过这个私钥，那么此处可以不⽤再次打开。
透明数据加密
  在SQL Server 2008中引⼊了透明数据加密(Transparent Data Encryption ，以下简称TDE)，之所以叫透明数据加密，是因为这种加密在使⽤数据库的程序或⽤户看来，就好像没有加密⼀样。TDE加密是数据库级别的。数据的加密和解密是以页为单位，由数据引擎执⾏的。在写⼊时进⾏加密，在读出时进⾏解密。客户端程序完全不⽤做任何操作。（Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.）
  TDE的主要作⽤是防⽌数据库备份或数据⽂件被偷了以后，偷数据库备份或⽂件的⼈在没有数据加密密钥的情况下是⽆法恢复或附加数据库的。我将⼀个encrypted database拷贝到另外的⼀台服务器上，视图attach这个database，但是报错如下：
上述错误表明这台服务器没有相应的证书。这从另⼀个⾓度也给我们做了提醒，如果是⼀个encrypted database的话，我不单要备份数据库，还要对证书做备份，如果我们⾃⾝证书丢失的话，也会开不开数据库。
  TDE使⽤数据加密密钥（DEK）进⾏加密。DEK是存在Master数据库中由服务主密钥保护，由的保护层级如下图所⽰。
使⽤TDE的四个步骤为：
1. 创建⼀个master key(Create a master key)
USE master;
GO
--在master数据库中创建⼀个master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='P@ssw0rd';
go
2. 创建或者获取⼀个由master key保护的证书(Create or obtain a certificate protected by the master key)
1. --使⽤masterkey创建证书MyServerCert
CREATE CERTIFICATE MyServerCert WITH SUBJECT ='My DEK Certificate';
go
3. 使⽤证书创建⼀个database密钥(Create a database encryption key and protect it by the certificate)
1. USE TESTDB2;
GO
--创建数据库加密key，使⽤MyServerCert这个证书加密
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
但是在创建完DATABASE ENCRYPTION KEY(DEK)以后，会报如下警告：
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate.If the certificate ever becomes unavailable or if you must
restore or attach the database on another server,you must have backups of both the certificate and the private key or you will not be able to open the database.
这是因为我们使⽤了证书来创建DEK，并且加密数据库，因此我们建议为证书做⼀个备份，否则如果证书被破坏以后，我们⾃⾝都⽆法打开数据库。证书备份的⽅法可以参考：，我的T-SQL所⽰：
BACKUP CERTIFICATE MyServerCert TO FILE='d:\storedcerts\MyServerCert'; --cert的保存地址
这⾥需要注意的是我们的MyServerCert没有由private key加密，⽽只是有master key加密。如果使⽤了private key的话还要为
private key进⾏备份。我们如果对MyServerCert进⾏备份private key操作会报错如下：
No decryption password should be provided because the private key of this certificate is encrypted by a master key.
4. 将数据库设置为TDE(Set the database to use encryption)
      　最后我们需要做的就是讲数据库设置为加密，T-SQL语句如下所⽰：
ALTER DATABASE TESTDB2 SET ENCRYPTION ON;
      　此时我们也可以右键TESTDB2->tasks->manage database encryption中查看加密设置，如下图所⽰：
我们也可以通过T-SQL语句来查看有哪些数据库进⾏了TDE加密，T-SQL语句如下：
View Code
/* The value 3 represents an encrypted state
on the database and transaction logs. */
SELECT DBName=DB_NAME(database_id),encryption_state
FROM sys.dm_database_encryption_keys
WHERE encryption_state =3;
GO
查询结果显⽰tempdb也被透明加密了，原因我们可以在msdn上到，我把原⽂摘出来：
Transparent Data Encryption and the tempdb System Database
The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This
might have a performance effect for unencrypted databases on the same instance of SQL Server. For more information about the tempdb system database, see .

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。