Bài giảng An toàn và khôi phục dữ liệu Các hệ cơ sở dữ liệu của Lương Trần Hy Hiến

Mục Tiêu

(cid:1) Khái niệm an toàn dữ liệu. (cid:1) Các loại sự cố. (cid:1) Sao lưu và phục hồi dữ liệu. (cid:1) Cài ñặt trên SQL Server. Cài ñặt trên SQL Server.

DBMS03 – Slides 2

Phân quyền truy cập

An Toàn Dữ Liệu

(cid:1) Các cơ chế an toàn dữ liệu:

(cid:1) Tạo bảng ảo View (cid:1) Cấp phát quyền cho người dùng: Dùng

(cid:1) Mã hóa dữ liệu (cid:1) Phân quyền truy cập

lệnh grant và revoke GRANT Update ON Product TO Smith (cid:1) GRANT Update ON Product TO Smith (cid:1) Cấp quyền sửa trên bảng Product cho user Smith

(cid:1) REVOKE Update ON Product TO Smith

(cid:1) Bỏ quyền sửa trên bảng Product của user Smith

(cid:1) Tạo user truy cập CSDL với quyền giới

hạn (cid:1) Xem hướng dẫn demo SQLSecurity Demo.rar

DBMS03 – Slides 3 DBMS03 – Slides 4

GRANT

GRANT (tt)

(cid:1) 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.

(cid:1) Quyền trên ñối tượng: GRANT { ALL [ PRIVILEGES ] | permission [ ,...n ] } {

(cid:1) Quyền trên câu lệnh: GRANT {ALL | statement [,..n]} TO GRANT {ALL | statement [,..n]} TO security_account[,..n]

[ ( column [ ,...n ] ) ] ON { table| view } | ON { table view } [ ( column [ ,...n ] ) ] | ON { table view } [ ( column [ ,...n ] ) ] | ON { stored_procedure| extended_procedure } | ON { user_defined_function}

(cid:1) 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. (cid:1) Security_account: tài khoản (user) mà các quyền

} TO security_account [ ,...n ] [ WITH GRANT OPTION ] [ AS { group | role }]

này ñược phép áp dụng.

DBMS03 – Slides 5 DBMS03 – Slides 6

GRANT (tt)

(cid:1) 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.

(cid:1) Ví dụ 1: GRANT CREATE DATABASE, CREATE

TABLE TO user1, user2

(cid:1) 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. khoản khác.

(cid:1) Ví dụ 2: Cấp phát quyền thực thi select (cid:1) Ví dụ 2: Cấp phát quyền thực thi select

trên bảng authors cho public

(cid:1) 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.

GRANT SELECT ON authors TO public

-- public role

(cid:1) 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

DBMS03 – Slides 7 DBMS03 – Slides 8

REVOKE

REVOKE (tt)

(cid:1) Loại bỏ quyền vừa cấp phát, hoặc ñã bị từ chối cho

(cid:1) Ví dụ: Bỏ quyền tạo bảng của user1

một người dùng trong CSDL hiện tại.

REVOKE CREATE TABLE FROM user1;

REVOKE { ALL | statement [ ,...n ] } FROM security_account [ ,...n ] REVOKE [ GRANT OPTION FOR ]

{ ALL [ PRIVILEGES ] | permission [ ,...n ] } { ALL [ PRIVILEGES ] | permission [ ,...n ] } {

[ ( 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 } ] DBMS03 – Slides 9 DBMS03 – Slides 10

Bảng Ảo View

ROLE – Vai trò

• View là bảng ảo giúp giới hạn truy cập một số cột

1. Thêm vai trò:

dòng trên các bảng dữ liệu.

(cid:1) Vai trò (role) là một kiểu người dùng trong CSDL với

• Views ñảm bảo tính bảo mật bằng các giới hạn truy

các quyền ñược cấp phát giống nhau. (cid:1) sp_addrole [ @rolename= ] 'role' [ , [

@ownername = ] 'owner' ] @ownername = ] 'owner' ]

trong ñó owner là tên chủ sở hữu của vai trò mới.

Specific rows of a table Specific rows of a table

(cid:1) Ví dụ: EXEC sp_addrole 'Managers'

Specific columns of a table

Specific rows and columns of a table

cập sau: • • • • •

2. Xóa ñi một vai trò:

(cid:1) sp_droprole [ @rolename = ] 'role‘ (cid:1) Ví dụ: EXEC sp_droprole 'Managers'

• Cú Pháp tạo View: CREATE VIEW ViewName

The rows fetched using joins

SELECT Col1, Col2 FROM Table1 WHERE Col = …

DBMS03 – Slides 11 DBMS03 – Slides 12

Bảng Ảo View

Problem statement: Bạn thường xuyên phải thực hiện các

• Thuận lợi của View:

câu truy vấn dạng như sau:

SELECT vFirstName, vSkill FROM Employee JOIN PositionSkill ON

Employee.cCurrentPosition = PositionSkill.cPositionCode Employee.cCurrentPosition = PositionSkill.cPositionCode

• 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 • Tổ chức dữ liệu từ nhiều tài nguyên không

JOIN Skill ON

ñồng nhất

PositionSkill.cSkillCode = Skill.cSkillCode

• Giảm kích cỡ của ñối tượng

SELECT vFirstName, vLastName, vSkill FROM Employee JOIN PositionSkill ON

Employee.cCurrentPosition =PositionSkill.cPositionCode

JOIN Skill ON PositionSkill.cSkillCode = Skill.cSkillCode

DBMS03 – Slides 13 DBMS03 – Slides 14

Bảng Ảo View

SELECT vFirstName, vLastName, vSkill

FROM Employee JOIN PositionSkill ON

