數(shù)據(jù)庫完整性與安全性試驗

1、1.實驗五數(shù)據(jù)庫完整性與安全性實驗1.1實驗?zāi)康?. 通過對完整性規(guī)則的定義實現(xiàn)，熟悉了解 數(shù)據(jù)完整性的理解。2. 通過對安全性相關(guān)內(nèi)容的定義，熟悉了解 庫安全性的理解Mysql中完整性保證的規(guī)則和實現(xiàn)方法，加深對Mysql中安全性的內(nèi)容和實現(xiàn)方法，加深對數(shù)據(jù)1.2實驗內(nèi)容1.2.1完整性實驗(1) 分別定義學生數(shù)據(jù)庫中各基表的主鍵、外鍵，實現(xiàn)實體完整性約束和參照完整性約束；(2) 分別向?qū)W生表、課程表插入具有相同學號和相同課程編號的學生數(shù)據(jù)和課程數(shù)據(jù)，驗證 其實體完整性約束；(3) 向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號是課程表中沒有的，驗證參照完整性約束；(4) 刪除學生表中的所有數(shù)據(jù)，驗證

2、參照完整性約束；(5) 定義存儲過程，完成查詢某個學生的選課情況，并執(zhí)行。(6) 定義觸發(fā)器，當向?qū)W生表插入新的一條記錄時，將所有學生出生日期加1 ;并對其進行 測試。(7) 用sql完成以上操作。1.2.2安全性實驗(1) 定義一新的登陸帳號、數(shù)據(jù)庫用戶，并授予其訪問學生數(shù)據(jù)庫的讀權(quán)限；(2) 分別用sa用戶和新定義的用戶訪問學生數(shù)據(jù)庫，并對其中的學生表數(shù)據(jù)進行修改;(3) 再次用此用戶訪問學生數(shù)據(jù)庫，并對其中的學生表數(shù)據(jù)進行修改。(4) 用SQL語句分別完成以上內(nèi)容。1.3實驗環(huán)境Window8操作系統(tǒng)Mysql 8.0版本數(shù)據(jù)庫Mysql workbench 8.0 可視化工具Mysql

3、命令行編輯器alter tible student drop primary hcyi tiery OK* 54 rous affected alter table course drop prinary ke； liery OK, EAffected 0,81 secyecids - Duffllcates - 0 Varfiinss: 0alter tble sc tlrDf) pvimarsi ；lierv CKi 142 rows affected iysql alter tetJble course MGry OK, S itqvs effected iecords； 0 Dupli

4、cates: 0iyaql alter table add iuery OK, 0 vqus affected :ecords： 0 Duplicates: 0add prinsLiy lei;cno J ； ； alter table sc add constraint foreign kcy； 9uet*y OK, 142 rows affected alter table sc ddd constraint foreign kcj/(cno references course； Query OK, 142 rou *Ffecte d Records : 142 Duplicates: 0

5、ings: 04.主鍵和外鍵都添加完畢，我們觀察一下目前各表的結(jié)構(gòu)。 Mysql語句（查看表定義）：show create table stude nt;show create table course;show create table sc;5hoiu ei*&ate table student ；I- -1 T ab le!TableLa_ _ ,T*-1 studiBn t i CREftTE TABLE 1 st；Lide；n1;1 1 sno ua.rchai NOT NULL, lsnarae uarciarC6 DEFAULT NULL, sx u&pcuifC2 DEFAUL

6、T NULL, bdatu datetime DEFAULT HULL# dept uhar DEFAULI NULL., clagsna1 varchap DEPHULT NULL/ FHrnftRV KE? UQ)ENGINE=InnoDB DEFAULT CHARSET=utfSc&ufse CPEftTE TABLE cou*se ( eno1 varGhar NOT HULL, Gnane1 vftrGJiar DEFAULT NULL, intCLl) DEFAULT HULL,* Szefliester1DEFAULT NULL.PR MAR? KEV CcnoiEHGINE=I

7、nnoDB DEFAULT CHAHSEI=utf81 sc! CREATE TABLE V sno uarcliar NOT NU1L, cna uarcliapC3 MOT NU1L, lsde lnt DEFAULT NULU PRIHARV KEV , KEV “的十CONSTRAINT sc_ibfk_L FOREIGN KEY C CONST BAIMT Ec_ibFk_2 TOREIGN KEV . cnol REFERENCES course lcct * from student;t classno Isno i sn insert into student uhIheC30

8、玄03茹興康.男” 19今/-B1? 00:00:00* 算機J 3174，”ERROR 1062 : Duplicate entr 3B2031 for key J,PIMABy,上面結(jié)果顯示：添加失敗，因為sno為student表的主鍵，不能重復(fù)，驗證了其完整性約束。 同上，我們向course表中添加一個具有相同eno的課程。Mysql語句：select *from course;insert into course value（ C01數(shù)據(jù)庫，50 春 ”）；1,；eno cnarne! C01! C02! C03! CQ4! C05編譯厘理*:W;! JAUA程序誼計i !計算機組成原

9、理!lliDiii! credit!semester !+ V 4 *3!秋!2I5!2丨軟!念I(lǐng)秋i2!5151514030select * fron course:rous in setsecinsert into course0 數(shù)扌居岸.0春；RROR 1K2 = Vuvlicte unWy JC0J for ley J PRIMARYJ上面結(jié)果顯示：成功驗證了完成性約束。1.4.1.3向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號是課程表中沒有的，驗證參照完整性約 束向?qū)W生表中添加一條數(shù)據(jù)，其中課程編號在course中不存在，我選擇了 C06號。Mysql語句（向?qū)W號為 30203的學生選課中

