SQLSERVER加密解密函数(非对称密钥 证书加密 对称密钥)使用方法代码_MsSql_

--SQLSERVER中的加密函数 2013-7-11
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安全选项中最激动人心的功能了，有了它，我们至少可以将一些初级的恶意窥视拒之见外。
--下面的两个例子将展示如何启用和维护透明数据加密
--示例一、启用透明加密(TDE)

USE Master
GO
--删除旧主密钥
--Drop MASTER KEY
--GO
--创建主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='B19ACE32-AB68-4589-81AE-010E9092FC6B'
GO
--创建证书,用于透明数据加密
CREATE CERTIFICATE TDE_Server_Certificate
WITH SUBJECT = 'Server-level cert for TDE'
GO

USE [pratice]
GO
--第一步：现在开始透明加密
CREATE DATABASE ENCRYPTION KEY--创建数据库加密密钥
    WITH ALGORITHM = TRIPLE_DES_3KEY--加密方式
    ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate--使用服务器级证书加密
GO
/*
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.
*/

--第二步：打开加密开关
ALTER DATABASE [pratice] SET ENCRYPTION ON
GO

--查看数据库是否加密
SELECT  is_encrypted FROM    sys.databases
WHERE   name = 'pratice'

--注意：一旦在数据库应用了加密，应该立刻备份服务器级证书！

--没有加密dek的证书，该数据库将无法打开，附加到别的服务器也无法使用，数据库文件亦不会被Hack。
--如果一个DBA想要合法地将数据库从一个SQL Server实例移动到另一个SQL Server实例，
--那么她应该首先备份服务器级证书，然后在新的SQL Server实例中创建证书。
--此时可以合法地备份、还原数据库或附加数据及日志文件。

--示例二、管理和移除透明加密(TDE)
USE [pratice]
GO
--修改加密算法
ALTER DATABASE  ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_128
Go

SELECT  DB_NAME(database_id) databasenm,
        CASE encryption_state
          WHEN 0 THEN 'No encryption'
          WHEN 1 THEN 'Unencrypted'
          WHEN 2 THEN 'Encryption in progress'
          WHEN 3 THEN 'Encrypted'
          WHEN 4 THEN 'Key change in progress'
          WHEN 5 THEN 'Decryption in progress'
        END encryption_state, key_algorithm, key_length
FROM    sys.dm_database_encryption_keys

/*
对所有用户数据库的加密处理也包含对tempdb的处理 ，因为表连接，临时表都要用到tempdb
databasenm encryption_state key_algorithm key_length
tempdb Encrypted AES 256
DB_Encrypt_Demo Encrypted AES 128
*/

--除了更改dek的算法，我们也可以更改用来加密数据库的服务器级证书（该证书应该定期更改）

USE master
GO
CREATE CERTIFICATE TDE_Server_Certificate_V2
WITH SUBJECT = 'Server-level cert for TDE V2'
GO
USE [pratice]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate_V2
--用新证书修改DEK

--移除数据库透明加密
ALTER DATABASE [pratice]
SET ENCRYPTION OFF
GO

--移除TDE后，可以删除DEK
USE [pratice]
GO
DROP DATABASE ENCRYPTION KEY
Go

--注意：如果删除DEK是SQL Server实例中最后一个使用TDE的用户定义数据库，
--在SQL Server实例重启后，tempdb也将变为不加密的状态。
--
--小结：
--
--1、本文主要介绍透明数据加密(TDE)的使用。
--
--2、对DEK的修改同时影响到tempdb数据库的加密状态。
------------------------------------------------------------------------------------------------

--SQLServer 2008中的代码安全（七） 证书加密
--
--证书可以在数据库中加密和解密数据。
--证书包含密钥对、关于证书拥有者的信息、证书可用的开始和结束过期日期。
--
--证书同时包含公钥和密钥，前者用来加密，后者解密。SQL Server可以生成它自己的证书，
--也可以从外部文件或程序集载入。因为可以备份然后从文件中载入它们，证书比非对称密钥更易于移植，
--而非对称密钥却做不到。这意味着可以在数据库中方便地重用同一个证书。

--注意：证书和非对称密钥同样的消耗资源。

--我们看一组例子：
--
--示例一、创建数据库证书

