Chương 5 : Kiểu Con Trỏ (SQL Cursor)

-1Phạm vi hoạt động của Cursor:

Nếu bạn muốn tạo cursor Local bạn phải chỉ định rõ ràng trong khai báo cursor:

Declare Cursv Cursor Local For

Select Sbd, Tensv From Sinhvien Order By Tensv

Cursor Local có phạm vi hoạt động bên trong gói đã tạo nó. Và tự giải phóng khi kết thúc gói.

-2Phương Thức Di Chuyển Trên Cursor:

• 
  FORWARD_ONLY : là phương thức mặc định, chỉ cho phép di chuyển sang mẫu tin kế tiếp.
  
• 
  SCROLL : Cho phép di chuyển lên xuống trong tập mẫu tin.
  

-3Các Loại Cursor:

• 
  STATIC : có thuộc tính READ ONLY, do đó không thể cập nhật các bảng nền thông qua Cursor này. Khi tạo Cursor Static, dữ liệu từ các bảng gốc sẽ được Copy sang một bảng tạm trong CSDL tempdb. Do đó, Nếu các table nguồn của Cursor bị thay đổi dữ liệu thì các dữ liệu không xuất hiện trên Cursor.
  

The cursor is READ ONLY. The statement has been terminated.

• 
  DYNAMIC: Cho phép cập nhật dữ liệu trên các table nguồn (dùng mệnh đề WHERE CURRENT OF trong các phát biểu UPDATE or DELETE), và tự động hiển thị tất cả những thay đổi từ table nguồn. Tuy nhiên, dữ liệu và thứ tự của các mẫu tin trong tập mẫu tin có thể bị thay đổi.
  
• 
  KEYSET : Giống như cursor Dynamic. Nhưng nó chỉ được tạo khi bảng nguồn có khai báo khóa, nếu không thì SQL tự động chuyển sang loại STATIC. Khi tạo Cursor KEYSET, Tập các khóa của bảng nguồn được lưu trên một table của CSDL tempdb. Do đó, việc xóa mẫu tin hoặc thay đổi giá trị khóa trên các bảng nguồn không thông qua Cursor sẽ không phản hồi trên tập mẫu tin.
  

TYPE_WARNING : Gởi thông báo chú ý về client nếu Cursor thực hiện chuyển đổi ngầm định từ kiểu yêu cầu sang một kiểu khác.

-4Xử lý đồng thời:

Trừ phi sử dụng cursor trong một transaction, nếu không các table nguồn của cursor không tự động khóa dữ liệu. SQL Server 2000 có 4 chọn lựa cho phép ngăn cản việc sửa đổi mẫu tin cho tới khi thực hiện xong hoặc bằng cách khóa các table nguồn của cursor để bảo vệ các thay đổi của bạn.

• 
  READ_ONLY : Dùng khi chỉ truy xuất dữ liệu mà không sửa đổi dữ liệu.
  
• 
  SCROLL_LOCKS : Khoá các dòng đã được đọc vào Cursor đối với các User khác.
  
• 
  OPTIMISTIC WITH VALUES: Chỉ khóa các giá trị mà bạn vừa thay đổi. Nếu người dùng khác thay đổi các giá trị đó sẽ nhận được thông báo lỗi.
  
• 
  OPTIMISTIC WITH ROW VERSIONING —Khi muốn cả dòng được cập nhật, không chỉ một vài Fields trong nó.
  

-5Khai báo cột trong Cursor được phép cập nhật:

UPDATE [OF column_name [,...n]]

• 
  Nếu chỉ định OF column_name [,...n] chỉ những cột liệt kê mới được sửa đổi.
  
• 
  Nếu chỉ định UPDATE mà không chỉ định danh sách cột, thì tất cả các cột đều có khả năng cập nhật trừ phi chỉ định READ_ONLY.
  

III-Truy xuất dữ liệu trên Cursor:

FETCH [ NEXT | PRIOR | FIRST | LAST  

| ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ]

FROM [ GLOBAL ] cursor_name

[ INTO @variable_name [ ,...n ] ]

• 
  NEXT : Chuyển sang mẫu tin kế tiếp.
  
• 
  PRIOR : Chuyển về mẫu tin trước đó.
  
