SQL Server 加密案例解析分分快三全天计划网站

作者:分分快三全天计划网站

一、概述

加密是一种安全措施,有时候甚至是法律要求。作为攻破Windows系统的最后一道防线,通过加密可以保证在没有密钥的情况下获取备份或者物理介质变得毫无意义。

 

[sql] view plain copy

--==================(III)绕过加密数据的攻击=====================
--1.)攻击者使用其它数据行的加密数据替换某一行的数据
SELECT * FROM EmpSalary

案例1.备份加密

 通过使用证书加密备份,如果需要在新的服务器上还原备份,先还原数据库主密钥和证书,然后就可以自动解密还原备份。

--1.备份数据库主密钥
BACKUP MASTER KEY TO FILE = 'D:DECRYPTIONMasterKey' -----主密钥私钥文件
    ENCRYPTION BY PASSWORD = 'MasterKey';             -----加密主密钥私钥文件
GO 
--2.备份证书
BACKUP CERTIFICATE MyCerts TO FILE = 'D:DECRYPTIONMyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'D:DECRYPTIONMyCertsKey' ,   ----证书私钥文件
                       ENCRYPTION BY PASSWORD = 'MyCerts123' ); ----对私钥文件加密
GO

--3.备份数据库
USE MASTER
GO
BACKUP DATABASE [EncryDb]    
TO DISK = N'D:BackDBEncryDb.bak'    
WITH  COMPRESSION, stats = 10,   
    ENCRYPTION     
    (    
    ALGORITHM = AES_256,  
    SERVER CERTIFICATE = MyCerts
    )  
GO 
---4.在新服务器上还原数据库主密钥
USE MASTER
GO
RESTORE MASTER KEY  
FROM FILE = 'C:DECRYPTIONMasterKey' 
DECRYPTION BY PASSWORD = N'MasterKey'    ---解密主密钥文件
ENCRYPTION BY PASSWORD = N'MasterKey123'  ---加密导出的主密钥
--force;        ----指定即使当前数据库主密钥未打开,或者 SQL Server 无法对使用该主密钥加密的某些私钥进行解密,RESTORE 过程也应继续执行。
GO 
---5.在新服务器上还原证书
USE MASTER
GO
---需要先打开数据库主密钥
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
CREATE CERTIFICATE MyCerts FROM FILE = 'C:DECRYPTIONMyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'C:DECRYPTIONMyCertsKey' ,    ----证书私钥文件
    DECRYPTION BY PASSWORD = 'MyCerts123' );                  ----解密私钥文件

---6.在新服务器上还原数据库
USE [master]  
GO  
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
RESTORE DATABASE [EncryDb]  
FROM  DISK = N'C:DECRYPTIONEncryDb.bak'   
WITH  FILE = 1,  
MOVE N'EncryDb' TO N'C:DECRYPTIONEncryDb.mdf',     
MOVE N'EncryDb_log' TO N'C:DECRYPTIONEncryDb_log.ldf',    
NOUNLOAD,  STATS = 5  
GO 

注意:在master数据库中创建数据库主密钥和证书。

如果没有还原数据库主密钥和证书直接还原数据库报错如下

分分快三全天计划网站 1

2、主密钥

--5.)删除证书私钥
ALTER CERTIFICATE cert_TestCert1
    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_TestCert1'), @cleartext)
SELECT @cipher
SELECT CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert1'), @cipher, N'P@ssw0rd')) AS [ClearText]

案例4.数据列加密

CREATE DATABASE TestDb
GO
USE [TestDb]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey'
GO
---创建证书
CREATE CERTIFICATE MyCerts 
   WITH SUBJECT = 'BackDB Records', 
   EXPIRY_DATE = '10/31/2099';            ----证书过期时间,不指定开始时间默认开始时间为当前时间
GO
----创建对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE MyCerts;

----创建测试表
USE TestDb;
GO
DROP TABLE Test
GO
CREATE TABLE Test
(Id INT NOT NULL,
Name NVARCHAR(30) NOT NULL,
EncryptionName varbinary(500) null
);
GO
INSERT INTO Test(Id,Name) VALUES(1,'aa'),(2,'bb');
GO

----加密列
OPEN SYMMETRIC KEY SymmetricByCert 
     DECRYPTION BY CERTIFICATE MyCerts;  
UPDATE Test
SET EncryptionName= EncryptByKey(Key_GUID('SymmetricByCert'), Name);  
GO

SELECT * FROM TEST

GO
---解密查询
OPEN SYMMETRIC KEY SymmetricByCert 
     DECRYPTION BY CERTIFICATE MyCerts;  
select Id,
Name,
EncryptionName,
convert(nvarchar(30), DecryptByKey(EncryptionName)) ConvertEncryptionName  ----nvarchar(30)值和明文字段类型长度保持一致 
from test;

分分快三全天计划网站 2

注意:

1.加密列的数据类型必须是nvarchar数据类型,否则解密后的结果不会和明文一致。

2.解密过程定义的数据类型需要和明文的数据类型保持一致,包括长度也必须一致。

 

 

 

公钥和私钥的解释参考:http://blog.csdn.net/tanyujing/article/details/17348321

加密参考:

使用对称密钥加密数据:

 

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

《欢迎交流讨论》

 

[sql] view plain copy

[过程]
过程一共分为4个部分

2.安全对象范围:数据库

包含以下安全对象:

  • 用户
  • 角色
  • 应用程序角色
  • 程序集
  • 消息类型
  • 路由
  • 服务
  • 远程服务绑定
  • 全文目录
  • 证书
  • 非对称密钥
  • 对称密钥
  • 约定
  • 架构

 

