CHƯƠNG 2: QUẢN LÝ DỊCH VỤ SQL SERVER (3 tiết)
2.1. Các thành trong SQL Server.
Relational Database Engine - Lõi của SQL Server:
Ðây là một engine có khả năng chứa data ở các quy mô khác nhau dưới dạng table và support tất cả các kiểu kết nối (data connection) thông dụng của Microsoft như ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC).
Ngoài ra nó còn có khả năng tự điều chỉnh (tune up) ví dụ như sử dụng thêm các tài nguyên (resource) của máy khi cần và trả lại tài nguyên cho hệ điều hành khi một user log off.
Replication - Cơ chế tạo bản sao (Replica):
Giả sử bạn có một database dùng để chứa dữ liệu được các ứng dụng thường xuyên cập nhật. Một ngày đẹp trời bạn muốn có một cái database giống y hệt như thế trên một server khác để chạy báo cáo (report database) (cách làm này thường dùng để tránh ảnh hưởng đến performance của server chính). Vấn đề là report server của bạn cũng cần phải được cập nhật thường xuyên để đảm bảo tính chính xác của các báo cáo. Bạn không thể dùng cơ chế back up and restore trong trường hợp này. Thế thì bạn phải làm sao? Lúc đó cơ chế replication của SQL Server sẽ được sử dụng để bảo đảm cho dữ liệu ở 2 database được đồng bộ (synchronized).
Data Transformation Service (DTS)
Một dịch vụ chuyển dịch data. Nếu bạn làm việc trong một công ty lớn trong đó data được chứa trong nhiều nơi khác nhau và ở các dạng khác nhau cụ thể như chứa trong Oracle, DB2 (của IBM), SQL Server, Microsoft Access....Bạn chắc chắn sẽ có nhu cầu di chuyển data giữa các server này (migrate hay transfer) và không chỉ di chuyển bạn còn muốn định dạng (format) nó trước khi lưu vào database khác, khi đó bạn sẽ thấy DTS giúp bạn giải quyết công việc trên dễ dàng như thế nào.
Analysis Service - Một dịch vụ phân tích dữ liệu
Dữ liệu (Data) chứa trong database sẽ chẳng có ý nghĩa gì nhiều nếu như bạn không thể lấy được những thông tin (Information) bổ ích từ đó. Do đó Microsoft cung cấp cho bạn một công cụ rất mạnh giúp cho việc phân tích dữ liệu trở nên dễ dàng và hiệu quả bằng cách dùng khái niệm hình khối nhiều chiều (multi-dimension cubes) và kỹ thuật "đào mỏ dữ liệu" (data mining).
English Query :
Ðây là một dịch vụ giúp cho việc query data bằng tiếng Anh "trơn" (plain English).
Meta Data Service:
Meta data là những thông tin mô tả về cấu trúc của data trong database như data thuộc loại nào String hay Integer..., một cột nào đó có phải là Primary key hay không....Bởi vì những thông tin này cũng được chứa trong database nên cũng là một dạng data nhưng để phân biệt với data "chính thống" người ta gọi nó là Meta Data.
SQL Server Books Online

2.2. Trình quản lý dịch vụ Sql server
Service Manager: Đây là trình quản lý các dịch vụ trên SQL Server như: MSSQL Server, SQL Server Agent, Microsoft Distrinuted Transaction Coordinator (MS DTC).
• MSSQL Server : xử lý các phát biểu Transact-SQLvà quản lý các file lưu trữ các CSDL
• SQL Server Agent : dùng lập lịch thực hiện tự động các công việc như Backup dữ liệu, Replication, …
• Microsoft Distributed Transaction Coordinator (MSDTC): Hỗ trợ cho các ứng dụng Client làm việc với các dữ liệu được phân bổ trên nhiều máy chủ.
• Combo Server: Chứa danh sách các SQL Server có khả năng truy xuất từ máy của bạn. Tuy nhiên, bạn có thể gõ tên máy chủ cần kết nối không có trong danh sách và click nút mũi tên trên hộp Services, Tiện ích Services Manager sẽ cố gắng kết nối tới máy chủ ở xa. Combo Services: Chứa tên các dịch vụ được cung cấp bởi SQL Server đã chọn.
Mỗi dịch vụ sẽ ở 1 trong 3 trạng thái. Một số dịch vụ không cung cấp chức năng tạm dừng.
Ví dụ MSSQLServer vẫn tiếp tục hoạt động khi nó tạm dừng, nhưng các kết nối mới thì không được phép.
2.3. Sử dụng SQL Server Enterprise Manager
Enterprise Manager cung cấp các chức năng phát triển và quản trị SS bằng giao diện đồ họa. Các tính năng của EM:
• Tạo, xóa, cập nhật CSDL và các đối tượng của nó.
• Quản lý lịch trình Backup dữ liệu
• Quản lý người dùng đang truy cập CSDL trên SS.
• Tạo, xóa cập nhật quyền Login User
• Định cấu hình cho Server
• Tạo và quản lý tìm kiếm