10、添加 C06選課）：insert into sc values（ 30203 , C06 , 100 ）;insert in七o C ua丄ues ；ERROR 1452: Cannot add or updAt e a child t*ow： a f ot*eign key conatiaint failsCOHSTHAIHI lsc_ihfk_2* FOREIGN KEV Ono1) REFERENCES rcoursb 1 由結(jié)果來看：插入失敗，因為course的cno是SC的外碼，C06在course中不存在，由于參照完整性約束，所以添加失敗，驗證成功。1.4.1.4刪除學生表中的所有

11、數(shù)據(jù)，驗證參照完整性約束刪除學生表，驗證參照完整性約束。Mysql 語句：drop table student;hvsq1 drop cable student；ERROR 1217 : Cannot delet or update a parenta foreigrt key constraiit fails由結(jié)果來看：由于 student中的sno是sc的一個外碼，所以不能刪除student,成功驗證了參照完整性約束。3141.5定義存儲過程，完成查詢某個學生的選課情況，并執(zhí)行1.首先定義一個根據(jù)學生學號搜索的存儲過程。但是由于；是一個結(jié)束標志，因此存儲過程定義失敗，因為第一個遇到語句的;

12、 時候，該語句還沒結(jié)束導致；后面的語句沒有加工mysql create procedure seapcli_sc_5no - Vou have aninSQL syntax i check the nanual thatcorresponds to (/our M 卯 gQL seruei* vers ion f op t Jie right syntax to use near 1 At line 2isql de Llnitei tt ysql create 衛(wèi)護dee Hue唱 search_sc_snoCid chai- begin select sn4,cnQ ran sc h&re

13、 should; - endlt!itery OK, 0 raus affected C020 sec)1.4.1.6定義觸發(fā)器，當向?qū)W生表插入新的一條記錄時，將所有學生出生日期加1 ;并對其進行測試1.定義觸發(fā)器，當向?qū)W生表插入新記錄時，將所有學生出生日期+1 ；Mysql語句：create trigger add1_bdate before insert on stude nt for each rowbeg in update stude nt set bdate=date add(bdate,i nterval 1 dav):e nd#2.解決上面問題，將結(jié)束符號更換為其他字符，我替換

14、為了#，使用delimiter語句Mysql語句：delimiter #create procedure search_sc_s no(id char (6)begi n select sno,cno from sc where sno = id： end#存儲過程定義成功。使用存儲過程查看學生選課情況。Mysql語句(查詢學號為 30203的學生選課情況)call search sc sno( 30203 )#nysql caIIh_sc_rioiyeal create tpiggei* addl_ljdate- befove insept on student Fo* each hckj

15、beginupd耳上巳 student set bdate=dat e jaddODdate , irrteTvl 1 da.y J - endttJwcry OK. 0 rows effected insert into student valueC * 12345 J -f SBS * * * 1997-07-07 30:00： 001 , ifl 童機,3174, 3XERROR 1442 ： CanC update table student1 in stored function/trigger beea jse it is already used h# statenent vJi

