- 网站首页
- 数据库
- sql server
- SQLSERVER加密解密函数(非对称密钥 证书加密 对称密钥)
SQLSERVER加密解密函数(非对称密钥 证书加密 对称密钥)
ENCRYPTBYASYMKEY() --非对称密钥
ENCRYPTBYCERT() --证书加密
ENCRYPTBYKEY() --对称密钥
ENCRYPTBYPASSPHRASE() --通行短语（PassPhrase）加密
--非对称密钥包含数据库级的内部公钥和私钥，它可以用来加密和解密SQL Server数据库中的数据，它可以从外部文件或程序集中导入，也可以在SQL Server数据库中生成。它不像证书，不可以备份到文件。这意味着一旦在SQL Server中创建了它，没有非常简单的方法在其他用户数据库中重用相同的密钥。非对称密钥对于数据库加密属于高安全选项，因而需要更多的SQL Server资源。
一、非对称密钥
--我们看一组例子：
--示例一、创建非对称密钥
--创建非对称密钥使用如下命令:
--CREATE ASYMMETRIC KEY
--以下语句创建一个非对称密钥asymDemoKey
use [pratice]
go
CREATE ASYMMETRIC KEY asymDemoKey --创建非对称密钥名称
WITH ALGORITHM = RSA_512 --加密安全类型
ENCRYPTION BY PASSWORD = '123!' --密码
--示例二、查看当前数据库中的非对称密钥
--使用目录视图sys.asymmetric_keys(http://msdn.microsoft.com/en-us/library/ms188399.aspx)来查看。
--查看当前数据库中的非对称密钥
USE [pratice]
go
SELECT name, algorithm_desc, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys
--示例三、修改非对称密钥的私钥密码
--你可以使用带有ENCRYPTION BY PASSWORD和DECRYPTION BY PASSWORD选项的
--ALTER ASYMMETRIC KEY（http://technet.microsoft.com/en-us/library/ms189440.aspx）修改私钥的密码
--修改私钥密码
ALTER ASYMMETRIC KEY asymDemoKey--要修改的密钥名称
WITH PRIVATE KEY --私钥
(ENCRYPTION BY PASSWORD = '456',--指定新密码
DECRYPTION BY PASSWORD = '123!')--旧密码是用来解密的
--示例四、使用非对称密钥对数据进行加密和解密。由于同时需要公钥和密钥，在维护保密数据时使用非对称密钥来加密数据是非常安全的方式。但同时用于大数据集时将消耗更多的资源。不推荐使用非对称密钥对数据加密，但它仍然是一个选择。一旦将非对称密钥加到数据库，就可以用来加密和解密数据。
--用到以下两个sql函数:
--EncryptByAsymKey 加密数据。（http://technet.microsoft.com/en-us/library/ms186950.aspx）
--DecryptByAsymKey解密数据。（http://msdn.microsoft.com/en-us/library/ms189507.aspx）
--注意，在通过证书加密时，DecryptByAsymKey返回的是varbinary类型的加密数据。
--下面是一个例子：
use [pratice]
go
--创建需要加密的数据
Create Table BankUser
(PKID int primary key identity(1,1)
,UserNo varbinary(1000) null --一定要用二进制数据类型
,CurState datetime not null
)
go
insert into BankUser
(UserNo,CurState)
VALUES (EncryptByAsymKey(AsymKey_ID('asymDemoKey'),'137'),GETDATE())
--插入一条记录，字段UserNo存储了加密的号码值
go
--查看未加密的数据：
SELECT PKID,Curstate,
cast
(DecryptByAsymKey(AsymKey_ID('asymDemoKey'),UserNo,N'123')
as varchar(1000)) as UserNo --需要原始私钥
from BankUser
--示例五、删除非对称密钥
--命令：DROP ASYMMETRIC KEY 删除指定的非对称密钥( http://msdn.microsoft.com/en-us/library/ms188389.aspx)
--例子：
DROP ASYMMETRIC KEY asymDemoKey
DROP TABLE [dbo].[BankUser]
--小结：
--1、本文主要介绍非对称密钥的创建、删除、查看以及用它来修改私钥、进行数据的加密和解密。
--2、非对称密钥包含数据库级的内部公钥和私钥，它可以用来加密和解密SQL Server数据库中的数据。
--3、非对称密钥对于数据库加密属于高安全选项，因而需要更多的SQL Server资源，不推荐使用。
二、下文将主要介绍相对简单的并且广泛应用的对称密钥加密(Symmetric Key Encryption)
--SQL Server 2008引入透明数据加密（Transparent Data Encryption），即TDE，它允许你完全无需修改应用程序代码而对整个数据库加密。当一个用户数据库可用且已启用TDE时，在写入到磁盘时在页级实现加密。在数据页读入内存时解密。如果数据库文件或数据库备份被盗，没有用来加密的原始证书将无法访问。这几乎是SQL Server2008安全选项中最激动人心的功能了，有了它，我们至少可以将一些初级的恶意窥视拒之见外。
--下面的两个例子将展示如何启用和维护透明数据加密
View Code
--注意：一旦在数据库应用了加密，应该立刻备份服务器级证书！
--没有加密dek的证书，该数据库将无法打开，附加到别的服务器也无法使用，数据库文件亦不会被Hack。
--如果一个DBA想要合法地将数据库从一个SQL Server实例移动到另一个SQL Server实例，
--那么她应该首先备份服务器级证书，然后在新的SQL Server实例中创建证书。
--此时可以合法地备份、还原数据库或附加数据及日志文件。
View Code
--注意：如果删除DEK是SQL Server实例中最后一个使用TDE的用户定义数据库，
--在SQL Server实例重启后，tempdb也将变为不加密的状态。
--小结：
--1、本文主要介绍透明数据加密(TDE)的使用。
--2、对DEK的修改同时影响到tempdb数据库的加密状态。
--SQLServer 2008中的代码安全（七） 证书加密
--证书可以在数据库中加密和解密数据。
--证书包含密钥对、关于证书拥有者的信息、证书可用的开始和结束过期日期。
--证书同时包含公钥和密钥，前者用来加密，后者解密。SQL Server可以生成它自己的证书，也可以从外部文件或程序集载入。因为可以备份然后从文件中载入它们，证书比非对称密钥更易于移植，而非对称密钥却做不到。这意味着可以在数据库中方便地重用同一个证书。
--注意：证书和非对称密钥同样的消耗资源。
--我们看一组例子：
--示例一、创建数据库证书
View Code
--示例六、使用对称密钥对数据进行加密和解密。
--在前面的文章中，你已经看到打开用非对称密钥加密的对称密钥的演示，
--它分两个步骤，首先用OPEN SYMMETRIC KEY命令，然后是实际的DecryptByKey函数调用。
--SQL Server也提供了能够将这两个步骤合二为一的额外的解密函数：
--DECRYPTBYKEYAUTOASYMKEY（http://msdn.microsoft.com/en-us/library/ms365420.aspx）
--和DecryptByKeyAutoCert（http://msdn.microsoft.com/en-us/library/ms182559.aspx）
View Code
--小结：
--1、本文主要介绍证书的创建、删除、查看以及用它来修改加密方式、进行数据的加密和解密。
--2、证书加密和非对称密钥加密相对对称密钥加密更为消耗资源。
--下文将主要介绍SQL Server中最为令人鼓舞的透明数据加密(TDE)
--SQL Server 2008中的代码安全（六）：对称密钥加密
--证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据。
--而对称密钥相对简单，它们包含一个同时用来加密和解密的密钥。
--困此，使用对称密钥加密数据更快，并且用在大数据时更加合适。
--尽管复杂度是考虑使用它的因素，但它仍然是一个很好的加密数据的选择。
--示例一、创建对称密钥
--
--对称密钥的特性是：在数据库会话中使用它对数据进行加密和解密前必须首先打开。
--
--创建对称密钥使用如下命令:CREATE SYMMETRIC KEY 创建对称密钥。
--（http://msdn.microsoft.com/en-us/library/ms188357.aspx）
USE [pratice]
go
-- 创建一个用于加密对称密钥的非对称密钥
CREATE ASYMMETRIC KEY symDemoKey --名称
WITH ALGORITHM = RSA_512 --加密算法
ENCRYPTION BY PASSWORD ='TestSYM456!'
--密码
-- 创建一个对称密钥
CREATE SYMMETRIC KEY sym_Demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY symDemoKey
--示例二、查看当前数据库中的对称密钥
--
--使用目录视图sys.symmetric_keys(http://msdn.microsoft.com/en-us/library/ms189446.aspx)来查看。
--查看当前数据库中的非对称密钥
USE [pratice]
go
SELECT name, algorithm_desc
FROM sys.symmetric_keys
--示例三、修改非对称密钥的加密方式
--
--你可以使用ALTER SYMMETRIC KEY（http://technet.microsoft.com/en-us/library/ms189440.aspx）命令修改
--对称密钥的加密方式。
--但执行前必须使用OPEN SYMMETRIC KEY(http://msdn.microsoft.com/en-us/library/ms190499.aspx)命令打开它。
USE [pratice]
go
--先用私钥密码打开对称密钥
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY ASYMMETRIC KEY symDemoKey
WITH PASSWORD ='TestSYM456!'
--打开之后，先增加密码加密，取代原密钥
ALTER SYMMETRIC KEY sym_Demo
ADD ENCRYPTION BY PASSWORD ='newnew!456'
--再删除非对称密钥加密
ALTER SYMMETRIC KEY sym_Demo
DROP ENCRYPTION BY ASYMMETRIC KEY symDemoKey
--完成操作后，关闭对称密钥
CLOSE SYMMETRIC KEY sym_Demo
--示例四、使用对称密钥对数据进行加密和解密。
--
--1、为了使用对称密钥对数据进行加密，必须首先打开它，
--然后使用函数EncryptByKey 加密数据。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)
--2、使用DecryptByKey来解密使用对称密钥加密的数据。注意DecryptByKey不像EncryptByKey，
--无须使用对称密钥GUID。因此，为了解密，必须打开正确的对称密钥会话，否则会显示null。
--
--下面是一个例子：
USE [pratice]
GO
--DROP TABLE [dbo].[PWDQuestion]
--GO
--创建测试数据表，用于对称加密
CREATE TABLE dbo.PWDQuestion
(
CustomerID INT NOT NULL PRIMARY KEY ,
PasswordHintQuestion NVARCHAR(300) NOT NULL ,
PasswordHintAnswer VARBINARY(200) NOT NULL
)
GO
--插入加密数据
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
INSERT dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
VALUES ( 12, '您出生的医院名称？', ENCRYPTBYKEY(KEY_GUID('sym_Demo '), '杭州市一') )
CLOSE SYMMETRIC KEY sym_Demo
--查看未加密的数据：
--解密数据
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
SELECT CustomerID, PasswordHintQuestion,
CAST(DECRYPTBYKEY(PasswordHintAnswer) AS VARCHAR(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 12
--打开后切记关闭！！！
CLOSE SYMMETRIC KEY sym_Demo
--不打开直接读取
SELECT CustomerID, PasswordHintQuestion,
CAST(DECRYPTBYKEY(PasswordHintAnswer) AS VARCHAR(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID = 12
--至此，好像已经大功告成了，别，千万别高兴得太早！
--这里有个问题，如果恶意用户不知道CustomerID＝13的PasswordHintAnswer列的真实值，
--但知道CustomerID＝14的PasswordHintAnswer列的真实值,
--则完全可以通过恶意替换PasswordHintAnswer列而绕过加密！！
--此时，我们索性连CustomerID列作为验证列也一起加密，以绝后患 !
--
--注意：加密的验证列也可以由另一个相关表的列作为参数传入。
--示例五、删除对称密钥
--命令：DROP SYMMETRIC KEY 删除指定的对称密钥( http://technet.microsoft.com/en-us/library/ms182698.aspx)
--
--例子：
DROP SYMMETRIC KEY sym_Demo
--注意：如果加密密钥打开没有关闭，则drop失败。
--小结：
--
--1、本文主要介绍对称密钥的创建、删除、查看以及用它来修改加密方式、进行数据的加密和解密。
--
--2、对称密钥的特性是：在数据库会话中使用它对数据进行加密和解密前必须首先打开。
--
--3、对称密钥可用于大数据的加密。
-------------------------------------------------------------------------------------------
--SQL Server 2008中的代码安全（四）：主密钥
--在SQL Server中的加密由层次结构形式进行处理以提供多级别的安全。
--SQL Server包含两个用于加密数据的密钥类型。如下图：
--1、服务器主密钥（Service Master Key）,位于层次结构的最顶端，并且在安装SQL Server时自动创建，
--用于加密系统数据、链接的服务器登录名以及数据库主密钥。
--在第一次通过SQL Server使用服务主密钥来加密证书、数据库主密钥或链接的服务器主密码时，
--服务主密钥会自动生成，并且使用SQL Server服务账户的Windows证书来生成它。
--如果必须改变SQL Server服务账号，微软建议使用SQL Server配置管理器，
--因为这个工具将执行生成新服务主密钥需要的合适的解密和加密方法，而且可以使加密层次结构保持完整。
--服务主密钥也用于加密其下的数据库主密钥。
--
--2、数据库主密钥（Database Master Key）,用于加密证书，以及非对称密钥和对称密钥。
--所有数据库都可以只包含一个数据库主密钥，在创建它时，通过服务主密钥对其加密。
--创建非对称密钥时，可以决定在加密非对称密钥对应的私钥是否包含密码。
--如果示包含密码，将使用数据库主密钥来加密私钥。
--
--我们看一组例子：
--
--示例一、备份及还原服务主密钥
--例一、备份及还原服务主密钥
--
--用到以下两个sql命令:
--
--BACKUP SERVICE MASTER KEY 导出服务主密钥。（http://msdn.microsoft.com/zh-cn/library/ms190337.aspx）
--
--RESTORE SERVICE MASTER KEY从备份文件中导入服务主密钥。（http://msdn.microsoft.com/zh-cn/library/ms187972.aspx）
--以下语句备份服务主密钥到C:\SqlBackup\SMK.bak
BACKUP SERVICE MASTER KEY
TO FILE='D:\SMK.bak'
ENCRYPTION BY PASSWORD ='123!1AB'
----注意该密码可以使用单引号
go
--恢复服务主密钥
RESTORE SERVICE MASTER KEY
FROM FILE='D:\SMK.bak'
DECRYPTION BY PASSWORD ='123!1AB'
go
--如果该密钥没有实际变化，而执行密钥恢复时，会收到提示：
--The old and new master keys are identical. No data re-encryption is required.
--示例二、创建、再生成和删除数据库主密钥
--
--用到以下两个sql命令:
--
--CREATE MASTER KEY 创建数据库主密钥（http://technet.microsoft.com/zh-cn/library/ms174382.aspx）
--
--ALTER MASTER KEY 重新生成数据库主密钥（http://msdn.microsoft.com/en-us/library/ms186937%28SQL.90%29.aspx）
--
--DROP MASTER KEY 删除数据库主密钥（http://msdn.microsoft.com/en-us/library/ms180071.aspx）
--
--当数据库主密钥被显式创建时，会同时自动生成一个额外生成的安全层，
--用于加密数据库中的新证书和非对称密钥，更进一步保护已加密的数据。
IF NOT EXISTS ( SELECT name
FROM sys.databases
WHERE name = 'BookStore' )
BEGIN
CREATE DATABASE BookStore
END
GO
USE BookStore
GO
--创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
go
USE BookStore
GO
--重新生成数据库主密钥
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD ='password'
--删除数据库主密钥
USE BookStore
GO
DROP MASTER KEY
--注意：如果该数据库主密钥仍然被其他数据库对象使用，则不能被删除，这点与架构类似。
--
--同时一旦创建数据库主密钥，就立刻备份它是一个好的习惯。
--备份数据库主密钥
USE BookStore
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MagneticFields!'
GO
BACKUP MASTER KEY TO FILE='D:\BookStore_Master_Key.BAK'
ENCRYPTION BY PASSWORD ='4D280837!!!'
--恢复数据库主密钥
RESTORE MASTER KEY FROM FILE='D:\BookStore_Master_Key.BAK'
DECRYPTION BY PASSWORD ='4D280837!!!'
ENCRYPTION BY PASSWORD ='MagneticFields!'
--与服务主密钥类似，如果没有修改，则会收到如下提示：
--The old and new master keys are identical. No data re-encryption is required.
--示例三、从数据库主密钥删除服务主密钥
--
--当一个数据库主密钥被创建时，它被默认使用两种方式加密：
--服务主密钥和被使用CREATE MASTER KEY 命令中使用的密码。
--如果你不想使用服务主密码加密数据库主密钥
--（这种情况下，拥有sysadmin特权的login在不知道数据库主密钥的前提下将不能访问加密数据），
--你可以使用ALTER MASTER KEY 命令删除服务主密钥。
--
--简略语法如下：
ALTER MASTER KEY
ADD ENCRYPTION BY SERVICE MASTER KEY |
DROP ENCRYPTION BY SERVICE MASTER KEY
--由于服务主密钥允许拥有足够许可（如sysadmin）的用户自动使用数据库主密钥解密，
--因此，一旦删除了服务主密钥的加密，而再想修改数据库主密钥时，你必须使用一个新的命令访问它。
--OPEN MASTER KEY, 语法如下：
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
--下面是一个例子：
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
--一旦执行，任何数据库主密钥的修改需要使用OPEN MASTER KEY的口令访问，
--这样是为了重新应用服务主密钥的加密
OPEN MASTER KEY DECRYPTION BY PASSWORD ='123456!'
--一旦服务主密钥被用于加密数据库主密钥，数据库主密钥不再需要被显式打开或关闭。
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
--关闭数据库主密钥
CLOSE MASTER KEY
--小结：
--
--1、本文主要介绍服务主密钥的备份与还原，数据库的主密钥的创建、重新生成、删除和备份、还原。
--
--2、一旦创建主密钥，立刻备份它是一个很好的习惯。
------------------------------------------------------------------------------------
--SQL Server 2008中的代码安全（三）：通过PassPhrase加密
--导读：本文主要涉及EncryptByPassPhrase和DecryptByPassPhrase函数进行通行短语（PassPhrase）加密。
--前言：
--
--在SQL Server 2005和SQL Server 2008之前。
--如果希望加密敏感数据，如财务信息、工资或身份证号，必须借助外部应用程序或算法。
--SQL Server 2005引入内建数据加密的能力，使用证书、密钥和系统函数的组合来完成。
--
--与数字证书类似。SQL Server 证书包括公钥和私钥这一对密钥，它们都用来加密和解密数据。
--SQL Server也拥有创建非对称密钥和对称密钥对象的能力。非对称密钥（asymmetric key）与证书相似，
--公钥用来加密数据库，私钥用来解密数据。
--非对称密钥和证书都提供了强大的加密强度。但在完成复杂的加密|解密过程中具有更多的性能开销。
--更适合对大量数据进行加密，且具有较低性能开销的解决方案是对称密钥（symmetric key），
--它是对相同数据进行加密和解密的一个密钥。
--
--SQL Server允许将这些加密能力放到加密层次结构中。
--当安装了SQL Server后，在数据库master中创建名为服务主密钥的服务器级别证书，
--并将其默绑定到SQL Server服务账号登录名。
--服务主密钥用来加密所有其他数据库证书和创建在SQL Server实例中的密钥。
--另外，你也可以在用户数据库中创建数据库主密钥（Database Master Key）,
--它可以用来加密数据库证书和密钥。
--
--在SQL Server 2008中，微软引入了透明数据加密(TDE),它对整个数据库进行加密，
--而不需要修改任何访问它的应用程序。数据、日志文件和相关的数据库备份都是加密的。
--假如数据库被偷，如果没有数据库加密密钥（DEK）是不能访问数据的。
--本文及后面几篇文章将会举例说明。
--在SQL Server 2008中，还引入了对可扩展密钥管理（EKM）的支持，
--也就意味着SQL Server可以使用硬件安全模块（HSM）来存储和和管理加密密钥。
--HSM可以减少数据和实际的加密密钥耦合。
--此部分内容共分六篇文章：
--1、通过PassPhrase加密
--2、主密钥
--3、非对称密钥加密
--4、对称密钥加密
--5、证书加密
--6、透明数据加密
--一、通过通行短语(PassPhrase)加密
--
--对于不涉及证书及密钥的应急的数据加密，可以直接基于用户提供的密码来加密和解密数据。
--通行短语(PassPhrase)是允许存在空格的密码。
--这个PassPhrase不会存储在数据库中，因而也就意味着不会被使用存储的系统数据“破解”。
--同时，可以使用空格创建一个长的、易于记忆的句子来加密和解密敏感数据。
--我们需要了解的一对函数是ENCRYPTBYPASSPHRASE（http://technet.microsoft.com/zh-cn/library/ms190357.aspx）和DECRYPTBYPASSPHRASE（http://technet.microsoft.com/zh-cn/library/ms188910.aspx）
--
--这一对函数必须使用相同的参数。
--
--我们看一个示例：
USE [pratice]
go
-- Table used to store the encrypted data
-- for the purposes of this recipe
--DROP TABLE SecretInfo
--GO
CREATE TABLE SecretInfo
(
MySecret varbinary(max) NOT NULL --一定要二进制数据格式
)
GO
INSERT SecretInfo ( MySecret ) SELECT ENCRYPTBYPASSPHRASE('123456','你好啊')
SELECT MySecret FROM SecretInfo
--想知道原文本的内容，使用以下语句：
SELECT CAST(DECRYPTBYPASSPHRASE('123456', MySecret) AS VARCHAR(MAX))
FROM SecretInfo
--注意：
--
--1、使用通行短语进行加密数据，不用担心sysadmin服务器角色成员读取数据
--（在后面文章会看到，服务器角色成员sysadmin拥有读取其他形式的加密数据的内在权限。)
--
--
--2、假定没有将密码存储在表中或在任何模块（存储）