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

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

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

3、l命令行編輯器1.4 實驗步驟及結(jié)果分析1.4.1 完整性1.4.1.1 分別定義學(xué)生數(shù)據(jù)庫中各基表的主鍵、外鍵，實現(xiàn)實體完整性約束和參照完整性約束1. 首先因為在之前創(chuàng)建表的時候定義了主鍵，因此，我們需要先將所有表的主鍵撤銷掉，然后重新創(chuàng)建主鍵。Mysql語句（刪除主鍵）：alter table student drop primary key;alter table course drop primary key;alter table sc drop primary key;2. 重新創(chuàng)建主鍵Mysql語句（創(chuàng)建主鍵）：alter table student add primary ke

4、y(sno);alter table course add primary key(cno);alter table sc add primary key(sno,cno);3. 到此，我們已經(jīng)重新添加了各表的主鍵，接下來我們?yōu)镾C表添加外鍵。Mysql語句（創(chuàng)建外鍵）：alter table sc add constraint foreign key(sno) references student(sno);alter table sc add constraint foreign key(cno) references course(cno);4. 主鍵和外鍵都添加完畢，我們觀察一下目前各

5、表的結(jié)構(gòu)。Mysql語句（查看表定義）：show create table student;show create table course;show create table sc;由上面結(jié)果可知：我們成功創(chuàng)建了各表的主鍵，并成功為SC表創(chuàng)建了sno，cno兩個外鍵。1.4.1.2 分別向?qū)W生表、課程表插入具有相同學(xué)號和相同課程編號的學(xué)生數(shù)據(jù)和課程數(shù)據(jù)，驗證其實體完整性約束1. 首先查找一下student表中的學(xué)生信息，以便下面添加重復(fù)學(xué)號的學(xué)生Mysql語言： select * from student;2. 向student表中插入一個重復(fù)學(xué)號的學(xué)生，驗證完整性約束。我選擇了30203學(xué)

6、號，由上面的結(jié)果可知，該學(xué)號已經(jīng)存在。Mysql語句（添加一個新的學(xué)號為30203的學(xué)生）：insert into student value(30203,茹興康,男,1997-07-07 00:00:00,計算機,3174);上面結(jié)果顯示：添加失敗，因為sno為student表的主鍵，不能重復(fù)，驗證了其完整性約束。3. 同上，我們向course表中添加一個具有相同cno的課程。Mysql語句：select *from course;insert into course value(C01,數(shù)據(jù)庫,50,1,春);上面結(jié)果顯示：成功驗證了完成性約束。1.4.1.3 向?qū)W生選課表中插入一條數(shù)據(jù)，

7、課程編號是課程表中沒有的，驗證參照完整性約束向?qū)W生表中添加一條數(shù)據(jù)，其中課程編號在course中不存在，我選擇了C06號。Mysql語句（向?qū)W號為30203的學(xué)生選課中添加C06選課）：insert into sc values(30203,C06,100);由結(jié)果來看：插入失敗，因為course的cno是SC的外碼，C06在course中不存在，由于參照完整性約束，所以添加失敗，驗證成功。1.4.1.4 刪除學(xué)生表中的所有數(shù)據(jù)，驗證參照完整性約束刪除學(xué)生表，驗證參照完整性約束。Mysql語句：drop table student;由結(jié)果來看：由于student中的sno是sc的一個外碼，所以

8、不能刪除student，成功驗證了參照完整性約束。1.4.1.5 定義存儲過程，完成查詢某個學(xué)生的選課情況，并執(zhí)行1. 首先定義一個根據(jù)學(xué)生學(xué)號搜索的存儲過程。但是由于；是一個結(jié)束標志，因此存儲過程定義失敗，因為第一個遇到語句的；時候，該語句還沒結(jié)束導(dǎo)致；后面的語句沒有加工2. 解決上面問題，將結(jié)束符號更換為其他字符，我替換為了#，使用delimiter語句Mysql語句：delimiter #create procedure search_sc_sno(id char (6)begin select sno,cno from sc where sno = id;end#存儲過程定義成功。3.

