Bài giảng SQL server - Chương 8: Quản lý người dùng và bảo mật hệ thống - Lê Thị Minh Nguyện

10/03/2015 TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH Chương 8. Quản lý người dùng và bảo mật hệ thống GV: Lê Thị Minh Nguyện Email: leminhnguyen@itc.edu.vn NỘI DUNG  Khái niệm  Chế độ bảo mật  Kiểm soát quyền truy cập trên CSDL  Vai trò của Server và Database  Quản trị người dùng 1 10/03/2015 1.Khái niệm Bảo mật cho phép người quản trị CSDL tự ra quyết định cho phép, hay không cho phép người dùng truy cập và thao tác trên CSDL sao cho có hiệu quả bảo mật dữ liệu tốt nhất. Bảo mật trong SQL Server có thể sử dụng mô hình 3 tầng. 1. Sử dụng thẩm định quyền (Login security) 2. Khả năng để người sử dụng sử dụng 1 hoặc nhiều CSDL(Database access security) 3. Các quyền cụ thể sử dụng bảo vệ CSDL (Permission security) 3 2.Chế độ bảo mật SQL Server có 2 chế độ bảo mật . Windows Authentication Mode (Windows Authentication) . Mixed Mode (Windows Authentication and SQL Server Authentication) 4 2 10/03/2015 2.Chế độ bảo mật Windows Authentication: Là chế độ bảo mật mà những User truy nhập SQL Server phải là những User của Windows. Khi Server đặt ở chế độ bảo mật này, những User phải là những User được Windows quản lý mới được truy nhập. 5 2.Chế độ bảo mật SQL Server Authentication: Khi thiết lập ở chế độ bảo mật này, những User được quyền khai thác phải là những User do quản trị SQL Server tạo ra, mà những user của Windows không được khai thác. SQL Server cho phép thiết lập hai chế độ . Windows Authentication Mode (Windows Authentication) . Mixed Mode (Windows Authentication and SQL Server 6 Authentication) 3 10/03/2015 7 3. Kiểm soát quyền truy cập trên CSDL • Xét CSDL bán hàng gồm: PhongBan, SanPham, DonHang, CTDonHang, KhachHang và NhanVien. NhanVien(MaNV, HoLot, TenNV, NgayVaoLam, MaNQL, MaPB, Luong, HoaHong). Khi đó, mỗi phát biểu sau chỉ ra một mức truy cập tới CSDL trên và có thể được trao cho một nhóm user. 8 4 10/03/2015 3. Kiểm soát quyền truy cập trên CSDL  User được phép truy xuất không điều kiện tới toàn bộ CSDL và thực hiện bất kỳ thao tác nào trên CSDL đó.  User không được phép truy xuất tới bất kỳ bộ phận nào của CSDL  User có thể đọc một bộ phận của CSDL nhưng không được thay đổi nội dung của bộ phận đó  User có thể đọc đúng một bộ trong bảng NhanVien nhưng không được sửa đổi bộ này  User có thể đọc đúng một bộ trong bảng NhanVien và sửa đổi nhưng không phải tất cả giá trị của bộ này. 9 3. Kiểm soát quyền truy cập trên CSDL  User có thể đọc các thuộc tính MaNV, TenNV, MaPB nhưng trong mỗi bộ chỉ được sửa giá trị của các thuộc tính MaPB  User có thể đọc các thuộc tính MaNV, TenNV, Luong nhưng trong mỗi bộ chỉ được sửa giá trị của các thuộc tính Luong trong khoảng thời gian từ 8h -> 11h từ một thiết bị đầu cuối đặt trong phòng tài vụ  User có thể đọc các thuộc tính MaNV, TenNV, Luong nhưng trong mỗi bộ chỉ được sửa giá trị của các thuộc tính Lương nếu và chỉ nếu giá trị hiện tại của thuộc tính Luong < 500 USD 10 5 10/03/2015 3. Kiểm soát quyền truy cập trên CSDL  User có thể áp dụng các phép toán thống kê cho thuộc tính Lương (như tính lương trung bình của từng phòng ban) nhưng không được đọc hay sửa đổi các giá trị cá biệt.  User có thể đọc các thuộc tính MaNV, Luong, HoaHong và trong mỗi bộ có thể sửa giá trị của thuộc tính HoaHong của nhân viên nếu và chỉ nếu người đó là trưởng phòng của phòng đó.  User có thể bổ sung thêm nhân viên vào một phòng ban nào đó hay xóa nhân viên khỏi một phòng nào đó. 11 3. Kiểm soát quyền truy cập trên CSDL  Xác nhận người dùng  Phân quyền 12 6 10/03/2015 3.1. Xác nhận người dùng  Những user khác nhau tùy theo vị trí, vai trò, trách nhiệm và quyền hạn trong hệ thống sẽ có các quyền khác nhau đối với CSDL hay các bộ phận khác nhau của CSDL như các quan hệ hay thuộc tính của các quan hệ. Các quyền này bao gồm đọc, thêm, xóa hay sửa đổi các đối tượng của CSDL. 13 3.1. Xác nhận người dùng  Các DBMS phải đảm bảo không cho chép user thực hiện bất kỳ thao tác nào nếu không được phép. Người quản trị CSDL (DBA) là người có quyền cấp tài nguyên và quyền thao tác CSDL, nghĩa là người quản trị phải: (1) Xác nhận cho hệ thống những quyền hay cụ thể những thao tác mà mỗi user được phép thực hiện. (2) Cung cấp một phương tiện cho user để hệ thống nhận biết được họ. 14 7 10/03/2015 3.1. Xác nhận người dùng  Nhiệm vụ của DBA: . Định nghĩa lược đồ CSDL. . Định nghĩa cấu trúc lưu trữ và phương thúc truy xuất dữ liệu. . Sửa đổi lược đồ và tổ chức vật lý. . Cấp quyền User để truy xuất dữ liệu 15 3.1. Xác nhận người dùng  Nhiệm vụ của DBA: . Thực hiện vai trò liên kết các user. . Giám sát việc thực thi và đáp ứng các thay đổi phù hợp. . Quản lý không gian lưu trữ, thực hiện sao lưu dữ liệu. . Việc xác nhận user thường được sử dụng là mật khẩu. 16 8 10/03/2015 3.2.Phân quyền người dùng  Quyền người dùng được định nghĩa như mức độ người dùng có thể hay không thể thực thi trên CSDL, quyền được chia thành 4 loại như sau: – Quyền truy cập vào SQL Server – Quyền truy cập vào CSDL – Quyền thực hiện trên các đối tượng của CSDL – Quyền xử lý dữ liệu 17 4.Vai trò của Server và Database Server role (vai trò phía Server) Database role (vai trò phía Database) 18 9 10/03/2015 4.1. Server role Role mức server do hệ thống tạo ra không thay đổi được Được phép thực hiện Bulk Insert. Được phép tạo và sửa đổi cấu trúc CSDL. Có thể quản trị các file trên đĩa. Quản trị các dịch vụ đang chạy của SQL Server Quản trị hệ thống bảo mật 19 Quản trị server tblEmployee(MaNV, Ten, ChucDanh, ChuyenMon, ChucVu) bulk insert tblEmployee from 'd:\employee.txt' with ( fieldterminator='|', rowterminator='\n' ) 20 10 10/03/2015 4.2. Database role  Database Role. - Role là đối tượng mà thông qua nó người quản trị có thể gán quyền khai thác cho người sử dụng. - Role do CSDL quản lý, khi tạo CSDL hệ thống tự đặt một số Role ngầm định. 21 4.2. Database role Vai trò Diễn giải Db_owner Đây là vai trò cao nhất. Người dùng có toàn quyền kiểm soát CSDL. Người dùng sa là thành viên của vai trò này. Db_securityadmin Cho phép người dùng quản lý tất cả các vai trò và các thành viên của chúng. Vai trò này cũng cho phép gán các quyền hạn cho các vai trò. Db_ accessadmin Vai trò này dùng cung cấp các quyền người dùng để thêm hoặc gỡ bỏ các người dùng trong CSDL Db_addadmin Cho phép người dùng thao tác tất cả các đối tượng trong CSDL. Ví dụ: có thể tạo lập, sửa chữa hoặc xoá các đối tượng trong CSDL. Db_backupoperator Cho phép người dùng thực hiện thao tác sao chép dự phòng 22 11 10/03/2015 4.2. Database role Vai trò Diễn giải Db_datawriter Cho phép sửa chữa dữ liệu trong tất cả các bảng người dùng định nghĩa trong CSDL Db_datareader Cho phép người dùng hiển thị dữ liệu từ các bảng người dùng định nghĩa trong CSDL Db_denydatawriter Ngăn chặn người dùng sửa chữa dữ liệu trong bảng người dùng định nghĩa trong CSDL Db_denydatareader Ngăn chặn người dùng hiển thị dữ liệu trong bảng người dùng định nghĩa trong CSDL Public Vai trò public này hầu như không có quyền 23 hạn ngoại trừ vài quyền rất cơ bản cho phép người dùng tạo kết nối tới CSDL 4.2. Database role Tạo role: trong CSDL hiện hành . Sp_addrole ‘role’ Đưa một user vào role . Sp_addrolemember ‘role’, ‘user’ Xóa nhóm . Sp_droprole ‘role’ Xóa một thành viên trong nhóm . Sp_droprolemember ‘role’, ‘user’ 24 12 10/03/2015 5.Quản trị người dùng  Người dùng trong SQL Server được chia thành 2 mức: . Người truy nhập vào SQL Server gọi là Login . Người khai thác CSDL gọi là User. 25 5. Quản trị người dùng  Nhà quản trị hệ thống (System administrator).  Người chủ CSDL (database owner).  Tạo login đăng nhập.  Cấp quyền truy cập vào CSDL.  Quyền thực hiện trên các đối tượng của CSDL.  Quyền xử lý dữ liệu. 26 13 10/03/2015 5.1. Nhà quản trị hệ thống (System administrator)  Nhà quản trị hệ thống có login là sa.  Có tòan quyền truy xuất đến tất cả đối tượng trong SQL Server  Không thể xóa login sa 27 5.2. Người chủ CSDL (database owner)  Login dbo được gọi là database owner.  dbo là thành viên của nhóm db_owner.  Không thể xóa khỏi nhóm này. 28 14 10/03/2015 5.3.Tạo login đăng nhập • Tạo login bằng phát biểu SQL – Create Login Login_name with password • Ví dụ: – Create login aido with password = ‘123456’ • Liệt kê danh sách login – Select name, createdate From syslogins 29 5.3.Tạo login đăng nhập • Dùng sp EXEC sp_addlogin [@login= ] [,@password= ] [,@defdb= ] • Ví dụ Use QLSV EXEC sp_addlogin 'aido', '123456' 30 15 10/03/2015 5.4.Cấp quyền truy cập vào CSDL • Cú pháp SQL Create user user_name For Login • Ví dụ: Use QLSV Create user tuiday For login aido 31 5.4.Cấp quyền truy cập vào CSDL • Cú pháp sp EXEC sp_grantdbaccess [@loginname= ] [,@name_in_db= ] Ví dụ: Use qlsv exec sp_grantdbaccess aido, tuiday 32 16 10/03/2015 5.4.Cấp quyền truy cập vào CSDL  Thay đổi password • Cú pháp: EXEC sp_password [@old= ] [,@new= ] [,@loginname= ] • EXEC sp_password ‘123456’, ‘123’, ‘aido’ 33 5.4.Cấp quyền truy cập vào CSDL  Xóa quyền truy cập vào csdl • Cú pháp: EXEC sp_revokedbaccess [@loginname= • Ví dụ: EXEC sp_revokedbaccess ‘tuiday’ 34 17 10/03/2015 5.5.Quyền thực hiện trên các đối tượng của CSDL • Cấp quyền tạo đối tượng - Create database – Cú pháp - Create table Grant to [tên user] - Create View • Ví dụ: Cấp quyền tạo view - Create proc Use QLSV - Create rule GRANT create table, create view - Create default To tuiday - Backup database • Cấm quyền tạo đối tượng - Backup log Deny to [tên user] • Ví dụ: DENY create table To pvn 35 5.6.Quyền xử lý dữ liệu • Cấp quyền xử lý dữ liệu - Select Grant <ALL| các quyền ON - Update TO - Insert • Ví dụ: - Delete GRANT INSERT, UPDATE, DELETE ON sinhvien - References TO lmn • Cấm quyền tạo đối tượng - Execute Deny on to [tên user] (hoặc dùng revoke) 36 • Ví dụ: Deny insert on sinhvien to lmn 18 10/03/2015 5.6.Quyền xử lý dữ liệu • Grant/ Revoke/ Deny còn cho phép cấp quyền trên từng Field của table. • Ví dụ: cho user ltm chỉ được quyền hiệu chỉnh dữ liệu trên các Field: Ho, Ten, Phai, DiaChi, NgaySinh của NHANVIEN GRANT UPDATE (Ho, Ten, Phai, DiaChi, NgaySinh) ON NHANVIEN To lmn 37 Ví dụ Tạo 3 login A, B, C có chung 1 password là ‘password’ làm việc trên CSDL QLTV Tạo nhóm PK và cho A,B thuộc nhóm này Use master Exec sp_addlogin ‘A’, ‘password’, ‘QLTV’ Exec sp_addlogin ‘B’, ‘password’, ‘QLTV’ Exec sp_addlogin ‘C’, ‘password’, ‘QLTV’ 38 19 10/03/2015 Ví dụ Use qltv Exec sp_grantdbaccess @loginame= ‘A’ Exec sp_grantdbaccess @loginame= ‘B’ Exec sp_grantdbaccess @loginame= ‘C’ Exec sp_addrole ‘PK’ Exec sp_addrolemember ‘PK’, ‘A’ Exec sp_addrolemember ‘PK’, ‘B’ 39 20 10/03/2015 TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH 21