Solution • Action:

Employee.cCurrentPosition=PositionSkill.cPositionCode

JOIN Skill ON PositionSkill.cSkillCode =Skill.cSkillCode JOIN Skill ON PositionSkill.cSkillCode =Skill.cSkillCode

WHERE vFirstName = 'Angela‘

• The tables from where the view derives its data are Employee, derives its data are Employee, positionSkill, and Skill

SELECT vFirstName, vLastName, vQualification, vSkill

• 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

FROM Employee JOIN PositionSkill ON Employee.cCurrentPosition = PositionSkill.cPositionCode JOIN Skill ON PositionSkill.cSkillCode = Skill.cSkillCode

DBMS03 – Slides 15 DBMS03 – Slides 16

Bảng Ảo View

Solution

Query on View:

• SELECT vFirstName, vSkill FROM

vwEmpSkill

CREATE VIEW vwEmpSkill AS SELECT vFirstName, vLastName, SELECT vFirstName, vLastName,

vQualification, vSkill

• SELECT vFirstName, vLastName, • SELECT vFirstName, vLastName,

FROM Employee JOIN PositionSkill

vSkill FROM vwEmpSkill

ON Employee.cCurrentPosition =

PositionSkill.cPositionCode

JOIN Skill

ON PositionSkill.cSkillCode =

Skill.cSkillCode

DBMS03 – Slides 17 DBMS03 – Slides 18

Bảng Ảo View

Create the view

Altering, Dropping, and Renaming Views

• A view can be created using the CREATE

• Altering Views

VIEW statement

• You can modify a view by using the ALTER VIEW • You can modify a view by using the ALTER VIEW

• Syntax •

statement

• Syntax

CREATE VIEW view_name

[(column_name [, column_name]...)]

ALTER VIEW view_name [(column_name)]

[WITH ENCRYPTION]

AS select_statement

AS select_statement [WITH CHECK OPTION]

[WITH CHECK OPTION]

DBMS03 – Slides 19 DBMS03 – Slides 20

Bảng Ảo View

Hạn chế của View

(cid:1) Không thể chứa mệnh ñề COMPUTE

hoặc COMPUTE BY

Altering, Dropping, and Renaming Views (Contd.)

(cid:1) Không thể chứa mệnh ñề ORDER BY trừ

• Dropping Views

khi có TOP n khi có TOP n

• You can drop a view from a •

database by using the DROP VIEW statement

(cid:1) Không thể chứa mệnh ñề INTO (cid:1) Không thể chứa các table tạm (cid:1) Có tối ña 1024 cột

• Syntax DROP VIEW view_name

DBMS03 – Slides 21 DBMS03 – Slides 22

Bài tập áp dụng

Một số loại sự cố

(cid:1) Sự cố do thực hiện thao tác sai: thêm

xóa sửa dữ liệu không ñúng (cid:1) Trên CSDL thường không thể nhấn Crtl + Z

ñể undo lại một thao tác như insert, ñể undo lại một thao tác như insert, delete, update hay drop…. (cid:1) Sự cố do hệ thống phần mềm (cid:1) Sự cố do lỗi thiết bị

(cid:1) Loai (MaLoai, TenLoai) (cid:1) SanPham (MaSP, TenSP, MaLoai) (cid:1) NhanVien (MaNV, HoTen, NgaySinh, Phai) (cid:1) PhieuXuat (MaPX, NgayLap, MaNV) (cid:1) PhieuXuat (MaPX, NgayLap, MaNV) (cid:1) ChiTietXuat (MaPX, MaSP, SoLuong) (cid:1) 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.

DBMS03 – Slides 23 DBMS03 – Slides 25

Khôi Phục Dữ Liệu

Cài ñặt trên SQL Server

(cid:1) Khôi phục sau sự cố từ một giao tác

(cid:1) Sao lưu (Backup) (cid:1) Khôi phục (Restore)

(cid:1) Dựa vào file log

(cid:1) Khôi phục sau sự cố hệ thống

(cid:1) Dựa vào file backup

(cid:1) Khôi phục sau sự cố từ thiết bị lưu trữ

(cid:1) ???

DBMS03 – Slides 26 DBMS03 – Slides 27

Sao lưu (Backup)

(cid:1)

(cid:1) 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.

(cid:1) Differential Database Backups : Copy những thay ñổi trong tất cả data files kể từ lần full backup gần nhất. (cid:1) File or File Group Backups : Copy một data file ñơn (cid:1) File or File Group Backups : Copy một data file ñơn

hay một file group.

(cid:1) 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.

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.

(cid:1) 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).

DBMS03 – Slides 28 DBMS03 – Slides 29

Thuật ngữ về Backup

Backup

Giải Thích Thuật Ngữ

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) drive name hay physical filename)

(cid:1) 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 dùng lại ñặc biệt cho việc tự ñộng hóa công việc backup.

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 Device

File chứa một backup set Backup File

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).

(cid:1) Ðể backup database bạn có thể dùng Backup Wizard hoặc click lên trên database muốn backup sau ñó Right-click(cid:2)(cid:2)(cid:2)(cid:2)All Tasks (cid:2)(cid:2)(cid:2)(cid:2) Backup Database

Một bộ backup từ một lần backup ñơn ñược chứa trên backup media. Backup Set DBMS03 – Slides 30 DBMS03 – Slides 31

Ví dụ Backup

Phục Hồi (Restore)

(cid:1) 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. ñiểm transaction gần nhất ñược commited.

(cid:1) 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.

(cid:1) 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ứ.

DBMS03 – Slides 32 DBMS03 – Slides 33

Phục hồi (Restore)

(cid:1) 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 . backup) tương ứng . (cid:1) Lưu ý nếu bạn muốn