数据库完整性与安全性实验

.实验五 数据库完整性与安全性实验实验目的.通过对完整性规则的定义实现，熟悉了解Mysql中完整性保证的规则和实现方法，加深对数据完整性的理解。.通过对安全性相关内容的定义，熟悉了解Mysql中安全性的内容和实现方法，加深对数据库安全性的理解1.2实验内容完整性实验分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据， 验证其实体完整性约束；向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；删除学生表中的所有数据，验证参照完整性约束；定义存储过程，完成查询某个学生的选课情况，并执行。定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加 1;并对其进行测试。用sql完成以上操作。安全性实验定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限；分别用sa用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改;再次用此用户访问学生数据库，并对其中的学生表数据进行修改。用SQL语句分别完成以上内容。实验环境Window8操作系统Mysql版本数据库Mysqlworkbench可视化工具Mysql命令行编辑器实验步骤及结果分析完整性分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束1.首先因为在之前创建表的时候定义了主键，因此，我们需要先将所有表的主键撤销掉，然后重新创建主键。Mysql语句（删除主键）：altertablestudentdropprimarykey;altertablecoursedropprimarykey;altertablescdropprimarykey;altert«iblestudentdropprimaryhcviueryOK,54rousaffected<1,13sec>ecords：54Duplicates=l/arnings=BaltertAblecoursedropprinarvueUK,5rousAffected<0,81sec^ecords-SDiii)llcate£：0Uarnings：0altertabledrni)ppi皿日Key；tieryUM』142rousaffecteddEsec^ecovds：142Dunlicates：QUaFninus102.重新创建主键2.重新创建主键CB.ySsec>Warnings：0addjprlnarvl(ei/<cno?；<1-14sec?CB.ySsec>Warnings：0addjprlnarvl(ei/<cno?；<1-14sec?Warnings：0pplnari/IcejKsno;<6,83sec>Warnings：03.至iJ此，我们已经重新添加了各表的主键，接下来我们为 SC表添加外键。Mysql语句(创建外键)：altertablescaddconstraintforeignkey(sno)referencesstudent(sno);altertablescaddconstraintforeignkey(cno)referencescourse(cno);Mysql语句(创建主键)：altertablestudentaddprimarykey(sno);altertablecourseaddprimarykey(cno);altertablescaddprimarykey(sno,cno);nysql?altertablestudentaddprimarykeyCsno?QueryOK,0rousaffectedHecords：0Duplicates-0mysql?iyEql>altertablecourseQuevyOK,0rowsaffectedRecordstHDuplicates：0mysql>altertablescaddQueryOK,0ibowsaffectedRecords：0Duplicates：0nysql>altertablescAddconstraintforeignkcy<sno?referencesstudent(sno>■9uet*yOK,142rowsaffected<1.16sec)Records：142Duplicat&s：6妙骅:0^)ysQl>altertablescdddconstraintforeignkcj/<cno>referencescourse<cno>»9ueryOK,142rou£*Ffected<1.35sec)Records：142Duplicates：0Uai^nings：0

4.主键和外键都添加完毕，我们观察一下目前各表的结构。Mysql语句（查看表定义）：showcreatetablestudent;showcreatetablecourse;showcreatetablesc;ysql>shoivjcr&atetablestudent； _i !TableICreateTableistudent1CREATETABLEstudent<1sno'ua.rchai"<6>NOTNULL,'snarae'uarc}iarC6>DEFAULTNULL.‘嚣ex'varchat<2>DEFAULTNULL."bdatfeistudent1CREATETABLEstudent<1sno'ua.rchai"<6>NOTNULL,'snarae'uarc}iarC6>DEFAULTNULL.‘嚣ex'varchat<2>DEFAULTNULL."bdatfe1datetimeDEFAULTNULL,'dept'vaFthar<10>DEFAULTNULL."cla5sna1varchar<4>DEFAULTNULLPFmftRVKE?>ENGINE=InnoDBDEFAULTCHARSEI=utfSCDUi*&eGn。Gname!CBEfiTETABLE'course'<varchar<3>NOTNULL.'varchar<12>DEFAULTNULL.'Diou—intCll>DEFAULINULL,credit'intCil>DEFAULTHULL,*seraestei''varehap<2>DEFAULTNULLPRIMARVKEVCcino*>ENGlNE=lnnoDBDEFAULTCHARSEI=utf8!sc!CREATETABLE'心.<Sho'uai'Cliar<6>NOTNULL^cno'uarchapCS)MOTNULL,yM&E" DEFAULTNULUFRirARVKEV<*sno4,'cnoKEV"cna"CONSTRAINT'sc_ibfk^L'FOREIGNKEY('eno1?HEFEHENCESstudsnt1(飞nn'3CONSTRAINT'sc_ihfk_2'FOREIGNKEVC'cno'?REFERENCEScourse"<、5口’)〉ENGINE-InnoDBDEFAULTCHARSET=utf8：SC表创建了sno,cno两个外键。SC表创建了sno,cno两个外键。.首先查找一下student表中的学生信息，以便下面添加重复学号的学生Mysql语言：select*fromstudent;ijisql?seIcct*sno\snanestudent；x；bdatept