• 
  FIRST : Chuyển về mẫu tin đầu tiên.
  
• 
  LAST : Chuyển đến mẫu tin cuối cùng.
  
• 
  ABSOLUTE {n | @nvar} : Nếu n or @nvar > 0, tìm đến dòng thứ n tính từ dòng đầu tiên đếm xuống trong tập mẫu tin. Nếu n or @nvar < 0, tìm đến dòng thứ n tính từ dòng cuối cùng đếm lên. Nếu n or @nvar = 0, chuyển đến vùng BOF và không có giá trị trả về. Hằng số n phải là số nguyên và biến @nvar phải thuộc kiểu smallint, tinyint, hoặc int. Không sử dụng phương thức ABSOLUTE cho kiểu DYNAMIC.
  
• 
  RELATIVE {n | @nvar} : Nếu n hoặc @nvar > 0, chuyển xuống n dòng tính từ dòng kề dưới dòng hiện hành. Nếu n or @nvar < 0, Chuyển lên n dòng trước dòng hiện hành. Nếu n or @nvar = 0, trả về dòng hiện hành.
  
• 
  cursor_name: Tên cursor đang mở. Nếu tồn tại cursor cục bộ và cursor toàn cục có cùng tên thì tên cursor được sử dụng sẽ là cursor cục bộ nếu không có từ khóa GLOBAL.
  
• 
  INTO @varname[,...n] : Danh sách biến cục bộ nhận dữ liệu tương ứng từ các cột trên mẫu tin hiện hành, theo thứ tự từ trái sang phải. Số biến phải bằng số cột đã liệt kê trong câu lệnh Select khi tạo Cursor. Kiểu dữ liệu của mỗi biến phải tương thích với kiểu dữ liệu của cột hoặc được hổ trợ chuyển kiểu ngầm định theo kiểu của cột.
  

Kiểm tra kết quả của lệnh FETCH: Sử dụng hàm @@FETCH_STATUS sau lệnh FETCH. Hàm trả về một trong 3 giá trị:

0	Nếu lệnh FETCH chuyển đến 1 mẫu tin trong danh sách.
-1	Nếu lệnh FETCH chuyển đến vùng BOF hoặc EOF
-2	Nếu chuyển đến 1 dòng đã bị xóa trên Server (Keyset).

Ví dụ:

CREATE proc sprcur

As

Declare @ms char(6), @ten varchar(30)

Declare a cursor SCROLL for select mssv, tensv from sinhvien

Open a

Fetch next from a into @ms, @ten

While @@fetch_status = 0

Begin

Print @ms + '-' + @ten

Fetch Next from a into @ms, @ten

End

Close a

Deallocate a

GO

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:

-1Tạ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.

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

-2Thay đổ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

-3Cấ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.

-4Cấp Quyển Tạo Đối Tượng Trên CSDL:

• 
  Click phải vào tên CSDL, chọn Properties
  
• 
  Trong HT Properties, chọn trang Permissions
  
• 
  Đá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.
  

-5Xóa User trên một CSDL: Click phải vào tên user và chọn Delete. -6Tạ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ũ.

-7Xó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:

-1Tạo Tài Khoản Đăng Nhập (Login Acount):

-aThê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' ]

• 
  @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.
  
• 
  @deflanguage : Ngôn ngữ mặc định
  
• 
  @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.
  
• 
  @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.
  
• 
  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

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

-2Thay đổi mật khẩu:

Sp_Password [ @old = ] <'oldPW'>

[ , @new = ] <'newPW'>

[ , @LoginName = ] <'login'>

-3Cấ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'

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

Sp_RevokeDBAccess [ @name_in_db = ] 'TênUser'

Ví dụ: Sp_RevokeDBAccess 'Anh'

-5Cấ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 ]

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

• 
  WITH GRANT OPTION : Cho phép Login cấp quyền đã chỉ định trên đối tượng cho Login khác.
  
• 
  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

-6Từ 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

-7Xó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

-8Cấ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]

-9Từ 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]

-10Xó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

• 
  Vai trò trên SQL Server Chứa các quyền quản trị SQL Server
  
• 
  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.
  

-1Tạ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'

SP_ADDROLE 'QuanLy'

Go

GRANT SELECT ON DeTai TO QuanLy

-2Thê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'

- -