Cơ sở dữ liệu - Chương 11: Bảo mật mức hàng hóa trong SQL server

GV Phi Loan - Khoa CNTT – UIH Bảo mật mức hàng trong SQL Server 1 GV Phi Loan - Khoa CNTT – UIH 2 GV Phi Loan - Khoa CNTT – UIH Nội dung • Case study –Xây dựng ba ̉ng, hàm, thu ̉ tục và trigger cho mục đích ba ̉o mâ ̣t mư ́c hàng 3 GV Phi Loan - Khoa CNTT – UIH Đặt vá̂n đề • SQL Server tuy bảo mật bảng và cột (vertical security) rất tó̂t nhưng lại không có khả năng bảo mật mư ́c hàng (row- level security). • Views vơ ́i tùy cho ̣n with check option có thể cung cấp dạng bảo mật mư ́c hàng nhưng nếu phải duy trì hàng tá view để quản l{ thì quá phức ta ̣p. 4 GV Phi Loan - Khoa CNTT – UIH View CREATE VIEW [ .] [ . ] view_name [ ( column [ ,...n ] ) ] AS select_statement [ WITH CHECK OPTION ] . 5 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 tuâ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 nào mà làm cho các hàng biến mât́ khỏi view sẽ bị loại trừ và hiển thị thông báo lỗi. 6 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? 7 GV Phi Loan - Khoa CNTT – UIH Case study • Cơ sở dư ̃ liệu mẫu OBXKites cần thực thi bảo mật mức hàng. • Mõ̂i nhân viên (employee) trong bảng Contact có thể được cấp các quyền khác nhau (read, write, hay administrator) khi truy xuât́ dữ liệu về bán hàng và kho hàng tùy theo mõ̂i chi nhánh. 8 GV Phi Loan - Khoa CNTT – UIH Business-security rules • Tuy dư ̃ liệu về kho, kế hoạch sa ̉n xuất được qua ̉n l{ bởi riêng phòng kế hoạch, nhưng tất ca ̉ phòng ban trong công ty được quyền xem dư ̃ liệu về hàng hóa, tình tra ̣ng kho • Dư ̃ liê ̣u về mỗi nhân viên chỉ có phòng nhân sự và trưởng phòng của nhân viên đó mới được quyêǹ xem. 9 GV Phi Loan - Khoa CNTT – UIH Business-security rules • Hệ thôńg đă ̣t mua hàng chi ̉ cho phép người mua loại nào mới được đă ̣t mua hàng thuô ̣c loa ̣i đó. • Môĩ chi nhánh ngân hàng có thể đo ̣c được dữ liê ̣u khách hàng nhưng chỉ có ngân hàng nào mà khách hàng là thành viên mới được phép chỉnh sửa dư ̃ liê ̣u của khách hàng đó. 10 GV Phi Loan - Khoa CNTT – UIH Yêu cà̂u bảo mật • Để thực thi việc ba ̉o mâ ̣t mức hàng, câǹ 4 thành phâǹ sau: 1. Security table: chư ́a danh sách user bao gồm phòng ban nhân viên đó trực thuô ̣c và các quyền read/write. 2. Security procedure: kiểm tra quyền của user cùng vơ ́i dư ̃ liệu mà user đó yêu câù. 3. Fetch procedure: thông qua việc kiểm tra security procedure tra ̉ về dư ̃ liệu thích hợp 4. Triggers: go ̣i security procedure để kiê ̉m tra quyền user khi thực thi các lệnh DML. 11 GV Phi Loan - Khoa CNTT – UIH Bảng security • Ba ̉ng security table dùng như ba ̉ng kết hợp many-to-many giữa 2 bảng contact và location tables. • Mức độ bảo mật xác định mức truy xuât́: –0 (or no rows): 0 access –1: Read access –2: Write access –3: Admin access 12 GV Phi Loan - Khoa CNTT – UIH Bảng security • Bảng có 2 khóa ngoại tham chiếu đến 2 ba ̉ng contact và location theo cơ chế cascading deletes. • Bảng có 2 ràng buô ̣c (constraint): – Ha ̣n chê ́dữ liệu nhâ ̣p cho cô ̣t security codes (0–3) – Ràng buô ̣c duy nhất để bảo đảm mỗi contact và location chỉ có duy nhất 1 mã security. 13 GV Phi Loan - Khoa CNTT – UIH Tạo Bả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 ); 14 GV Phi Loan - Khoa CNTT – UIH Hai ràng buộc của bả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); 15 GV Phi Loan - Khoa CNTT – UIH Trigger bảo đảm CREATE TRIGGER ContactID_RI ON dbo.Security AFTER INSERT, UPDATE AS 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; END; 16 Mu ̣c đích của Trigger?? GV Phi Loan - Khoa CNTT – UIH Admin procedures • Thủ tục tra ̉ vê ̀quyền của mõ̂i user • Thủ tục gán quyền cho mõ̂i nhân viên • Thủ tục kiểm tra mức bảo mật của nhân viên • Hàm kiểm tra bảo mật 17 GV Phi Loan - Khoa CNTT – UIH Thủ tục trả về quyêǹ của user CREATE PROCEDURE pSecurity_Fetch @LocationCode VARCHAR(15) = NULL, @ContactCode VARCHAR(15) = NULL AS 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); 18 GV Phi Loan - Khoa CNTT – UIH Thu ̉ tu ̣c gán quyền security cho user CREATE PROCEDURE pSecurity_Assign @ContactCode VARCHAR(15), @LocationCode VARCHAR(15), @SecurityLevel INT AS SET NOCOUNT ON; DECLARE @ContactID UNIQUEIDENTIFIER, @LocationID UNIQUEIDENTIFIER; -- Get ContactID SELECT @ContactID = ContactID FROM dbo.Contact WHERE ContactCode = @ContactCode; IF @@ERROR 0 RETURN -100 IF @ContactID IS NULL BEGIN RAISERROR (‘Contact: "%s" not found’, 15,1,@ContactCode); RETURN -100; END; -- Get LocationID SELECT @LocationID = LocationID FROM dbo.Location WHERE LocationCode = @LocationCode; IF @@ERROR 0 RETURN -100; IF @LocationID IS NULL BEGIN RAISERROR (‘Location: "%s" not found’, 15,1,@LocationCode); RETURN -100; END; -- Insert INSERT dbo.Security (ContactID,LocationID, SecurityLevel) VALUES (@ContactID, @LocationID, @SecurityLevel); IF @@ERROR 0 RETURN -100; RETURN; 19 GV Phi Loan - Khoa CNTT – UIH Tìm thông tin nhân viên và chi nhánh • Tìm ContactCode của nhân viên: SELECT ContactCode FROM dbo.Contact WHERE IsEmployee = CAST(1 AS bit); • Tìm mã các chi nhánh (LocationCode): SELECT LocationCode FROM dbo.Location; 20 GV Phi Loan - Khoa CNTT – UIH Gán quyền bảo mật cho các nhân viên • Gán cho nhân viên 118 các quyền khác nhau ở các chi nhánh khá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; 21 GV Phi Loan - Khoa CNTT – UIH Thủ tục kiểm tra bảo mậ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; 22 GV Phi Loan - Khoa CNTT – UIH Thủ tục kiểm tra bảo mật (tt) IF @ActualLevel < @SecurityLevel SET @Approved = CAST(0 AS bit); ELSE SET @Approved = CAST(1 AS bit); RETURN 0; • Chạy thử: DECLARE @OK BIT; EXEC p_SecurityCheck @ContactCode = ‘118’, @LocationCode = ‘Clt’, @SecurityLevel = 3, @Approved = @OK OUTPUT; SELECT @OK; 23 GV Phi Loan - Khoa CNTT – UIH Hàm kiểm tra bảo mật • Hàm fSecurityCheck có cùng chức năng như thủ tục pSecurity_Check • Mục đích của việc ta ̣o hàm: –Có thê ̉ dùng trực tiếp trong lê ̣nh IF mà không cần dùng biến để lưu trữ tham số output cu ̉a thu ̉ tục. 24 GV Phi Loan - Khoa CNTT – UIH Hà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 25 GV Phi Loan - Khoa CNTT – UIH Hàm fSecurityCheck (tt) BEGIN SET @Approved = CAST(1 AS bit); END; RETURN @Approved; END; 26 GV Phi Loan - Khoa CNTT – UIH Kiểm tra hàm fSecurityCheck (tt) IF dbo.fSecurityCheck( ‘118’, ‘Clt’, 3) = CAST(0 AS bit) BEGIN RAISERROR(‘Security Violation’, 16,1); ROLLBACK TRANSACTION; RETURN -100; END; 27