Giáo trình Lập trình SQL Server

Server 
- 50 - 
CHƯƠNG 6 : BẢO MẬT (SECURITY) 
I- Khái Niệm: 
Mỗi CSDL có 1 hay nhiều users được chỉ định quyền truy xuất dữ liệu. Người quản trị 
có thể cấp quyền truy xuất CSDL bằng cách tạo một tài khoản đăng nhập (login) SQL Server 
cho User, thêm User vào CSDL và gán quyền cho User trên CSDL đó. Bao gồm các loại 
quyền: 
• Quyền truy cập vào SQL Server 
• Quyền truy xuất CSDL 
• Quyền thực hiện trên các đối tượng của CSDL 
• Quyền xử lý dữ liệu 
II- Sử dụng EM: 
1- Tạo Tài Khoản Đăng Nhập (Login Acount): 
(1) Mở mục Security, click phải mục Login và chọn New Login… 
(2) Trên trang General, nhập tên đăng nhập, chế độ xác nhận, CSDL mặc định. 
Chú ý: Đăng nhập với SQL Server thường dùng cho việc kết nối quay số và mạng peer-to-
peer. 
(3) Trang Server Roles: Chọn vai trò quản trị mức Server cho tài khoản đăng nhập 
System Administrators 
Security Administrators 
Server Administrators 
Setup Administrators 
Database Creator 
Disk Administrators 
Process Administrators 
Bulk Administrators 
Đặc quyền cao nhất; cho phép thực hiện mọi tác vụ trên SQL 
Quản lý các server logins. 
Cho phép bạn định cấu hình những cài đặt server-wide. 
Cho phép thêm và xóa các linked servers, và truy xuất vài SP 
Tạo và hiệu chỉnh databases. 
Quản lý các files trên đĩa. 
Quản lý tiến trình đang chạy trong một thể hiện của SQL Server. 
Thực hiện phát biểu BULK INSERT. 
Chú ý :Bất kỳ Users của Windows NT thuộc nhómBUILTIN\Administrators đều có vai 
trò sysadmin. 
Lập trình SQL Server 
- 51 - 
(4) Trang Database Access: Chọn CSDL được phép truy xuất và vai trò của nó trong từng 
CSDL được chọn. 
Fixed Database Role Description 
Public Vai trò chung cho tất cả người dùng. 
db_owner Quyền cao nhất trong database. 
db_accessadmin Điều khiển truy xuất, cài đặt hoặc xóa user accounts. 
db_datareader Đọc tất cả dữ liệu trên database. 
db_datawriter Thêm, sửa, xóa dữ liệu trên các tables người dùng trong database. 
db_ddladmin Thêm, sửa, xóa các đối tượng objects (runs all DDLs). 
db_securityadmin Quản lý các roles, các thành viên của role, giấy phép trong database. 
db_backupoperator Cho phép back up database. 
db_denydatareader Từ chối quyền truy vấy dữ liệu trong database. 
db_denydatawriter Từ chối quyền thay đổi dữ liệu trong database. 
Sau khi tạo login, nó tự động nhập vào tập Users của mỗi database được chọn, với tên 
User trùng với tên Login. Bạn có thể thay đổi thu hồi vai trò của nó trên từng CSDL bằng cách 
sửa đổi thuộc tính của Login, hoặc chuyển đến tập Users của database và thay đổi thuộc tính 
hoặc xoá user nào mà bạn không muốn cho truy xuất data của bạn. 
Các Login được lưu trong table SysLogins của CSDL Master: 
If Exists( Select 1 From Master..SysLogins Where Name = 'Login') 
Các User trong một CSDL được lưu trong table SysUsers của CSDL đó 
If Exists( Select 1 From SysUsers Where Name = 'User01') 
2- Thay đổi thuộc tính cho Login: 
Bấm đúp vào tên Login hoặc click phải và chọn mục Properties 
3- Cấp Quyền Thực Thi Trên Mỗi CSDL: 
Chọn database, trong mục Users bấm đúp vào tên User cần hiệu chỉnh (Login-ID). 
Click nút Permission để chỉ định quyền truy cập dữ liệu trên từng Table, View. Quyền 
kiểm tra RB tham chiếu (DRI - Declarative Referential Integrity). Quyền thực hiện các thủ tục 
lưu trử. 
Chú thích: Quyền kiểm tra RB tham chiếu được sử dụng khi Table A được cấp quyền Update 
hoặc Insert. Table A có RB FOREIGN KEY với table B, mà table B không được cấp 
quyền SELECT. 
4- Cấp Quyển Tạo Đối Tượng Trên CSDL: 
o Click phải vào tên CSDL, chọn Properties 
o Trong HT Properties, chọn trang Permissions 
o Đánh dấu chọn các phát biểu được cần cấp quyền thực hiện cho các User. 
Lập trình SQL Server 
- 52 - 
5- Xóa User trên một CSDL: 
Click phải vào tên user và chọn Delete. 
6- Tạo User với Login đã có: 
Click phải vào mục Users và chọn New 
Database User... 
Chú ý: Bạn có thể thay đổi tên User bằng cách xóa 
và tạo lại với login cũ. 
7- Xóa Tài Khoản Đăng Nhập: 
Click phải vào tên Login và chọn Delete 
Với cách này sẽ xóa bỏ các user trong các CSDL đã chọn cho tài khoản này. 
III- Sử Dụng QA: 
1- Tạo Tài Khoản Đăng Nhập (Login Acount): 
a- Thêm Tài khoản với chế độ xác nhận SQL Server: 
SP_ADDLOGIN [ @login = ] 
[ , @password = ] 
[ , @defdb = ] 
[ , @deflanguage = ] 
[ , @sid =] 
[ , @encriptopt = 'skip_encription' ] 
o @defdb : tên CSDL mặc định được mở khi đăng nhập. Nếu không chỉ định mặc định là 
CSDL Master. 
o @deflanguage : Ngôn ngữ mặc định 
o @sid : varbinary(16) : Mã nhận dạng của hệ thống. Nếu không chỉ định, hệ thống tự tạo 
một mã số mới. 
o @encriptopt varchar(20): Mặc định Password sẽ được mã hóa khi lưu trong các table hệ 
thống. Ngoại trù bạn gán giá trị skip_encryption cho tham số này. 
o Thủ tục trả về giá trị 0 nếu thành công, ngược lại trả về giá trị 1. 
Ví dụ: Tạo tài khoản tên 'myname', mật khẩu 'mypwd' vào CSDL 'QLDeTai' 
Sp_AddLogin 'user01', '01', 'QLDeTai' 
Kết quả trả về : New loigin created 
b- Cấp quyền kết nối cho User hay nhóm User của Windows kết nối đến SQL Server: 
SP_GRANTLOGIN [@loginame =] 'login' 
'Login': |\ 
Ví dụ: Thêm tài khoản cho user Windows NT [Server4\User01] kết nối đến SQL Server. 
EXEC sp_grantlogin ' Server4\User01' 
Hay EXEC sp_grantlogin [Server4\User01] 
Chú ý: Chỉ được thức hiện bởi những thành viên có vai trò sysadmin và securityadmin. 
2- Thay đổi mật khẩu: 
Sp_Password [ @old = ] 
Lập trình SQL Server 
- 53 - 
[ , @new = ] 
[ , @LoginName = ] 
3- Cấp quyền Truy xuất CSDL Hiện Hành cho Login: 
Sp_GrantDBAccess [ @loginname = ] 'Tênđăngnhập' 
[ [, @name_in_db = ] 'TênUser' ] 
Ví dụ: Use QLDeTai 
Go 
sp_GrantDBAccess 'user01', 'Anh' 
4- Xóa quyền truy xuất CSDL hiện hành: 
Sp_RevokeDBAccess [ @name_in_db = ] 'TênUser' 
Ví dụ: Sp_RevokeDBAccess 'Anh' 
5- Cấp quyền thực thi trên CSDL: 
Bao gồm các quyền: Select, Insert, Update, Delete, Reference, Excecute. 
GRANT ALL | [,…] 
ON [(,..)] | 
TO [,…] 
[WITH GRANT OPTION] 
[AS ] 
o All : Cấp tất cả các quyền thực thi Select, Insert, Update, Delete hay Reference trên table 
hay view; quyền Excecute cho SP. 
o TO : Khi cấp quyền cho nhóm hay user của Windows NT, phải 
chỉ định: |\. 
Để cấp quyền cho nhóm cục bộ Windows NT built-in, dùng từ khóa BUILTIN thay 
thế tên domain hoặc computer name. 
Quyền thực thi đã cấp cho role Public được áp dụng cho tất cả users trong CSDL. 
Quyền thực thi đã cấp cho user Guest được sử dụng cho tất cả Users không được phân 
quyền truy xuất trên CSDL. 
Ví dụ: USE QLDeTai 
Grant All On DeTai To User01 
GO 
GRANT SELECT ON DeTai TO public 
GO 
GRANT INSERT, UPDATE, DELETE ON DeTai TO Mary, [Corporate\BobJ] 
GO 
o WITH GRANT OPTION : Cho phép Login cấp quyền đã chỉ định trên đối tượng cho 
Login khác. 
o AS {group | role} : Được dùng khi quyền thực thi trên một đối tượng đã cấp cho nhóm 
hoặc role, và một User của nhóm hoặc Role muốn cấp quyển thực thi cho User khác không 
là thành viên của nhóm hoặc Role. 
Ví dụ: table NhatKy được tạo bới user Lac. Lac cấp quyền SELECT table NhatKy cho 
Role BanBe với mênh đề WITH GRANT OPTION để các user thành viên của Role BanBe có 
thể nhường quyền này cho các user khác không thuộc Role BanBe. 
User Hong, là thành viên của BanBe, muốn nhường quyền SELECT table NhatKy cho 
user Khoa, không là thành viên của role BanBe. 
/* User Lac */ 
GRANT SELECT ON NhatKy TO BanBe WITH GRANT OPTION 
/* User Hong */ 
GRANT SELECT ON NhatKy TO Khoa AS BanBe 
Lập trình SQL Server 
- 54 - 
6- Từ Chối quyền thực thi trên CSDL: 
DENY ALL | [,…] 
 [ ( column [ ,...n ] ) ] ON { table | view } 
 | ON table | view [ ( column [ ,...n ] ) ] 
 | ON stored_procedure 
