v   Quản lý quyền

I-             Sử dụng EM

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

1.     Tạo tài khoản đăng nhập

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

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

(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ấn 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 ràng buộc 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 ràng buộc  tham chiếu được sử dụng khi Table A được cấp quyền Update hoặc Insert. Table A có ràng buộc  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 hộp thoại  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.

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

II-         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 = ] <'Tênđăngnhập'>

[ , @password = ] <'password'>

[ , @defdb = ] <'TênCSDL'>

[ ,  @deflanguage = ] <'Ngônngữ'>

[ , @sid =] <'Mã nhận dạng Login'>

[ , @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 'manhthu', '11', 'NorthWind'

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':  <Domain>|<Computer name>\<tênnhóm>

Ví dụ: Thêm tài khoản cho user Windows NT [Server4\User01] kết nối đến SQL Server.

sp_grantlogin

'MANHTHU-PC\SQLEXPRESS\Databases\Northwind\Security\Users\manhthu'

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 = ] <'oldPW'> 

[ , @new = ] <'newPW'>

[ , @LoginName = ] <'login'>

Ví dụ: đổi mật khẩu của tài khoản :tranmanhthu là 11 thành 123

Sp_password ‘11’, ‘123’, ‘tranmanhthu’

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 NorthWind

Go

sp_GrantDBAccess 'tranmanhthu', 'manhthu'

4.     Xóa quyền truy xuất CSDL hiện hành:

Sp_RevokeDBAccess  [ @name_in_db = ] 'TênUser'

Ví dụ: Sp_RevokeDBAccess  'manhthu'

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 | <quyền> [,…]

ON  <TênTable|View>[(<têncột>,..)] | <tênSP>

TO  <tên Login hoặc Role> [,…]

[WITH GRANT OPTION]

[AS <tên Role> ]

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  <tên Login hoặc Role> : Khi cấp quyền cho nhóm hay user của Windows NT, phải chỉ định: <Domain>|<Computer name>\<tênnhóm>.

Để 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 Northwind

Grant All On  sinhvien To manhthu

GO

GRANT SELECT ON sinhvien TO public

GO

GRANT INSERT, UPDATE, DELETE ON sinhvien TO manhthu

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 THU. HIEP  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 THU, là thành viên của BanBe, muốn nhường quyền SELECT table NhatKy cho user THI, không là thành viên của role BanBe.

GRANT SELECT ON NhatKy TO BanBe WITH GRANT OPTION

GRANT SELECT ON NhatKy TO THI AS BanBe

6.     Từ Chối quyền thực thi trên CSDL:

DENY ALL | <quyền> [,…]

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

        | ON  table | view  [ ( column [ ,...n ] ) ]

        | ON  stored_procedure

TO <tên Login hoặc Role> [,…]       [Cascade]

Ví dụ: USE northwind

GO

GRANT SELECT ON sinhvien TO public

GO

DENY SELECT, INSERT, UPDATE, DELETE

ON sinhvien TO manhthu

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 manhthu  trên TABLE  sinhvien.

REVOKE  SELECT  ON  sinhvien  TO manhthu

8.     Cấp Quyền tạo đối tượng trong CSDL:

GRANT ALL | <lệnh> [,…]

TO <tên Login hoặc Role> [,…]

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 manhthu

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 manhthu

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 manhthu