Tham khảo tài liệu 'tin học hệ quản trị - toán tin dữ liệu 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ả
AMBIENT/
Chủ đề:
Nội dung Text: Tin Học Hệ Quản Trị - Toán Tin 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).
Thêm vào BST
Báo xấu
Download
Vui lòng tải xuống để xem tài liệu đầy đủ
