Toán - Tin Học Hệ Quản Trị phần 4
lượt xem 6
download
Tham khảo tài liệu 'toán - tin học hệ quản trị phần 4', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Bình luận(0) Đăng nhập để gửi bình luận!
Nội dung Text: Toán - Tin Học Hệ Quản Trị phần 4
- 35 Bài gi ng tóm t t H qu n tr cơ s d li u − Khi g i hàm lo i 1 (tr v giá tr cơ b n), ph i có tên owner c a hàm i kèm (ví d dbo.SoLonNhat(5,8,-10)). Thay i hàm ngư i dùng Thay t khóa create trong các l nh t o hàm b ng t khóa alter Xóa hàm ngư i dùng Drop function tên_hàm_c n _xóa Ví d : Drop function DanhSachMatHang 3. Các hàm h th ng Ngoài các hàm do ngư i dùng nh nghĩa, SQL Server còn cung c p các hàm xây d ng s n c a h th ng. Các hàm này cung c p ti n ích như x lý chu i, x lý th i gian, x lý s h c… Sinh viên tìm hi u thêm v các hàm này trong Books on-line và các tài li u tham kh o. t o hàm h th ng c n ti n hành theo các bư c sau: • T o hàm trong cơ s d li u Master u b i fn_functionName Tên hàm b t Thay i ch nhân c a hàm b ng th t c sp_changeobjectowner như sau: EXEC sp_changeobjectowner ‘fn_ functionName’ , ‘system_function_schema’ Ví d : T o hàm h th ng th c chuy n i m t bi n ki u ngày tháng sang ki u chu i. --T o hàm fn_doingay create function fn_doingay(@ngay datetime) returns char(10) as begin return convert(nchar(10),@ngay,103) end --Thay i ch nhân c a hàm EXEC sp_changeobjectowner 'fn_doingay' , 'system_function_schema' Sau l nh này hàm fn_doingay có th dùng ư c cho CSDL b t kỳ. Select manv, hoten, fn_doingay(ngaysinh) From nhanvien VI. Triggers và cài t ràng bu c d li u 1. Gi i thi u • Trigger là m t lo i stored procedure c bi t có các c i m sau:
- 36 Bài gi ng tóm t t H qu n tr cơ s d li u −T ng th c hi n khi có thao tác insert, delete ho c update trên d li u. − Thư ng dùng ki m tra các ràng bu c toàn v n c a CSDL ho c các qui t c nghi p v . − M t trigger ư c nh nghĩa trên m t b ng, nhưng các x lý trong trigger có th s d ng nhi u b ng khác. • X lý c a trigger thư ng c n s d ng n hai b ng t m: − Inserted: ch a các dòng v a m i ư c thao tác insert/ update thêm vào b ng. − Deleted: ch a các dòng v a m i b xóa kh i b ng b i thao tác update/delete. Lưu ý: update = delete dòng ch a giá tr cũ + insert dòng ch a giá tr m i − Inserted và deleted là các b ng trong b nh chính: C c b cho m i trigger. Có c u trúc gi ng như b ng (table) mà trigger nh nghĩa trên ó Ch t n t i trong th i gian trigger ang x lý. − N u thao tác insert/ delete/ update th c hi n trên nhi u dòng, trigger cũng ch ư c g i m t l n B ng inserted/ deleted có th ch a nhi u dòng. 2. S d ng Trigger Khai báo trigger − Cú pháp: Create trigger tên_trigger On {tên_b ng|tên_view} {For| After| Instead of } { [delete] [,] [insert] [,] [update] } As { các l nh T-sql } Go rong ó: For | After: − Trigger ư c g i th c hi n sau khi thao tác delete/ insert/ update tương ng ã ư c th c hi n thành công: Các dòng m i ư c thêm ch a ng th i trong b ng d li u và b ng inserted. Các dòng b xoá ch n m trong b ng deleted ( ã b xoá kh i b ng d li u). − Có th x lý quay lui thao tác ã th c hi n b ng l nh rollback transaction. Instead of: − Trigger ư c g i th c hi n thay cho thao tác delete/ insert/ update tương ng:
- 37 Bài gi ng tóm t t H qu n tr cơ s d li u Các dòng m i ư c thêm ch ch a trong b ng inserted. Các dòng b ch nh xoá n m ng th i trong b ng deleted và b ng d li u (d li u không b xoá). − Trigger Instead of thư ng ư c dùng x lý c p nh t trên khung nhìn. Lưu ý: − L nh t o trigger ph i là l nh u tiên trong m t query batch. − Trên m t b ng có th nh nghĩa nhi u trigger for/after cho m i thao tác nhưng ch có th nh nghĩa m t trigger instead of cho m i thao tác. − Không th nh nghĩa trigger instead of update/ delete trên b ng có cài t khóa ngo i d ng update cascade/ delete cascade. − Trong thân trigger, có th s d ng hàm Update(tên_c t) ki m tra xem vi c c p nh t ư c th c hi n trên c t nào. Update(tên_c t) = true : có th c hi n c p nh t trên c t tên_c t S d ng trigger cài t m t s lo i ràng bu c Ví d 1- Ràng bu c liên thu c tính – liên quan h Cho CSDL: DatHang(MaPDH, NgayDH,…) GiaoHang(MaPGH, MaPDH, NgayGH,…) Ràng bu c: Ngày giao hàng không th nh hơn ngày t hàng tương ng B ng t m nh hư ng: Thêm Xóa Sa DatHang - - + (NgayDH) GiaoHang + - + (NgayGH, MaPDH) C n cài t trigger cho thao tác s a trên b ng DatHang, và thêm/s a trên b ng Giaohang Trigger cho thao tác thêm và s a trên giao hàng: Create trigger tr_GH_ins_upd_NgayGH On GIAOHANG for insert, update As if update(MaPDH) or update (NgayGH) if exists(select * from inserted i, DatHang d where i.MaPDH = d.MaPDH
- 38 Bài gi ng tóm t t H qu n tr cơ s d li u and i.NgayGH 2 begin raiserror('So lan thi phai
- 39 Bài gi ng tóm t t H qu n tr cơ s d li u • Vi t trigger th c hi n c p nh t giá tr c a ThanhTien khi thêm m t chi ti t hóa ơn
- 40 Bài gi ng tóm t t H qu n tr cơ s d li u Chương 4 B O M T VÀ AN TOÀN D LI U I. B o m t trong h qu n tr cơ s d li u 1. Khái ni m cơ b n v b o m t Nh m b o v h th ng CSDL không b xâm nh p, ngư i qu n tr cơ s d li u ph i quy t nh cho phép hay không cho phép ngư i dùng truy c p và thao tác trên cơ s d li u d a vào nhi m v c a ngư i dùng trên h CSDL. Ngư i qu n tr thư ng d a trên n n t ng lý thuy t b o m t c a h cơ s d li u a ngư i dùng, nh m tìm ra phương pháp b o m t theo úng v i nhu c u c a b o m t d li u. V i m c ích tăng tính b o m t d li u, SQL Server h tr các tính năng cho phép ngư i qu n tr thi t l p cơ ch b o v cơ s d li u trong môi trư ng a ngư i dùng, bao g m các y u t chính sau: o Vai trò c a ngư i dùng trong h th ng và cơ s d li u. o Quy n s d ng các ng d ng cơ s d li u trong SQL Server. o Quy n t o và s a i c u trúc các i tư ng CSDL. o Quy n truy c p, x lý d li u. Khi ăng nh p vào m t h th ng CSDL a ngư i dùng, ngư i s d ng c n ph i cung c p UserID (tài kho n) và Password (m t kh u). D a trên UserID h th ng có kh năng ki m soát t t c các hành vi c a ngư i s d ng trên CSDL SQL Server. th c hi n ư c ch c năng này, ngư i qu n tr CSDL c n ph i thi t l p các quy n x lý và truy c p vào CSDL khi t o ra UserID, ngoài ra còn có m t s thu c tính khác c a SQL Server như quy n backup d li u, trao i d li u v i các ng d ng CSDL khác, … Khi nói n b o m t, ngư i qu n tr c n quan tâm n các thông tin sau c a ngư i dùng: o M t ngư i dùng ch có m t UserID và m t m t kh u. o Th i gian có hi u l c c a m t kh u. o Gi i h n chi u dài c a m t kh u. o Gi i h n ngư i s d ng theo license hay m r ng. o Thông tin v ngư i s d ng. Khi t o ngư i s d ng, tên tài kho n c n rõ ràng, d hi u d g i nh , và không cho phép các ký t c bi t, không nên có kho ng tr ng. 2. L a ch n b o m t
- 41 Bài gi ng tóm t t H qu n tr cơ s d li u Khi t o ra m t ngư i dùng (login user) trong SQL Server, có 3 cách tăng tính b o m t cho ngư i s d ng ó: o Giao ti p v i h i u hành: s d ng UserID và Password c a h i u hành Windows ăng nh p SQL Server. V i lo i b o m t này, ngư i dùng truy c p vào m ng và có th s d ng CSDL SQL Server, ng th i m t ngư i dùng có UserID và Password có th s d ng tài nguyên trên m ng. o B o m t chu n: v i lo i này, ngư i s d ng có UserID và Password tách r i v i h i u hành m ng, ng v i lo i b o m t này ngư i s d ng ch có hi u l c trong CSDL SQL Server, không th s d ng tài nguyên trên m ng. o T ng h p c hai trư ng h p trên: m t s ngư i dùng s d ng quy n s d ng trên h i u hành và SQL Server, m t s khác ch s d ng quy n truy c p vào SQL Server. Lưu ý: Tài kho n ngư i dùng có giá tr trên SQL Server hi n hành, khi sang m t SQL Server khác ph i t o ra tài kho n ngư i dùng trên server ó. o SQL Server cung c p các ch c năng hay các th t c t o m i và qu n tr ngư i dùng CSDL SQL Server như sau: S d ng th t c sp_addlogin. S d ng công c Enterprise Manager. • S d ng th t c sp_addlogin Cú pháp sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt = ] 'encryption_option' ] trong ó các tham s có ý nghĩa như sau: @loginame: tên tài kho n s t o. @passwd: m t kh u cho ngư i dùng có tài kho n trên. @defdb: cơ s d li u m c nh khi ngư i dùng ăng nh p vào SQL Server. @deflanguage: ngôn ng m c nhiên cho ngư i dùng SQL Server. @sid: s nh n d ng h th ng khi ngư i dùng ăng nh p vào. @encryptopt: khi t o tài kho n ngư i dùng trong CSDL, các thông tin v tài kho n, m t kh u ư c lưu tr trong b ng sysusers c a CSDL Master, n u b n cung c p tham s skip_enctription thì không mã hoá m t kh u trư c khi lưu vào b ng sysusers, n u không cung c p tham s hay tr ng, SQL Server s mã hoá m t kh u trư c khi lưu tr .
- 42 Bài gi ng tóm t t H qu n tr cơ s d li u Ví d : t o ngư i dùng có tên ‘nam’, m t kh u ‘123’, cơ s d li u m c nh ‘QLKyNang’ Exec sp_addlogin ‘nam’, ‘123’, ‘QLKyNang’ - Thay i m t kh u sp_password [[ @old = ] 'old_password' ,] { [ @new =] 'new_password' } [ , [ @loginame = ] 'login' ] 3. Quy n ngư i dùng và qu n tr quy n ngư i dùng Quy n c a ngư i dùng ư c nh nghĩa như m c ngư i dùng có th hay không th th c thi trên CSDL, quy n ư c chia thành 4 lo i như sau: o Quy n truy c p vào SQL Server. o Quy n truy xu t vào CSDL. o Quy n th c hi n trên các i tư ng c a CSDL. o Quy n x lý d li u. C p phát quy n truy c p vào CSDL Cú pháp: Use db_name Go sp_grantdbaccess [@loginame =] 'login' [,[@name_in_db =] 'name_in_db' [OUTPUT]] Các tham s : @loginame: tài kho n c a ngư i s d ng ăng nh p vào SQL Server @name_in_db: t o bí danh (tên khác) c a tài kho n ngư i dùng khi truy c p vào CSDL db_name ư c ch nh, n u không ch rõ CSDL mu n cho phép ngư i dùng truy c p thì ngư i dùng ư c c p quy n trên CSDL hi n hành. Lo i b quy n truy c p vào CSDL db_name c a ngư i dùng Use db_name Go sp_revokedbaccess [@loginame =] 'login' C p phát quy n th c thi trên cơ s d li u Sau khi c p phát quy n cho ngư i dùng truy c p vào CSDL, k ti p cho phép ngư i dùng ó có quy n truy c p và x lý các i tư ng trong CSDL cũng như x lý d li u trên các i tư ng ó. Các quy n truy c p trên các i tư ng trong m t CSDL:
- 43 Bài gi ng tóm t t H qu n tr cơ s d li u Quy n Di n gi i SELECT Cho phép ngư i s d ng nhìn th y d li u, n u ngư i s d ng có quy n này thì h ch có th th c thi nh ng phát bi u select truy v n d li u trên các b ng hay các view ư c cho phép. INSERT Cho phép ngư i s d ng thêm d li u, n u ngư i s d ng có quy n này, h có th th c hi n phát bi u Insert, i v i m t s h th ng CSDL khác, mu n th c thi phát bi u Insert, ngư i s d ng ph i có quy n Select, trong SQL Server quy n Insert không liên quan n quy n truy v n Select. UPDATE Quy n này cho phép ngư i s d ng ch nh s a d li u b ng phát bi u Update. DELETE Quy n này cho phép ngư i s d ng xóa d li u b ng phát bi u Delete. REFERENCE Cho phép ngư i s d ng thêm d li u vào b ng có khóa ngo i b ng phát bi u Insert, trong SQL Server quy n Insert không liên quan n quy n truy v n Select. EXECUTE Quy n này cho phép ngư i s d ng th c thi các th t c (SP) trong CSDL. Th t c c p quy n GRANT GRANT ALL | < PERMISSION> [, …n] ON [( [, … n])] | TO [, … n] [WITH GRANT OPTION] [AS ] Trong ó: t khóa ALL cho phép ngư i s d ng có t t c các quy n. PERMISSION là m t trong các quy n: SELECT, INSERT, UPDATE, DELETE, REFERENCE, EXECUTE. Ch rõ nh ng b ng d li u, view ho c th t c nào cho phép ngư i dùng truy c p và x lý. T ch i quy n truy v n và x lý d li u DENY ALL | < PERMISSION> [, …n] ON [( [, … n])] |
- 44 Bài gi ng tóm t t H qu n tr cơ s d li u TO [, … n] [Cascade] Lo i b quy n truy v n và x lý d li u REVOKE [ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | 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 } ] Quy n t o i tư ng trong CSDL Trong CSDL có m t s i tư ng và các ch c năng như sao lưu d li u mà m i ngư i s d ng trên CSDL tùy theo ch c năng và nhi m v c th ư c phép hay không ư c phép t o các i tư ng như table, view, stored procedure, … và t o CSDL. Các quy n t o các i tư ng như sau: Create Database. Create Table. Create View. Create Procedure. Create Rule. Create Default. Backup Database. Backup Log. phân quy n t o i tư ng trong CSDL cho ngư i dùng, trong SQL Server có th s d ng th t c GRANT như sau: GRANT < ALL | Statement [, … n]> TO [, … n]
- 45 Bài gi ng tóm t t H qu n tr cơ s d li u 4. Vai trò c a ngư i s d ng trong SQL Server và cơ s d li u Vai trò trên SQL Server: Vai trò (Role) Di n gi i sysadmin Có các quy n tương ương v i sa. serveradmin C u hình m t s tham s và t t server. setupadmin B gi i h n b t m t s ch c năng liên k t server và kh i ng m t s th t c. securityadmin Qu n lý ngư i dùng và t o CSDL. processadmin ư c phép d ng các giao tác ang th c hi n trên CSDL và m t s quá trình th c hi n khác c a SQL Server. dbcreator ư c phép t o CSDL. Diskadmin Qu n lý các t p tin liên quan n CSDL SQL Server. Vai trò trên CSDL: Vai trò Di n gi i V i vai trò này, ngư i s d ng (NSD) thu c nhóm s h u db_owner CSDL m i có th truy c p vào CSDL. Th c hi n các ch c năng gi ng như securityadmin. db_accessadmin NSD ư c phép select trên các b ng d li u c a các ngư i db_datareader dùng khác trong CSDL. NSD ư c phép insert, update, delete trên các b ng d li u db_datawriter c a các ngư i dùng khác trong CSDL. db_ddladmin NSD có th thêm hay ch nh s a các i tư ng c a CSDL. db_securityadmin NSD có quy n tương ương v i quy n c a securityadmin. db_backupoperator NSD có th th c hi n ch c năng backup d li u. db_denydareader Không cho phép s d ng phát bi u SELECT trên t t c các b ng d li u c a CSDL. db_denydawriter Không cho phép s d ng phát bi u INSERT, UPDATE, DELETE trên t t c các b ng d li u c a CSDL. S d ng các th t c h th ng t o m t role m i, thêm m t ngư i dùng vào m t role, lo i b ngư i s d ng ra kh i m t role. o T o m t role sp_addrole [ @rolename = ] 'role_name' [ , [ @ownername = ] 'owner' ] Trong ó:
- 46 Bài gi ng tóm t t H qu n tr cơ s d li u @rolename: tên role m i @ownername: ch s h u c a role m i, m c nh là dbo Sau khi t o role m i c n ph i gán m t s quy n truy c p và x lý trên các b ng d li u nào ó trong CSDL cho role m i ó. o Thêm ngư i s d ng vào Role sp_addrolemember [ @rolename = ] 'role_name' , [ @membername = ] ‘login_ID' o Lo i b ngư i s d ng ra kh i m t role sp_droprolemember [ @rolename = ] 'role_name' , [ @membername = ] 'login_ID ' II. B n sao d li u Ph n này s gi i thi u k thu t làm gi m lưu lư ng d li u giao d ch v i SQL Server khi ã c u hình nhi u Server trên m ng. 1. Gi i thi u v nhân b n d li u Nhân b n d li u (Replication) là công c ư c s d ng sao chép m t ho c nhi u CSDL n m t ho c nhi u server (SQL Server) khác. Các Server ư c t trong m ng máy tính n i b (LAN). Ngư i khai thác có th th c hi n truy c p n CSDL có trong Server ch a d li u ư c nhân b n. D li u gi a các máy ư c th c hi n ng b v i nhau theo l ch ho c theo s ki n, khi có yêu c u. Nhân b n d li u có nh ng ưu i m sau: − D li u ư c lưu tr nhi u nơi, hi u qu trong vi c có nhi u ng d ng cùng truy c p, khai thác. − Thích h p v i các ng d ng phân tích d li u c a h th ng x lý giao d ch tr c tuy n (OLTP) trong kho d li u (Data warehouse). − Có th khai thác d li u khi không k t n i n Server. − Gi m thi u xung t do s lư ng l n các giao d ch trên m ng. − Là m t gi i pháp an toàn khi Server b l i ho c b o dư ng. Mô hình nhân b n. D ch v nhân b n d li u g m các thành ph n cơ b n sau: Publisher, Distributor, Subscribers, Publications, Articles, Subscriptions. − Publisher: là server cung c p d li u nhân b n cho các server khác. M t publisher có th thi t l p nhi u b d li u nhân b n (g i là publication).
CÓ THỂ BẠN MUỐN DOWNLOAD
-
Bài giảng Hệ quản trị cơ sở dữ liệu Access: Chương 3,4: Một số thao tác trên CSDL/ Query
94 p | 212 | 51
-
Bài giảng Hệ quản trị cơ sở dữ liệu - Nguyễn Trường Sơn
359 p | 315 | 40
-
Bài giảng Hệ quản trị cơ sở dữ liệu Access - ĐH Phạm Văn Đồng
159 p | 112 | 17
-
Bài giảng Hệ quản trị cơ sở dữ liệu Oracle: Chương 7 - Ngô Thùy Linh
140 p | 67 | 13
-
Bài giảng Hệ quản trị cơ sở dữ liệu: Chương 4 - ĐH Công nghiệp Thực phẩm
92 p | 145 | 11
-
Bài giảng Xử lý ảnh - Chương 8: Các phép toán hình học
19 p | 51 | 7
-
Bài giảng Hệ quản trị cơ sở dữ liệu: Chương 3 - ĐH Công nghiệp Thực phẩm
218 p | 68 | 7
-
Giáo trình Hệ quản trị cơ sở dữ liệu: Phần 2
115 p | 9 | 6
-
Bài giảng Hệ quản trị cơ sở dữ liệu: Lập trình trên SQL Server - TS. Lại Hiền Phương (Phần 3)
40 p | 50 | 6
-
Đề cương chi tiết học phần Các hệ quản trị cơ sở dữ liệu (Database Management Systems - DBMS)
14 p | 43 | 5
-
Bài giảng Hệ quản trị cơ sở dữ liệu: Phần 2 - Trường ĐH Công nghiệp Quảng Ninh
89 p | 24 | 5
-
Bài giảng Hệ quản trị cơ sở dữ liệu (Database Management Systems) - Bài 6: Các hàm thường dùng
4 p | 15 | 5
-
Bài giảng Hệ quản trị cơ sở dữ liệu: Ngôn ngữ lập trình trong SQL server - Phan Hiền
30 p | 90 | 4
-
Bài giảng Hệ quản trị cơ sở dữ liệu: Chương 5.1 - Phạm Nguyên Thảo
34 p | 41 | 4
-
Bài giảng Hệ quản trị cơ sở dữ liệu: Chương 4 - Nguyễn Trường Sơn
73 p | 39 | 4
-
Bài giảng Hệ quản trị cơ sở dữ liệu MSSQL 2005: Chương 3 - Hồ Thị Anh Đào
46 p | 65 | 3
-
Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 3: Ngôn ngữ thao tác dữ liệu
15 p | 28 | 2
-
Giáo trình Hệ quản trị Cơ sở dữ liệu: Phần 1 - Nguyễn Vũ Duy
49 p | 25 | 2
Chịu trách nhiệm nội dung:
Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA
LIÊN HỆ
Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM
Hotline: 093 303 0098
Email: support@tailieu.vn