北郵數(shù)據(jù)庫實驗五---數(shù)據(jù)庫完整性與安全性實驗

1、精選優(yōu)質(zhì)文檔-傾情為你奉上實驗五 數(shù)據(jù)庫完整性與安全性實驗1、實驗?zāi)康?. 通過對完整性規(guī)則的定義實現(xiàn)，熟悉了解kingbase中完整性保證的規(guī)則和實現(xiàn)方法，加深對數(shù)據(jù)完整性的理解。2. 通過對安全性相關(guān)內(nèi)容的定義，熟悉了解kingbase中安全性的內(nèi)容和實現(xiàn)方法，加深對數(shù)據(jù)庫安全性的理解2、實驗環(huán)境操作系統(tǒng)：Microsoft Windows 7旗艦版 （32位）。 數(shù)據(jù)庫版本：MySQL 6.23、實驗內(nèi)容完整性實驗：（1） 分別定義學(xué)生數(shù)據(jù)庫中各基表的主鍵、外鍵，實現(xiàn)實體完整性約束和參照完整性約束；在create table 時已經(jīng)定義了

2、各個基表的主鍵。因此，首先對每個基表刪除主鍵，再添加主鍵。實現(xiàn)實體完整性約束。如下圖。刪除主鍵：添加主鍵：sc表中的cno和sno分別是course表和student表的外關(guān)鍵字。下圖為給sc表添加student和course的外鍵，實現(xiàn)參照完整性約束。alter table sc add constraint fk_student foreign key(sno) references student(sno);alter table sc add constraint fk_course foreign key(cno) references course(cno); 主鍵和外鍵均添加成功

3、，輸入show create table 命令來查看各基表信息。（2） 分別向?qū)W生表、課程表插入具有相同學(xué)號和相同課程編號的學(xué)生數(shù)據(jù)和課程數(shù)據(jù)，驗證其實體完整性約束；l 向課程表中插入課程號為C01的課程。l 向?qū)W生表中插入學(xué)號為30201的學(xué)生。由上面兩個實驗來看，分別對student和course表插入具有相同學(xué)號和相同課程號的學(xué)生信息和課程信息，都顯示sql語句錯誤信息為：Duplicate entry * for key PRIMARY。驗證了實體完整性約束。（3） 向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號是課程表中沒有的，驗證參照完整性約束；插入成績信息是學(xué)號為31428（student

4、中存在），課程號為C07（course中不存在）的學(xué)生成績信息。受參照完整性約束，插入失敗。（4） 刪除學(xué)生表中的所有數(shù)據(jù)，驗證參照完整性約束；因為sc表中的sno是student表的外鍵，刪除學(xué)生表中的所有數(shù)據(jù)違背了參照完整性約束，所以無法刪除。（5） 定義存儲過程，完成查詢某個學(xué)生的選課情況，并執(zhí)行。本次實驗設(shè)計的存儲過程完成的是查詢某個學(xué)生的選課情況。參數(shù)指定為sno學(xué)號。存儲過程是一組為了完成特定功能的SQL 語句集，存儲在數(shù)據(jù)庫中經(jīng)過第一次編譯后再次調(diào)用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數(shù)來執(zhí)行它。因為在存儲過程中會用到結(jié)束符；，所以首先用delimiter修改結(jié)束符

5、為/，創(chuàng)建存儲過程的sql語句為：create procedure search_sc（num char（6）begin select sno，cno from sc where sno=num；end其中定義了一個讀入的參數(shù)為num，用來讀入所要查詢的學(xué)生的學(xué)號。創(chuàng)建成功。調(diào)用該存儲過程，查看學(xué)號為31428的學(xué)生的選課信息。（6） 定義觸發(fā)器，當(dāng)向?qū)W生表插入新的一條記錄時，將所有學(xué)生出生日期加1；并對其進行測試。觸發(fā)器的功能是監(jiān)視某種情況，并觸發(fā)某種操作。創(chuàng)建觸發(fā)器的語句為： create trigger update_bdateafter insert on studentfor eac

6、h row beginupdate student set bdate=bdate+1;end表示當(dāng)對student表進行插入操作后，對student表進行出生年月日加1的sql語句的執(zhí)行。向student里插入一條新紀錄，觸發(fā)觸發(fā)器update_bdate的操作，但是出現(xiàn)了下述錯誤信息：it is already used by statement which invoked this stored function/trigger。經(jīng)過了解，mysql中需要復(fù)制重建一個新的table，然后通過對舊table的操作來觸發(fā)對新table 的操作。create table new_student

7、 select * from student; /建立新表create trigger t_bdate /建立觸發(fā)器before insert on student for each row update new_student set bdate=date_add(bdate,interval 1 day)insert into student /插入查詢語句values('31011','lihua','男','1991-01-01 00:00:00','計算機','3147') 查看更新后的s

