Tin Học Quản Trị - Hệ Cơ Sở Dữ Liệu phần 4

Chia sẻ: Qwdqwgferhrt Verbnrtjheth | Ngày: | Loại File: PDF | Số trang:12

Thêm vào BST

Báo xấu

74
lượt xem 4
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Khi nhiều người cùng truy nhập CSDL với các mục đích khác nhau. Rất có t hể sẽ xảy ra hiện tượng tranh chấp dữ liệu. Cần có cơ chết ưu tiên khi truy cập CSDL. Ví dụ: admin luôn có thể tru cập cơ sở dữ liệu.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Tin Học Quản Trị - Hệ Cơ Sở Dữ Liệu 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).