华南理工大学-计算机学院-数据库-实验二-报告

华南理工大学《数据库》课程实验报告实验题目：实验二：数据库的安全和完整性约束姓名：学号：班级：17计科一班组别：无合作者：无指导教师：董守玲实验概述【实验目的及要求】实验目的：通过创建视图、触发器，设计安全机制等方式掌握数据库的安全和完整性的设计。实验要求：1．采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。2）创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。2.在数据库中创建以下触发器：1）Upd_Credit要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。2）Upd_StuView（Insteadof触发器）要求：当对视图Student_Grade作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作。如：当执行InsertintoStudent_Gradevalues(‘王刚’，’数据库’，54)则触发器完成另一插入操作：InsertintoSCvalues(‘980201’，’CS-110’，54)另外，需要检查当前插入的学生和课程是否已在Students，和Courses表中存在，如不存在，不执行任何操作，并提示用户错误信息。3）PK_SC,FK_SC_SNO,FK_SC_CNO)（选做）要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键（SNO，CNO）和外键SNO，CNO的约束定义。3．为Student数据库设计安全机制。要求：在该数据库系统中，有三类用户：1)学生，权限包括：查询所有的课程信息，根据学号和课程号来查询成绩。但不允许修改任何数据。(必做)只能查询自己的成绩，不能查询别人的成绩。（选做）2）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。3）教务员：权限包括：查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象。要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。【实验环境】PC机，WINDOWS操作系统，Oracle或MicrosoftSQLServer数据库实验内容【实验过程】一、实验步骤：1.登录数据库，使用实验相关数据库2.完成触发器部分：（1）新增数据库并计算插入相关数据（需要辅助表）（2）创建视图（3）在表的插入过程中建立触发器（4）在视图的插入过程中创建触发器3.完成安全性部分：（1）完成三个用户的创建，并测试初始权限（2）授予学生权限，学生重新登录，测试相关权限有无（3）授予老师权限，老师重新登录，测试相关权限有无（4）教务员学生权限，教务员重新登录，测试相关权限有无二、实验数据：实验一使用的数据库。三、实验主要过程：1．采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。Step1创建表新增表Credits(SNO,SumCredit,NoPass)代码：createtableCredits(SNOvarchar(15),SumCreditdecimal(5,1),NoPasssmallint);Step2创建辅助表tmpSumCredit(SNOvarchar(15),SumCreditdecimal(5,1))两个代码：createtabletmpSumCredit(SNOvarchar(15),SumCreditdecimal(5,1));createtabletmpNoPass(SNOvarchar(15),NoPasssmallint);Step3向辅助表插入数据tmpSumCredittmpNoPass代码：insertintotmpSumCredit(SNO,SumCredit)selectSC.SNO,sum(CREDIT)fromCourses,SCwhereSC.CNO=Courses.CNOgroupbySNO;insertintotmpNoPass(SNO,NoPass)selectSC.SNO,sum(CASEWHENGRADE60THEN1ELSE0END)fromCourses,SCwhereSC.CNO=Courses.CNOgroupbySNO;Step4向Credit插入数据（通过辅助表）代码：insertintoCredits(SNO,SumCredit,NoPass)selecttmpSumCredit.SNO,tmpSumCredit.SumCredit,tmpNoPass.NoPassfromtmpSumCredit,tmpNoPasswheretmpSumCredit.SNO=tmpNoPass.SNO;2）创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。创建视图结果代码：createviewStudent_Grade(Sname,Cname,Grade)asselectSC.SNO,SNAME,GRADEfromStudents,Courses,SCwhereStudents.SNO=SC.SNOandCourses.CNO=SC.CNO;2.在数据库中创建以下触发器：1）Upd_Credit要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。Step1:创建触发器,插入数据，并查看Credits表代码：delimiter//creat[e-mail]=CNO;updateCreditssetSumCredit=SumCredit+@Credit,NoPass=NoPass+(casewhennew.GRADE60then1else0end)whereSNO=new.SNO;end//Step2:结果（Credits前后对比，在插入’201592450588’’EE1’59后）代码：insertintoSCvalues('201592450588','EE1',59);2）Upd_StuView（Insteadof触发器）要求：当对视图Student_Grade作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作。如：当执行InsertintoStudent_Gradevalues(‘王刚’，’数据库’，54)则触发器完成另一插入操作：InsertintoSCvalues(‘980201’，’CS-110’，54)另外，需要检查当前插入的学生和课程是否已在Students，和Courses表中存在，如不存在，不执行任何操作，并提示用户错误信息。Step1:触发器的创建得知mysql的DBMS无法在视图上建立触发器，后改用存储过程。代码：delimiter//createprocedureUpd_StuView(newSNAMEvarchar(5),newCNAMEvarchar(10),newGRADEsmallint)begindeclaretmpSNOvarchar(15);declaretmpCNOvarchar(10);casewhenexists(select*fromStudentswhereSNAME=newSNAME)andexists(select*fromCourseswhereCNAME=newCNAME)thenselectSNOintotmpSNOfromStudentswherenewSNAME=SNAME;selectCNOintotmpCNOfromCourseswherenewCNAME=CNAME;insertintoSCvalues(tmpSNO,tmpCNO,newGRADE);endcase;end//Step2:插入数据代码：callUpd_StuView('王刚','数据库',54);callUpd_StuView('张嘉奥','数字电路',59);Step3:查询结果（根据成绩反向查找，由上图的查询条件）3）PK_SC,FK_SC_SNO,FK_SC_CNO)（选做）要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键（SNO，CNO）和外键SNO，CNO的约束定义。在存储过程内，使用约束性命名子句进行实体完整性和参照完整性约束代码：delimiter//createprocedureCons_Def()beginaltertableSCaddconstraintC1primarykey(SNO,CNO);altertableSCaddconstraintC2foreignkey(SNO)referencesStudents(SNO);altertableSCaddconstraintC3foreignkey(CNO)referencesCourses(CNO);end//3．为Student数据库设计安全机制。要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。要求：在该数据库系统中，有三类用户：Step1:创建学生，老师，教务员三个用户代码：createuser`teacher`@`%`identifiedby'123456';createuser`student`@`%`identifiedby'123456';createuser`dean`@`%`identifiedby'123456';Step2:在授权之前，相应的用户没有权限（举例访问Students数据库）(1)Student(第一行可看出的登录用户，最后报错可看出无访问权限)(2)Teacher(第一行可看出的登录用户，最后报错可看出无访问权限)(3)Dean(第一行可看出的登录用户，最后报错可看出无访问权限)1)学生，权限包括：查询所有的课程信息，根据学号和课程号来查询成绩。但不允许修改任何数据。(必做)Step1:授予学生权限：代码：Step2:学生拥有权限（第一行为用户登录，最后查询有结果，有权限）grantselectonlab.Coursestostudent;grantselect(GRADE)onlab.SCtostudent;3）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。Step1:授予老师权限代码：grantselectonlab.Studentstoteacher;grantselectonlab.Coursestoteacher;grantselect(GRADE)onlab.SCtoteacher;Step2:老师拥有权限（1.重新登录，2.可以查询相关内容，3.但不能进行修改）(1).(2).(3).4）教务员：权限包括：查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象。Step1:授予教务员权限(分别读写两个表，四个权限)代码：grantselectonlab.Coursestodean;grantselectonlab.Studentstodean;grantupdateonlab.Coursestodean;grantupdateonlab.Studentstodean;Step2:教务员拥有权限（