/*
[课程]使用数据库加密保护敏感数据

4.非对称密钥

“非对称密钥”是数据库级的安全对象实体。该实体的默认格式包含公钥和私钥。当未使用 FROM 子句执行时,CREATE ASYMMETRIC KEY 会生成新的密钥对。当使用 FROM 子句执行时,CREATE ASYMMETRIC KEY 会从文件中导入密钥对,或从程序集中导入公钥。
默认情况下,私钥受数据库主密钥保护。如果尚未创建任何数据库主密钥,则需要使用密码保护私钥。如果不存在数据库主密钥,则可以选择性地使用密码。

通常使用RSA加密算法,RSA_512、RSA_1024、RSA_2048。

---1.创建非对称密钥;非对称密钥可以由密码、数据库主密钥、EKM模块加密
--使用密码加密
CREATE ASYMMETRIC KEY AsymmetricByPW
    WITH ALGORITHM = RSA_2048   ---使用RSA_2048加密算法
    ENCRYPTION BY PASSWORD = 'AsymmetricByPW111'; 
GO
--2.通过文件创建非对称密钥
CREATE ASYMMETRIC KEY AsymmetricByFile 
    AUTHORIZATION Christina ----授予Christina用户使用该非对称密钥
    FROM FILE = 'c:PacSalesManagersChristinaCerts.tmp'  
    ENCRYPTION BY PASSWORD = 'AsymmetricByFile111';
GO
---3.使用数据库主密钥加密
CREATE ASYMMETRIC KEY AsymmetricByMasterKey
    WITH ALGORITHM = RSA_2048;   ---使用RSA_2048加密算法

---4.删除非对称密钥
DROP ASYMMETRIC KEY AsymmetricByPW
GO

如果需要正常的将一个数据库从一个实例上移动到另一个实例上,那么可以备份服务器级别的证书,并且在另一个实例上创建这个证书,这样就可以还原TDE的备份或者附加数据文件和日志文件了

--3.)查看数据库主密钥的信息
USE Northwind
SELECT * FROM sys.symmetric_keys
GO

案例2.TDE透明数据库加密

 通过使用证书加密数据库

 分分快三全天计划网站 3

步骤操作如下:

  • 创建主密钥
  • 创建或获取由主密钥保护的证书
  • 创建数据库加密密钥并通过此证书保护该密钥
  • 将数据库设置为使用加密

    USE EncryDb; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyCerts; GO USE EncryDb; GO ---启用数据库加密 ALTER DATABASE EncryDb SET ENCRYPTION ON; GO USE EncryDb; GO ---禁用数据库加密 ALTER DATABASE EncryDb SET ENCRYPTION OFF; GO

1.“透明数据加密”(TDE) 可对数据和日志文件执行实时 I/O 加密和解密。这种加密使用数据库加密密钥 (DEK),该密钥存储在数据库引导记录中以供恢复时使用。DEK 是使用存储在服务器的 master 数据库中的证书保护的对称密钥,或者是由 EKM 模块保护的非对称密钥。TDE 保护“处于休眠状态”的数据,即数据和日志文件。它提供了遵从许多法律、法规和各个行业建立的准则的能力。软件开发人员籍此可以使用 AES 和 3DES 加密算法来加密数据,且无需更改现有的应用程序。
2.启用 TDE 时,应该立即备份证书和与证书相关联的私钥。如果证书变为不可用,或者如果必须在另一台服务器上还原或附加数据库,则必须同时具有证书和私钥的备份,否则将无法打开该数据库。即使不再对数据库启用 TDE,也应该保留加密证书或非对称密钥。即使数据库没有加密,数据库加密密钥可能也保留在数据库中,执行某些操作时可能需要访问这些加密密钥。
3.数据库文件的加密在页级执行。已加密数据库中的页在写入磁盘之前会进行加密,在读入内存时会进行解密。TDE 不会增加已加密数据库的大小。

注意:在master数据库中创建数据库主密钥和证书。

测试在新的数据库中还原TDE透明加密数据库

---备份数据库
USE MASTER
GO
BACKUP DATABASE [EncryDb]    
TO DISK = N'C:DECRYPTIONEncryDb0122.bak'    
WITH  COMPRESSION, stats = 10  

---在新服务器中还原数据库主密钥
USE MASTER
GO
RESTORE MASTER KEY  
FROM FILE = 'C:DECRYPTIONMasterKey' 
DECRYPTION BY PASSWORD = N'MasterKey'    ---解密主密钥文件
ENCRYPTION BY PASSWORD = N'MasterKey123'  ---加密导出的主密钥
--force;        ----指定即使当前数据库主密钥未打开,或者 SQL Server 无法对使用该主密钥加密的某些私钥进行解密,RESTORE 过程也应继续执行。
GO 
---在新服务器上还原证书
USE MASTER
GO
---需要先打开数据库主密钥
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
CREATE CERTIFICATE MyCerts FROM FILE = 'C:DECRYPTIONMyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'C:DECRYPTIONMyCertsKey' ,    ----证书私钥文件
    DECRYPTION BY PASSWORD = 'MyCerts123' );                  ----解密私钥文件


---还原备份
USE [master]  
GO  
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  
GO 
RESTORE DATABASE [EncryDb_20180122]  
FROM  DISK = N'C:DECRYPTIONEncryDb0122.bak'   
WITH  FILE = 1,  
MOVE N'EncryDb' TO N'C:DECRYPTIONEncryDb0122.mdf',     
MOVE N'EncryDb_log' TO N'C:DECRYPTIONEncryDb0122_log.ldf',    
NOUNLOAD,  STATS = 5  
GO 

注意:经测试发现只有同数据库版本可以还原成功,在搞版本中还原提示会提示错误页,比如2014版本加密的数据库在2016版本中还原保持如下:

分分快三全天计划网站 4

第二个是数据库主密钥,位于加密层次结构中SQL Server安全性的其它层,用来加密数据库证书、非对称密钥、对称密钥。所有数据库都可以只包含一个数据库主密钥,创建时通过服务主密钥对其加密。创建非对称密钥时,可以决定在加密非对称密钥对中的私钥时,是否包含密码。如果不包含密码,将使用数据库主密钥来加密私钥。

UPDATE EmpSalary SET Salary

    (SELECT Salary FROM EmpSalary WHERE EmpID = 1)
    WHERE EmpID = 3

--5.)被篡改后的加密了的数据列变成无效
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = 'P@ssw0rd'
SELECT EmpID, Title, CAST(DecryptBykey(Salary, 1, CAST(EmpID AS VARCHAR(3))) AS VARCHAR(20)) AS Salary FROM EmpSalary
CLOSE SYMMETRIC KEY sym_Salary

/*
[课程]使用数据库加密保护敏感数据

DEMO 3
使用证书签署存储过程

[过程]
过程一共分为2个部分

*/

--==================(I)示例准备=====================
--1.)创建数据库主密钥
USE Northwind
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

--2.)创建签署存储过程所需要的证书
CREATE CERTIFICATE cert_Products
    WITH SUBJECT = 'Products Sign',
    START_DATE = '2006/1/1',
    EXPIRY_DATE = '2008/1/1'

--3.)创建SPDeveloper登录帐户和用户,该用户创建访问Products表的存储过程
CREATE LOGIN [SPDeveloper] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[Northwind]
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 TOP 5 * FROM dbo.Products
GO

REVERT
SELECT USER

--4.)创建普通用户Jerry
CREATE LOGIN jerry WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[Northwind]
CREATE USER jerry FOR LOGIN jerry

--==================(II)使用证书签署存储过程=====================
--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

--4.)以Jerry的身份重新执行存储过程,成功,
--     因为存储过程将以ProductsReader的权限上下文执行
EXECUTE AS USER = 'jerry'
SELECT  USER
GO
EXECUTE products.usp_Products

问题:如果没有使用密钥,会出现什么样的安全问题呢?
回答:只有在数据库中有需要保护的敏感数据时,才使用数据库加密,所以如果在数据库中没有这样的数据信息,那么也就可以不启用数据库加密,也就不用创建相应的密钥了。

问题:当使用OPEN SYMMETRIC KEY打开多个对称密钥时,用函数DecryptByKey解密时SQL 2005会使用那个密钥呢?
回答:使用对称密钥加密的密文起始位置表明了生成该密文对称密钥的GUID,所以它会自动根据密钥的GUID来选择相应的密钥进行解密。

问题:如何备份对称密钥和非对称密钥?
回答:对称密钥和非对称密钥是数据库级别的对象,虽然没有提供直接备份它们的机制,但是可以通过对数据库备份来实现对密钥的备份。另外在生成对称密钥时可以使用密码短语来生成密钥,这样保存了密码短语就备份了相应的对称密钥。对于非对称密钥可以使用有强名称文件来生成密钥对,只要保存了该文件也就备份了相应的非对称密钥。

1.安全对象范围:服务器

包含以下安全对象:

  • 端点
  • 登录帐户
  • 数据库

三、SQL Server还拥有创建非对称密钥和对称密钥的能力。非对称密钥与证书相似,公钥用来加密数据库,私钥用来解密数据。非对称密钥和证书都提供了强大的加密强度,但在完成复杂的加密、解密过程时,需要更多的性能开销。对称密钥更适合对大量数据进行加密,且具有较低性能开销,它是对相同数据进行加密和解密的一个密钥。

--3.)关闭对称密钥
CLOSE SYMMETRIC KEY sym_Salary

三、安全对象

安全对象是 SQL Server 数据库引擎授权系统控制对其进行访问的资源。通过创建可以为自己设置安全性的名为“范围”的嵌套层次结构,可以将某些安全对象包含在其他安全对象中。安全对象范围有服务器、数据库和架构。

建议不直接访问远程的数据库,而是通过中间件。

--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 = 'Northwind';

四、案例 

用非对称密钥加密时,不用打开,只需要引用非对称密钥的id。

*/

1.服务主密钥(Service Master Key)

每一个实例只有一个服务主密钥,服务主密钥用于加密数据库主密钥,服务主密钥为 SQL Server 加密层次结构的根。服务主密钥是首次需要它来加密其他密钥时自动生成的。默认情况下,服务主密钥使用 Windows 数据保护 API 和本地计算机密钥进行加密。只有创建服务主密钥的 Windows 服务帐户或有权访问服务帐户名称和密码的主体能够打开服务主密钥。

