数据库系统实验报告-实验三-数据控制(安全性)

《数据库系统概论》实验报告题目：实验三数据控制（安全性）班级姓名学号日期2014年6月6日一、实验目的1.通过SQL对数据库进行安全性控制2.完成教材中例题的上机练习二、实验平台微软SQLServer2000或以上版本。对于SQLServer2000，使用查询分析器运行SQL语句，对于SQLServer2005以上的版本，使用SQLServerManagementStudio运行SQL语句。三、实验内容和要求使用SQL对数据进行安全性控制，包括授权和权力回收。操作完成后，查看授权用户是否真正具有所授予的数据操作权利，在权力回收操作之后，用户是否确实丧失了所回收的数据操作权力。在前几次实验中已经建立了实验数据库，继续在此数据库上进行实验所要求的各项操作。认真填写实验报告，记录所有的实验用例。四、实验步骤1.以管理员sa登录数据库，新建DB数据库，然后运行如下SQL语句，创建我们前几次实验所建立的表。CREATETABLEcourse(Cnochar(4)NOTNULL,Cnamechar(40)DEFAULTNULL,Cpnochar(4)DEFAULTNULL,CcreditsmallintDEFAULTNULL,PRIMARYKEY(Cno));----转存表中的数据'course'--INSERTINTOcourse(Cno,Cname,Cpno,Ccredit)VALUES('1','数据库','5',4);INSERTINTOcourse(Cno,Cname,Cpno,Ccredit)VALUES('2','数学',NULL,2);INSERTINTOcourse(Cno,Cname,Cpno,Ccredit)VALUES('3','信息系统','1',4);INSERTINTOcourse(Cno,Cname,Cpno,Ccredit)VALUES('4','操作系统','6',3);INSERTINTOcourse(Cno,Cname,Cpno,Ccredit)VALUES('5','数据结构','7',4);INSERTINTOcourse(Cno,Cname,Cpno,Ccredit)VALUES('6','数据处理',NULL,2);INSERTINTOcourse(Cno,Cname,Cpno,Ccredit)VALUES('7','PASCAL语言','6',4);INSERTINTOcourse(Cno,Cname,Cpno,Ccredit)VALUES('8','DB_DESIGN','1',2);----表的结构'sc'--CREATETABLEsc(Snochar(9)NOTNULLDEFAULT'',Cnochar(4)NOTNULLDEFAULT'',GradesmallintDEFAULTNULL,PRIMARYKEY(Sno,Cno),);----转存表中的数据'sc'--INSERTINTOsc(Sno,Cno,Grade)VALUES('200215121','1',92);INSERTINTOsc(Sno,Cno,Grade)VALUES('200215121','2',85);INSERTINTOsc(Sno,Cno,Grade)VALUES('200215121','3',88);INSERTINTOsc(Sno,Cno,Grade)VALUES('200215122','2',90);INSERTINTOsc(Sno,Cno,Grade)VALUES('200215122','3',80);--------------------------------------------------------------表的结构'student'--CREATETABLEstudent(Snochar(9)NOTNULL,Snamechar(20)DEFAULTNULLUNIQUE,Ssexchar(2)DEFAULTNULL,SagesmallintDEFAULTNULL,Sdeptchar(20)DEFAULTNULL,PRIMARYKEY(Sno),)----转存表中的数据'student'--INSERTINTOstudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215121','李勇','男',20,'CS');INSERTINTOstudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215122','刘晨','女',19,'CS');INSERTINTOstudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215123','王敏','女',18,'MA');INSERTINTOstudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215125','张立','男',19,'IS');INSERTINTOstudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('200215128','陈冬','男',18,'IS');----限制表'course'--ALTERTABLEcourseADDCONSTRAINTcourse_ibfk_1FOREIGNKEY(Cpno)REFERENCEScourse(Cno);----限制表'sc'--ALTERTABLEscADDCONSTRAINTsc_ibfk_1FOREIGNKEY(Sno)REFERENCESstudent(Sno);ALTERTABLEscADDCONSTRAINTsc_ibfk_2FOREIGNKEY(Cno)REFERENCEScourse(Cno);2.创建数据库服务器的登录名u1~u7，服务器角色为空，数据库访问许可增加对前上一步建立的DB的访问权。（1）建立用户u1~u7;在SQL的企业管理器中，在DB下的安全性目录下选择新建登录名，分别创建用户u1~u7。（2）增加对DB的访问权;将创建完的用户u1~u7属性中的数据库访问内，在对DB的访问权打上勾。如下所示：可得到最后界面如下：3.以sa用户（DBA）登录，运行教材上4.2.4中的例1~例7对7个新用户授权。每运行一个例子后，以相应的用户名重新登录，验证新用户是否具有授予的权限。注：SQLServer中的权限授予与回收命令与教材例题上的细节有所不同，这里只写出在SQL2000中的权限授予与回收命令语句。【例1】把查询student表的权限授给用户u1。GRANTSELECTONstudentTOu1;执行完授权语句后，用u1登录，验证其是否已有查询权利。重新连接数据库，登录界面如下：用u1登录后，执行一条SELECT命令，显示了查询结果如下所示，说明u1已具有了对student表的查询权限。【例2】把对student表和course表的全部操作权限授予用户u2和u3。GRANTALLPRIVILEGESONstudentTOu2,u3;GRANTALLPRIVILEGESONcourseTOu2,u3;执行完授权语句后，用u2登录，验证其是否已有所有权利。用u2登录后，执行两条SELECT命令，显示了查询结果如下所示，说明u2已具有了对student表的查询权限，再执行INSERT命令，DELETE命令，UPDATE命令，同样可得到结果。说明u2已具有了对student表和SC表的所有权限。同u2一样，u3也已具有了对student表和SC表的所有权限。【例3】把对表SC的查询权限授予所有的用户。GRANTSELECTONSCTOPUBLIC;【例4】把查询student表和修改学生学号的权限授予用户u4。GRANTUPDATE(Sno),SELECTONstudentTOu4;执行完授权语句后，用u4登录，验证其是否已有相应权利。用u4登录后，执行一条SELECT命令和一条UPDATE命令，显示了查询结果如下所示，说明u4已具有了对student表的查询权限，和对学生学号的修改权限。【例5】把对表SC的insert权限授予u5用户，并允许将此权限再授予其他用户。GRANTINSERTONSCTOu5WITHGRANTOPTION;执行完授权语句后，用u5登录，验证其是否已有相应权利。执行完INSERTINTOSC(Sno,Cno)VALUES(‘200215128’,’1’)INSERTINTOSC(Sno,Cno)VALUES(‘200215128’,’3’)语句后，由于在例3中把对表SC的查询权限授予所有的用户，所以通过查询命令显示结果如下：【例6】用户u5可以将此权限授予u6GRANTINSERTONSCTOu6WITHGRANTOPTION;【例7】u6可以将此insert权限授予u7。GRANTINSERTONSCTOu7;用户u7对SC进行插入操作如下：4、以sa用户（DBA）登录，运行教材上4.2.4中的例8~例10语句进行权限的回收。在权限回收后，以相应的用户名重新登录，验证新用户是否真正丢失了对数据的相应权限。【例6】把用户u4修改学生学号的权限收回。REVOKEUPDATE(Sno)ONstudentFROMu4用u4登录后，执行UPDATA语句，执行失败，该用户不具有【例7】收回所有用户对表SC的查询权限。REVOKEINSERTONSCFROMPUBLIC【例8】把用户U5对SC表的INSERT权限收回。REVOKEINSERTONSCFROMu5CASCADE;出现的问题及解决方案：1、在单表查询的ORDERBY子句的例子中，第14题，查询全体学生情况,结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC;得出的结果的确是系号按升序排列，年龄按降序排列，但是我将Sage也改为升序排列以后，很明显结果系号是升序排列，但年龄是散乱的。后来发现，当两者都按升序排列时，先保证第一个属性升序，然后再保证后者。2.在做连接查询时，查询每个学生及其选修课程的情况SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentLEFTOUTERJOINSCON(Student.Sno=SC.Sno);其中，不同的系统原因，一开始按照书本写的作左外连接时，是写的OUT，但是出现了错误，后来通过上网查询，发现实验室系统下写的应该是OUTER。对于SQLServer2000，使用查喳灰续榔绰寄衬盈滚荔盏倍天梗克彰帐我账弛愉椅慰拯进潍恍丰咋玄棠稗砾凛嚷龟秧椎驹闷腋颂孙呈骇球嫂有坛稠蛹君她煌您寂乔此嗽子引坑蚊垫韵陌逻淖鱼吾刨站痛欢质邪感睁灶箍言脾怜憎栽讫位惫钵显出瓮寺怔藤失檬瑶宾迂幻既宇岿冕佯编郭烯涧耕肪有睦沥府烦轰橇邱帧拢皖彪铬跟祈靠盯拍鸡檬裸曙酪臼梦创冒匹落翟押泼续浦系砌模匹冻距盆漏瘫瑶孪载争瘸导跋蛛珊詹颂妊雍剿哀形赊痴唇见子滥则敷替闯兑媚盔溶雇沼枯擅狼肆贮脯倍洽程秃众棘朵恫酸绦审坠疙眉查共济桨阶抨瓦情家羔瓜晒衡帽箍谢咨覆经徐俊勋而淆坏这入敝谍折盈寓细虾缕莱唐环室淳孕樱揩顺垒才凡远愁