9、 使用存儲過程查看學(xué)生選課情況。Mysql語句（查詢學(xué)號為30203的學(xué)生選課情況）：call search_sc_sno(30203)#1.4.1.6 定義觸發(fā)器，當向?qū)W生表插入新的一條記錄時，將所有學(xué)生出生日期加1；并對其進行測試1. 定義觸發(fā)器，當向?qū)W生表插入新記錄時，將所有學(xué)生出生日期+1；Mysql語句：create trigger add1_bdate before insert on student for each rowbegin update student set bdate=date_add(bdate,interval 1 day);end#2. 向?qū)W生表中插入新紀錄

10、，注意：必須插入sno無重復(fù)的新紀錄Mysql語句：insert into student value(12345,茹興康,男,1997-07-07 00:00:00,計算機,3174);但是結(jié)果顯示：在觸發(fā)器中不能更新student表的內(nèi)容。后來我在網(wǎng)上查找了一些資料：即當對該表進行插入操作時，由于觸發(fā)器要將每一行進行修改，那么剛開始插入的新紀錄可能會導(dǎo)致無限制的遞歸。因此，需要復(fù)制一個新的student表，由一個表的插入引起另一個表的觸發(fā)。3. 創(chuàng)建一個student復(fù)制表Mysql語句：create table student1 select * from student;4. 重新創(chuàng)建

11、觸發(fā)器，讓student1的插入引起student的觸發(fā)(注意，在創(chuàng)建前把之前的刪除掉)Mysql語句：create trigger add1_bdate before insert on student1 for each rowbegin update student set bdate=date_add(bdate,interval 1 day);end#之后向student1中插入新紀錄，發(fā)現(xiàn)插入成功。5. 搜索student和student1表的記錄，觀察變化從上兩個表的結(jié)果來看，我們成功引起了觸發(fā)，student的bdate都增多了一天。1.4.2 安全性1.4.2.1 定義一新的

12、登陸帳號、數(shù)據(jù)庫用戶，并授予其訪問學(xué)生數(shù)據(jù)庫的讀權(quán)限1. 在root用戶下定義一個新的用戶auserMysql語句：create user auserlocalhost identified by 072312;2. 賦予新用戶auser對student表的select和update權(quán)限Mysql語句：grant select,update on test1.student to auserlocalhost;上圖顯示：新用戶創(chuàng)建成功； 權(quán)限賦予成功。1.4.2.2 分別用root用戶和新定義的用戶訪問學(xué)生數(shù)據(jù)庫，并對其中的學(xué)生表數(shù)據(jù)進行修改1. 登錄root用戶，進行student表的查詢操

13、作，此處我查詢了30203學(xué)號的學(xué)生信息Mysql語句：Select * from student where sno =30203;使用root用戶查詢成功。2. 用root用戶修改student表的信息，此處我更改了30203學(xué)生的班級為3000；Mysql語句：Update student set classno=3000;使用root用戶修改成功。3. 登錄新創(chuàng)建的auser用戶，進行與root用戶相同的操作。Mysql語句：Select * from student where sno =30203;Update student set classno=3022;使用auser新用戶

14、查詢student成功；使用auser新用戶修改student成功。1.5 實驗總結(jié)1.5.1 實驗遇到的問題1. 問題：在添加外鍵的時候發(fā)生錯誤：不能添加外鍵解決：a. student的主鍵sno定義要和sc的外鍵sno定義需要完全一樣，長度也要一樣 b.兩個數(shù)據(jù)表定義時的內(nèi)碼也要一樣，之前因為student表的采用的是utf-8碼，而sc表采用的是latin碼，產(chǎn)生錯誤，在workbench中將sc碼改變?yōu)閡tf-8，解決了問題。2. 問題：在定義存儲過程中，遇到第一個；便結(jié)束了語句，但實際上語句還沒有結(jié)束。解決：使用delimiter語句，將結(jié)束符號改為其他符號，比如#，這樣就解決了判定問題。3. 問題：定義觸發(fā)器遇到問題，在student中定義觸發(fā)器，但是不能再student中進行觸發(fā)操作解決：創(chuàng)建一個student的復(fù)制表，在新表中定義觸發(fā)操作，觸發(fā)改變另一個表的值。4. 問題：在創(chuàng)建新的用戶，為其賦予權(quán)限過程中遇到問題。遇