TO [,…] [Cascade] 
Ví dụ: USE QLDeTai 
GO 
GRANT SELECT ON DeTai TO public 
GO 
DENY SELECT, INSERT, UPDATE, DELETE 
ON DeTai TO Mary, John, Tom 
7- Xóa bỏ quyền thực thi đã cấp hoặc từ chối trên CSDL: 
REVOKE [ GRANT OPTION FOR ] 
 { ALL | 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 } ] 
Ví dụ : Xóa bỏ quyền Select đã từ chối cho User Mary trên table DeTai. 
REVOKE SELECT ON DeTai TO Mary 
8- Cấp Quyền tạo đối tượng trong CSDL: 
GRANT ALL | [,…] 
TO [,…] 
Bao gốm các lệnh: CREATE DATABASE; CREATE DEFAULT; 
CREATE PROCEDURE; CREATE RULE; CREATE TABLE; CREATE VIEW; 
BACKUP DATABASE; BACKUP LOG 
Ví dụ: GRANT CREATE DATABASE, CREATE TABLE 
TO Mary, John, [Corporate\BobJ] 
9- Từ Chối quyền tạo đối tượng trên CSDL: 
DENY { ALL | statement [ ,...n ] } FROM security_account [ ,...n ] 
Ví dụ: DENY CREATE TABLE FROM Joe, [Corporate\BobJ] 
10- Xóa bỏ quyền tạo đối tượng đã cấp hoặc từ chối trên CSDL: 
REVOKE { ALL | statement [ ,...n ] } FROM security_account [ ,...n ] 
Ví dụ : REVOKE CREATE TABLE FROM Joe, [Corporate\BobJ] 
IV- Vai Trò Của User Trong SQL Server 
SQL Server hổ trợ 2 nhóm roles:sion 28—SQL Server Security 321 
o Vai trò trên SQL Server Chứa các quyền quản trị SQL Server 
o Vai trò trên Database: Chứa các quyền quản lý và thực thi trên các đối tượng của 
CSDL. Ngoài các vai trò được cung cấp bởi SQL Server có thể tạo thêm vai trò 
khác. 
1- Tạo Vai trò trên CSDL: 

 Trong EM: Mở CSDL, Click phải vào mục Roles và chọn New Database Role…, 
Nhập tên Role mới và click nút Add để thêm User 

 Trong QA: SP_ADDROLE [@rolename =] 'role' [ , [@ownername =] 'owner'] 
Ví dụ: Trong CSDL QLDeTai thêm role 'QuanLy' 
Lập trình SQL Server 
- 55 - 
SP_ADDROLE 'QuanLy' 
Go 
GRANT SELECT ON DeTai TO QuanLy 
2- Thêm User vào Role: 
SP_ADDROLEMEMBER [@rolename =] 'role', 
 [@membername =] 'UserName' 
Ví dụ: Cấp quyền truy cập CSDL QLDeTai cho User và thêm vai trò ‘QuanLy’ cho User. 
USE QLDeTai 
GO 
EXEC SP_GRANTDBACCESS 'Server4\User01', 'Hong' 
GO 
EXEC SP_ADDROLEMEMBER 'QuanLy', 'Hong'