#眉標=SQL Server 2008
#副標=SQL Server 2008新功能概覽（2）
#大標=通透地全資料庫加密
#作者=文／胡百敬



==<box>===========
程式1
USE master;
GO
-- 加密資料庫前須先建立鑰匙
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'password';
GO
-- Restore 被加密資料庫的伺服器執行個體需要有相同的憑證
CREATE CERTIFICATE NorthwindCert
WITH SUBJECT = 'Nothwind TDE Certificate';
GO
-- 經由以下的語法對 NorthwindEncrypted 資料庫進行加密
USE NorthwindEncrypted;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE NorthwindCert;
GO
--設定NorthwindEncrypted 要加密
ALTER DATABASE NorthwindEncrypted
SET ENCRYPTION ON;
GO
-- 監控TDE 的執行進度
--查詢sys.dm_database_encryption_keys 動態管理檢視的encryption_state 欄位
--若其值為2 代表背景的程序尚未完成
--其值為3 則表示加密程序已完成
SELECT * FROM sys.dm_database_encryption_keys
WHERE [database_id] = DB_ID ('NorthwindEncrypted');
==<end>==============




==<box>===========
程式2
BACKUP DATABASE NorthwindEncrypted 
TO DISK='C:\SQL2008\myDemos\Security Demos\DB\NorthwindEncrypted.bak'
WITH INIT
--切換到另外一個SQL Server 2008 執行個體嘗試還原
RESTORE DATABASE NorthwindEncrypted 
FROM DISK = N'C:\SQL2008\myDemos\Security Demos\DB\NorthwindEncrypted.bak' WITH
MOVE 'Northwind' TO
	N'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI2\MSSQL\DATA\NorthwindEncrypted.mdf',
MOVE 'Northwind_log' TO
	N'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI2\MSSQL\DATA\NorthwindEncrypted_log.ldf',
REPLACE;
==<end>==============





==<反灰>===========
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xB40822C4AF74BD3F843ABF1879019794B66DCDA8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
==<end>==============





==<box>===========
程式3
BACKUP CERTIFICATE NorthwindCert
TO FILE = 'C:\SQL2008\myDemos\Security Demos\DB\MyServerCert'
WITH PRIVATE KEY (FILE = 'C:\SQL2008\myDemos\Security Demos\DB\MyServerCertKey',
	ENCRYPTION BY PASSWORD = 'password');
==<end>==============




==<box>===========
程式4
-- 需要先建立master key，才能加密隨後還原的憑證
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'password';
GO
-- 建立先前加密資料庫的憑證
CREATE CERTIFICATE MyServerSert
FROM FILE = 'C:\SQL2008\myDemos\Security Demos\DB\MyServerCert'
WITH PRIVATE KEY (FILE = 'C:\SQL2008\myDemos\Security Demos\DB\MyServerCertKey',
	DECRYPTION BY PASSWORD = 'password');
==<end>==============




==<反灰>===========
Database Engine→Security and Protection→Secure Operation→SQL Server Encryption→Auditing (Database Engine)→Understanding SQL Server Audit→SQL Server Audit Action Groups and Actions
==<end>==============





==<box>===========
程式5
-- 建立測試資料庫
USE master;
GO
CREATE DATABASE AuditTest;
GO
USE AuditTest;
GO
-- 建立測試資料表與測試資料
CREATE TABLE TestTable1 (c1 INT, c2 INT);
CREATE TABLE TestTable2 (Name VARCHAR (10), DateOfBirth SMALLDATETIME);
GO
INSERT INTO TestTable1 VALUES (1, 1);
INSERT INTO TestTable1 VALUES (2, 2);
INSERT INTO TestTable2 VALUES ('Katelyn', '2000-03-05 00:00:00.000');
INSERT INTO TestTable2 VALUES ('Kiera', '2001-09-27 00:00:00.000');
GO
-- 建立帳後，並賦予操作資料的權限
CREATE LOGIN paul WITH PASSWORD = 'password',
DEFAULT_DATABASE = AuditTest;
GO
CREATE USER paul FOR LOGIN paul;
GO
GRANT SELECT ON TestTable1 TO paul;
GO
GRANT SELECT ON TestTable2 TO paul;
GRANT INSERT ON TestTable2 TO paul;
==<end>==============



==<box>===========
程式6
-- 須在Master 資料庫下建立Server Audit
USE master;
GO
CREATE SERVER AUDIT ExampleAudit TO FILE
(FILEPATH = 'C:\SQL2008\myDemos\Security Demos\Audit\',
 MAXSIZE = 2MB,
 -- 設定了硬碟上開啟檔案的最大大小後
 -- 透過此選項先取得指定的空間大小
 RESERVE_DISK_SPACE = ON) 
 -- 若無法紀錄，例如硬碟空間用完，執行個體就停止執行
WITH (ON_FAILURE = SHUTDOWN);
==<end>==============





==<box>===========
程式7
USE Master
GO
CREATE SERVER AUDIT SPECIFICATION ExampleServerAuditSpec
FOR SERVER AUDIT ExampleAudit ADD
(SERVER_PRINCIPAL_IMPERSONATION_GROUP)
WITH (STATE = ON);
==<end>==============





==<box>===========
程式8
USE AuditTest;
GO
CREATE DATABASE AUDIT SPECIFICATION ExampleDBAuditSpec
FOR SERVER AUDIT ExampleAudit
ADD (SELECT ON TestTable1 BY paul),
ADD (INSERT, UPDATE ON TestTable2 BY paul)
WITH (STATE = ON);
==<end>==============





==<box>===========
程式9
-- 啟動監控
USE master;
GO
ALTER SERVER AUDIT ExampleAudit WITH (STATE = ON);
==<end>==============





==<box>===========
程式10
-- 觀察系統所設定的監控
SELECT * FROM sys.dm_server_audit_status;
GO
-- 透過SQL 語法檢視存放監控資料的檔案內容
SELECT AuditFile.* FROM sys.dm_server_audit_status AS AuditStatus
CROSS APPLY sys.fn_get_audit_file (
	AuditStatus.audit_file_path, default, default) AS AuditFile
WHERE AuditStatus.name = 'ExampleAudit';
==<end>==============





==<box>===========
程式11
-- 做一些會被監控的動作
EXECUTE AS LOGIN = 'paul';
GO
USE AuditTest;
GO
SELECT * FROM TestTable1;
GO
SELECT * FROM TestTable2;
GO
INSERT INTO TestTable2 VALUES ('Coco', '2005-03-27 00:00:00.000');
USE Master
REVERT;
==<end>==============





==<box>===========
程式12
SELECT status_time,server_principal_name,database_principal_name,
database_name,schema_name,object_name,statement 
FROM sys.dm_server_audit_status AS AuditStatus
CROSS APPLY sys.fn_get_audit_file (
	AuditStatus.audit_file_path, default, default) AS AuditFile
WHERE AuditStatus.name = 'ExampleAudit';
==<end>==============