SQL2012数据库加密方法(一)

1、非对称密钥来保护新的对称密钥

/*--测试环境
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
	Oct 19 2012 13:38:57 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)
*/
--新建测试登陆账号User1
USE [master]
GO
CREATE LOGIN [User1] WITH PASSWORD=N'1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

GO
--设置示例加密数据库
CREATE DATABASE EncryptionDB;
GO

USE EncryptionDB;
GO
--向当前数据库添加用户User1
CREATE USER User1 FOR LOGIN User1;
go
--新增测试表Customer加密信用卡类型
CREATE TABLE Customer (
	CustId int, 
	Name nvarchar(30), 
	City varchar(20), 
	CreditCardType varbinary(1000)
);
GO

-- 授予数据库用户User1查询\新增）权限
GRANT SELECT,INSERT on Customer to User1;

go
USE EncryptionDB;
GO

-- 数据库没有万能钥匙，默认情况下，所以您必须创建它,然后才可以使用它.
-- 1、创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'gz@SQL2012';

-- 创建非对称密钥来保护新的对称密钥
CREATE ASYMMETRIC KEY User1AsymmetricKey
	AUTHORIZATION User1
	WITH ALGORITHM = RSA_2048

-- 创建对称密钥
CREATE SYMMETRIC KEY User1SymmetricKey
    AUTHORIZATION User1
	WITH ALGORITHM = TRIPLE_DES
	ENCRYPTION BY ASYMMETRIC KEY User1AsymmetricKey;

-- 查看数据库中的对称密钥
SELECT * FROM sys.symmetric_keys;

--切换用户
EXECUTE AS USER = 'User1';

select USER_NAME()
-- 插入数据，使用各种非对称密钥。
-- 如果对称密钥不对时，打开没有错误，没有插入的数据。
OPEN SYMMETRIC KEY User1SymmetricKey
	DECRYPTION BY ASYMMETRIC KEY User1AsymmetricKey;
INSERT INTO Customer VALUES (1, 'Roy Wu', 'GZ',
	EncryptByKey(Key_GUID('User1SymmetricKey'), 'UnionPay'));
CLOSE SYMMETRIC KEY User1SymmetricKey;

-- 查看使用对称密钥输入的新数据。
SELECT * FROM Customer;
/*
CustId	Name	City	CreditCardType
1	Roy Wu	GZ	0x001F1EE79BE1EC4CB9F9D2118762E87F010000008266403C365BDE7D45F5821A8B10823BBCB0BD78F8EC9142D58461B936ADE8E7
*/
--  查看的数据。请注意您不必指定哪个对称密钥
-- 要用于解密的数据，即使多个对称密钥可以打开。
OPEN SYMMETRIC KEY User1SymmetricKey
	DECRYPTION BY ASYMMETRIC KEY User1AsymmetricKey;
SELECT CustID, Name, City,
	CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CreditCardType
FROM Customer;
/*
CustID	Name	City	CreditCardType
1	Roy Wu	GZ	UnionPay
*/
CLOSE SYMMETRIC KEY User1SymmetricKey;



--由于权限问题，需要新打开一个查询窗口

-- 加密视图查看(现有密钥)

--为数据库中的每个证书返回一行。
SELECT * FROM sys.certificates;
--为每个非对称密钥返回一行。
SELECT * FROM sys.asymmetric_keys;
--对于使用 CREATE SYMMETRIC KEY 语句创建的每个对称密钥，返回与其对应的一行。
SELECT * FROM sys.symmetric_keys;
--为数据库中的每个主体返回一行。
SELECT * FROM sys.database_principals;

-- 有关密钥的信息
-- 为使用 CREATE SYMMETRIC KEY 语句的 ENCRYPTION BY 子句指定的每个对称密钥加密返回一行。

SELECT * FROM sys.key_encryptions;

-- 对于与安全对象关联的每个加密属性，返回与其对应的一行。
SELECT * FROM sys.crypt_properties;

-- 删除测试
USE master;
GO
DROP DATABASE EncryptionDB;
DROP LOGIN User1;

USE master;
GO

-- 创建3个登陆账号
CREATE LOGIN User1 WITH password = '1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
CREATE LOGIN User2 WITH password = '2', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
CREATE LOGIN User3 WITH password = '3', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

CREATE DATABASE EncryptionDB;
GO

USE EncryptionDB;
GO
--创建登陆账号
CREATE USER User1 FOR LOGIN User1;
CREATE USER User2 FOR LOGIN User2;
CREATE USER User3 FOR LOGIN User3;
GO

-- 服务主密钥
USE master;
GO

-- 备份服务主密钥，将存储在安全的位置,该文件被加密的使用提供的密码作为密钥
BACKUP SERVICE MASTER KEY TO FILE = 'D:\Data\servicemasterkey.dat' 
	ENCRYPTION BY P