---备份服务主密钥
BACKUP SERVICE MASTER KEY TO FILE = 'D:DECRYPTIONServerMasterKey' 
    ENCRYPTION BY PASSWORD = 'password'

----还原服务主密钥
RESTORE SERVICE MASTER KEY FROM FILE = 'D:DECRYPTIONServerMasterKey'
    DECRYPTION BY PASSWORD = 'password' 
    [FORCE];   ----即使存在数据丢失的风险,也要强制替换服务主密钥。

注意:
1.服务主密钥直接或间接地保护树中的所有其他密钥。如果在强制的还原过程中不能对某个相关密钥进行解密,则由该密钥所保护的数据便会丢失。
2.重新生成加密层次结构是一种消耗大量资源的操作。您应当将该操作安排在资源需求较低的时段进行。
3.当还原服务主密钥时,SQL Server 将对所有已使用当前服务主密钥加密的密钥和机密内容进行解密,然后使用从备份文件中加载的服务主密钥对这些密钥和机密内容进行加密。

 

--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.存储过程加密

在AS前增加WITH ENCRYPTION加密选项即可

USE EncryDb;
GO
CREATE PROCEDURE Sptest
WITH ENCRYPTION ---加密选项
AS
BEGIN


END

注意:
1.加密前先保留存储副本,否则加密完再需要解密就很麻烦
2.加密过的存储过程不影响修改、删除,但是无法查看存储过程的定义比如:sp_helptext、生成create语句、生成alter语句等。

[sql] view plain copy

C.      非对称密钥的公钥

对象

下面是对象类的成员:

    • 聚合
    • 约束
    • 函数
    • 过程
    • 队列
    • 统计信息
    • 同义词
    • 视图  

非对称密钥可以从外部文件或程序集中导入,并且可以在数据库中生成。不像证书,非对称密钥不可以备份到文件,也就是创建了非对称密钥后没有简单的办法在其他数据库中重用相同的密钥。

--==================(IV)非对称密钥=====================
--1.)使用sn.ext生成非对成密钥文件
--     sn -k C:DBFileasy_Test.key

3.安全对象范围:架构

包含以下安全对象:

  • 类型
  • XML 架构集合
  • 对象

SQL Server 2008引入了透明数据加密,称为TDE,运行不修改应用程序的代码来完整的加密数据库文件。当用户数据库可用,并且启用了TDE时,数据写入磁盘时会在页级别进行加密,当数据读入内存时进行解密。

B.       加密存储过程,防止其它人查看到存储过程中的T-SQL语句

5.对称密钥

创建对称密钥时,必须至少使用以下项之一来对该对称密钥进行加密:证书、密码、对称密钥、非对称密钥或 PROVIDER。可使用上述每种类型中的多项对密钥进行加密。换言之,可以同时使用多个证书、密码、对称密钥以及非对称密钥对单个对称密钥进行加密

通常使用AES算法,有AES_128、AES_192、AES_256

 

--1.创建对称密钥,对称密钥可以由密码、非对称密钥、对称密钥、EKM模块加密
---使用密码加密
CREATE SYMMETRIC KEY SymmetricByPW
    WITH ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'SymmetricByPW111';
GO
--注意:当使用密码(而不是数据库主密钥的公钥)对对称密钥进行加密时,便会使用 TRIPLE DES 加密算法。因此,用强加密算法(如 AES)创建的密钥本身受较弱算法的保护。
---2.使用证书加密
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE MyCerts;

---3.删除非对称密钥
DROP SYMMETRIC KEY TestSymmetric
GO

四、SQL Server将这种加密能力放到加密层次结构中,当安装了SQL Server后,在数据库master中创建名为服务主密钥的服务器级别证书,并将其绑定到SQL Server服务账户登录名。

--     打开数据库主密钥未
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'
SELECT * FROM sys.openkeys

2.数据库主密钥

数据库主密钥创建于对应数据库下,具体的保护对象可以参考下面的数据库范围的安全对象。如果要对数据库备份或者透明数据库加密那么需要将服务主密钥创建于Master数据库下。

----1.创建数据库主密钥
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey'
---2.删除数据库主密钥
DROP MASTER KEY

---3.备份数据库主密钥
/*
主密钥必须为打开状态,因此在备份主密钥之前应对其进行解密。如果主密钥使用服务主密钥进行加密,则不必显式打开。但如果主密钥仅使用密码进行加密,则必须显式打开。
建议在创建主密钥之后立即对其进行备份,并存储于另外一个安全的位置中。
*/

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MasterKey'; ---打开数据库主密钥,这里的密码为创建主密钥时设的密码
BACKUP MASTER KEY TO FILE = 'D:DECRYPTIONMasterKey' -----主密钥私钥文件
    ENCRYPTION BY PASSWORD = 'MasterKey';             -----加密主密钥私钥文件
GO 
---4.还原数据库主密钥
/*
还原主密钥之后,SQL Server 会对使用当前活动的主密钥加密的所有密钥进行解密,然后使用还原后的主密钥对这些密钥进行加密。这种大量消耗资源的操作应当安排在资源需求较低的时段执行。如果当前的数据库主密钥未打开或无法打开,或者无法对任何使用该主密钥加密的密钥进行解密,则还原操作将失败。
如果当前数据库中没有主密钥,则 RESTORE MASTER KEY 将创建一个主密钥。新的主密钥不会自动使用服务主密钥进行加密。
请仅在主密钥无法恢复或解密失败时,才使用 FORCE 选项。仅由不可恢复密钥加密的信息将会丢失。
如果主密钥通过服务主密钥进行加密,则还原后的主密钥也通过该服务主密钥进行加密(当前服务器)。
*/
RESTORE MASTER KEY  
FROM FILE = 'C:DECRYPTIONMasterKey' 
DECRYPTION BY PASSWORD = N'MasterKey'  
ENCRYPTION BY PASSWORD = 'MasterKey123'  ---加密导出的主密钥
--force;        ----指定即使当前数据库主密钥未打开,或者 SQL Server 无法对使用该主密钥加密的某些私钥进行解密,RESTORE 过程也应继续执行。
GO 

---5.打开数据库主密钥
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'MasterKey123'  

GO

注意:

1.数据库主密钥是指用于保护证书私钥的对称密钥以及数据库中存在的非对称密钥。当创建主密钥时,会使用 Triple DES 算法以及用户提供的密码对其进行加密。
2.请使用服务主密钥对该主密钥的副本进行加密,并将副本存储在数据库和 master 中。通常,每当主密钥更改时,便会在不进行提示的情况下更新存储在 master 中的副本。
3.在当前服务器下创建的数据库主密钥默认就使用了服务主密钥加密和自动解密,不必使用 OPEN MASTER KEY 语句。如果还原到了新的服务器那么服务主密钥则不存在.必须使用 OPEN MASTER KEY 语句解密数据库主密钥。一旦数据库主密钥解密后,通过使用 ALTER MASTER KEY 语句向服务器提供数据库主密钥(使用服务主密钥加密)的副本,即可拥有将来启用自动解密的选项。
4.通过使用带 DROP ENCRYPTION BY SERVICE MASTER KEY 选项的 ALTER MASTER KEY 语句,可从自动密钥管理中排除特定数据库的数据库主密钥。然后,必须显式打开带密码的数据库主密钥。
5.数据库主密钥使用公钥对证书、非对称密钥进行加密,使用私钥进行解密,如果在当前创建的服务器上默认自动解密,如果还原到一台新的服务上时可能需要使用OPEN MASTER KEY进行解密。

或者找到好的加密方式。

--     创建非对称密钥失败,数据库主密钥未打开
CREATE ASYMMETRIC KEY asy_TestKey2 WITH ALGORITHM = RSA_1024
GO

