Ở module trước chúng ta đã học cách cấp quyền cho user truy cập vào database, trong module này chúng ta sẽ thực hiện kiểm toán việc truy cập vào database và thực hiện mã hóa để ngăn chặn truy cập trái phép vào database. Đây cũng là một vấn đề về bảo mật trong SQL server
Thực hiện xong bài lab này, bạn có thể biết cách sử dụng DML trigger để audit, sử dụng SQL Server audit, thực hiện Transparent Data Encryption (TDE).
Thực Hiện Kiểm Toán và Mã Hóa Dữ liệu
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)
Mr CTL – labs.ctl.vn