Giáo trình Cơ sở dữ liệu (Phần 2)
Tóm tắt Giáo trình Cơ sở dữ liệu (Phần 2): ...oup_name [,...n ] Thực hiện: (1) Gõ lệnh trong cửa sổ Query Analyzer (2) Gọi thực thi câu lệnh Ví dụ: Tạo CSDL có tên là SalesDB, tập tin dữ liệu tên là SalesDB_dat.mdf đặt trong C:\Data, kích cở khởi tạo là 10MB, kích thước tối đa là 50MB, tỉ lệ gia tăng là 5MB, và tập tin vết tên là Sa...merID = C.CustomerID AND O.ShipCity=C.City ORDER BY O.OrderID Cross Jions: Mỗi dòng trong tất cả các dòng của bảng bên trái sẽ kết hợp với tất cả các dòng của bảng bên phải. Giả sử X, Y là số dòng của bảng bên trái và bên phải, thì tập kết quả sau khi Cross Join có X*Y dòng. Sefl Joins: ...ho biết ý nghĩa của đoạn lệnh sau: USE NorthWind SELECT ProductID, Quantity, UnitPrice, [discount%]= CASE WHEN Quantity <=5 THEN 0.05 WHEN Quantity BETWEEN 6 and 10 THEN 0.07 WHEN Quantity BETWEEN 11 and 20 THEN 0.09 ELSE 0.1 END FROM [Order Details] ORDER BY Quantity,...
quyền truy xuất đến các CSDL. Ngoại trừ các thành viên trong sysadmin role, thành viên trong một server role có sẳn quyền truy xuất CSDL. Các quyền truy xuất CSDL phải được cấp một cách rõ ràng bởi một system administrator hoặc thành viên của administrator role trong CSDL. Các quyền có thể được cấp (grant), từ chói (deny), cởi bỏ (revoke) và bao gồm các quyền tạo đối tượng, quản trị CSDL, thực thi các câu lệnh T-SQL, chèn dữ liệu vào bảng, xem dữ liệu bằng view. SQL Server 2000 có một số cơ chế để cấp các quyền cụ thể cho các user trong một CSDL. Các quyền cụ thể trên CSDL Quyền Mô tả Database owner User có thể được chỉ định như là chủ (owner) của CSDL và có thể thực hiện bất kỳ hành động liên quan đến CSDL. DBO role Tất cả các thành viên của sysadmin server role thì tự động là thành viên của dbo role trong mỗi CSDL, và có thể thực hiện bất kỳ thao tác liên quan đến CSDL. User Các user và group có thể được cấp user truy xuất đến CSDL theo tài khoản bảo mật của Windows 2000 hoặc SQL Server 2000. Sau đó một giấy phép người dùng CSDL được cấp các quyền trong CSDL thông qua database role, role chung, và chỉ định cấp các quyền câu lệnh và đối tượng. Guest user Một user mà có thể truy xuất đến 1 instance của SQL Server 2000 (nhưng người này không có tài khoản truy người dùng để truy xuất đến CSDL cụ thể) có thể được cho phép truy xuất đến CSDL như là một người khách (guest user). Tài khoản guest có thể được cấp các quyền cụ thể trong CSDL (đề đọc dữ liệu). Theo mặc nhiên, một CSDL không có tài khoản guest user. Public role Tất cả các user được phép truy xuất đến CSDL trở thành thành viên của public role trong mỗi CSDL. Public role có thể được cấp các quyền cụ thể (Tổng quát các quyền cần thiết cho tất cả các user của CSDL). Fixed database role Cho phép các user có thể được thêm vào các fixed database role trong một CSDL. Các Fixed database role chứa các quyền định trước trong CSDL để thi hành các hoạt động của database-wide. User-defined database role Cho phép các user có thể thêm vào user-defined database role trong một CSDL. Các role này có thể được tạo bở administrator và cấp một cách cụ thể các quyền đưa ra hoặc các quyền trong CSDL. Giáo trình SQL Server2000 Trang 132 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Quyền Mô tả Statement permissions Quyền thực thi các câu lệnh quản trị (như CREATE PROCEDURE) có thể được cấp, huỹ bỏ, từ chói các users, groups, và roles. Object permissions Quyền truy xuất đến các đối tượng CSDL (như là bảng hay view) có thể được cấp, huỹ bỏ, từ chối các users, groups, và roles. Application role Quyền thực thi các hành động trong một CSDL có thể được cấp cho một application, thậm chí cấp cho user. Một application kết nối đến một CSDL và kích hoạt application role. Các User truy xuất đến một CSDL thông qua sự kết nối này để dành lấy các quyền kết hớp với application role trong suốt quá trình kết nối. Các quyền phát hành đến một user cụ thể thì không liên quan khi user đang truy xuất CSDL thông qua application role. 15.1.5 Fixed Database Roles. Mỗi CSDL có 9 role về CSDL được định nghĩa trước với các quyền kết hợp với database- wide để thực hiện các tác vụ khác nhau. Bạn không thể xóa những database role này và cũng không thể thay đổi các quyền của chúng. Để gán một người dùng các quyền này trong một CSDL, bạn thêm tài khoản của người dùng vào database role. Nếu những fixed database role này không gán tổng hợp các quyền mà bạn cần thì bạn có thể tạo các role với các quyền người dùng (thông thường nhiều quyền hạn chế). Giáo trình SQL Server2000 Trang 133 Trung Tâm CNTT - Trường ĐHCN Tp.HCM 9 fixed database role có thể có trong SQL Server 2000. Database Role Thành viên của CSDL này có thể db_owner Thực hiện bất kỳ tác vụ trong CSDL của SQL Server 2000. Các thành viên của role này có cùng quyền như là chủ của CSDL là các thành viên của dbo role. db_accessadmin Thêm hay xóa các user và group của Windows 2000 hoặc Win NT4.0 và các user trong một CSDL (dùng thủ tục hệ thống sp_grantdbaccess). db_securityadmin Quản lý tất cả các permission, role, role membership, và chuyển ower (ownership) trong một CSDL (sử dụng lệnh GRANT, REVOKE, và DENY). db_ddladmin Thêm, hiệu chỉnh, xóa các đối tượng trong CSDL (sử dụng lệnh CREATE, ALTER, và DROP). db_backupoperator Chạy các lệnh DBCC, phát hành checkpoint, và dự phòng CSDL (sử dụng các câu lệnh T-SQL: DBCC, CHECKPOINT, và BACKUP). db_datareader Đọc dữ liệu từ bất kỳ các bảng hoặc view của người dùng trong CSDL (bạn có quyền SELECT đối với tất cả table và view). Db_datawriter Hiệu chỉnh hoặc xóa dữ liệu từ các bảng hay view của người dùng trong CSDL (bạn phải có quyền INSERT, UPDATE, và DELETE đối với tất cả các table và view). Db_denydatareader Không đọc dữ liệu từ bất kỳ bảng trong CSDL (bạn không có quyền SELECT đối với bất kỳ đối tượng). Có thể permission on any objects). Có thể được sử dụng với role db_ddladmin để cho phép tạo các đối tượng làm chủ bằng dbo role, nhung không có thể đọc nhạy cảm chứa trong các đối tượng đó. Db_denydatawriter Không hiệu chỉnh hay xóa dữ liệu từ các bảng của người dùng trong CSDL (bạn không có quyền INSERT, UPDATE, và DELETE đối với các đối tượng) 15.2 Tạo tài khoản đăng nhập (Login). 15.2.1 Dùng Create Login Wizard. Giáo trình SQL Server2000 Trang 134 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Hình 40: Chọn chế độ đăng nhập cho login đang tạo Nếu bạn chọn Windows authentication thì bạn phải liên kết login ID này với một user hay group của Windows 2000 có sẳn. Khai báo như trong hình sau Hình 41: Xác định 1 tài khoản của Windows 2000 và xác định cho phép hoặc từ chối login mới truy xuất đến Server Giáo trình SQL Server2000 Trang 135 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Nếu bạn chọn SQL Server authentication, bạn sẽ tạo một tài khoản bảo mật của SQL Server 2000 như trong hình kế. Hình 42: Khai báo LogiID và Password Phải chỉ định rõ tên login và Password. Để ngăn chặn (deny) một login, đơn giản bạn chỉ cần xóa bỏ login từ nơi chứa login trong Enterprise Manager (hoặc từ bảng sysxlogins trong CSDL master). Sau khi bạn xác định kiểu đăng nhập và liên kết hay tạo tài khoản bảo mật, bạn chỉ định server role (nếu cần) cho login này trong hộp thoại kế. Nếu user sẽ không là một server- wide administrator, thì không cần chọn các server role. Giáo trình SQL Server2000 Trang 136 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Hình 43: Chỉ định server role cho login đang được tạo Kế tiếp bạn chỉ định CSDL (nếu cần) để cho user này có thể truy xuất trong hộp thoại kế tiếp. Nhớ rằng hầu hết các server role không cung cấp CSDL truy xuất Hình 44: Chỉ định 1 hay nhiều CSDL để login này được truy xuất Cuối cùng, bạn được SQL Server cho xem trước các lựa chọn mà bạn đã thực hiện trước khi login mới được tạo thực sự. Click vào nút Finish để tạo login. Giáo trình SQL Server2000 Trang 137 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Hình 45: Chọn nút Finish để hoàn tất 15.2.2 Dùng Enterprise Manager để tạo một Login. Để tạo một login bằng Enterprise Manager, nhấp nút phải chuột tại Security/Login của 1 instance, và chọn New Login. Thông thường, các trang Server Role, và Database Access trong hợp thoại Propertie của login Giáo trình SQL Server2000 Trang 138 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Hình 46: Chỉ định Name, chế độ chứng thực, chọn CSDL mặc định Hình 47: Chọn tài khoản người dùng của Windows Giáo trình SQL Server2000 Trang 139 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Bạn cũng chọn một CSDL mặc định và ngôn ngữ trong trang General. CSDL mặc định sẽ là CSDL hiện hành khi mà User đăng nhập vào. Mặc định không chọn là CSDL Master nhưng bạn nên chọn 1 CSDL khác làm mặc định. Ngôn ngữ mặt định là ngôn ngữ mặc định của instance hiện hành. Lưu ý: Bạn cũng có thể từ chối sự truy xuất của một User hay group của Windows. Điều này được áp đặt đến việc truy xuất khác của user hay group (kể cả thành viên của một nhóm khác mà có login khác). Hình 48: Gán login thuộc server role nào đó nếu cần Lưu ý rằng nếu System Administrators server role được chọn, thì bạn có thể xem nhóm built-in Administrator group, và login sa của the SQL Server là những thành viên của sysadmin server role. Chọn nút properties để xem một số propererties của Server role Giáo trình SQL Server2000 Trang 140 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Hình 49:Thành viên của server role Hình 50: Các quyền hạn cụ thể của Server role Giáo trình SQL Server2000 Trang 141 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Hình 51: Chỉ định CSDL được truy xuất tới và các quyền cụ thể cho login Khi bạn chọn 1 CSDL thì login là thành viên của public role trong mỗi CSDL. Bạn không thể xóa (remove) một login ra khỏi public role. 15.2.3 Tạo Login bằng T-SQL. Bạn cũng có thể tạo các Login bằng T-SQL Windows Logins Các thủ tục hệ thống dùng để cấp, hũy, từ chối, hiệu chỉnh một login cho một user hay group của Windows. Chỉ có những thành viên của sysadmin hoặc securityadmin server roles mới có thể thực thi các thủ tục hệ thống này Thủ tục hệ thống Mô tả Sp_grantlogin 'login' Tạo một login cho một user hay group của Windows 2000. Sp_revokelogin 'login' Hũy login từ SQL Server đối với các user hay group của Windows 2000 (hoặc Windows NT 4.0). Điều này không rõ ràng lắm vì nó vẫn có thể được cấp quyền truy xuất đến SQL Server 2000. Sp_denylogin 'login' Ngăn chặn một user hay thành viên của Windows 2000 (hoặc Windows NT 4.0) kết nối đến SQL Server 2000. Ngăn chặn các user hay group của thôgn qua một login khác kết với user hay group của Windows. Sp_defaultdb 'login', 'database' Thay đổi CSDL mặc định cho một login. Giáo trình SQL Server2000 Trang 142 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Thủ tục hệ thống Mô tả Sp_defaultlanguage 'login', 'language' Thay đổi ngôn ngữ mặc định của một login. Ví dụ: Sp_grantlogin 'CDCN4\Bill' SQL Server Logins Các thủ tục hệ thống sau cho phép cấp, hũy, từ chối, hiệu chỉnh một login kết với một tài khoản người dùng SQL Server. Chỉ có các thành viên của sysadmin hoặc securityadmin server roles mới có thể thực thi các thủ tục hệ thống này Thủ tục hệ thống Mô Tả Sp_addlogin 'login', ['password', 'database', 'language', 'sid', encryption_option'] Tạo một login SQL Server mới. Password là NULL nếu không chỉ định. CSDL mặc định là master nếu không chỉ định. Ngôn ngữ mặc định là ngôn ngữ của server hiện hành nếu không chỉ định. Mặc định, password ở trong csdl master. Sp_droplogin 'login' Xóa một SQL Server login. Sp_password 'old_password', 'new_password', 'login' Thêm hoặc thay đổi password cho SQL Server login. Sp_defaultdb 'login', 'database' Thay đổi CSDL mặc định Sp_defaultlanguage 'login', 'language' Thay đổi ngôn ngữ mặc định. Ví dụ: Sp_addlogin 'Joe', 'Joe123', 'Northwind' Tạo một SQL Server login mới có tên là Joe, với password là Joe123 và CSDL mặc định Northwind. Server Roles Các thủ tục hệ thống sau được dùng để thêm hay xóa một login vào một server role. Chỉ có thành viên của sysadmin server role mới có thể thêm các login vào bất kỳ server role. Các thành viên của server role cũng có thể thêm các login vào server role đó. Thủ tục hệ thống Mô tả Sp_ addsrvrolemember 'login', 'role' Thêm login như là tàhnh viên của server role. Sp_dropsrvrolemember 'login', 'role' Xoá login không là thành viên của một server role. Ví dụ: Sp_addsrvrolemember 'Joe', 'securityadmin' Thêm login có tên là Joe vào server role Security Administrator. Database Access Các thủ tục sau đây được dùng để thêm hay xóa một login (Windows hoặc SQL Server) hiện hữu được quyền truy xuất trong CSDL hiện hành. Không giống như SQL Server Enterprise Manager, bạn có thể cấp một nhóm của Windows 2000 (hoặc Windows NT 4.0) group truy xuất đến CSDL mà không cần tạo login trước một cách tường minh trong Giáo trình SQL Server2000 Trang 143 Trung Tâm CNTT - Trường ĐHCN Tp.HCM bảng sysxlogins. Chỉ có các thành viên của sysadmin server role, và db_accessadmin và db_owner fixed database role mới có thể thực thi các thủ tục hệ thống này. Thủ tục hệ thống Mô tả Sp_grantdbaccess 'login', 'name_in_db' Thêm một login như là một user trong CSDL hiện hành. Mặc dù tên user name trong CSDL có thể khác với tên login, điều này không khuyến cáo. Sp_revokedbaccess 'name' Bỏ một login như là một user trong CSDL hiện hành. Ví dụ: USE Northwind EXEC Sp_grantdbaccess 'Joe' Cho phép login tên là Joe truy xuất đến CSDL hiện hành, dùng user name là Joe trong CSDL Northwind. Database Roles Các thủ tục hệ thống sau đây được dùng để thay đổi database owner, thêm hoặc xóa một tài khoản bảo mật vào một database role có sẳn, hoặc tạo hoặc xóa một user-defined database role. Use Northwind EXEC Sp_addrolemember 'db_securityadmin', ' CDCN4\KeToan' Thêm tài khoản CDCN4\KeToanvào db_securityadmin database role trogn CSDL Northwind. Thủ tục hệ thống Mô tả Sp_changedbowner 'login', remap_alias_flag Thay đổi owner của một CSDL người dùng. Chỉ có những thành viên của sysadmin server role hoặc owner database hiện hành mới có thể thau đổi owner của CSDL. Sp_addrolemember 'role', 'security_account' Thêm một tài khoản vào một database role trong CSDL hiện hành. Bạn có thể thêm một user-defined database role vào fixed hoặc user-defined database role. Chỉ có những thành viên của sysadmin server role và db_owner and db_security fixed database roles mới có thể thêm thành viên vào database role. Thành viên của database role có thể thành viên vào cho database role đó. Sp_droprolemember 'role', 'security_account' Xóa một tài khoản từ một CSDL vào CSDL hiện hành. Chỉ có những thành viên của sysadmin server role và db_owner và db_security fixed dababase roles mới có thể xóa các thành viên ra khỏi database role. Các thành viên của database role mới có thể xóa các thành viên ra khỏi database role. Sp_addrole 'role', 'owner' Thêm một user-defined database role mới trong CSDL hiện hành. Mặc dù bạn có thể chỉ định một owner của role, sử dụng mặc định là dbo là không được khuyến cáo. Các thành viên của sysadmin server role và db_securityadmin và db_owner fixed database roles mới có thể tạo user-defined database roles. Sp_droprole 'role' Xóa một user-defined database role ở CSDL hiện hành. Các tàhnh Giáo trình SQL Server2000 Trang 144 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Thủ tục hệ thống Mô tả viên của sysadmin server role, db_securityadmin và db_owner fixed database roles mới có thể xóa user-defined database roles. Để xem các thông tin về login, dùng các thủ tục hệ thống sau: Thủ tục hệ thống Mô tả Sp_helplogins [ 'login' ] Trả về các thông tin của tất cả các login hoặc một login được chỉ định, kể cả các CSDL mà login có truy xuất đến và các database roles mà login là thành viên. Sp_helpsrvrolemember [ 'role' ] Trả về thông tin về tất cả các server roles và những thành viên của chúng hoặc tất cả các thành viên trong một server role chỉ định. Sp_helpuser [ 'security_account' ] Trả về thông tin về tất cả các user hoặc user chỉ định trong CSDL hiện hành, kể cả tất cả các hội thành viên của database role. Sp_helprolemember [ 'role' ] Trả về các thông tin của tất cả các database role hoặc tất cả các thành viên trong database role chỉ định trong CSDL hiện hành. Sp_helpntgroup [ 'name' ] Trả về các thông tin về các nhóm hoặc 1 nhóm chỉ định của Windows 2000 (hoặc Windows NT 4.0) trong CSDL hiện hành. Gán các quyền Database Các view và các stored procedure có thể được tạo trên các table. Khi một user cố gắng lấy thông tin thông qua view hoặc procedure, thì SQL Server 2000 phải kiểm tra user có được phép lấy dữ liệu hay không. Nếu view hoặc procedure được làm chủ bởi một user và của các bảng cơ sở lại là của một user khác thì SQL Server 2000 phải kiểm tra các quyền trên mỗi object trong dây chuyền đó. Khi một chuỗi các ownership kéo dài thì điều này sẽ ảnh hưởng đến việc thực hiện. Nhưng có lẽ quan trọng hơn là nó có thể bị gắt gối cho nhà quản trị vạch ra và gỡ lỗi các từ việc đưa ra bảo mật Các quyền về lệnh Câu lệnh Transact- SQL Quyền để thực thi câu lệnh Transact-SQL CREATE DATABASE Thừa kế bởi thành viên của sysadmin và dbcreator server roles. Mặc dù sysadmin và securityadmin server roles có thể cấp quyền một cách trực tiếp cho các tài khoản để thực hiện câu lệnh này, Tóm lại các tài khoản bảo mật sử dụng dbcreator server role nếu system administrator đại diện quyền. Quyền này chỉ tồn tại trong CSDL master. BACKUP DATABASE BACKUP LOG Kế thừa bởi các thành viên của sysadmin server role và db_owner và db_backupoperator fixed database roles. Mặc dù bạn có thể cấp quyền để chạy những câu lệnh này một cách trực tiếp đến các tài khoản bảo mật, một cách tổng quát bạn sẽ sử dụng db_backupoperator fixed database role. Giáo trình SQL Server2000 Trang 145 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Câu lệnh Transact- SQL Quyền để thực thi câu lệnh Transact-SQL CREATE TABLE CREATE VIEW CREATE PROCEDURE CREATE DEFAULT CREATE RULE CREATE FUNCTION Kế thừa các thành viên của sysadmin server role và db_owner và db_ddladmin fixed database roles. Quyền cho phép tạo những đối tượng thì được cấp trực tiếp đến nhà lập trình trong suốt thời gian triển khai. Theo mặc định các object được làm chủ bởi người tạo ra đối tượng (mặc dù các đối tượng tạo bởi các thành viên của sysadmin server role thì chủ sẽ là dbo role). Các thành viên của db_owner hoặc db_ddladmin fixed database roles có thể được chỉ định dbo role như là owner của đối tượng đựơc tạo. Ngoài ra, các thành viên của sysadmin server role hoặc db_owner hoặc db_ddladmin fixed database role có thể chỉ định bất kỳ user như là chủ của object mà chúng tạo ra. Tuy nhiên, các user mà không là thành viên của một trong các role này thì không thể chỉ định user khác hoặc dbo role làm chủ của object chúng tạo ra. CREATE TRIGGER Kế thừa bởi chủ của table mà của sysadmin server role, và db_owner and db_ddladmin fixed database roles. Những thành viên này không thể cấp quyền để chạy câu lệnh này cho những tài khoản bảo mật khác.. Ví dụ: CREATE TABLE Northwind.dbo.CustomerTable (CustID nchar (5), CustomerName nvarchar (40)) Cho phép tạo một bảng, cấp ownership cho dbo role. Chỉ có những thành viên sysadmin server role và db_owner hoặc db_ddladmin fixed database roles có thể thực hiện một cách thành công câu lệnh này. Thay đổi Ownership của Object Một thành viên của db_owner, db_ddladmin, hoặc db_securityadmin fixed database role, hoặc a member of the sysadmin server role có thể thay đổi ownership của bất kỳ object trong bằng cách chạy thủ tục sp_changeobjectowner. sp_changeobjectowner 'CDCN4\KeToan.Customer', 'dbo' Đổi ownership của table Customer từ CDCN4\KeToanBill cho dbo role. Lưu ý Thay đổi owner của một object thì sẽ xó tất cả các quyền hiện có trên đối tượng. Nếu bạn cần giữ lại các quyền thì nên tạo Scrip trước khi đổi owner. Dùng Enterprise Manager đề Grant, Deny, or Revoke quyền lệnh Nhắp nút phải chuột tại CSDL cần thực hiện cấp/xoá/từ chối/xem các quyền Giáo trình SQL Server2000 Trang 146 Trung Tâm CNTT - Trường ĐHCN Tp.HCM Hình 52: Trang Permission của hộp thoại thuộc tính của CSDL Dùng Transact-SQL để Grant, Deny, or Revoke các quyền Dùng câu lệnh GRANT CREATE TABLE TO . GRANT CREATE TABLE TO Joe, SalesManagers, [CDCN4\SQLServerAdmins] Cấp quyền lệnh CREATE TABLE cho Joe (một SQL Server login), SalesManagers (a user-defined database role), và CDCN4\SQLServerAdmins (một Windows group). Xem các quyền bằng Transact-SQL : Dùng sp_helprotect EXEC sp_helprotect NULL, NULL, NULL, 's' Xem tất cả các quyền câu lệnh trong CSDL hiện hành.
File đính kèm:
- giao_trinh_co_so_du_lieu_phan_2.pdf