二、概念

加密层次结构

分分快三全天计划网站 5

加密层次结构的每一层是如何对它下面的一层进行加密的,并且显示了最常用的加密配置。对层次结构的开始进行的访问通常受密码保护。SQL Server 用分层加密和密钥管理基础结构来加密数据。每一层都使用证书、非对称密钥和对称密钥的组合对它下面的一层进行加密。非对称密钥和对称密钥可以存储在 SQL Server 之外的可扩展密钥管理 (EKM) 模块中。

注意:

  • 为了获得最佳性能,使用对称密钥(而不是证书或非对称密钥)加密数据。

  • 数据库主密钥受服务主密钥保护。 服务主密钥由 SQL Server 安装程序创建,并且使用 Windows 数据保护 API (DPAPI) 进行加密。

  • 堆叠其他层的其他加密层次结构是可能的。

  • 可扩展密钥管理 (EKM) 模块将对称密钥或非对称密钥保存在 SQL Server 的外部。

  • 透明数据加密 (TDE) 必须使用称为数据库加密密钥的对称密钥,该密钥受由 master 数据库的数据库主密钥保护的证书保护,或者受存储在 EKM 中的非对称密钥保护。

  • 服务主密钥和所有数据库主密钥是对称密钥。

 

*/

3.证书

 证书使用公钥对安全对象进行加密,使用私钥进行解密,默认证书存在就自动解密。

----1.创建自我签名的证书,使用数据库主密钥进行加密证书
USE MASTER;
GO
CREATE CERTIFICATE MyCerts 
   WITH SUBJECT = 'BackDB Records', 
   EXPIRY_DATE = '10/31/2099';            ----证书过期时间,不指定开始时间默认开始时间为当前时间
GO

---使用密码进行加密证书
USE MASTER;
GO
CREATE CERTIFICATE CertsByPW 
   ENCRYPTION BY PASSWORD = 'CertsByPW111'
   WITH SUBJECT = 'BackDB Records', 
   EXPIRY_DATE = '10/31/2099';            ----证书过期时间,不指定开始时间默认开始时间为当前时间
GO

----2.备份证书
----警告: 用于对数据库加密密钥进行加密的证书尚未备份。应当立即备份该证书以及与该证书关联的私钥。如果该证书不可用,或者您必须在另一台服务器上还原或附加数据库,则必须对该证书和私钥均进行备份,否则将无法打开该数据库。
BACKUP CERTIFICATE MyCerts TO FILE = 'D:DECRYPTIONMyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'D:DECRYPTIONMyCertsKey' ,   ----证书私钥文件
                       ENCRYPTION BY PASSWORD = 'MyCerts123' ); ----对私钥文件加密
GO

---备份使用私钥进行加密的证书,必须先对私钥进行解密
BACKUP CERTIFICATE CertsByPW TO FILE = 'D:DECRYPTIONMyCerts' ----证书文件
    WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'CertsByPW111',----解密证书
                       FILE = 'D:DECRYPTIONMyCertsKey' ,   ----证书私钥文件
                       ENCRYPTION BY PASSWORD = 'MyCerts123' ); ----对私钥文件加密
GO

----3.通过备份文件创建证书,还原证书,
CREATE CERTIFICATE MyCerts FROM FILE = 'C:DECRYPTIONMyCerts' ----证书文件
    WITH PRIVATE KEY ( FILE = 'C:DECRYPTIONMyCertsKey' ,    ----证书私钥文件
    DECRYPTION BY PASSWORD = 'MyCerts123' );                  ----解密私钥文件


---4.删除证书
DROP CERTIFICATE MyCerts 

注意:

当使用数据库主密钥对私钥进行加密时,不需要 ENCRYPTION BY PASSWORD 选项。

只有在使用密码对私钥进行加密时,才使用该选项。

如果未指定密码,则使用数据库主密钥对证书的私钥进行加密。 如果数据库主密钥无法打开,则省略该子句会导致错误。

[sql] view plain copy

--3.)关闭打开的对称密钥
CLOSE SYMMETRIC KEY sym_Salary

6、透明数据加密

DEMO 2
使用密钥对列数据进行加密

 

--3.)

  1. --1.创建非对称密钥  
  2. create asymmetric key asymKey  
  3. with algorithm = RSA_512              --加密算法:RSA_512,RSA_1024,RSA_2048  
  4. ENCRYPTION BY PASSWORD = 'asymKey123' --加密私钥的密码,如果不提供密码,  
  5.                                       --会自动用数据库主密钥对私钥进行加密  
  6.                                       
  7.   
  8. create asymmetric key asymKey1          
  9. with algorithm = RSA_512        --这里没有提供密码,所以自动用数据库主密码来加密  
  10.   
  11.   
  12. --2.查询非对称密钥  
  13. select name,                           --费对称密钥名称  
  14.        asymmetric_key_id,              --非对称密钥id  
  15.        pvt_key_encryption_type_desc,   --私钥的加密方法  
  16.        algorithm_desc,                 --加密算法  
  17.        key_length,                     --键的长度  
  18.          
  19.        sid,  
  20.        string_sid,  
  21.        public_key                      --公钥  
  22. from sys.asymmetric_keys  
  23.   
  24.   
  25.   
  26. create table booksellerbankrouting  
  27. (booksellerid int not null primary key,  
  28.  bankroutingNBR varbinary(300) not null)  
  29.   
  30. --3.用非对称密钥来加密加密   
  31. insert into dbo.booksellerbankrouting  
  32. values(  
  33.    1,  
  34.    ENCRYPTBYASYMKEY(asymkey_id('asymKey'),                        --非对称密钥的id  
  35.                     'this string will be encrypted with asymkey.' --要加密的文体  
  36.                    )  
  37.   )  
  38.     
  39.   
  40. --4.直接查询显示的是乱码  
  41. select CAST(bankroutingNBR as varchar(100))  
  42. from dbo.booksellerbankrouting  
  43.   
  44.   
  45.   
  46. --5.解密,显示正确的数据  
  47. select CAST(DECRYPTBYASYMKEY(  
  48.                    asymkey_id('asymKey'), --非对称密钥id  
  49.                    bankroutingNBR,        --需要解密的文本  
  50.                    N'asymKey123'          --非对称密钥的私钥的密码,用这个密码来解密,  
  51.                                           --取得私钥,再用私钥来解密被加密的文本  
  52.                                           --此参数为nvarchar类型,所以字符串前要加N  
  53.                  )  
  54.               as varchar(100)  
  55.            )  
  56. from dbo.booksellerbankrouting  
  57.   
  58.   
  59.   
  60. --6.删除非对称密钥,如果已经用非对称密钥建了数据,那么不可以删除,否则数据会无法解密  
  61. drop asymmetric key asymKey1  

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

--==================(IV)使用验证器防止绕过加密数据的攻击=====================
--1.)删除前面添加的数据行
DELETE FROM 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'))
CLOSE SYMMETRIC KEY sym_Salary

--3.)解密并访问被加密了的数据列
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = 'P@ssw0rd'
SELECT EmpID, Title, CAST(DecryptBykey(Salary, 1, CAST(EmpID AS VARCHAR(3))) AS VARCHAR(20)) AS Salary FROM EmpSalary
CLOSE SYMMETRIC KEY sym_Salary

--4.)攻击者使用相同的方法篡改数据
SELECT * FROM EmpSalary

  1. create table #SecretInfo  
  2. (secret varbinary(8000) not null) --存放经过加密的二进制数据  
  3. go  
  4.   
  5.   
  6. --用通行短语来加密  
  7. insert #SecretInfo(secret)  
  8. select ENCRYPTBYPASSPHRASE(  
  9.             'My password used to encrypt this string in 2008.', --通行短语  
  10.             'This is the text I need to secure.'                --需要加密的文本  
  11.             )  
  12.               
  13. --用通行短语来解密  
  14. select CAST(  
  15.               DECRYPTBYPASSPHRASE(  
  16.                    'My password used to encrypt this string in 2008.',  
  17.                    secret)  
  18.               as varchar(100)  --解密后是二进制数据,必须要转化成文本  
  19.            )  
  20. from #SecretInfo              