Enterprise Manager bao gồm các thành phần:
• Databases : chứa các CSDL được cài đặt trên máy chủ.
• Data Transform Service (DTS) : Cung cấp phương tiện chuyển đổi dữ liệu từ nhiều nguồn khác nhau vào SQL Server hoặc từ SQL Server sang các nguồn khác. Nó cũng có giao diện cho phép lập trình xây dựng những gói dịch vụ chuyển đổi dữ liệu.
• Management: Cho phép bạn thực hiện các tác vụ quản trị CSDL, xem nhật ký hoạt động của Server, quản lý SQL Server Agent.
• Replication: cho phép phân bổ dữ liệu và các đối tượng Database từ một CSDL đến một database đến một database khác. Typically, replication is used between physically distributed servers.
• Security : chứa các chức năng điều khiển tất cả các hoạt động liên quan đến việc bảo mật của SQL Server.
• Support Services: cho phép điều khiển các dịch vụ khác có quan hệ với SQL Server như : Distributed Transaction Coordinator, Full Text Search và SQL Mail cho phép nhận e-mail từ SQL Server.
• Metadata Services enables you to manage metadata, or data about data.



CHƯƠNG 8: QUẢN LÝ BẢO MẬT VÀ NGƯỜI DÙNG (5 tiết)
8.1. Tạo và quản lý người dùng
8.1.1. Các chế độ xác thực
Để truy cập vào SQL Server ta dùng hai chế độ xác thực:
• Xác thực thông qua hệ điều hành: Với kiểu xác thực này, SQL Server dựa vào Windows NT/2000 để cấp bảo mật đăng nhập. Khi người dùng đăng nhập vào Windows NT/2000, số định danh tài khoản người dùng được kiểm tra sự hợp lệ. SQL Server tích hợp quá trình bảo mật đăng nhập của nó với quá trình bảo mật đăng nhập của Windows để cung cấp những dịch vụ này. Khi người dùng xác thực bằng hệ điều hành thì không cần thêm chế độ xác thực nào nữa để truy cập SQL Server.
• Chế độ xác thực hỗn hợp: Với chế độ xác thực hỗn hợp người dùng có thể truy cập vào SQL Server bằng xác thực thực Windows hoặc bằng xác thực SQL Server. Khi chế độ xác thực kết hợp được dùng, kết nối được tạo từ hệ thống không bảo mật, SQL Server xác thực đăng nhập bằng cách kiểm tra tài khoản đăng nhập có được thiết lập để truy cập hay không. SQL Server thực hiện xác thực tài khoản này bằng cách so sánh tên tài khoản và mật khẩu do người dùng cung cấp để thực hiện kết nối với thông tin tài khoản được lưu trong CSDL. Nếu tài khoản đăng nhập chưa được thiết lập hoặc người dùng nhập sai thì SQL Server từ chối kết nối.
Thiết lập chế độ xác thực:
• Trong cửa sổ Enterprise Manager, Right click lên tên server quản lý CSDL mà muốn thiết lập chế độ xác thực và chọn Properties. Xuất hiện cửa sổ Properties như hình 3.58. Chọn tab Security.
• Vùng Security ta chọn chế độ xác thực: SQL Server and Windows hoặc Windows Only. Và các mức dò xét hành vị đăng nhập, tùy thuộc vào yêu cầu ảo mật. Có 4 mức cho sẵn đó là:
o None: Không thực hiện dò xét hành vi,
o Success: Ghi nhận tất cả các đăng nhập thành công,
o Failure: Ghi nhận những đăng nhập không thành công,
o All: ghi nhận tất cả các lần đăng nhập.
• Trong vùng Startup service account: Chỉ ra tài khoản Windows được dùng khi SQL Server khởi động.
• System account: các tài khoản hệ thống cục bộ được xây dựng sẵn.
• This account: Chỉ ra một tài khoản cụ thể

