Bài giảng Các hệ cơ sở dữ liệu - 3. An toàn và khôi phục dữ liệu

Mục Tiêu
Khái niệm an toàn dữ liệu.
Các loại sự cố.
Sao lưu và phục hồi dữ liệu. Cài ñặt trên SQL Server. DBMS03 – Slides 2
An Toàn Dữ Liệu
Các cơ chế an toàn dữ liệu:
Mã hóa dữ liệu
Phân quyền truy cập DBMS03 – Slides 3 Phân quyền truy cập
Tạo bảng ảo View
Cấp phát quyền cho người dùng: Dùng lệnh grant và revoke
GRANT Update ON Product TO Smith DBMS03 – Slides 4
Cấp quyền sửa trên bảng Product cho user Smith
REVOKE Update ON Product TO Smith
Bỏ quyền sửa trên bảng Product của user Smith
Tạo user truy cập CSDL với quyền giới hạn
Xem hướng dẫn demo SQLSecurity Demo.rar GRANT
Cho phép người dùng có thể thực thi một số câu lệnh trên CSDL hiện tại.
Quyền trên câu lệnh: GRANT {ALL | statement [,..n]} TO DBMS03 – Slides 5 security_account[,..n]
Statement: câu lệnh ñược phép thực thi, là một trong các câu lệnh CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, BACKUP DATABASE, BACKUP LOG.
Security_account: tài khoản (user) mà các quyền này ñược phép áp dụng. GRANT (tt)
Quyền trên ñối tượng: GRANT { ALL [ PRIVILEGES ] | permission [ ,...n ] } { [ ( column [ ,...n ] ) ] ON { table| view } | ON { table view } [ ( column [ ,...n ] ) ] DBMS03 – Slides 6 | ON { stored_procedure| extended_procedure } | ON { user_defined_function} } TO security_account [ ,...n ] [ WITH GRANT OPTION ] [ AS { group | role }] GRANT (tt)
Permission: Nếu ñối tượng là Table, View thì permission là SELECT, INSERT, DELETE, UPDATE, REFERENCES. Nếu ñối tượng là Stored Procedure thì permission là EXCUTE.
WITH GRANT OPTION: lựa chọn này cho phép tài khoản này ñược lấy các quyền trên các ñối tượng như vậy cho các tài khoản khác. DBMS03 – Slides 7
AS {group|role}: tên tài khoản trong CSDL hiện tại có quyền thực thi câu lệnh GRANT. Dùng lựa chọn này khi cần cấp phát quyền cho một nhóm người dùng và một người dùng không thuộc nhóm.
Một số câu lệnh không yêu cầu cấp phát quyền thực thi: BEGIN TRANSACTION, COMMIT TRANSACTION, PRINT, RAISERROR, ROLLBACK TRANSACTION, SAVE TRANSACTION, SET GRANT (tt)
Ví dụ 1: GRANT CREATE DATABASE, CREATE TABLE TO user1, user2
Ví dụ 2: Cấp phát quyền thực thi select DBMS03 – Slides 8 trên bảng authors cho public GRANT SELECT ON authors TO public -- public role REVOKE
Loại bỏ quyền vừa cấp phát, hoặc ñã bị từ chối cho một người dùng trong CSDL hiện tại. REVOKE { ALL | statement [ ,...n ] } FROM security_account [ ,...n ] REVOKE [ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | permission [ ,...n ] } DBMS03 – Slides 9 { [ ( column [ ,...n ] ) ] ON { table | view } | ON { table | view } [ ( column [ ,...n ] ) ] | ON { stored_procedure | extended_procedure } | ON { user_defined_function } } { TO | FROM } security_account [ ,...n ] [ CASCADE ] [ AS { group | role } ] REVOKE (tt)
Ví dụ: Bỏ quyền tạo bảng của user1 REVOKE CREATE TABLE FROM user1; DBMS03 – Slides 10 ROLE – Vai trò 1. Thêm vai trò:
Vai trò (role) là một kiểu người dùng trong CSDL với các quyền ñược cấp phát giống nhau.
sp_addrole [ @rolename= ] 'role' [ , [ @ownername = ] 'owner' ] DBMS03 – Slides 11 trong ñó owner là tên chủ sở hữu của vai trò mới.
Ví dụ: EXEC sp_addrole 'Managers' 2. Xóa ñi một vai trò:
sp_droprole [ @rolename = ] 'role‘
Ví dụ: EXEC sp_droprole 'Managers' • View là bảng ảo giúp giới hạn truy cập một số cột dòng trên các bảng dữ liệu. • Views ñảm bảo tính bảo mật bằng các giới hạn truy cập sau: • Specific rows of a table Bảng Ảo View DBMS03 – Slides 12 • Specific columns of a table • Specific rows and columns of a table • The rows fetched using joins • Cú Pháp tạo View: CREATE VIEW ViewName AS SELECT Col1, Col2 FROM Table1 WHERE Col = • Thuận lợi của View: • Cung cấp dữ liệu cần thiết cho người dùng • Che dấu sự phức tạp của dữ liệu • Tổ chức dữ liệu từ nhiều tài nguyên không Bảng Ảo View DBMS03 – Slides 13 ñồng nhất • Giảm kích cỡ của ñối tượng Problem statement: Bạn thường xuyên phải thực hiện các câu truy vấn dạng như sau: SELECT vFirstName, vSkill FROM Employee JOIN PositionSkill ON Employee.cCurrentPosition = PositionSkill.cPositionCode Bảng Ảo View DBMS03 – Slides 14 JOIN Skill ON PositionSkill.cSkillCode = Skill.cSkillCode SELECT vFirstName, vLastName, vSkill FROM Employee JOIN PositionSkill ON Employee.cCurrentPosition =PositionSkill.cPositionCode JOIN Skill ON PositionSkill.cSkillCode = Skill.cSkillCode SELECT vFirstName, vLastName, vSkill FROM Employee JOIN PositionSkill ON Employee.cCurrentPosition=PositionSkill.cPositionCode JOIN Skill ON PositionSkill.cSkillCode =Skill.cSkillCode Bảng Ảo View DBMS03 – Slides 15 WHERE vFirstName = 'Angela‘ SELECT vFirstName, vLastName, vQualification, vSkill FROM Employee JOIN PositionSkill ON Employee.cCurrentPosition = PositionSkill.cPositionCode JOIN Skill ON PositionSkill.cSkillCode = Skill.cSkillCode Solution • Action: • The tables from where the view derives its data are Employee, Bảng Ảo View DBMS03 – Slides 16 positionSkill, and Skill • The columns that are to be included in the view are vFirstName, vLastName, and vQualification from the Employee table and vSkill from the Skill table Solution CREATE VIEW vwEmpSkill AS SELECT vFirstName, vLastName, Bảng Ảo View DBMS03 – Slides 17 vQualification, vSkill FROM Employee JOIN PositionSkill ON Employee.cCurrentPosition = PositionSkill.cPositionCode JOIN Skill ON PositionSkill.cSkillCode = Skill.cSkillCode Query on View: • SELECT vFirstName, vSkill FROM vwEmpSkill • SELECT vFirstName, vLastName, Bảng Ảo View DBMS03 – Slides 18 vSkill FROM vwEmpSkill Create the view • A view can be created using the CREATE VIEW statement • Bảng Ảo View DBMS03 – Slides 19 Syntax CREATE VIEW view_name [(column_name [, column_name]...)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION] Altering, Dropping, and Renaming Views • Altering Views • You can modify a view by using the ALTER VIEW Bảng Ảo View DBMS03 – Slides 20 statement • Syntax ALTER VIEW view_name [(column_name)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION] Altering, Dropping, and Renaming Views (Contd.) • Dropping Views • Bảng Ảo View DBMS03 – Slides 21 You can drop a view from a database by using the DROP VIEW statement • Syntax DROP VIEW view_name Hạn chế của View
Không thể chứa mệnh ñề COMPUTE hoặc COMPUTE BY
Không thể chứa mệnh ñề ORDER BY trừ khi có TOP n DBMS03 – Slides 22
Không thể chứa mệnh ñề INTO
Không thể chứa các table tạm
Có tối ña 1024 cột Bài tập áp dụng
Loai (MaLoai, TenLoai)
SanPham (MaSP, TenSP, MaLoai)
NhanVien (MaNV, HoTen, NgaySinh, Phai)
PhieuXuat (MaPX, NgayLap, MaNV) DBMS03 – Slides 23
ChiTietXuat (MaPX, MaSP, SoLuong)
Tạo View vThongKe cho biết “mã sản phẩm, tên sản phẩm, tổng số lượng xuất của từng sản phẩm trong năm 2009”. Lấy dữ liệu từ View này sắp xếp tăng dần theo cột TenSP. Một số loại sự cố
Sự cố do thực hiện thao tác sai: thêm xóa sửa dữ liệu không ñúng
Trên CSDL thường không thể nhấn Crtl + Z ñể undo lại một thao tác như insert, DBMS03 – Slides 25 delete, update hay drop.
Sự cố do hệ thống phần mềm
Sự cố do lỗi thiết bị Khôi Phục Dữ Liệu
Khôi phục sau sự cố từ một giao tác
Dựa vào file log
Khôi phục sau sự cố hệ thống DBMS03 – Slides 26
Dựa vào file backup
Khôi phục sau sự cố từ thiết bị lưu trữ
??? Cài ñặt trên SQL Server
Sao lưu (Backup)
Khôi phục (Restore) DBMS03 – Slides 27 Sao lưu (Backup)
Full Database Backups : Copy tất cả data files trong một database. Tất cả những user data và database objects như system tables, indexes, user-defined tables ñều ñược backup.
Differential Database Backups : Copy những thay ñổi trong tất cả data files kể từ lần full backup gần nhất.
File or File Group Backups : Copy một data file ñơn DBMS03 – Slides 28 hay một file group.
Differential File or File Group Backups : Tương tự như differential database backup nhưng chỉ copy những thay ñổi trong data file ñơn hay một file group.
Transaction Log Backups : Ghi nhận một cách thứ tự tất cả các transactions chứa trong transaction log file kể từ lần transaction log backup gần nhất. Loại backup này cho phép ta phục hồi dữ liệu trở ngược lại vào một thời ñiểm nào ñó trong quá khứ mà vẫn ñảm bảo tính ñồng nhất (consistent). Sao lưu (Backup)
DBMS03 – Slides 29 Trong ví dụ này ta schedule một Full Database Backup vào ngày Chủ Nhật và Differential Backup vào các ngày thứ Ba và Thứ Năm. Transaction Log Backup ñược schedule hằng ngày. Thuật ngữ về Backup Thuật Ngữ Giải Thích Backup Quá trình copy toàn bộ hay một phần của database, transaction log, file hay file group hình thành một backup set. Backup set ñược chứa trên backup media (tape or disk) bằng cách sử dụng một backup device (tape drive name hay physical filename) DBMS03 – Slides 30 Backup Device Một file vật lý (như C:\SQLBackups\Full.bak) hay tape drive cụ thể (như \\.\Tape0) dùng ñể record một backup vào một backup media. Backup File File chứa một backup set Backup Media Disk hay tape ñược sử dụng ñể chứa một backup set. Backup media có thể chứa nhiều backup sets (ví dụ như từ nhiều SQL Server 2000 backups và từ nhiều Windows 2000 backups). Backup Set Một bộ backup từ một lần backup ñơn ñược chứa trên backup media. Backup
Chúng ta có thể tạo một backup device cố ñịnh (permanent) hay tạo ra một backup file mới cho mỗi lần backup. Thông thường chúng ta sẽ tạo một backup device cố ñịnh ñể có thể dùng ñi dùng lại ñặc biệt cho việc tự ñộng hóa công việc DBMS03 – Slides 31 backup.
Ðể backup database bạn có thể dùng Backup Wizard hoặc click lên trên database muốn backup sau ñó Right-clickAll Tasks  Backup Database Ví dụ Backup DBMS03 – Slides 32 Phục Hồi (Restore)
Full Recovery Model: Ðây là model cho phép phục hồi dữ liệu với ít rủi ro nhất. Nếu một database ở trong mode này thì tất cả các hoạt ñộng không chỉ insert, update, delete mà kể cả insert bằng Bulk Insert, hay bcp ñều ñược log vào transaction log file. Khi có sự cố thì ta có thể phục hồi lại dữ liệu ngược trở lại tới một thời ñiểm trong quá khứ. Khi data file bị hư nếu ta có thể backup ñược transaction log file thì ta có thể phục hồi database ñến thời ñiểm transaction gần nhất ñược commited. DBMS03 – Slides 33
Bulk-Logged Recovery Model: Ở mode này các hoạt ñộng mang tính hàng loạt như Bulk Insert, bcp, Create Index, WriteText, UpdateText chỉ ñược log minimum vào transaction log file ñủ ñể cho biết là các hoạt ñộng này có diễn ra mà không log toàn bộ chi tiết như trong Full Recovery Mode. Các hoạt ñộng khác như Insert, Update, Delete vẫn ñược log ñầy ñủ ñể dùng cho việc phục hồi sau này.
Simple Recovery Model: Ở mode này thì Transaction Log File ñược truncate thường xuyên và không cần backup. Với mode này bạn chỉ có thể phục hồi tới thời ñiểm backup gần nhất mà không thể phục hồi tới một thời ñiểm trong quá khứ. Phục hồi (Restore)
Trước khi restore database ta phải xác ñịnh ñược thứ tự file cần restore. Các thông tin này ñược SQL Server chứa trong msdb database và sẽ cho ta biết backup device nào, ai DBMS03 – Slides 34 backup vào thời ñiểm nào. Sau ñó ta tiến hành restore. Ðể restore bạn Right-clickAll TasksRestore database Phục hồi (Restore)
Nếu bạn restore từ một instance khác của SQL Server hay từ một server khác bạn có chọn From device option và chọn backup device (file backup) tương ứng . DBMS03 – Slides 35
Lưu ý nếu bạn muốn overwrite database có sẵn với data ñược backup bạn có thể chọn option Force restore over existing database như hình vẽ sau: Tóm lại
An toàn dữ liệu
Các loại sự cố thường xảy ra
Sao lưu và phục hồi dữ liệu trên SQL Server DBMS03 – Slides 36 Câu hỏi
Phân biệt: thông tin (information), dữ liệu (data) và tri thức (knowlegde)
Transaction là gì?
Nếu không sao lưu dữ liệu thường xuyên thì gặp phải vấn ñề gì? DBMS03 – Slides 37
Một số database object trong SQL Server? Table, View, Store, Trigger, Index, User, (Cursor)
Một số action object trong SQL Server? Backup, Restore, Transaction, DTS, BCP