--2.)创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

证书和非对称密钥使用数据库级内部的公钥来加密,并且使用数据库级内部的私钥来解密。对称密钥相对简单,包含了一个同时用来加密和解密的密钥,所以对称密钥加密数据更快,并且用在大数据集时更加合适。

SELECT * FROM sys.certificates

 

A.       使用证书加密所有敏感数据,并用对称密钥加密保护证书的私钥

  1. --1.1创建非对称密钥  
  2. create asymmetric key asymSymkey  
  3. with  algorithm = RSA_512  
  4. ENCRYPTION BY PASSWORD ='12345ABCDE'  
  5.   
  6. /*====================================================  
  7.   
  8. 对称密钥可以使用的加密算法包括:  
  9.     des,desx,triple_des,triple_des_3key,  
  10.     rc2,rc4,  
  11.     aes_128,aes_192,aes_256  
  12.       
  13. 需要用:非对称密钥、其他对称密钥、证书、密码 来加密对称密钥  
  14. ======================================================*/  
  15. --1.2创建对称密钥,这里用非对称密钥来加密对称密钥  
  16. CREATE symmetric key symKey  
  17. with algorithm = triple_des  
  18. encryption by asymmetric key asymSymKey  
  19.   
  20.   
  21. --2.查看对称密钥  
  22. select name,  
  23.        symmetric_key_id,  
  24.        key_length,  
  25.        algorithm_desc   
  26.   
  27. from sys.symmetric_keys  
  28.   
  29.   
  30. --3.修改对称密钥加密方式,原来是用非对称密钥来加密的,现在改为用密码加密  
  31. --3.1打开对称密钥  
  32. open symmetric key symKey  
  33. decryption by asymmetric key asymSymkey  
  34. with password = '12345ABCDE'  --这儿的解密密码是非对称密钥的私钥的加密密码  
  35.                               --先用这个密码解密,取得私钥  
  36.                               --然后用这个私钥解密,取得对称密钥  
  37.   
  38. --3.2先增加密码  
  39. alter symmetric key symKey  
  40. add encryption by password = 'ABCDE12345'  
  41.   
  42. --3.3再删除非对称密钥加密,如果先删除非对称密钥加密,  
  43. --会报错:无法删除 非对称密钥'asymSymKey'所创建的加密  
  44. alter symmetric key symKey  
  45. drop encryption by asymmetric key asymSymKey  
  46.   
  47.   
  48. close symmetric key symKey  
  49.   
  50.   
  51.   
  52. --4.用对称密钥加密  
  53. create table ttt  
  54. (id int not null primary key,   
  55.  Question varchar(300) not null,  
  56.  Answer varbinary(200) not null  
  57. )  
  58.   
  59. --4.1先打开对称密钥  
  60. open symmetric key symKey  
  61. decryption by password = 'ABCDE12345'  
  62.   
  63.   
  64. --4.2添加数据  
  65. insert into ttt  
  66. values(1,  
  67.        'what is your name?',  
  68.        ENCRYPTBYKEY(key_guid('symKey'), --对称密钥的guid  
  69.                     'abcdefg'           --需要加密的文本  
  70.                    )  
  71.       )  
  72.   
  73. --4.3关闭对称密钥  
  74. close symmetric key symKey  
  75.   
  76.   
  77.   
  78. --5.用对称密钥解密  
  79. --5.1先打开对称密钥  
  80. open symmetric key symKey  
  81. decryption by password = 'ABCDE12345'  
  82.   
  83.   
  84. --5.2显示解密数据  
  85. select ttt.id,  
  86.        ttt.Question,  
  87.        ttt.Answer,  
  88.        cast(DECRYPTBYKEY(Answer) as varchar(200)) --解密函数不需要对称密钥的guid  
  89. from ttt  
  90.   
  91.   
  92. --5.3关闭对称密钥  
  93. close symmetric key symKey  
  94.   
  95.   
  96.   
  97. --6.删除对称密钥  
  98. drop symmetric key symKey  

SELECT * FROM sys.openkeys        --查看打开的对称密钥

第一个是服务主密钥,位于层次结构的顶端,在安装SQL Server时自动创建,服务主密钥也可以用来加密其下的数据库主密钥、证书、链接服务器密码。在第一次加密时,服务主密钥会自动生成,并且使用SQL Server服务账户的Windows证书来生成它,如果必须修改SQL Server服务账户,建议使用SQL Server配置管理器,因为这个工具会生成新服务主密钥需要的合适的加密、解密方法,而且保持加密层次结构的完整。

--3.)备份导出证书和私钥
BACKUP CERTIFICATE cert_TestCert1
    TO FILE = 'c:DBFileTestCert1.cer'
    WITH PRIVATE KEY
        (DECRYPTION BY PASSWORD = 'P@ssw0rd' ,
         FILE = 'c:DBFileTestCert1_pvt' ,
         ENCRYPTION BY PASSWORD = 'Pa$$w0rd')

 

--2.)从文件导入证书
USE Northwind
GO
CREATE CERTIFICATE cert_TestCert2
    FROM FILE = 'C:DBFileMSCert.cer'
GO

如果数据文件或数据库备份丢失,那么如果没有用于加密DEK的服务器证书,数据库是不可以被还原或者正常的附加到其他SQL Server实例上的。

SELECT * FROM sys.certificates

1、通过通行短语来加密

--     恢复服务主密钥对数据库主密钥的保护
ALTER MASTER KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

非对称密钥是数据加密的高级选项,当用在大数据集时,非对称密钥进行加密相对于对称密钥加密来说,比较耗费资源,对称密钥使用一个密钥来加密和解密,速度更快。

D.      使用非对称密钥的私钥加密所有敏感数据,并用证书加密保护该密钥对的公钥

 

--4.)创建用于加密的对称密钥
CREATE SYMMETRIC KEY sym_Salary
    WITH ALGORITHM = AES_192
    ENCRYPTION BY PASSWORD = 'P@ssw0rd';

 

--4.)对数据库主密钥进行备份
USE Northwind
GO
BACKUP MASTER KEY
    TO FILE = 'C:DBFileDMK.bak'
    ENCRYPTION BY PASSWORD = 'P@ssw0rd!@'
GO

证书和非对称密钥加密都提供了一种非常安全的加密数据的方式,但是这种强度很高的加密方式也带来了较大的性能消耗,使用证书或非对称加密非常大的数据集,可能会对运行环境产生很大的开销,而使用对称密钥相对的开销较低。

--==================(I)准备=====================
--1.)创建示例表
USE Northwind
IF EXIST dbo.EmpSalary DROP TABLE dbo.EmpSalary;

[sql] view plain copy

--2.)查看数据库加密状态
SELECT [name], is_master_key_encrypted_by_server
    FROM sys.databases WHERE name = 'Northwind';