8.1.2. Người dùng và đăng nhập
Tài khoản dùng để kết nối tới SQL Server được gọi là tài khoản đăng nhập SQL Server. Cùng với tài khoản đăng nhập SQL Server, mỗi CSDL có một tài khoản người dùng ảo được gán với nó. Những tài khoản ảo này cung cấp một bí danh tới tài khoản đăng nhập SQL Server được gọi là tài khoản người dùng CSDL.
* Tạo tài khoản đăng nhập SQL Server:
Dùng Enterprise manager:
Trong cửa sổ Enterprise Manager, mở rộng server muốn tạo tài khoản đăng nhập và mở rộng mục Security. Right click lên Logins và chọn New Login để xuất hiện cửa sổ SQL Server Login Properties (hình 3.60).

• Trên tab General:
• Name: Nhập tên tài khoản đăng nhập. nếu chọn chế độ xác thực bằng Window thì tên tài khoản đăng nhập phải là tài khoản đã tồn tại trong Windows.
• Authentication: Chọn chế độ xác thực của Windows là Windows Authentication hay chế độ xác hỗn hợp SQL Server Authentication.
• Default: Chọn CSDL và ngôn ngữ mặc định sẽ được dùng.
• Tab Server Roles: Ở đây ta chọn nhóm quyền server cho đăng nhập mới bằng cách chọn các nhóm quyền trong danh sách. Click vào nút Properties để xem và sửa đổi nhóm quyền đã chọn. Nếu là tài khoản đăng nhập thường thì không cần cấp quyền server.
• Tab Database Access: Cho phép chọn CSDL mà người dùng được phép truy cập.
Dùng T-SQL:
• Ta có thể tạo tài khoản đăng nhập bằng thủ tục sp_addlogin hoặc sp_grandlogin.
o Thủ tục sp_addlogin chỉ có thể thêm người dùng được xác thực bằng SQL Server.
o Thủ tục sp_grandlogin có thể thêm người dùng được xác thực bằng Windows.
Ví dụ 3.11. Tạo tài khoản đăng nhập Huongct với Password là ‘abcd’ và CSDL mặc định là ‘QLDiemSV’
EXEC sp_addlogin 'Huongct1','abcd','QLDiemSV'
Ví dụ 3.12. Tạo tài khoản đăng nhập Huongct với chế độ xác thực Windows.
EXEC sp_grantlogin 'THUHUONG\Huongct'
Ngoài ra, đối với SQL Server 2005 ta có thể sử dụng cú pháp T- SQL sau:
+ Tạo Login
CREATE LOGIN login_name { WITH PASSWORD =
'password' [,<option_list>[ ,... ]] | FROM
WINDOWS [ WITH <windows_options> [ ,... ]]}
<option_list> ::=
DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| CHECK_EXPIRATION = { ON | OFF}
| CHECK_POLICY = { ON | OFF}
[ CREDENTIAL = credential_name ]

<windows_options> ::=
DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
Sửa Login
ALTER LOGIN login_name
{
<status_option>
| WITH <set_option> [ ,... ]
}

<status_option> ::=
ENABLE | DISABLE

