SQL2012数据库加密方法(二)
ASSWORD = 'SQL@2014';
--还原服务主密钥。（这是可以立即执行，因为SQL Server 会看到钥匙是相同的并不会重新对数据进行加密。）
RESTORE SERVICE MASTER KEY FROM FILE = 'd:\Data\servicemasterkey.dat'
DECRYPTION BY PASSWORD = 'SQL@2014';
-- 此外可以使用 FORCE 选项,重新生成服务主密钥。要小心 !对用旧 SMK 加密的所有密钥进行都解密，然后对它们进行加密用新的密钥，这可需要大量的处理时间。不执行，除非你确定你想要重新生成密钥.
ALTER SERVICE MASTER KEY REGENERATE;
-- 如果主密钥无法再生过程将检索失败，或者是无法解密它加密的所有密钥。FORCE 选项强制再生，即使数据将会丢失。
ALTER SERVICE MASTER KEY FORCE REGENERATE;
-- 创建数据库主密钥
USE EncryptionDB;
GO
-- 数据库没有一把万能钥匙，默认情况下，所以您必须创建它,你可以使用它之前：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DB@SQL2012';
-- 如果数据库掌握关键未使用服务主密钥，加密你必须在使用它之前将其打开：
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'DB@SQL2012';
--但通常这不是必要的除非您删除的加密服务主密钥 （如果它是打开，这关闭键）：
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
-- 可以添加服务主密钥加密：
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
/*
-- 这很有用，当将数据库移动到另一个服务器。删除加密,以前分离，并将其添加回新的服务器上。
-- 你也可以顺便您用来创建它的密码的加密：
ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = 'DB@SQL2012';
-- (如果将失败的关键是开放的因为它是如果您执行了前面的代码作为它会出现在此文件中的脚本。），然后你可以将其添加回与新的或旧的密码
ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'DB@SQL2012';
-- 如果已经存储了密钥，要么 drop 语句将会失败。
*/
-- 如果您显式打开数据库主密钥，您必须先关闭它当你完成它：
CLOSE MASTER KEY;
-- 数据库主密钥像服务主密钥，可以转储，并从文件中加载密钥。执行此操作之前，必须打开密钥。
BACKUP MASTER KEY TO FILE = 'd:\Data\EncryptionDBMasterKey.dat'
ENCRYPTION BY PASSWORD = 'DB@SQL2012bak';
GO
--还原数据库主密钥
RESTORE MASTER KEY F
ROM FILE = 'd:\Data\EncryptionDBMasterKey.dat'
DECRYPTION BY PASSWORD = 'DB@SQL2012bak'
ENCRYPTION BY PASSWORD = 'DB@SQL2012';
-- 如果您正在加载的关键是在先声明不会做任何事与现有的主机密钥相同。可以使用 FORCE 选项，以及如果不需要密钥, 删除它时.
--DROP MASTER KEY;
-- 如果主密钥保护数据库中的任何私有密钥将失败
CREATE TABLE Customer2 (
CustId int,
Name nvarchar(30),
City varchar(20),
CreditCardType varbinary(1000)
)
GO
-- 授权
GRANT SELECT,INSERT on Customer2 to User1;
GRANT SELECT,INSERT on Customer2 to User2;
GRANT SELECT,INSERT on Customer2 to User3;
GO
-- 1、证书加密
-- 创建与 User1，加密与数据库相关联的证书主密钥。数据库主密钥必须已经存在。请注意，你不必显式打开主密钥，因为它加密的服务主密钥。
CREATE CERTIFICATE User1Certificate AUTHORIZATION User1 WITH subject = 'Certificate For User1';
GO
-- 授权条款是可选的。给 User1 的所有权。此外可以使用给予控制可以使用它。
-- 为User2创建证书
CREATE CERTIFICATE User2Certificate AUTHORIZATION User2
ENCRYPTION BY PASSWORD = 'User2Password'
WITH subject = 'Certificate For User2',
EXPIRY_DATE = '12/31/2020';
GO
--删除User2Certificate
DROP CERTIFICATE User2Certificate
-- 备份证书User1Certificate
BACKUP CERTIFICATE User1Certificate TO FILE = 'd:\Data\myCert.dat'
WITH PRIVATE KEY (FILE = 'd:\Data\myCertPrivateKey',
ENCRYPTION BY PASSWORD = 'User1CertificatePassword');
/*
-- 您也可以从文件加载证书。通常，这将是一个证书,来自受信任的证书颁发机构。
CREATE CERTIFICATE MyCertificate FROM FILE = 'd:\Data\myCert.dat'
WITH PRIVATE KEY (FILE = 'd:\Data\myCertPrivateKey',
DECRYPTION BY PASSWORD = 'User1CertificatePassword');
-- 创建证书的声明，一次只能存在于数据库。
--CREATE CERTIFICATE MyEXEcertificate FROM EXECUTABLE FILE = 'Z:\SomeFile.exe'
--CREATE CERTIFICATE MyNETcertificate FROM ASSEMBLY myLoadedAssembly
-- 删除证书
DROP CERTIFICATE MyCertificate;
*/
-- 使用证书来加密数据
INSERT INTO Customer2 VALUES (1, 'Roy Wu', 'GZ',
EncryptByCert(Cert_ID('User1Certificate'), 'Visa'));
GO
-- 查看数据。请注意 enctypted 数据的长度,其中包括签名
SELECT * FROM Customer2;
/*
CustId Name City CreditCardType
1 Roy Wu GZ 0xB5B4C36DD479BE908577A93D82235EE2FC23F9A5B4AF1D8FF3E91BD848AC3DD04