Kiểm Toán và Mã Hóa Dữ liệu – Lab SQL 2014 P9

A. Sử dụng DML trigger để audit:

Tại SSMS, mở file DML trigger.sql trong folder module 10, chọn các lệnh trong phần Create a log table, ấn F5 để tạo table AuditRateChanges trong schema HumanResources của AdventureWorks database,

— Create a log table

USE AdventureWorks;

GO

CREATE TABLE HumanResources.AuditRateChanges

(LogEntryID INTEGER IDENTITY PRIMARY KEY,

EventTime DATETIME DEFAULT GETDATE(),

UserName SYSNAME DEFAULT SUSER_NAME(),

EmployeeID INT,

OldRate MONEY,

NewRate MONEY);

GO

Tiếp tục chọn các lệnh trong phần create trigger, ấn F5 để tạo ra một trigger trên table EmployeePayHistory, khi rate column của table này bị thay đổi, sẽ chèn thêm một dòng thông tin vào AuditRateChanges table đã tạo ở bước trên

— Create a Trigger

CREATE TRIGGER HumanResources.EmployeePayHistory_Update

ON HumanResources.EmployeePayHistory

FOR UPDATE

AS

BEGIN

IF UPDATE(Rate)

BEGIN

INSERT INTO HumanResources.AuditRateChanges (EmployeeID, OldRate, NewRate)

SELECT i.BusinessEntityID, d.Rate,i.Rate

FROM inserted i

JOIN deleted d ON i.BusinessEntityID = d.BusinessEntityID;

END;

END;

GO

Chọn các lệnh trong phần Update a rate, ấn F5 để thực hiện sửa dữ liệu cho rate column trong table EmployeePayHistory.

–Update a Rate

UPDATE HumanResources.EmployeePayHistory

SET Rate = Rate * 1.1

WHERE BusinessEntityID = 1

AND RateChangeDate = (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory

WHERE BusinessEntityID = 1);

GO

Chọn các lệnh trong phần View the audit log, ấn F5 để xem thong tin trong table AuditRateChanges

— View the audit log

SELECT * FROM HumanResources.AuditRateChanges;

B. Sử dụng SQL server audit:

1. Tạo Audit: mở file Audit.sql trong folder module 10, chọn các lệnh trong phần Create an Audit, ấn F5 để tạo Audit AW_Audit lưu các sự kiện vào F:\Audit

— Create an audit

USE master;

CREATE SERVER AUDIT AW_Audit

TO FILE

(FILEPATH = ‘F:\Audit\’

,MAXSIZE = 0 MB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

)

WITH

(QUEUE_DELAY = 1000

,ON_FAILURE = FAIL_OPERATION

)

GO

ALTER SERVER AUDIT AW_Audit

WITH (STATE = ON);

GO

Tại cửa sổ Object Explorer chọn Security Audit, phải chuột vào AW_Audit vừa tạo chọn properties, quan sát các tham số vừa tạo trong Audit Cancel

2. Tạo Server Audit Specification:chọn các lệnh trong phần Create a server audit specification, ấn F5 để tạo server audit specification cho AW_Audit ghi lại các sự kiện khi các login kết nối vào SQL server thành công hay thất bại.

–Create a server audit specification

CREATE SERVER AUDIT SPECIFICATION AW_ServerAuditSpec

FOR SERVER AUDIT AW_Audit

ADD (FAILED_LOGIN_GROUP),

ADD (SUCCESSFUL_LOGIN_GROUP)

WITH (STATE = ON);

GO

Tại cửa sổ Object Explorer chọn Security Server Audit Specifications, phải chuột vào AW_ServerAuditSpec vừa tạo chọn properties xem lại các thông số vừa tạoCancel

3. Tạo Database Audit Specification: chọn các lệnh trong phần Create a database audit specification, ấn F5 để tạo database audit AW_DatabaseAuditSpec cho AW_Audit.

— Create a database audit specification

USE AdventureWorks;

CREATE DATABASE AUDIT SPECIFICATION AW_DatabaseAuditSpec

FOR SERVER AUDIT AW_Audit

ADD (SELECT ON SCHEMA::HumanResources BY hr_reader),

ADD (INSERT ON SCHEMA::HumanResources BY hr_writer),

ADD (UPDATE ON SCHEMA::HumanResources BY pay_admin)

WITH (STATE = ON);

GO

Tại cửa sổ Object Explorer chọn Database . AdventureWorks Security Database audit specifications, phải chuột vào AW_DatabaseAuditSpec chọn properties xem các thông số vừa tạo Cancel

4. Xem Audited Events: Vào run: gõ lệnh cmd và thực hiện lệnh sau và nhập password vào:

runas /user:CTL\HumanResource_User /noprofile sqlcmd

Tại cửa sổ SQLCMD thực hiện lệnh sau:

SELECT LoginID, JobTitle FROM HumanResources.Employee;

GO

Sau khi thực hiện xong đóng cửa sổ SQLCMD, vào folder F:\Audit sẽ có audit file đã được tạo.

Trở lại SSMS, trong cửa sổ query audit.sql chọn các lệnh trong phần View audited events, ấn F5 hiển thị các sự kiện trong audit file ngoại trừ thông sự kiện của student và ServiceAcct. Chú ý các sự kiện của HumanResource_User.

— View audited events

SELECT event_time, action_id, succeeded, statement, user_defined_information, server_principal_name, database_name, schema_name, object_name

FROM sys.fn_get_audit_file (‘F:\Audit\*’,default,default)

WHERE server_principal_name NOT IN (‘CTL\Student’, ‘ctl\ServiceAcct’);

C. Thực hiện Transparent Data Encryption:

1. Tạo database master key: Tại SSMS mở file TDE.sql trong folder Module 10. Chọn các lệnh trong phần create DMK, ấn F5 để tạo database master key trong master database

— Create DMK

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pa$$w0rd’;

GO

2. Tạo Server Certificate: chọn các lệnh trong phần Create server certificate, ấn F5 để tạo certificate và thực hiện backup certificate với private key

— Create server certificate

Use master;

CREATE CERTIFICATE TDE_Server_Cert

WITH SUBJECT = ‘TDE Server Certificate’;

GO

BACKUP CERTIFICATE TDE_Server_Cert

TO FILE = ‘F:\Audit\TDE_Server_Cert.cer’

WITH PRIVATE KEY

(FILE = ‘F:\Audit\TDE_Server_Cert.key’ ,

ENCRYPTION BY PASSWORD = ‘CertPa$$w0rd’);

3. Tạo Database Encryption Key: chọn các lệnh trong phần create DEK, ấn F5 để tạo database encryption key trong DEMODB4 database

–Create DEK

USE DEMODB4;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Cert;

4. Enable Database Encryption: chọn các lệnh trong phần Enable encryption, ấn F5 để thực hiện mã hóa DEMODB4 database,

–Enable encryption

USE master;

ALTER DATABASE DEMODB4

SET ENCRYPTION ON;

SELECT name, is_encrypted FROM sys.databases;

Và xem trong cửa sổ Results, DEMODB4 có is_encrypted là 1(hiện đang mã hóa)