<set_option> ::=
PASSWORD = 'password'
[
OLD_PASSWORD = 'oldpassword'
]
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| NAME = login_name
| CHECK_POLICY = { ON | OFF }
| CHECK_EXPIRATION = { ON | OFF }
+ Xóa Login
DROP LOGIN login_namee
Dùng Wizard trong SQL Server 2000:
Vào Tools\Wizard xuất hiện cửa sổ hình 3.5. Chọn mục Database và chọn Create Login Wizard. Sau đó thực hiện theo sự chỉ dẫn của trình Wizard.
* Tạo người dùng SQL Server:
Để tạo người dùng SQL Server, trước hết ta phải tạo đăng nhập SQL Server cho người dùng đó vì tên người dùng tham chiếu đến tên đăng nhập.
Dùng Enterprise manager:
+ Trong cửa sổ Enterprise Manager, mở rộng mục Database. Right click CSDL muốn tạo người dùng và chọn New\Database User xuất hiện cửa sổ New User (hình 3.64).

+ Nhập tên đăng nhập hợp lệ trong danh sách các tên đăng nhập của hộp combo Login Name và nhập tên người dùng mới vào hộp User Name (Mặc định SQL Server tự điền tên User Name trùng tên Login Name, ta có thể thay đổi tên này)
+ Chọn nhóm quyền CSDL mà người dùng mới này là thành viên, sau đó chọn OK.
Dùng T-SQL:
• Ta có thể tạo người dùng mới bằng thủ tục sp_adduser.
• Đối với SQL Server 2005 ta có thể dùng cú pháp sau:
+ Tạo User
CREATE USER user_name
{ { FOR | FROM } LOGIN login_name |
WITHOUT LOGIN }
[ WITH DEFAULT_SCHEMA = schema_name ]
+ Sửa User
ALTER USER user_name
WITH <set_item> [ ,...n ]
<set_item> ::=
NAME = new_user_name
| DEFAULT_SCHEMA = schema_name
+ Xóa User
DROP USER user_name
8.2. Quản lý quyền CSDL
Các nhóm quyền CSDL được thiết kế cho phép các nhóm những người dùng nhận các quyền CSDL giống nhau mà không cần phải cấp quyền một cách riêng biệt cho từng người dùng.
a) Các nhóm quyền Server cố định
Một số nhóm quyền ở cấp server đã được định nghĩa trước tại thời điểm cài đặt SQL Server. Những nhóm quyền cố định này được dùng để cấp quyền cho người quản trị CSDL. Các nhóm quyền server cố định được liệt kê trong danh sách sau:
+ bulkadmin: Có thể thực thi lệnh BULK INSERT để thêm lượng lớn dữ liệu vào bảng
+ dbcreator: Có thể tạo và sửa đổi CSDL.
+ diskadmin: Có thẻ quản lý các tập tin trên đĩa.
+ processadmin: Có thể quản lý các quá trình của SQL Server.
+ securityadmin: Có thể quản lý đăng nhập và tạo các quyền CSDL.
+ serveradmin: Có thể thiết lập bất kỳ tùy chọn server nào và có thể đóng CSDL.
+ setupadmin: Có thể quản lý các server liên kết và có thể đóng CSDL.
+ sysadmin: Có thể thực hiện bất kỳ hoạt động server nào.
Thêm người dùng vào các nhóm quyền server cố định.
Dùng Enterprise Manager
+ Trong cửa sổ Enterprise Manager, mở rộng server và mở rộng mục Security.


bài tập csdl