e；clas?no；302013Q2023Q20330204302B6吴袁唐吴连春1万里耳雷霏洪s斗Hnr—ijisql?seIcct*sno\snanestudent；x；bdatept

e；clas?no；302013Q2023Q20330204302B6吴袁唐吴连春1万里耳雷霏洪s斗Hnr—CDS-mb.目/目”1900-01-0219S0-01-021980-81-021980-01-021980-01-0200:00=0000：O0：WRO：00：00=H一豆_亘_亘_一9022?B223眩3凿诙Z.向student表中插入一个重复学号的学生，验证完整性约束。我选择了 30203学号，由上面的结果可知，该学号已经存在。Mysql语句（添加一个新的学号为 30203的学生）：insertintostudentvalue（ 茹jW03T,,''197-0700:00:00'计算机'，'3174'）;nysql>Insertintostudent 30203’茹兴康**'男‘19y7-07-0700：00：00f,*章机，33174，)；WRBOR1062(23000>：DuplicateentryJ30203JforkeyJIPRIMRR?J上面结果显示：添加失败，因为sno为student表的主键，不能重复，验证了其完整性约束。.同上，我们向course表中添加一个具有相同 cno的课程。Mysql语句：select*fromcourse;insertintocoursevalue（'C0傲据库'，'5匿'）；'1','n)ysql>select*froncouvse；enocnarneIhoui"!creditsemester理社成senocnarneIhoui"!creditsemester理社成s原统曲组谭强g算编皙Jo计rotisinset sec>insertIntacourse C01'/数中居库//5物,/i*♦J春")；RROR1062<2300O>=Puvlicateentr^JC61JfprWyJPRIMAEyj上面结果显示：成功验证了完成性约束。向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束向学生表中添加一条数据，其中课程编号在 course中不存在，我选择了 C06号。Mysql语句（向学号为30203的学生选课中添加C06选课）：insertintoscvalues（ '30203','C06','100'）;吧寻ql》insertintoSCvAlues<J30203*,C0&1,11U0J>；EJRROR14S2 ：CannotaddofupdAteachildt*ow：afot*eignkeyconstvaintfails lsc',CONGTHAIHTTsc_ihfk_2*FOREIGNKEV<'cnol)REFERENCES'cours8'<'cno'>>由结果来看：插入失败，因为course的cno是SC的外码，C06在course中不存在，由于参照完整性约束，所以添加失败，验证成功。删除学生表中的所有数据，验证参照完整性约束删除学生表，验证参照完整性约束。Mysql语句：droptablestudent;[i5rsql>droptablestudent；ERROR1217<236@0>：Cannotdelet«orupdateaparentt'ow：aforeignkeyccnstraiitfails由结果来看：由于student中的sno是sc的一个外码，所以不能删除student,成功验证了参照完整性约束。定义存储过程，完成查询某个学生的选课情况，并执行.首先定义一个根据学生学号搜索的存储过程。但是由于‘；’是一个结束标志，因此存储过程定义失败，因为第一个遇到语句的时候，该语句还没结束导致‘；‘后面的语句没有加工riysql>createprocedni'esearcli_sc_sno<idchai*《63》beginselectsnoAcnofromscwlieresno-id；ERR0R1064(42000>~YouhaveanerrorinSQLsyntax；checkthemanualthatcori'espondstoyourMySQLserveruet*sionfortherightsyntaxtousenear1,atline2.解决上面问题，将结束符号更换为其他字符，我替换为了 #,使用delimiter语句Mysql语句：delimiter#createproceduresearch_sc_sno(idchar(6))beginselectsno,cnofromscwheresno=id;end#deLlnitei'tiivsql>createprocedurescarch_sc_snoCidchar<6J>->beginselectsno^cnofporsc sua2id；->endltmevyOK,0rousaffected《0_2回sec)存储过程定义成功。.使用存储过程查看学生选课情况。Mysql语句(查询学号为30203的学生选课情况)callsearch_sc_sno('30203')#nysql>cellscareh_sc_srtoC,3B2B3*+ + +isno Ieno130282!C03!30晒!C04!302S3IC05ImusIn苕e*(0.00谷6心》ueryOK-6row5affected<0.01scc>1.4.1.6定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加 1;并对其进行测试1.定义触发器，当向学生表插入新记录时，将所有学生出生日期 +1;Mysql语句：createtriggeradd1_bdatebeforeinsertonstudentforeachrowbeginupdatestudentsetbdate=date_add(bdate,interval1day);end# niyeql>createtplggepaddljbdate>beforeinsertonstudent>Foi*each好口。begin―yupdatee七uHcn七sethdate=idate^adtKhdatc,irTtenrual1day)；>endttQiwt'yOK.8rowseffected<0.12.向学生表中插入新纪录，注意：必须插入 sno无重复的新纪录Mysql语句：insertintostudentvalue（ 茹^234眦，：'-09970700:00:00计算机'，'3174'）;T*ysql>insertintostudentvalueC*12345'/茹兴康I,男1T997一a7-目7典士的二施，」计|直机「3174”,ERROR1442<HV00O>：Can"Cupdatetable^student1instoredfunction/triggerbeeajseitisalreadyusedbystatenenttdiickinvokedthisstoredfunction/crigger,但是结果显示：在触发器中不能更新 student表的内容。后来我在网上查找了一些资料：即当对该表进行插入操作时，由于触发器要将每一行进行修改，那么刚开始插入的新纪录可能会导致无限制的递归。因此，需要复制一个新的 student表，由一个表的插入引起另一个表的触发。.创建一个student复制表Mysql语句：createtablestudent1select*fromstudent;treAtetabLestudentlselect* 令Uer-iP0K„54rausaf£ected《0-66sec>co：54Duplicate?=0Uarn:0.重新创建触发器，让studentl的插入引起student的触发（注意，在创建前把之前的删除掉）Mysql语句：createtriggeradd1_bdatebeforeinsertonstudentlforeachrowbeginupdatestudentsetbdate=date_add（bdate,interval1day）;end#之后向studentl中插入新纪录，发现插入成功。mysql〉createtriggeraddl_bdate一》afterinsertonstudentl一AForeachpowheqin一〉updateStudentsetbdate-date_addtbdate,intbi'UfiL1day）：—>endltQueryOK,3rausaffectedCQ_09sec^ |insertintostudentlu日lueC12345’,‘茹兴康',‘男'''1，？7一叼7一（37 rJ讦算机「3174”林QueryOK.*1rowaffected（0.24sec>5.搜索student和studentl表的记录，观察变化nysql>select*fpon&tudentlJtisno'占nwme'sex' 1dept'classnoi!30291:302破!30203：302S4!302%：3S2Q7;30208!30291:302破!30203：302S4!302%：3S2Q7;30208春炽些--石塞耳雷霏洪金广吴寺唐吴连奈-男男男男男里用!19S0-01-02!19S0-01-02!19fi0-01-02r19B0-01-02i19S0-01-02；17B0-01-02：l?S0-01-02QI0:豳:0Q00=00=0000：00：09HU：00：0000:00：0000:08:00.三豆一亘=s=s-星一曰上下l.aj#hJLaKaz3022302290223&2Z302230223022)</sq1>seled*fr-DFist：udent：tt*sno*wnanwIsex：bdac& idepticlassnoi:392U1!3W2目2：302H3:392U1!3W2目2：302H3!30204!202^6：30207；302BR春炽应子石望星田霏洪金广晶黄B:昊连壬苏1980-01-021?80-01-031?80-01-031980-01-031?60-01-031780-01-0300：00：0000：00:0000:00：0000:00：0000:00：00@0:00:00目附二旧月；00信信信信信信1122a322032203从上两个表的结果来看，我们成功引起了触发， student的bdate都增多了一天。1.4.2安全性定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限.在root用户下定义一个新的用户 auserMysql语句：createuser'auser'@localhost'identifiedby'072312'.赋予新用户auser对student表的select和update权限Mysql语句：grantselect,updateonto'auser'@localhost'；向.『1>ci*eAteu.£BPJau.£ei*J(?Jloc^Lhost'identifiedby-0723121；lluei^yOK,Uf-dusafFectedL0.14sec)行«1>j广0nt?c1cctju.pd.4tgon1.sttto (?Jloc«ilhQ?tJriuer-y0%0r-owsaffected<0.14sec)上图显示：新用户创建成功;权限赋予成功。1.4.2.2分别用root用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改1.使用root用户查询成功。登录1.使用root用户查询成功。登录root用户，进行student表的查询操作，此处我查询了 30203学号的学生信息Mysql语句：Select*fromstudentwheresno=30203;2.用root用户修改student表的信息，此处我更改了 30203学生的班级为3000;Mysql语句：Updatestudentsetclassno='3000'；updatestudentsetelaS5no=J;juevy54musaffected<0_07sec^Rovismatched=54Changed：S4Uarnings'0TOC\o"1-5"\h\zn'wql'心七HfranstudentulieFe£no=30203i+ h h B- F ——1~ T!sno!snane!sex'hdate !deptIcl^&sno!f ■+ ■+ + 4- -4!30203!唐雷I男I19S0-01-02%二0机的!电信I30的 !+ h h B- F —— *Irowanset<0.00sen?使用root用户修改成功。

Updatestudentsetclassno=3.登录新创建的auser用户，进行与root用户相同的操作。Mysql语句：Select