--创建数据库证书:CREATE SYMMETRIC KEY （http://msdn.microsoft.com/en-us/library/ms187798.aspx）
USE [pratice]
GO
--创建证书
CREATE CERTIFICATE cert_Demo --证书名称
ENCRYPTION BY PASSWORD = '123!!!' --加密证书的密码
WITH SUBJECT = 'DB_Encrypt_Demo Database Encryption Certificate',--证书主题
START_DATE = '3/14/2012', EXPIRY_DATE = '10/20/2016'--起止日期
GO

--示例二、查看数据库中的证书

--使用目录视图sys.certificates(http://msdn.microsoft.com/en-us/library/ms189774.aspx)来查看。

--查看当前数据库中的证书
USE [pratice]
go
--查看证书
SELECT  name, pvt_key_encryption_type_desc, issuer_name
FROM    sys.certificates

--示例三、备份和还原证书

--创建证书后，也可以使用BACKUP CERTIFICATE（http://msdn.microsoft.com/en-us/library/ms178578.aspx）
--命令备份到文件，为了安全地保存或在其他数据库中还原它。
--备份证书
BACKUP CERTIFICATE cert_Demo
TO FILE = 'D:\certDemo.BAK'--证书备份路径,用来加密
WITH PRIVATE KEY (FILE='D:\certDemoPK.BAK',--证书私钥文件路径，用来解密
ENCRYPTION BY PASSWORD = '465!!!',--加密私钥密码
DECRYPTION BY PASSWORD = '123!!!' )--解密私钥密码

--备份后，可以在其他数据库中使用这个证书，或使用DROP CERTIFICATE命令删除它。
DROP CERTIFICATE cert_Demo
GO
--从备份文件中还原证书到数据库中
CREATE CERTIFICATE cert_Demo
FROM FILE = 'D:\\certDemo.BAK'
WITH PRIVATE KEY (FILE = 'D:\certDemoPK.BAK',
DECRYPTION BY PASSWORD = '456!!!' ,--解密私钥密码
ENCRYPTION BY PASSWORD = '123!!!')--加密私钥密码

--示例四、管理证书的私钥

--使用ALTER CERTIFICATE( http://msdn.microsoft.com/en-us/library/ms189511.aspx)
--命令为证书增加或删除私钥。
--这个命令允许删除私钥（默认通过数据库主密钥加密）、增加私钥或修改私钥的密码。

--从证书中删除私钥
ALTER CERTIFICATE cert_Demo
REMOVE PRIVATE KEY

--从备份文件为既有证书重新增加私钥
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY
(FILE = 'D:\certDemoPK.BAK',
DECRYPTION BY PASSWORD = '1234GH!!!' ,--解密私钥密码
ENCRYPTION BY PASSWORD = '123!!!')--加密私钥密码

--修改既有私钥的密码
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = '123!!!',
ENCRYPTION BY PASSWORD = '789!!!13E')

--示例五、使用证书加密和解密。
--
--使用函数EncryptByCert加密数据。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)
USE [pratice]
GO
CREATE TABLE PWDQuestion
(
  CustomerID INT ,
  PasswordHintQuestion NVARCHAR(200) ,
  PasswordHintAnswer NVARCHAR(200)
)
--插入测试数据
INSERT  dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
VALUES  ( 10, '您出生的医院名称?', ENCRYPTBYCERT(CERT_ID('cert_Demo'), '北京四合院家中') )

--查看密文
SELECT [PasswordHintQuestion], CAST(PasswordHintAnswer AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 10

--查看原文
SELECT  PasswordHintQuestion,
        CAST(DECRYPTBYCERT(CERT_ID('cert_Demo'), PasswordHintAnswer,
                           N'789!!!13E') AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 10

 

--示例六、使用对称密钥对数据进行加密和解密。

--在前面的文章中，你已经看到打开用非对称密钥加密的对称密钥的演示，
--它分两个步骤，首先用OPEN SYMMETRIC KEY命令，然后是实际的DecryptByKey函数调用。
--SQL Server也提供了能够将这两个步骤合二为一的额外的解密函数：
--DECRYPTBYKEYAUTOASYMKEY（http://msdn.microsoft.com/en-us/library/ms365420.aspx）
--和DecryptByKeyAutoCert（http://msdn.microsoft.com/en-us/library/ms

提示： 本文由神整理自网络，如有侵权请联系本站删除！
本站声明：
1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！