2.1 Cho biết danh sách các đối tác cung cấp hàng cho công ty.
2.2 Mã hàng,tên hàng và số lượng của các mặt hàng hiện có trong công ty.
2.3 Họ tên và địa chỉ và năm bắt đầu làm việc của các nhân viên trong công ty.
2.4 Địa chỉ và điện thoại của nhà cung cấp có tên giao dịch VINAMILK là gì?
2.5 Cho biết mã và tên của các mặt hàng có giá trị lớn hơn 100000 và số lượng hiên có ít hơn 50.
2.6 Cho biết mỗi mặt hàng trong công ty do ai cung cấp.
2.7 Công ty Việt Tiến đã cung cấp những mặt hàng nào.
2.8 Loại hàng thực phẩm do những công ty nào cung cấp và địa chỉ của các công ty đó là gì?
2.9 Những khách hàng nào(tên giao dịch) đã đặt mua măt hàng sữa hộp XYZ của công ty?
2.10 Đơn đặt hàng số 1 do ai đặt và do nhan vie nào lập,thời gian và địa điểm giao hàng là ở đâu?
2.11 Hãy cho biết số tiền mà công ty phải trả cho mỗi nhân viên là bao nhiêu(lương=lương cơ bản + phụ cấp).
2.12 Trong đơn đặt hàng số 3 đặt mua những mặt hàng nào và số tiền mà khách hàng phải trả cho mỗi mặt hàng là bao nhiêu(số tiền phải trả được tính theo công thức: SỐ LƯỢNG x GIÁ BÁN – SỐ LƯỢNG x GIÁ BÁN x MỨC GIẢM GIÁ/100).
2.13 Hãy cho biết có những khách hàng nào lại chính là đối tác cung cấp hàng của công ty(tức là có cùng tên giao dịch).
2.14 Trong công ty có những nhân viên nào có cùng ngày sinh?
2.15 Những đơn đặt hàng nào yêu cầu giao hàng ngay tại công ty đặt hàng và những đơn đó là của công ty nào.
2.16 Cho biết công ty, tên giao dịch, địa chỉ và điện thoại của khách hàng và các nhà cung cấp hàng cho công ty.
2.17 Những mặt hàng nào chưa từng được khách hàng đặt mua?
2.18 Những nhân viên nào của công ty chưa từng lập bất kỳ hóa đơn đặt hàng nào ?
2.19 Những nhân viên nào của công ty có lương cơ bản cao nhất?
2.20 Tổng số tiền mà khách phải trả cho mỗi đơn đặt hàng là bao nhiêu?
2.21 Trong năm 2003, những mặt hàng nào chỉ được đặt mua đúng một lần.
2.22 Hãy cho biết mỗi khách hàng đã phải bỏ ra bao nhiêu tiền để đặt mua hàng của công ty?
2.23 Mỗi một nhân viên của công ty đã lập bao nhiêu đơn đặt hàng(nếu nhân viên chưa hề lập môt đơn đặt hàng nào thì cho kết quả là 0).
2.24 Cho biết tổng số tiền hàng mà cửa hàng thu được trong mỗi tháng của năm 2003(Thời gian được tính theo ngày đặt hàng)
2.25 Hãy cho biết tổng số tiền mà công ty thu được từ mỗi mặt hàngtrong năm 2003.
2.26 Hãy cho biết tổng số lượng hàng của mỗi mạt hàng mà công ty đã có(tống số lượng hàng hiên có và đã bán).
2.27 Nhân viên nào của công ty bán được số lượng hàng hiều nhất và số lượng hàng bán được của những nhân viên này là bao nhiêu?
2.28 Đơn đặt hàng nào có số lượng hàng được đặt mua ít nhất?
2.29 Số tiền nhiều nhất mà mỗi khách hàng đã từng bỏ ra để đặt hàng trong các đơn đặt hàng là bao nhiêu?
2.30 Mỗi đơn đặt hàng đặt mua những mặt hàng nào và tổng số tiền mà mỗi đơn đặt hàng là bao nhieu?
2.31 Hãy cho biết mỗi loại hàng bao gồm những mặt hàng nào, tổng số lượng hàng của mỗi loại hàng và tổng số lượng của tất cả các mặt hàng hiện có trong công ty là bao nhiêu?
2.32 Thống kê xem trong năm 2003, mỗi một mặt hàng trong mỗi tháng và trong cả năm bán được với số lượng bao nhiêu?
Yêu cầu: Kết quả được hiển thị dưới dạng bảng,hai cột,cột đầu là mã hàng và tên hàng,các cột còn lại tương ứng với với các tháng 1 đến 12 va cả năm.Như vậy mỗi dòng trong kết quả cho biết số lượng hàng đã bán được mỗi tháng và trong cả năm của mỗi mặt hàng.
Sử dụng câu lệnh UPDATE để thực hiện các yêu cầu sau:
2.33 Cập nhật lại giá trị của trường NGAYCHUYENHANG của những bản ghi có NGAYCHUYENHANG chưa xác định(NULL) trong bảng DONDATHANG bằng giá trị của trường NGAYDATHANG
2.34 Tăng số lượng hàng của những mặt hàng do công ty VINAMILK cung cấp lên gấp đôi.
2.35 Cập nhật giá trị của trường NOIGIAOHANG