实验三实现数据库的安全性与数据的完整性

1实验三实现数据库的安全性与数据的完整性一、实验目的1.了解SQLServer2000的身份验证方法。2.掌握合法登录帐户的设置。3.掌握用户的权限管理方法。4.掌握实体完整性与参照完整性的实现方法。5.掌握域完整性的实现方法。二、实验内容1.Windows和SQLServer2000身份验证的比较。2.设置登录帐户。3.设置数据库用户权限。4.参照完整性5.实体完整性6.域完整性三、实验准备1.登录模式a)SQLServer和Windows:指定用户可以使用SQLServer身份验证和Windows身份验证连接到SQLServer。b)仅Windows:指定用户只能使用Windows身份验证连接SQLServer。22.权限验证当验证了用户的身份并允许其登录到SQLServer2000实例之后，在用户必须访问的每个数据库中都要求单独的用户帐户。在每个数据库中都要求用户帐户可防止用户连接到SQLServer实例并访问服务器上的所有数据库3.角色利用角色，SQLServer管理者可以将某些用户设置为某一角色，这样只对角色进行权限设置便可以实现对所有用户权限的设置，大大减少了管理员的工作量。SQLServer提供了用户通常管理工作的预定义服务器角色和数据库角色。服务器角色系统管理员：拥有SQLServer所有的权限许可。服务器管理员：管理SQLServer服务器端的设置。磁盘管理员：管理磁盘文件。3进程管理员：管理SQLServer系统进程。安全管理员：管理和审核SQLServer系统登录。安装管理员：增加、删除连接服务器，建立数据库复制以及管理扩展存储过程。数据库创建者：创建数据库，并对数据库进行修改数据库角色public：维护全部默认许可。db_owner：数据库的所有者，可以对所拥有的数据库执行任何操作。db_accessadmin：可以增加或者删除数据库用户、工作组和角色。db_addladmin：可以增加、删除和修改数据库中的任何对象。db_securityadmin：执行语句许可和对象许可。db_backupoperator：可以备份和恢复数据库。db_datareader：能且仅能对数据库中的任何表执行select操作，从而读取所有表的信息。db_datawriter：能够增加、修改和删除表中的数据，但不能进行select操作。db_denydatareader：不能读取数据库中任何表中的数据。db_denydatawriter：不能对数据库中的任何表执行增加、修改和删除数据操作。4.实体完整性把表中的每行看做一个实体，它要求所有行都具有惟一标识通过建立索引、PRIMARYKEY约束、UNIQUE约束以及列的IDENTITY来实现。例1：CREATEtableemployee(job_idsmallintPRIMARYKEY,job_namechar(20)notnull)例2：CREATEtablestudent(snamechar(10),Snointnotnull,Primarykey(sno))例3：createtableii(aintnotnullIDENTITY(1,3),bchar)5.参照完整性维护被参照表与参照表之间的数据一致性，通过主键（PRIMARYKEY）和外键（FOREIGNKEY）约束实现。使用FOREIGNKEY约束需注意：FOREIGNKEY约束只参照同一个数据库中的其它表，跨数据库表的参照只能通过触发器来实现。例：CREATEtablePR(idintnotnullprimarykey,4Namechar(10))CREATEtableFR(idintnotnullReferencesPR(id),Scoreintnotnull)6.域完整性（用户定义）要求表中指定列的数据具有正确的数据类型、格式和有效的数据范围。通过默认值、CHECK、规则等约束实现例1：CHECK约束CREATEtableexample(idintnotnull,Scoreintnotnullcheck(score=0andscore=100))例2：对计算列使用表达式CREATETABLEmytable(lowint,highint,myavgAS(low+high)/2)四、实验步骤1.数据库安全性的实现（1）练习1：创建用户（本部分练习请参看课本P137-143）A．创建一个testlogin数据库B．新建登录wangming,liyong,liuxing,yanglan,并设定密码，服务器角色设为“系统管理员”数据库访问为testlogin。[提示]1.SQLServer默认的用户登录帐号：Sa、builtin\administrators2.新建登录（注意身份验证模式），对“服务器角色”及“数据库访问”的设定。（2）练习2：数据库用户权限管理(注：部分操作还可以通过企业管理器实现)给定两个关系模式E(职工)（职工号，姓名，年龄，职务，工资，部门号）P(部门)（部门号，名称，经理名，地址，电话）用SQL语言中的GRANT和REVOKE语句，完成以下授权定义或存取控制功能：a)用户wangming对表E有SELECT权力b)用户liyong对P有INSERT和DELETE权力c)把对表E的查询权力授予用户liuxing,并给liuxing有再授权的权力d)用户liuxing对表P的字段（地址，电话）有修改的权力e)用户yanglan具有从每个部门职工中SELECT最高工资、最低工资，平均工资的权力，不能查看每个人的工资（可以用视图）f)对上述每一种情况，撤销各用户所授予的权力。5[提示]用户（或应用程序）使用数据库的方式称为权限。权限有几种：读权限：允许用户读数据，但不能修改数据插入权限：允许用户插入新的数据，但不能修改数据修改权限：允许用户修改数据，但不能删除数据删除权限：允许用户删除数据Transaction_SQL语句使用grant、revoke和deny三种命令来实现管理权限GRANT权限依赖于所授予的语句权限和权限中涉及的对象。sysadmin角色中的成员可在任何数据库中授予任何权限。对象所有者可为他们所拥有的对象授予权限。db_owner或db_securityadmin角色的成员可授予其数据库中任何语句或对象上的任何权限。2．数据完整性的实现1）练习3：请通过数据完整性策略实现数据的完整性。（见下述每个表的说明部分)表1学生情况表XSQK的结构列名数据类型长度是否允许为空值默认值说明学号Char6N主键姓名Char8N性别Bit2N出生日期smalldatetime4N专业名Char10N所在系Char10N联系电话char13Y表2课程表KC的结构列名数据类型长度是否允许为空值默认值说明课程号Char3N主键课程名Char20N教师Char10开课学期Tinyint1学时Tinyint360学分Tinyint2N表3成绩表XS_KC的结构列名数据类型长度是否允许为空值默认值说明学号Char6N参考XSQK表的学号课程号Char3N参考KC表的课程号成绩int40-1002）练习4：根据表4及表5，完成以下题目：6a)在部门表和员工表的结构中标注主关键字。b)确定在部门表和员工表中共有的属性。c)指出哪个表中的属性是外关键字。d)确定哪个表是主表，哪个表是从表。e)部门表和员工表是如何通过关键字实施数据完整性的。f)用SQL语句建立符合上述数据的表，并实现数据的完整性。表4部门表部门代码部门名负责人地点0001生产部李华江浙江杭州0002销售部张丽浙江宁波0003市场部王欣浙江温州表5员工表员工代码姓名家庭住址联系电话邮政编码部门代码200001王华杭州869609863100060001200002李想富阳854387693100100003200003张丽杭州678935423100170002200004李华江萧山828498733101010001