GO

  1. --1.打开主数据库,这样才能创建服务器级别的证书  
  2. use master  
  3. go  
  4.   
  5.   
  6. --可以生成下面的密码  
  7. select NEWID()  
  8.   
  9. --2.创建数据库主密钥  
  10. create master key   
  11. encryption by password = '123A4C23-DDA2-437A-A320-FF3B787E3B8B'  
  12.   
  13.   
  14.   
  15. --3.1创建服务器级别的证书,用数据库主密钥来加密证书的私钥  
  16. create certificate TDE_SERVER_CERTIFICATE  
  17. with subject = 'Server-level certificate for TDE'  
  18.   
  19.   
  20. --3.2备份服务器级别的证书,由于这个证书是用数据库主密码加密的,  
  21. --所以在备份时,证书的私钥由数据库主密钥自动解密,在备份到私钥文件时,必须进行再次加密  
  22. --如果没有备份私钥文件,没有加密密码,会使得从备份创建的证书无法解密  
  23. backup certificate TDE_SERVER_CERTIFICATE  
  24. to file ='c:TDE_SERVER_CERTIFICATE.bak'  
  25. with private key  
  26. (  
  27.  file = 'c:TDE_SERVER_CERTIFICATE_private_key.bak',  
  28.  encryption by password = '123456789abcedfg'   --用来加密证书私钥的密码  
  29. )  
  30.   
  31.   
  32. --3.3删除服务器级别的证书  
  33. drop certificate TDE_SERVER_CERTIFICATE  
  34.   
  35.   
  36. --3.4从备份创建服务器证书  
  37. create certificate TDE_SERVER_CERTIFICATE  
  38. from file ='c:TDE_SERVER_CERTIFICATE.bak'  
  39. with private key  
  40. (  
  41.   file = 'c:TDE_SERVER_CERTIFICATE_private_key.bak',  
  42.   decryption by password = '123456789abcedfg'   --用来解密证书私钥的密码  
  43. )  
  44.   
  45.   
  46.   
  47. --打开需要启用透明数据加密的数据库  
  48. use excel  
  49. go  
  50.   
  51. --4.创建以透明方式加密数据库的加密密钥  
  52. --DEK是用来加密整个数据库的加密密钥  
  53. create database encryption key  
  54. with algorithm = TRIPLE_DES_3KEY  --AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY   
  55. encryption by server certificate TDE_SERVER_CERTIFICATE  
  56.   
  57.   
  58. --5.启用加密  
  59. alter database excel  
  60. set encryption on  
  61.   
  62.   
  63. --6.验证数据库是否真正加密  
  64. select name,  
  65.        is_encrypted  
  66. from sys.databases  
  67. where database_id = DB_ID()  
  68.   
  69.   
  70.   
  71. --7.1修改DEK密钥的算法强度  
  72. alter database encryption key  
  73. regenerate with algorithm = AES_128  
  74.   
  75.   
  76. --7.2返回所有数据库中相关DEK状态的信息  
  77. select DB_NAME(database_id),  
  78.        case encryption_state  
  79.             when 0 then 'no encryption'  
  80.             when 1 then 'unencrypted'  
  81.             when 2 then 'encryption in progress'  
  82.             when 3 then 'encrypted'  
  83.             when 4 then 'key change in progress'  
  84.             when 5 then 'decryption in progress'  
  85.        end encryption_state,  
  86.          
  87.        key_algorithm,  
  88.        key_length  
  89.                  
  90. from sys.dm_database_encryption_keys  
  91.   
  92.   
  93.   
  94. --8.1创建一个新的服务器级证书  
  95. use master  
  96. go  
  97.   
  98. create certificate TDE_SERVER_CERTIFICATE_X  
  99. with subject = 'Server-level certificate for TDE X'  
  100.   
  101.   
  102. USE wc  
  103. GO  
  104.   
  105. --8.2修改加密DEK的服务器级证书  
  106. alter database encryption key  
  107. encryption by server certificate TDE_SERVER_CERTIFICATE_X  
  108.   
  109.   
  110.   
  111. --9.1从数据库中移除TDE  
  112. alter database excel  
  113. set encryption off  
  114.   
  115.   
  116. --9.2删除DEK  
  117. drop database encryption key   

2.       当采用加密技术来保护数据库中的大量敏感数据时,为了兼顾性能和数据的安全性,最佳的做法是:(C)

在SQL Server中把加密放到层次结构形式中,可以提供多级别的安全。SQL Server包含两个用于加密数据的密钥类型。

[过程]
过程一共分为4个部分

4、对称密钥加密

--4.)查看表中存放的数据
SELECT * FROM EmpSalary           

非对称密钥包含了数据库级的内部公钥和私钥,可以用来加密和解密SQL Server数据库中的数据。公钥用来加密数据,而私钥用来解密数据,会用创建非对称密钥时提供的密码,或者数据库主密钥,对私钥进行加密来保护私钥。

--==================(I)服务主密钥=====================
--1.)备份服务主密钥到文件
BACKUP SERVICE MASTER KEY TO FILE = 'C:DBFileSMK.bak'
ENCRYPTION BY PASSWORD = 'P@ssw0rd'

一、通过函数加密。

D.      服务主密钥

 

--==================(II)数据库主密钥=====================
--1.)为Northwind数据库创建数据库主密钥
USE Northwind
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

五、在SQL Server 2008中引入了透明数据加密,它对整个数据库进行加密,而不需要修改任何访问它的应用程序。数据、日志文件和相关的数据库备份都是加密的。如果数据库被窃取,那么如果没有数据库加密密钥,是不能访问数据的。此外,还引入了可扩展密钥管理的支持,也就是SQL Server可以使用硬件安全模块来存储和管理加密密钥,从而减少数据和实际加密密钥的耦合。

--1.)打开对称密钥
OPEN SYMMETRIC KEY sym_Salary
    DECRYPTION BY PASSWORD = 'P@ssw0rd'

证书包含密钥对(公钥和私钥)、证书拥有者的信息、证书可用的开始和结束过期日期。

B.       使用非对称密钥的公钥加密所有敏感数据,并用对称密钥加密保护该密钥对的私钥

[sql] view plain copy

B.       非对称密钥的私钥

 

3.       在SQL Server 2005中使用证书签署存储过程的目的是:(D)

 

--==================(II)加密列数据=====================

 

--     创建非对称密钥成功
CREATE ASYMMETRIC KEY asy_TestKey2 WITH ALGORITHM = RSA_1024
GO

 

 

C.      加密存储过程执行返回的数据结果集

在SQL Server 2005引入了内建数据加密,通过系统函数、证书、密钥完成加密。

--==================(III)解密并访问被加密了的数据列=====================
--1.)打开对称密钥
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = 'P@ssw0rd'

对于一个不涉及证书和密钥的应急的数据加密,可以直接基于用户提供的密码来加密、解密数据。通行短语允许密码中存在空格,而且密码不会存储在数据库中。使用通行短语可以创建一个易于记忆的句子来加密解密数据。

SELECT * FROM sys.symmetric_keys WHERE [name] = 'sym_Salary'

二、与通过证书授权实现的数字证书相似,SQL Server证书包括了公钥和私钥这一对密钥,是数据库级的安全对象,他们用来加密和解密数据。

