数据库的完整性和安全性实验报告

信息工程学院实验报告课程名称：《数据库原理》实验项目名称：数据库的完整性和安全性一、实验目的:（1）掌握数据库约束的概念；（2）熟悉SQLSERVER的完整性约束技术。（3）了解SQLSERVER的违反完整性处理措施。（4）了解登录账户的管理理念与具体方法。（5）了解数据库用户的管理的要则。（6）了解用户权限管理的内涵与方法。二、实验设备与器件Win7+Sqlserver2008三、实验内容与步骤（一）测试完整性运行附录中的SQL语句，理解SQL语句中包含的完整性定义。然后执行下面的SQL语句，看是否能正常运行，若无法执行，请说明原因。1.对dept表进行数据增删改，并检查完整性规则Dept已存在的完整性规则如下：dnoCHAR(2)PRIMARYKEYdnameVARCHAR(20)NOTNULL,UNIQUE(1)增加数据INSERTINTOdeptVALUES('D1','计科系');----正常插入INSERTINTOdeptVALUES('D2','电信系');----正常插入INSERTINTOdeptVALUES(NULL,'机械系');----违反dno主键（NOTNULL）规则INSERTINTOdeptVALUES('D2','机械系');----违反dno主键（UNIQUE）INSERTINTOdeptVALUES('D3',NULL);----违反dname的NOTNULL规则INSERTINTOdeptVALUES('D3','计科系');----违反dname的UNIQUE规则INSERTINTOdeptVALUES('D3','机械系');----正常插入成绩：指导老师(签名)：(2)删除数据DELETEFROMdeptWHEREdno='D3';----正常删除(3)修改数据UPDATEdeptSETdname='计算机科学系'WHEREdno='D1';----正常修改UPDATEdeptSETdname='电信系'WHEREdno='D1';----违反dname的UNIQUE规则UPDATEdeptSETdname=NULLWHEREdno='D1';违反dname的UNIQUE规则2.对student表进行数据增删改，并检查完整性规则Student已存在完整性规则如下：snoCHAR(2)PRIMARYKEYsnameVARCHAR(20)NOTNULL,ssexCHAR(2)NOTNULL,CHECK(ssexin('男','女'))sageINTNOTNULL,dnoCHAR(2)NOTNULL,FOREIGNKEYREFERENCESdept(dno)ONDELETECASCADEONUPDATECASCADE(1)增加数据INSERTINTOstudentVALUES('S1','张刚','男',20,'D1');----正常插入INSERTINTOstudentVALUES('S2','李梅','女',21,'D2');----正常插入INSERTINTOstudentVALUES('S2','吴敏','男',20,'D1');----正常插入INSERTINTOstudentVALUES(NULL,'吴敏','男',20,'D1');----违反sno的主键（NOTNULL）规则INSERTINTOstudentVALUES('S3','吴敏','男',NULL,'D1');违反sage的主键（NOTNULL）规则INSERTINTOstudentVALUES('S3','吴敏','M',20,'D1');违反ssex的CHECK规则INSERTINTOstudentVALUES('S3','吴敏','男',20,'D3');----正常插入INSERTINTOstudentVALUES('S3','吴敏','男',20,'D1');----正常插入(2)删除数据DELETEFROMstudentWHEREsno='S3';----正常删除(3)修改数据UPDATEstudentSETsname='赵强',dno='D2'WHEREsno='S1'-----正常修改UPDATEstudentSETssex='F'WHEREsno='S1';-----违反ssex的CHECK规则UPDATEstudentSETsno='S2'WHEREsno='S1';-----违反sno的主键（UNIQUE）规则UPDATEstudentSETdno='D3'WHEREsno='S1';违反dno的外键规则UPDATEdeptSETdno='D3'WHEREdno='D1';----检查dno的外键ONUPDATE规则，观察运行后效果DELETEFROMdeptWHEREdno='D2';----检查dno的外键ONDELETE规则，观察运行后效果（二）使用规则实现数据完整性（1）在查询分析器中，利用命令（CREATERULE），创建一个关于年龄(sage)约束的规则，将“sage”列的值约束在0~200之间；然后将所创建的规则绑定到“sage”列（提示：用命令Sp_bindrule）。CREATERULErule_sageas@sagebetween1and100;Sp_bindrule'rule_sage','student.sage';（2）在查询分析器中，利用命令（CREATERULE），创建一个关于学分(credit)约束的规则，将“credit”列的值约束在1~8之间的整数；然后将所创建的规则绑定到“credit”列（提示：用命令Sp_bindrule）。CREATERULErule_creditas@creditbetween1and8;Sp_bindrule'rule_credit','course.credit';（3）在企业管理器中，创建一个关于成绩(grade)约束的规则，将“grade”列的值约束在0~100以内；然后将所创建的规则绑定到“grade”列。CREATERULErule_gradeas@gradebetween1and100;Sp_bindrule'rule_grade','study.grade';（三）使用触发器实现数据完整性(选做)（1）在查询分析器中，输入以下CREATETRIGGER语句，创建以下触发器：为student表创建一个INSERT触发器，当插入的新行中年龄的值不是0~180时，就激活该触发器，撤销该插入操作，并使用RAISERROR语句返回一个错误信息。（2）在查询分析器中，执行INSERT语句为student表添加一年龄为220的学生，此时触发器是否执行？分析原因。（四）测试数据库安全性设置身份验证模式:右键服务器实例，选择属性，在Security(安全性)项中，将服务器身份验证设置为SQLServer和Windows身份验证模式，确定。根据提示重新启动sql服务。然后执行下面的语句启用sa用户。sp_password[[@old=]'old_password',]{[@new=]'new_password'}[,[@loginame=]'login']EXECsp_passwordnull,db123456,'sa'ALTERLOGINsaENABLE1.建立登录、授予登录的服务器角色。(1)建立和删除登录打开企业管理器和查询分析器，以超级用户sa连接SQLServer服务器，建立SQLServer登录，登录名为loginsql,并设置口令为123456。sp_addlogin'loginsql','123456'另外打开SQLServerManagementStudio，以loginsql登录连接服务器，测试服务器权限。试运行CREATEDATABASEtest，能否成功？(2)以sa登陆查询分析器，授予loginsql登录的服务器角色——'dbcreator',sp_addsrvrolemember'loginsql','dbcreator'然后以loginsql登录查询分析器，并运行CREATEDATABASEtest，能否成功？2.创建用户、授予用户的权限。(1)以sa登陆查询分析器，建立数据库。CREATEDATABASEdbstudentgoUSEdbstudentgoCREATETABLEtbstudent(snoint,snamevarchar(10));以loginsql登录查询分析器，测试能否使用数据库？(2)以sa登陆查询分析器，创建用户，授予用户的权限sp_adduser'loginsql','usera'以loginsql登录另一个查询分析器，进入数据库dbstudent(usera用户)，测试权限。可以使用数据库dbstudent①sa授予用户语句权限GRANTCREATETABLETOusera②授予用户的对象权限GRANTSELECTONtbstudentTOuseraGRANTINSERTONtbstudentTOusera再次以loginsql登录进入数据库dbstudent(usera用户)，测试被授予权限。3.创建数据库角色，授予数据库角色的权限。(1)sa进入数据库，创建数据库角色USEdbstudentgosp_addrole'rolea'(2)授予数据库角色的权限GRANTCREATETABLETOroleaGRANTSELECT,INSERT,deleteONtbstudentTOrolea(3)授予用户的数据库角色sp_addrolemember'rolea','usera'，测试loginsql登录是否具有update权限insertintotbstudentvalues('103','jake')updatetbstudentsetsname='tom'wheresno='103'不具有update权限以sa登录运行语句sp_addrolemember'db_datawriter','usera'，再次测验loginsql的update权限。4.回收数据库角色、用户、登录的权限，删除数据库角色、用户、登录。①回收用户的数据库角色sp_droprolemember'rolea','usera'sp_droprolemember'db_datawriter','usera'②回收数据角色权限REVOKECREATETABLEFROMroleaREVOKESELECT,INSERTONtbstudentFROMrolea③删除数据库角色sp_droprole'rolea'④回收用户的对象权限REVOKESELECTONtbstudentFROMuseraREVOKEINSERTONtbstudentFROMusera⑤回收用户的语句权限REVOKECREATETABLEFROMusera⑥删除用户sp_dropuser'usera'⑦回收登录的服务器角色sp_dropsrvrolemember'loginsql','dbcreator'⑧删除SQLServer登录Dropdatabasetestsp_droplogin'loginsql'四、实验总结：我在此过程中不但应用了所学的知识，而且还不断的学习新的知识、工具，以完成设计的需要