Bài giảng Hệ thống thông tin - Chương 5: Bảo mật và an toàn dữ liệu Trigger - Phạm Nguyên Thảo

Nội dung text: Bài giảng Hệ thống thông tin - Chương 5: Bảo mật và an toàn dữ liệu Trigger - Phạm Nguyên Thảo

1. Chương 5: Bảo mật và an toàn dữ liệu Trigger Phạm Nguyên Thảo pnthao@fit.hcmuns.edu.vn Trường Đại học Khoa học Tự nhiên Khoa Công nghệ Thông tin Bộ môn Hệ thống Thông tin
2. Nội dung • Trigger • Khung nhìn • Quản trị quyền người dùng • Sao lưu và phục hồi dữ liệu 2
3. Trigger -Giới thiệu • Là một loại stored procedure đặc biệt – Tự động thực hiện khi có thao tác insert, delete hoặc update trên dữ liệu – Thường dùng để kiểm tra các ràng buộc toàn vẹn của CSDL hoặc các qui tắc nghiệp vụ. – Một trigger được định nghĩa trên một bảng, nhưng các xử lý trong trigger có thể sử dụng nhiều bảng khác. 3
4. Trigger – giới thiệu (tt) • Xử lý của trigger thường cần sử dụng đến hai bảng tạm: – Inserted: chứa các dòng vừa mới được thao tác insert/ update thêm vào bảng. – Deleted: chứa các dòng vừa mới bị xóa khỏi bảng bởi thao tác update/delete. (update = delete dòng chứa giá trị cũ+ insert dòng chứa giá trị mới) – Được HQT tạo/xóa tự động khi lính canh tương ứng được/bỏ kích hoạt. 4
5. Trigger – giới thiệu • Inserted và Deleted là các bảng trong bộ nhớ chính – Cục bộ cho mỗi trigger – Có cấu trúc giống như bảng (table) mà trigger định nghĩa trên đó – Chỉ tồn tại trong thời gian trigger đang xử lý 5
6. Trigger – giới thiệu(tt) • Nếu thao tác insert/ delete/ update thực hiện trên nhiều dòng, trigger cũng chỉ được gọi một lần Bảng inserted/ deleted có thể chứa nhiều dòng 6
7. Khai báo trigger • Cú pháp: Create trigger tên_trigger On {tên_bảng|tên_view} {For| After| Instead of } { [delete] [,] [insert] [,] [update] } As { các lệnh T-sql } go 7
8. Khai báo trigger (tt) • For | After: – Trigger được gọi thực hiện sau khi thao tác delete/ insert/ update tương ứng đã được thực hiện thành công Các dòng mới được thêm chứa đồng thời trong bảng dữ liệu và bảng inserted Các dòng bị xoá chỉ nằm trong bảng deleted (đã bị xoá khỏi bảng dữ liệu) – Có thể xử lý quay lui thao tác đã thực hiện bằng lệnh rollback transaction 8
9. Khai báo trigger (tt) • Instead of: – Trigger được gọi thực hiện thay cho thao tác delete/ insert/ update tương ứng Các dòng mới được thêm chỉ chứa trong bảng inserted Các dòng bị chỉ định xoá nằm đồng thời trong bảng deleted và bảng dữ liệu (dữ liệu không bị xoá). – Trigger Instead of thường được dùng để xử lý cập nhật trên khung nhìn (view). 9
10. Khai báo trigger (tt) • Lưu ý: – Lệnh tạo trigger phải là lệnh đầu tiên trong một query batch – Trên một bảng có thể định nghĩa nhiều trigger for/after cho mỗi thao tác – nhưng chỉ có thể định nghĩa một trigger instead of cho mỗi thao tác 10
11. Khai báo trigger (tt) – Không thể định nghĩa trigger instead of update/ delete trên bảng có cài đặt khoá ngoại dạng update cascade/ delete cascade – Trong thân trigger, có thể sử dụng hàm Update(tên_cột) để kiểm tra xem việc cập nhật/ insert được thực hiện trên cột nào. Update(tên_cột) = true : có thực hiện cập nhật trên cột tên_cột 11
12. Trigger -Ví dụ 1 • Cho CSDL: DatHang(MaPDT, NgayDH, ) GiaoHang(MaPGH, MaPDH, NgayGH, ) Ràng buộc: Ngày giao hàng không thể nhỏ hơn ngày đặt hàng tương ứng 12
13. Trigger -Ví dụ 1 • Bảng tầm ảnh hưởng: Thêm Xoá Sửa DatHang - -(*) +(NgayDH) GiaoHang + - +(NgayGH, MaPDH) Cần cài đặt trigger cho thao tác sửa trên đặt hàng, và thêm/sửa trên giao hàng 13
14. Trigger -Ví dụ 1 (tt) • Trigger cho thao tác thêm và sửa trên giao hàng: Create trigger tr_GH_ins_upd_NgayGH On GIAOHANG for insert, update As 14
15. Trigger -Ví dụ 1 (tt) if update(MaPDH) or update (NgayGH) if exists(select * from inserted i, DatHang d where i.MaPDH = d.MaPDH and i.NgayGH<d.NgayDH) begin raiserror (N‘Ngày GH không thể nhỏ hơn ngày ĐH,15,1) rollback tran end go 15
16. Trigger -Ví dụ 1 (tt) • Trigger cho thao tác sửa trên đặt hàng? 16
17. Trigger - Ví dụ 2 • Cho CSDL: 17
18. Trigger - Ví dụ 2 (tt) • Qui định: – Các trận đấu của cùng một đội bóng phải cách nhau tối thiểu 24 giờ – Cầu thủ chỉ có thể thi đấu trong một trận đấu mà đội của cầu thủ đó tham gia • Cài đặt các trigger để kiểm tra các ràng buộc trên 18
19. Thay đổi và xóa trigger • Thay đổi nội dung trigger: Thay từ khóa create trong các lệnh tạo trigger bằng từ khóa alter • Xóa trigger Drop trigger tên_trigger_cần _xóa [, n] 19
20. Nội dung • Trigger • Khung nhìn • Quản trị quyền người dùng • Sao lưu và phục hồi dữ liệu 20
21. View – Khái niệm • View (khung nhìn) là một bảng ảo được định nghĩa bởi một lệnh select – Đối với người dùng: view giống như một bảng thật – Dữ liệu của view là dữ liệu trong một hoặc nhiều bảng thật (view không chứa dữ liệu thật sự) Sự thay đổi dữ liệu tương ứng trong các bảng thật luôn được phản ánh lên view 21
22. View – Khái niệm (tt) • Mục đích sử dụng: – Thể hiện dữ liệu ở dạng phù hợp với từng nhóm nghiệp vụ/ nhóm NSD. – Bảo mật dữ liệu(security). – Lược đồ ngoài 22
23. View – Khai báo • Cú pháp: Create view view_name [(column_name [ , n ])] [with Schemabinding] As select_statement 23
24. View – khai báo (tt) – Schemabinding: gắn kết view với lược đồ, khi đó: Không thể xóa bảng hay view khác có liên quan đến định nghĩa của view có schemabinding Không thể thay đổi cấu trúc bảng/ view nếu việc thay đổi này ảnh hưởng đến định nghĩa view có schemabinding Các tên bảng, view trong câu select phải được viết dưới dạng owner.Tênbảng/Tênview và tất cả phải trong cùng 1 database. Nếu view schemabinding định nghĩa trên một view khác, view đó cũng phải ở dạng schemabinding. – Câu select định nghĩa view phải chỉ định rõ thuộc tính (không được select * ) 24
25. View – Khai báo (tt) • Ví dụ: Create view ThiSinh_Nganh (SBD, HoTen, MaNganh, TenNganh) as select ts.SBD, ts.HoTen, ts.Nganh, n.TenNganh from ThiSinh ts, Nganh n where ts.Nganh = n.MaNganh 25
26. Khai báo view – Lưu ý • Lệnh select trong định nghĩa view không được chứa: – Order by, nếu có phải kèm với TOP – Compute/ Compute by – Select into • Không thể gắn kết rule hay default vào view • Không thể khai báo trigger for/after trên view (chỉ có thể khai báo trigger instead of) • Một view có thể được định nghĩa trên một view khác, nhưng không quá 32 cấp 26
27. View – Sử dụng • Truy xuất dữ liệu từ view (select) – Như truy xuất trên bảng • Cập nhật dữ liệu (insert/ delete/ update) – Dùng các lệnh insert / delete / update tương tự như thao tác trên bảng. cần xem xét thêm một số vấn đề dưới đây nguyên tắc chung: có thể cập nhật dữ liệu 1 dòng của view nếu có thể xác định được chính xác các dòng dữ liệu được cập nhật tương ứng trong bảng gốc. – Dữ liệu cập nhật trên view sẽ được cập nhật trong các bảng dữ liệu thật sự bên dưới view 27
28. Cập nhật dữ liệu trên view • View chỉ có thể được cập nhật (insert/ update/ delete) nếu: – lệnh select định nghĩa view không chứa: Cột dẫn suất (tính toán từ các cột dữ liệu thật sự) Trong một số trường hợp có thể xóa được. Các hàm tổng hợp (count, sum, avg, max, ) Group by,Top, Select distinct Union (trừ partitioned view) 28
29. Cập nhật dữ liệu trên view (tt) – Trong mệnh đề from của lệnh select phải tham chiếu đến ít nhất một bảng ( hoặc view với dữ liệu được lấy từ bảng), và phải select ít nhất một cột từ dữ liệu. – Lệnh cập nhật phải viết sao cho chỉ làm thay đổi dữ liệu thật sự trong một bảng (lệnh xóa chỉ thực hiện được nếu view chỉ tham chiếu đến đúng một bảng) – Không thỏa các điều kiện trên, nhưng có trigger instead of for insert/ update/ delete tương ứng 29
30. Cập nhật dữ liệu trên view (tt) • Ví dụ: Các view định nghĩa như sau không thể cập nhật được: Create view V_SoNV As Select MaPhong, Count (*) as SoNV From NhanVien Group by MaPhong Create View V_NhanVien As select MaNV, HoNV + ‘ ‘ +TenNV as HoTen Có thể From NhanVien xóa Create View V1 As select getdate() as NgayHienHanh 30
31. Cập nhật view với Trigger Instead of • Có thể dùng trigger instead of để cập nhật view – Tự xử lý thao tác cập nhật • Ví dụ: Viết trigger instead of để cho phép insert trên view V_NhanVien 31
32. Cập nhật view với Trigger Instead of create trigger tr_Ins_View_NV on V_NhanVien instead of insert as insert into NhanVien (MaNV, HoNV, TenNV) select MaNV, left(TenNV, CharIndex(' ',TenNV)), right(TenNV, len(TenNV) - CharIndex(‘ ',TenNV)) from inserted go 32
33. Indexed View • Dùng khi dữ liệu của view lớn, truy xuất thường xuyên • Cài đặt một cluster index trên view view trở thành index view – Dữ liệu và index của view được lưu trữ lại như một bảng – Có thể định nghĩa thêm các chỉ mục nonclustered trên indexed view – View phải được khai báo với SchemaBinding 33
34. Thay đổi và xóa view • Thay đổi cấu trúc view: Thay từ khóa create trong các lệnh tạo view bằng từ khóa alter • Xóa view Drop view tên_view_cần _xóa [, n] 34