8、tudent表和new_student表如下：student表中的數(shù)據(jù)沒有發(fā)生改變，只是插入了一條新的學(xué)生信息，而new_student表中所有學(xué)生信息的出生日期都為1月2號，比student表中的日期加1，但是沒有新插入的學(xué)生信息。安全性實驗內(nèi)容（1） 定義一新的登陸帳號、數(shù)據(jù)庫用戶，并授予其訪問學(xué)生數(shù)據(jù)庫的讀權(quán)限；創(chuàng)建新的登陸賬號、數(shù)據(jù)庫用戶有兩種方式，分別為grant 授予權(quán)限時創(chuàng)建和create user。下面分別用兩種方式創(chuàng)建用戶。grant usage on *.* to 'lqy1''localhost' identified by '&#

9、39; with grant option;with grant option 是使lqy1用戶擁有授予權(quán)限的權(quán)利，是一句表示權(quán)限傳遞的語句。create user 'lqy2''localhost' identified by ''以lqy1為例，授予其訪問學(xué)生數(shù)據(jù)庫的讀權(quán)限。發(fā)現(xiàn)在user table 中并沒有找到lqy1用戶，發(fā)生這一錯誤的原因是在變更了mysql.user表之后，沒有使用FLUSH PRIVILEGES命令來更新權(quán)限表（grant tables）。flush privileges 命令本質(zhì)上的作用是將當(dāng)前user和priv

10、ilige表中的用戶信息/權(quán)限設(shè)置從mysql庫(MySQL數(shù)據(jù)庫的內(nèi)置庫)中提取到內(nèi)存里。MySQL用戶數(shù)據(jù)和權(quán)限有修改后，希望在"不重啟MySQL服務(wù)"的情況下直接生效，那么就需要執(zhí)行這個命令。通常是在修改ROOT帳號的設(shè)置后，怕重啟后無法再登錄進來，那么直接flush之后就可以看權(quán)限設(shè)置是否生效。而不必冒太大風(fēng)險。更新如下：查看user table ，發(fā)現(xiàn)lqy1和lqy2已更新。再次執(zhí)行授權(quán)語句，發(fā)現(xiàn)仍然無法找到該用戶。然后查找了關(guān)于“mysql新建用戶本地?zé)o法登錄”的資料，發(fā)現(xiàn)問題在localhost和%上。當(dāng)你試圖連接MySQL服務(wù)器時，服務(wù)器基于你的身份以及你

11、是否能通過供應(yīng)正確的密碼驗證身份來接受或拒絕連接。你的身份取決于兩個信息：一是從哪個主機連接，二是mysql用戶名。Host值'%'匹配任何主機名，因此將定義新的用戶的sql語句改為：grant usage on *.* to 'lqy1'%' identified by '' with grant option;再次執(zhí)行授權(quán)語句,授予lqy3用戶訪問學(xué)生數(shù)據(jù)庫的讀權(quán)限。（2） 分別用root用戶和新定義的用戶訪問學(xué)生數(shù)據(jù)庫，并對其中的學(xué)生表數(shù)據(jù)進行修改；root超級用戶登錄：lqy3用戶登錄：Root超級用戶查詢及修改課程信息：Lqy3

12、 用戶查詢及修改課程信息，因為lqy3用戶僅有讀權(quán)限，所以可以查詢數(shù)據(jù)庫，但是不能夠?qū)?shù)據(jù)庫中的信息進行修改。（3） 再次用此用戶訪問學(xué)生數(shù)據(jù)庫，并對其中的學(xué)生表數(shù)據(jù)進行修改。登錄root用戶，授予lqy3用戶寫權(quán)限：再次用lqy3用戶登錄，更改課程信息，成功。實驗總結(jié)1、實現(xiàn)參照完整性約束時遇到的問題最初建立外鍵時sql語句錯誤，錯誤信息是“cannot add or update a child row: a foreign key constraint fails”，原因在于：查詢優(yōu)化實驗中，對sc表插入了三個元組，sno的值為10111，而該值并沒有在student表中存在。參照完整性

13、約束的規(guī)定是：若M是關(guān)系S中的一屬性組，且M是另一關(guān)系Z的主關(guān)鍵字，則稱M為關(guān)系S對應(yīng)關(guān)系Z的外關(guān)鍵字。若M是關(guān)系S的外關(guān)鍵字，則S中每一個元組在M上的值必須是空值或是對應(yīng)關(guān)系Z中某個元組的主關(guān)鍵字值。因此，存在三個sno='10111'的元組，而student表中并不存在sno='10111'的學(xué)生信息，相當(dāng)于三個不存在的學(xué)生卻擁有成績表。這顯然是不合理的。因此只要將這三行元組刪除即可。2、定義存儲過程中遇到的問題注意要先更改結(jié)束符，因為存儲過程中的sql語句以通用的分號為結(jié)束符，其作為定義存儲過程的sql語句的一部分，會引發(fā)語句錯誤。3、定義觸發(fā)器時遇到的問題使用create trigger 語句創(chuàng)建觸發(fā)器是可以成功的，但是向student插入記錄，觸發(fā)觸發(fā)器的操作時，出現(xiàn)了循環(huán)的錯誤信息，后來將student