16、ich invoked this stored function/crigger,但是結(jié)果顯示：在觸發(fā)器中不能更新student表的內(nèi)容。后來我在網(wǎng)上查找了一些資料：即當對該表進行插入操作時，由于觸發(fā)器要將每一行進行 修改，那么剛開始插入的新紀錄可能會導致無限制的遞歸。因此，需要復(fù)制一個新的student表,由一個表的插入引起另一個表的觸發(fā)。3. 創(chuàng)建一個student復(fù)制表Mysql語句：create table stude nt1 select * from stude nt;ystreJite tabLe studentl select*-tudent,!u.erif OKP 54 ro

17、u：s aFfectedseciecor-d: 54 Duplicate? = 0 Uamings : 04.重新創(chuàng)建觸發(fā)器，讓student1的插入引起student的觸發(fā)(注意，在創(chuàng)建前把之前的刪除掉)Mysql語句：create trigger add1_bdate before insert on stude nt1 for each row begi n update stude nt set bdate=date_add(bdate,i nterval 1 day);e nd#之后向student1中插入新紀錄，發(fā)現(xiàn)插入成功。create trigger addl_bdate- a

18、fter insert on studentl fof eAch pow begin一Update Student Set bdftte -daIntEirufii. 1卯； endltQuery 0K 0 raus affected C0-09 ?ec|mysql insert into studcntl value select * fpon &tudentlJtsex1 deptclassno i302913020230283 3S204302% 3B20730200春 熾黃- 石聖胃雷霏洪生廣 罠哀B吳連盍-男男男男男里男! 19S0-01-02! 19S9-01-02! 19fi0-

19、01-02 r 19B0-01-02i 19S0-01-02i 17B0-01-02:l?S0-01-0200:00:09 I00=00=00 1 B0：0Q = ( !B0：00：09 ； (j0:QQ:0Q ! 00=00:00 i B0：08：oe I.亠皀口蘭亠一一口亠=口亠-一呈一呈一日! 9022! 3022I 3022；3&22! 3022i 3022:3022) seled * f r-DFi st：udent：tt* sno * sna.nie i sex : bdac&pte392013Q20230203302042020t 30207 302BR春 熾廷于 石s冃雷霏洪金

20、廣 S艮f:壬連壬蘇 P m-BrJTTlnTlBn._l?80-01-0i 1980-01-02 1?80-01-03 1?80-01-03 1980-01-03 1?&0-01-03 1780-01-0300 00 一00亠_吏一_曰亠一H-a=呈e_p從上兩個表的結(jié)果來看，我們成功引起了觸發(fā)，student的bdate都增多了一天。1.4.2安全性1.4.2.1定義一新的登陸帳號、數(shù)據(jù)庫用戶，并授予其訪問學生數(shù)據(jù)庫的讀權(quán)限1. 在root用戶下定義一個新的用戶auserMysql語句：create user auser localhost identified by 072312 ；2.

21、 賦予新用戶 auser對student表的select和update權(quán)限Mysql語句：grant select,update on test1.student to auser localhost ;nys(jl ci*eat e u.sep au.sei* (? Loc.Lhos t idlent if icd. h* 0723121 : Sueiy OK, U f-oM affecte1 0.14 aec)grant selcct j u-pdtc on1to J ftuscr1 (?J Locilho?tJ rSuery OK, 0 roW5 affcctcd mysql u mot

22、 p inter password：加*leLcaime： Ibo 七mon ConinbaLndB end.； op Sgfour MySQL connect ion id is 14erver version： 8.0.11 MySQL Comunit Server - GPLright 201S Oracle and/or its affiliatesH All eights reserved.Oracle a registered tpdemarh of Ofac le Corm ration *nd/oi* its ,ff iliites . Other nAmes may be t

23、i*adenai*k of the if repectiue uii ers ”Type J help；1 or 1 iJ for lie Ip. Type： J cf to c Tear tFie cur re nt i.npiit; statement.ysql use testl database cFlanged iErql celecl: * Ffoe ctujidenlr whei*e &no=3B20?7i dept ! c lasno i sno : wname ； sex : bdateI 30203 :唐雷 1 男:1980-01-02 0:00:00 J 電信 i 3022row in set Sous notched = 54 Chon日54 Wrn insrs : Qnysql? select * from student uFere no =30203;+HHh1fr1-snane ； sex : l)date； devt : clasno ；sno! 30203 ! fg雷 !男 ! 1980-01-02 00:00:68 !電信! 30QO1 row in set 使用root用戶修改成功