/*
[课程]使用数据库加密保护敏感数据

 

--==================(III)证书=====================
--1.)让SQL2005创建自签名的证书
USE Northwind
GO
CREATE CERTIFICATE cert_TestCert1
    ENCRYPTION BY PASSWORD = 'P@ssw0rd'
    WITH SUBJECT = 'TestCert1',
    START_DATE = '1/31/2006',
    EXPIRY_DATE = '1/31/2008'
GO

服务主密钥用来加密所有其他数据库证书和创建在SQL Server实例中的密钥。另外,你也可以在用户数据库中创建数据库主密钥,它可以用来加密数据库证书和密钥。

SELECT CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert1'), @cipher, N'P@ssw0rd')) AS [ClearText]

  1. --1.1创建证书  
  2. create certificate certEncrypt   
  3. encryption by password = 'cert12345'  
  4. with subject = 'encryption certificate',  
  5.      start_date = '2012-10-01',  
  6.      expiry_date = '2012-10-31'  
  7.   
  8.   
  9. --1.2.1创建数据库主密钥       
  10. create master key   
  11. encryption by password = 'excel12345'  
  12.   
  13.   
  14. --1.2.2不提供密码时,会自动用数据库主密钥来加密私钥  
  15. create certificate certEncrypt1  
  16. with subject = 'encryption certificate',  
  17.      start_date = '2012-10-01',  
  18.      expiry_date = '2012-10-31'  
  19.   
  20.   
  21. --1.2.3删除证书,然后才可以删除数据库主密钥,否则会报错  
  22. drop certificate certencrypt1  
  23.   
  24. drop master key  
  25.   
  26.   
  27.   
  28. --2.查看证书信息  
  29. select name,  
  30.        certificate_id,  
  31.        pvt_key_encryption_type_desc,--对私钥加密方式的说明  
  32.        is_active_for_begin_dialog,  --对于当前会话,是否启用了证书加密  
  33.        issuer_name,                 --证书颁发者的名称  
  34.        subject,                     --证书的主题  
  35.        start_date,  
  36.        expiry_date,  
  37.        pvt_key_last_backup_date     --上一次导出证书的私钥的日期和时间  
  38. from sys.certificates  
  39.   
  40.   
  41.   
  42. --3.备份证书  
  43. --备份证书时用于加密私钥的密码与对证书的私钥进行加密的密码,不是同一个密码  
  44. backup certificate certEncrypt  
  45. to file = 'c:certEncrypt.bak'   --证书的备份文件  
  46. with private key   
  47. (  
  48.  file = 'c:certEncrypt_privateKey.bak',--私钥文件  
  49.  decryption by password = 'cert12345',  --这个密码是在创建证书时指定的,  
  50.                                         --用于在备份密钥之前对私钥进行解密的密码  
  51.        
  52.  encryption by password = 'encryptCert123456'--在解密取得私钥后,  
  53.                                              --在将密钥写入私钥的备份文件之前,  
  54.                                              --对私钥进行加密的密码  
  55. )  
  56.   
  57.   
  58.   
  59. --4.还原证书  
  60. --4.1先删除证书,这样才能重建证书  
  61. drop certificate certEncrypt  
  62.   
  63.   
  64. --4.2然后从证书备份文件和私钥文件中,再次创建证书  
  65. create certificate certEncrypt  
  66. from file = 'c:certEncrypt.bak'   
  67. with private key   
  68. (  
  69.  file = 'c:certEncrypt_privateKey.bak',  
  70.  decryption by password = 'encryptCert123456',  --用于解密私钥文件中的私钥的密码  
  71.  encryption by password = 'cert12345'           --用于加密私钥的密码  
  72. )  
  73.   
  74.   
  75.   
  76. --5.管理证书的私钥  
  77. --5.1删除私钥的密码,这样默认通过数据库主密钥对私钥进行加密  
  78. alter certificate certEncrypt  
  79. remove private key  
  80.   
  81.   
  82. --5.2通过备份的私钥文件,为已经存在的证书重新增加私钥的密码  
  83. alter certificate certencrypt  
  84. with private key  
  85. (  
  86.  file = 'c:certEncrypt_privateKey.bak',  
  87.  decryption by password = 'encryptCert123456',  
  88.  encryption by password = 'cert12345'  
  89. )  
  90.   
  91.   
  92. --5.3修改私钥的密码  
  93. alter certificate certencrypt  
  94. with private key  
  95. (  
  96.  decryption by password = 'cert12345',  
  97.  encryption by password = '12345cert'  
  98. )  
  99.   
  100.   
  101.   
  102. --6.使用证书加密,解密  
  103. create table t  
  104. (id int not null primary key,  
  105.  question varchar(100) not null,  
  106.  answer varbinary(200) not null)  
  107.   
  108. --6.1加密数据  
  109. insert into t  
  110. values(1,  
  111.        'who are you?',  
  112.        ENCRYPTBYCERT(  
  113.                       cert_id('certencrypt'),  --证书的id  
  114.                       'kaka'                   --需要加密的文本  
  115.                     )  
  116.       )  
  117.   
  118. --6.2直接查看,发现是乱码  
  119. select ID,  
  120.        question,  
  121.        answer,  
  122.        CAST(answer AS varchar(200))  
  123. from t  
  124.   
  125.   
  126. --6.3解密,显示正确数据  
  127. select ID,  
  128.        question,  
  129.        answer,  
  130.        CAST(DECRYPTBYCERT(  
  131.                    cert_id('certencrypt'),  
  132.                    answer,  
  133.                    N'12345cert'    --私钥的密码,用来解密取得私钥,再用私钥来解密数据  
  134.                   ) AS varchar(200)  
  135.             )  
  136. from t  
  137.   
  138.   
  139.   
  140. --7.使用对称密钥执行解密,而该对称密钥则使用非对称密钥进行自动解密  
  141. create table tx  
  142. (id int not null primary key,  
  143.  question varchar(100) not null,  
  144.  answer varbinary(200) not null)  
  145.   
  146.   
  147. --7.1非对称密钥的私钥用数据库主密钥加密  
  148. --7.1.1创建数据库主密钥  
  149. create master key   
  150. encryption by password = 'excel12345'  
  151.   
  152.   
  153. --7.1.2创建非对称密钥,自动用数据库主密钥加密私钥  
  154. create asymmetric key asymKey  
  155. with algorithm = RSA_512  
  156.   
  157.   
  158. --7.1.3创建对称密钥,用非对称加密的私钥来加密对称密钥  
  159. CREATE symmetric key symKey  
  160. with algorithm = TRIPLE_DES  
  161. ENCRYPTION BY asymmetric key asymKey  
  162.   
  163.   
  164. --7.1.4打开对称密钥  
  165. open symmetric key symKey  
  166. decryption by asymmetric key asymKey  
  167.   
  168.   
  169. --7.1.5用对称密钥加密  
  170. insert into tx  
  171. values(1,  
  172.        'who are you?',  
  173.        ENCRYPTBYKEY(KEY_GUID('symKey'),  
  174.                     'kk')  
  175.        )  
  176.   
  177. --7.1.6关闭对称密钥  
  178. close symmetric key symKey  
  179.   
  180.   
  181. --7.1.7由于非对称加密的私钥是由数据库主密钥加密的,所以不需要提供私钥的密码,  
  182. --首先用数据库主密钥解密,取得非对称加密的私钥,  
  183. --然后用私钥解密,取得对称加密的公钥,用公钥解密被加密的文本  
  184. select id,  
  185.        question,  
  186.        answer,  
  187.        cast(DecryptBykeyAutoAsymkey(  
  188.                     asymkey_id('asymKey'), --用于保护对称密钥的非对称密钥的ID  
  189.                     null,                  --用于保护非对称密钥私钥的密码  
  190.                                            --如果私钥受数据库主密钥保护,则该值可以是 NULL  
  191.                     answer  
  192.                    )  
  193.                as varchar)  
  194. from tx  
  195.   
  196. delete from tx  
  197.   
  198.   
  199. --7.1非对称密钥的私钥用密码来加密  
  200. --7.2.1创建非对称密钥  
  201. create asymmetric key asymKey1  
  202. with algorithm = RSA_512  
  203. encryption by password = 'asymKey123456'  
  204.   
  205.   
  206. --7.2.2创建对称密钥,用非对称加密的私钥来加密对称密钥  
  207. CREATE symmetric key symKey1  
  208. with algorithm = TRIPLE_DES  
  209. ENCRYPTION BY asymmetric key asymKey1  
  210.   
  211.   
  212. --7.2.3打开对称密钥  
  213. open symmetric key symKey1  
  214. decryption by asymmetric key asymKey1  
  215. with password = 'asymKey123456'    --非对称密钥的私钥的密码  
  216.   
  217.   
  218. --7.2.4用对称密钥加密  
  219. insert into tx  
  220. values(1,  
  221.        'who are you?',  
  222.        ENCRYPTBYKEY(KEY_GUID('symKey1'),  
  223.                     'kk')  
  224.        )  
  225.   
  226. --7.2.5关闭对称密钥  
  227. close symmetric key symKey1  
  228.   
  229.   
  230. --7.2.6提供非对称加密的私钥的密码,首先用这个密码来解密,取得私钥,  
  231. --然后用私钥解密,取得对称加密的公钥,用公钥解密被加密的文本  
  232. select id,  
  233.        question,  
  234.        answer,  
  235.        cast(DecryptBykeyAutoAsymkey(  
  236.                     asymkey_id('asymKey1'),--用于保护对称密钥的非对称密钥的ID  
  237.                     N'asymKey123456',      --保护非对称密钥私钥的密码,类型为nvarchar  
  238.                                            --如果私钥受数据库主密钥保护,则该值可以是 NULL  
  239.                     answer  
  240.                    )  
  241.                as varchar)  
  242. from tx  
  243.   
  244. delete from tx  
  245.   
  246.   
  247. --7.3用证书的私钥用密码来加密  
  248. --7.3.1创建证书  
  249. create certificate certEncrypt1   
  250. encryption by password = 'cert12345'  
  251. with subject = 'encryption certificate',  
  252.      start_date = '2012-10-01',  
  253.      expiry_date = '2012-10-31'  
  254.   
  255.   
  256. --7.3.2创建对称密钥,用证书的私钥来加密对称密钥  
  257. CREATE symmetric key symKey11  
  258. with algorithm = TRIPLE_DES  
  259. ENCRYPTION BY certificate certEncrypt1  
  260.   
  261.   
  262. --7.3.3打开对称密钥  
  263. open symmetric key symKey11  
  264. decryption by certificate certEncrypt1  
  265. with password = 'cert12345'    --非对称密钥的私钥的密码  
  266.   
  267.   
  268. --7.3.4用对称密钥加密  
  269. insert into tx  
  270. values(1,  
  271.        'who are you?',  
  272.        ENCRYPTBYKEY(KEY_GUID('symKey11'),  
  273.                     'kk')  
  274.        )  
  275.   
  276. --7.3.5关闭对称密钥  
  277. close symmetric key symKey11  
  278.   
  279.   
  280. --7.3.6提供证书的私钥的密码,首先用这个密码来解密,取得私钥,  
  281. --然后用私钥解密,取得对称加密的公钥,用公钥解密被加密的文本  
  282. select id,  
  283.        question,  
  284.        answer,  
  285.        cast(DecryptBykeyAutoCert(  
  286.                  cert_id('certEncrypt1'),--用于保护对称密钥的证书的ID  
  287.                  N'cert12345',           --保护证书的私钥的密码,类型为nvarchar  
  288.                                          --如果私钥受数据库主密钥保护,则为NULL  
  289.                  answer  
  290.               )  
  291.              as varchar)  
  292. from tx  
  293.   
  294.   
  295. --8.删除证书  
  296. drop certificate certEncrypt  

CREATE TABLE dbo.EmpSalary(
    EmpID int,
    Title nvarchar(50),
    Salary varbinary(500)
)
GO

5、证书加密

--4.)使用证书加密、解密数据
DECLARE @cleartext varbinary(200)
DECLARE @cipher varbinary(200)
SET @cleartext = CONVERT(varbinary(200), 'Test text string')
SET @cipher = EncryptByCert(Cert_ID('cert_TestCert1'), @cleartext)
SELECT @cipher

证书的公钥用来加密数据,私钥用来解密数据。SQL Server可以生成证书,也可以从外部文件或程序集载入。由于可以备份证书,然后从文件载入,所以证书比非对称密钥更容易迁移,而非对称密钥则不能这样,所以多用户数据库可以方便的重用一个证书。

SELECT * FROM sys.asymmetric_keys

 

A.       确保只有拥有该证书对应私钥的用户才能执行该存储过程

 

--3.)从备份文件还原服务主密钥
RESTORE SERVICE MASTER KEY FROM FILE = 'C:DBFileSMK.bak'
DECRYPTION BY PASSWORD = 'P@ssw0rd'

  1. --1.1备份服务主密钥  
  2. backup service master key   
  3. to file ='c:smk.bak'                  --服务主密钥备份导出的文件路径  
  4. encryption by password = 'c:smk.bak'  --用来加密:包含密钥备份的文件的密码  
  5.       
  6.       
  7. --1.2还原服务主密钥  
  8. restore service master key  
  9. from file = 'c:smk.bak'  
  10. decryption by password = 'c:smk.bak'     
  11.   
  12.   
  13. --1.3参数force强制替换现有的服务主密钥,可能会使加密的数据不能被解密  
  14. restore service master key  
  15. from file = 'c:smk.bak'  
  16. decryption by password = 'c:smk.bak' force  
  17.   
  18.   
  19.   
  20. --2.数据库主密钥  
  21. use AdventureWorks  
  22. go  
  23.   
  24. --2.1创建数据库主密钥  
  25. create master key   
  26. encryption by password = 'AdventureWorks'  
  27.   
  28.   
  29. --2.2用新的密码重新生成数据库主密钥  
  30. alter master key  
  31. regenerate with encryption by password = '123456'  
  32.   
  33.   
  34. --2.3在没有用数据库主密钥加密其他密钥时,才可以删除数据库主密钥  
  35. drop master key  
  36.   
  37.   
  38. --2.4备份数据库主密钥  
  39. backup master key   
  40. to file = 'c:master_key.bak'  
  41. encryption by password = 'c:master_key.bak'  --保护备份文件的密码  
  42.   
  43.   
  44. --2.5还原数据库主密钥  
  45. restore master key  
  46. from file = 'c:master_key.bak'  
  47. decryption by password = 'c:master_key.bak'  --解密备份文件的密码  
  48. encryption by password = '1234567'            --还原后的数据库主密钥,进行加密的密码  
  49.   
  50.   
  51. restore master key  
  52. from file = 'c:master_key.bak'  
  53. decryption by password = 'c:master_key.bak'  --解密备份文件的密码  
  54. encryption by password = '1234567'            --还原后的数据库主密钥,进行加密的密码  
  55. force   --只有在主密钥无法恢复或解密失败时,才使用FORCE选项  
  56.   
  57.   
  58. /*===================================================  
  59. 2.6  
  60. 在创建数据库主密钥时,默认使用两种方法对它进行加密:  
  61. 服务主密钥和create master key中使用的密码。  
  62. 如果不希望通过服务主密钥对数据库主密钥进行加密,  
  63. 那么可以删除服务主密钥加密。  
  64.   
  65. 需要特别注意的是,如果删除了服务主密钥加密,  
  66. 那么有sysadmin权限的SQL登录名,有了数据库主密钥的密码,才能访问加密数据。  
  67. 因为服务主密钥允许有sysadmin权限的用户,自动对数据库主密钥进行解密,访问加密数据。  
  68. =====================================================*/  
  69. --从数据库主密钥,删除服务主密钥加密  
  70. alter master key  
  71. drop encryption by service master key  
  72.   
  73. --如果要重新使用服务主密钥来加密,必须打开数据库主密钥  
  74. open master key  
  75. decryption by password = 'AdventureWorks'  
  76.   
  77. --然后服务主密钥重新被添加到数据库主密钥中  
  78. alter master key  
  79. add encryption by service master key  
  80.   
  81. --关闭数据库主密钥  
  82. close master key  

--2.)从文件创建非对称密钥
USE Northwind
GO
CREATE ASYMMETRIC KEY asy_Test 
    FROM FILE = 'C:DBFileasy_Test.key' 
    ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

3、非对称密钥加密

C.      使用对称密钥加密所有敏感数据,并用证书加密保护该对称密钥

 

D.      让该存储过程以证书所对应的数据库用户的权限执行

SELECT * FROM sys.openkeys        --查看打开的对称密钥

--2.)使用对称密钥解密并访问被加密了的数据列
SELECT EmpID, Title, CAST(DecryptBykey(Salary) AS VARCHAR(20)) AS Salary FROM EmpSalary

DEMO 1
了解SQL2005加密层次结构

--2.)生成新的服务主密钥
ALTER SERVICE MASTER KEY REGENERATE;
GO

1.       在SQL Server 2005中,数据库的主密钥可以直接用来加密保护:(AB)

A.       证书的私钥

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划 SQL-MySQL