Bài giảng Kiến trúc cài đặt cơ sở dữ liệu - Chương 11: Bảo mật mức hàng trong SQL Server

Bảo mật mức hàng trong SQL Server

1 GV Phi Loan - Khoa CNTT – UIH

GV Phi Loan - Khoa CNTT – UIH

Nội dung

• Case study

– Xây dựng bảng, hàm, thủ tục và trigger cho

mục đích bảo mật mức hàng

GV Phi Loan - Khoa CNTT – UIH

Đặt vấn đề

• SQL Server tuy bảo mật bảng và cột

(vertical security) rất tốt nhưng lại không có khả năng bảo mật mức hàng (row- level security).

• Views với tùy chọn with check option có thể cung cấp dạng bảo mật mức hàng nhưng nếu phải duy trì hàng tá view để quản l{ thì quá phức tạp.

GV Phi Loan - Khoa CNTT – UIH

View

CREATE VIEW

[ < database_name >.] [ < owner > . ] view_name [ ( column [ ,...n ] ) ]

AS

select_statement [ WITH CHECK OPTION ] .

GV Phi Loan - Khoa CNTT – UIH

Lệnh View

• [ WITH CHECK OPTION ]: bắt buộc tất cả các lệnh chỉnh sửa dữ liệu liên quan đến view đều phải tuân theo điều kiện lọc trong mệnh đề select. – Khi 1 hàng bị sửa đổi thông qua view, WITH

CHECK OPTION bảo đảm là dữ liệu vẫn còn nhìn thấy được thông qua view.

– Bất kz lệnh sửa đổi nào mà làm cho các hàng biến mất khỏi view sẽ bị loại trừ và hiển thị thông báo lỗi.

GV Phi Loan - Khoa CNTT – UIH

Ví dụ

CREATE VIEW CAonly AS SELECT au_lname, au_fname, city, state FROM authors WHERE state = 'CA' WITH CHECK OPTION

UPDATE CAOnly Set state = ‘KS’ WHERE state =‘CA’ and au_fname ='Ann' Lệnh update này có thực hiện được

không?

GV Phi Loan - Khoa CNTT – UIH

Case study

• Cơ sở dữ liệu mẫu OBXKites cần thực thi

bảo mật mức hàng.

• Mỗi nhân viên (employee) trong bảng

Contact có thể được cấp các quyền khác nhau (read, write, hay administrator) khi truy xuất dữ liệu về bán hàng và kho hàng tùy theo mỗi chi nhánh.

GV Phi Loan - Khoa CNTT – UIH

Business-security rules

• Tuy dữ liệu về kho, kế hoạch sản xuất được quản l{ bởi riêng phòng kế hoạch, nhưng tất cả phòng ban trong công ty được quyền xem dữ liệu về hàng hóa, tình trạng kho …

• Dữ liệu về mỗi nhân viên chỉ có phòng nhân sự và trưởng phòng của nhân viên đó mới được quyền xem.

GV Phi Loan - Khoa CNTT – UIH

Business-security rules

• Hệ thống đặt mua hàng chỉ cho phép người mua loại nào mới được đặt mua hàng thuộc loại đó.

• Mỗi chi nhánh ngân hàng có thể đọc được dữ liệu khách hàng nhưng chỉ có ngân hàng nào mà khách hàng là thành viên mới được phép chỉnh sửa dữ liệu của khách hàng đó.

GV Phi Loan - Khoa CNTT – UIH

Yêu cầu bảo mật

• Để thực thi việc bảo mật mức hàng, cần 4 thành

phần sau:

1.

2.

3.

4. Security table: chứa danh sách user bao gồm phòng ban nhân viên đó trực thuộc và các quyền read/write. Security procedure: kiểm tra quyền của user cùng với dữ liệu mà user đó yêu cầu. Fetch procedure: thông qua việc kiểm tra security procedure trả về dữ liệu thích hợp Triggers: gọi security procedure để kiểm tra quyền user khi thực thi các lệnh DML.

GV Phi Loan - Khoa CNTT – UIH

Bảng security

• Bảng security table dùng như bảng kết hợp many-to-many giữa 2 bảng contact và location tables.

• Mức độ bảo mật xác định mức truy xuất:

– 0 (or no rows): 0 access – 1: Read access – 2: Write access – 3: Admin access

GV Phi Loan - Khoa CNTT – UIH

Bảng security

• Bảng có 2 khóa ngoại tham chiếu đến 2 bảng contact và location theo cơ chế cascading deletes.

• Bảng có 2 ràng buộc (constraint):

– Hạn chế dữ liệu nhập cho cột security codes

(0–3)

– Ràng buộc duy nhất để bảo đảm mỗi contact và location chỉ có duy nhất 1 mã security.

GV Phi Loan - Khoa CNTT – UIH

Tạo Bảng security

USE OBXKites; CREATE TABLE dbo.Security (

SecurityID int identity NOT NULL PRIMARY KEY NONCLUSTERED, ContactID UniqueIdentifier NOT NULL

REFERENCES dbo.Contact(ContactID) ON DELETE CASCADE,

LocationID UniqueIdentifier NOT NULL

REFERENCES dbo.Location(LocationID) ON DELETE CASCADE,

SecurityLevel INT NOT NULL DEFAULT 0

);

GV Phi Loan - Khoa CNTT – UIH

Hai ràng buộc của bảng security

ALTER TABLE dbo.Security

ADD CONSTRAINT ValidSecurityCode CHECK (SecurityLevel IN (0,1,2,3));

ALTER TABLE dbo.Security

ADD CONSTRAINT ContactLocation UNIQUE (ContactID, LocationID);

GV Phi Loan - Khoa CNTT – UIH

Trigger bảo đảm

CREATE TRIGGER ContactID_RI

ON dbo.Security AFTER INSERT, UPDATE

SET NOCOUNT ON; IF EXISTS(SELECT * FROM Inserted INNER JOIN dbo.Contact

ON Inserted.ContactID = Contact.ContactID WHERE Contact.ContactID IS NULL OR IsEmployee = CAST(0 AS bit))

BEGIN

RAISERROR (‘Foreign Key Constraint: Security.ContactID’, 16, 1); ROLLBACK TRANSACTION; RETURN;

Mục đích của Trigger??

END;

GV Phi Loan - Khoa CNTT – UIH

Admin procedures

• Thủ tục trả về quyền của mỗi user • Thủ tục gán quyền cho mỗi nhân viên • Thủ tục kiểm tra mức bảo mật của nhân

viên

• Hàm kiểm tra bảo mật

GV Phi Loan - Khoa CNTT – UIH

Thủ tục trả về quyền của user

CREATE PROCEDURE pSecurity_Fetch

@LocationCode VARCHAR(15) = NULL, @ContactCode VARCHAR(15) = NULL

SET NOCOUNT ON; SELECT c.ContactCode, l.LocationCode, s.SecurityLevel FROM dbo.Security AS s INNER JOIN dbo.Contact AS c

ON s.ContactID = c.ContactID INNER JOIN dbo.Location AS l ON s.LocationID = l.LocationID

WHERE (l.LocationCode = @LocationCode

OR @LocationCode IS NULL)

AND (c.ContactCode = @ContactCode

OR @ContactCode IS NULL);

GV Phi Loan - Khoa CNTT – UIH

Thủ tục gán quyền security cho user

-- Get LocationID SELECT @LocationID = LocationID

CREATE PROCEDURE pSecurity_Assign @ContactCode VARCHAR(15), @LocationCode VARCHAR(15), @SecurityLevel

FROM dbo.Location WHERE LocationCode = @LocationCode;

INT

IF @@ERROR <> 0 RETURN -100; IF @LocationID IS NULL BEGIN

AS SET NOCOUNT ON; DECLARE @ContactID UNIQUEIDENTIFIER,

@LocationID UNIQUEIDENTIFIER;

RAISERROR (‘Location: "%s" not found’, 15,1,@LocationCode);

RETURN -100;

-- Get ContactID SELECT @ContactID = ContactID

FROM dbo.Contact WHERE ContactCode = @ContactCode;

END; -- Insert INSERT dbo.Security

(ContactID,LocationID, SecurityLevel)

VALUES

IF @@ERROR <> 0 RETURN -100 IF @ContactID IS NULL BEGIN

(@ContactID, @LocationID, @SecurityLevel);

RAISERROR (‘Contact: "%s" not found’, 15,1,@ContactCode);

IF @@ERROR <> 0 RETURN -100; RETURN;

RETURN -100; END;

19 GV Phi Loan - Khoa CNTT – UIH

Tìm thông tin nhân viên và chi nhánh

• Tìm ContactCode của nhân viên:

SELECT ContactCode FROM dbo.Contact WHERE IsEmployee = CAST(1 AS bit); • Tìm mã các chi nhánh (LocationCode):

SELECT LocationCode FROM dbo.Location;

GV Phi Loan - Khoa CNTT – UIH

Gán quyền bảo mật cho các nhân viên

• Gán cho nhân viên 118 các quyền khác nhau ở các chi

nhánh khác nhau: EXEC pSecurity_Assign @ContactCode = ‘118’, @LocationCode = ‘CH’, @SecurityLevel = 3;

EXEC pSecurity_Assign @ContactCode = ‘118’, @LocationCode = ‘Clt’, @SecurityLevel = 2;

EXEC pSecurity_Assign @ContactCode = ‘118’, @LocationCode = ‘Elc’, @SecurityLevel = 1;

GV Phi Loan - Khoa CNTT – UIH

Thủ tục kiểm tra bảo mật

CREATE PROCEDURE p_SecurityCheck

@ContactCode VARCHAR(15), @LocationCode VARCHAR(15), @SecurityLevel INT, @Approved BIT OUTPUT

AS

SET NOCOUNT ON; DECLARE @ActualLevel INT = 0; SELECT @ActualLevel = s.SecurityLevel FROM dbo.Security AS s INNER JOIN dbo.Contact AS c ON s.ContactID = c.ContactID INNER JOIN dbo.Location AS l ON s.LocationID = l.LocationID WHERE c.ContactCode = @ContactCode

AND l.LocationCode = @LocationCode;

GV Phi Loan - Khoa CNTT – UIH

Thủ tục kiểm tra bảo mật (tt)

IF @ActualLevel < @SecurityLevel

SET @Approved = CAST(0 AS bit);

ELSE

SET @Approved = CAST(1 AS bit);

RETURN 0;

• Chạy thử: DECLARE @OK BIT; EXEC p_SecurityCheck @ContactCode = ‘118’, @LocationCode = ‘Clt’, @SecurityLevel = 3, @Approved = @OK OUTPUT;

SELECT @OK;

GV Phi Loan - Khoa CNTT – UIH

Hàm kiểm tra bảo mật

• Hàm fSecurityCheck có cùng chức năng

như thủ tục pSecurity_Check • Mục đích của việc tạo hàm:

– Có thể dùng trực tiếp trong lệnh IF mà

không cần dùng biến để lưu trữ tham số output của thủ tục.

GV Phi Loan - Khoa CNTT – UIH

Hàm fSecurityCheck

CREATE FUNCTION dbo.fSecurityCheck ( @ContactCode VACHAR(15), @LocationCode VARCHAR(15), @SecurityLevel INT) RETURNS BIT AS BEGIN

DECLARE @Approved BIT = CAST(0 AS bit); IF (SELECT s.SecurityLevel FROM dbo.Security AS s INNER JOIN dbo.Contact AS c ON s.ContactID = c.ContactID INNER JOIN dbo.Location AS l ON s.LocationID = l.LocationID WHERE c.ContactCode = @ContactCode

AND l.LocationCode = @LocationCode) >= @SecurityLevel

GV Phi Loan - Khoa CNTT – UIH