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

北京邮电大学实验报告课程名称 数据库系统概念实验名称数据库完整性与安全性实验 教师_ 成绩_ 实验目的：1. 通过对完整性规则的定义实现，熟悉了解SQL Server数据库中实体完整性、参照完整性、断言等完整性保证的规则和实现方法，加深对数据完整性的理解。2. 通过对安全性相关内容的定义，熟悉了解SQL Server数据库中安全性的内容和实现方法，加深对数据库安全性的理解。实验内容：一) 完整性实验与要求：一、建表阶段1. 利用SQL语句，分别定义数据库中各基本表的主键、候选键、外键，实现实体完整性约束和参照完整性约束。2. 在数据库中选取两张或三张具有外键关联的表，利用语句foreign keyreferences创建外键，实现参照完整性约束。3. 根据实际背景，对某些表中的某些特定属性，定义空值、非空约束。4. 建表时，使用check谓词，通过断言，添加如下一些约束：(1) 个MSC最多管理10个BSC。(2) 个BSC最多管理50个小区/扇区。(3) 个BTS可以连接至1-3个BSC。(4) 个MS可以位于1到6个BTS的覆盖范围内。(5) 小区/扇区所使用的频点号在，124之间。二、主键/候选键约束验证1. 选取定义了主键的关系表，向该表插入在主属性上取值为空的元组，观察系统反应；选取表中某些或某个元组，修改这些元组在主属性上的取值，或插入新元组，使这些元组与表中已有其它元组的主属性取值相同，观察系统反应；2. 选取定义了候选键的关系表，向该表插入在候选键属性上取值为空的元组，观察系统反应；选取表中某些或某个元组，修改这些元组在候选键属性上的取值，或插入新元组，使这些元组与表中已有其它元组的候选键属性取值相同，观察系统反应；三、外键约束验证1. 选取相互间定义了外键关联的一组表，分别在参照关系、被参照关系上，对表的主属性/外键属性作插入、删除、更新等操作，观察当1个表（如参照关系表、被参照关系表）在外键或主属性属性上的取值发生变化时，数据库管理系统对这些操作的反应，以及另外一个表（如被参照关系表、参照关系表）在主属性或外键属性上的取值的变化，并记录实验结果。 上述插入、删除、更新操作操作分为违反约束和不违反约束两种情况。2. 观察级联操作对外键约束的影响1）对相互间定义了外键关联关系的一组表，分别使用foreign keyreferences on delete cascadeon update cascadeon insert cascade语句创建级联外键约束。2) 分别在参照关系、被参照关系上，对表的主属性/外键属性作插入、删除、更新等操作，观察当1个表（如参照关系表、被参照关系表）在外键或主属性属性上的取值发生变化时，数据库管理系统对这些操作的反应，以及另外一个表（如被参照关系表、参照关系表）在主属性或外键属性上的取值的变化，并记录实验结果。 上述插入、删除、更新操作操作分为违反约束和不违反约束两种情况。三、check/触发器约束验证选取建表阶段定义的2个断言约束，对断所涉及的元组属性做插入、删除、更新操作，观察当违反断言、触发器约束条件时，DBMS的反映和关系表内元组数据的变化（对触发器）。二) 安全性实验内容与要求：SQL Server 数据库实验：SQL Server提供了多种安全机制。主要的有如下3条：1最小权限原则：SQL Server通过给不同用户赋予不同权限的方式来保证安全。本原则要求只给某用户完成工作所必须的权限，以尽量减小用户有意或无意的错误对数据库造成的损害。2CIA原则：C：机密性，未授权者或使用非法手段无法访问数据。I：完整性，未授权者或使用非法手段无法修改数据。A：可用性，已授权者可随时使用数据。3深度防护：实际上任何一个程序都运行在操作系统上，一台计算机上还有其它应用程序。所以SQL Server的安全不能仅仅考虑本身的安全，而必须结合操作系统安全及其它相关因素（比如杀毒软件和防火墙）来考虑。这就是深度防护。SQL Server仅仅运行在Windows操作系统上，允许直接使用Windows用户作为SQL Server用户，由于是同一公司的产品，其和操作系统的联系和配合特别紧密，为其它数据库所远远不及的。在SQL Server服务器及其它Windows服务器上，应该进行多方面的安全配置：包括操作系统补丁管理、管理员用户和组管理、其它本地用户组管理和设置本地安全策略。为了提供网络上的客户端访问，还需要进行端点等相关设置。SQL Server对两种实体提供安全机制。分别是登录名（以前曾称为login，现在在T-SQL中仍然这样使用，但为避免混淆，Microsoft已经开始改称其为服务器主体）和用户名（user，就是数据库主体）。现在有3种登录名：windows域登录名（就是域用户），windows本地登录名（本地用户）和SQL Server登录名。用户登录以后，SQL Server将其映射为自己的user，进行相应安全管理。从而提供了两种身份认证模式：Windows认证模式和SQL Server认证模式。1Windows认证模式SQL Server数据库系统通常运行在NT服务器平台或基于NT 构架的Windows server上，而NT作为网络操作系统，本身就具备管理登录、验证用户合法性的能力，所以Windows认证模式正是利用这一用户安全性和账号管理的机制，允许SQL Server也可以使用NT的用户名和口令。在该模式下，用户只要通过Windows的认证就可连接到SQL Server，而SQL Server本身也不需要管理一套登录数据。2SQL Server认证模式在SQL Server认证模式下，用户在连接到SQL Server时必须提供建立在SQL Server上的用户名（登录名）和登录密码，这些登录信息存储在系统表syslogins中，与NT的登录账号无关。SQL Server自己执行认证处理，如果输入的登录信息与系统表syslogins 中的某条记录相匹配则表明登录成功。注：该方式常用于系统开发中，因为客户机常常与服务器不是同一台计算机，甚至也不在同一个windows域中，所以使用该方式进行登录比较方便。实验内容：(1) 以DBA身份（可以是SQL Server上的sa或者windows上的系统管理组的某个成员）登陆系统，在图形界面下创建新登录，新建登录用户可以使用Windows认证模式和SQL Server认证模式。(2) 使用T-SQL命令create login login-name 和create login login-name from windows重复第1步的内容。(3) 在服务器角色选项卡中赋予新建用户角色。完成后查找T-SQL中对应命令，在图形界面上撤消该权限后使用命令完成授予权限和收回权限的任务。(4) 将新建用户映射到移动通信数据库某用户。执行相关SQL操作，检查该用户的权限。用户在权限范围内、超出权限范围访问相应数据对象，查看访问结果和DBMS的反映。(5) 对特定服务器对象设置权限，双击登录名以后选择安全对象，赋予该登录名对服务器内的某个对象的权限。执行相关SQL操作，检查该用户的权限。用户在权限范围内、超出权限范围访问相应数据对象，查看访问结果和DBMS的反映。(6) 分别在图形化界面、命令行中，撤销用户权限。(7) 分别在图形化界面、命令行中，删除数据库用户。(8) 根据实际操作，指出服务器角色和数据库角色有多少种，分别有什么权限？(9) 创建数据库时，还会自动创建 SYS、GUEST 和 dbo 组。通过帮助文档，了解这些组在数据库中起到的作用。(10) 以DBA身份登陆系统，创建用户组，为用户组授予（grant）或撤销（revoke）针对数据库中表、视图等不同数据对象的不同访问权限。(11) 将组成员资格授予现有用户或组，现有用户或组访问数据库对象，查看访问结果。撤销现有用户或组的组成员资格，查看访问结果。(12) 分别在图形化界面、命令行中，从数据库删除组。(13) 分别采用Windows认证方式和SQL Server认证方式用不同的用户进行登录连接。(14) 创建数据库角色，并授予访问通信数据库的读、写权限，并对其中的Cell表数据进行修改。(15) 将角色赋予（1）中定义的用户，建立用户和角色联系。a. 再次用此用户访问通信数据库，并对其中的Cell表数据进行修改。实验环境：采用SQL Server数据库管理系统作为实验平台。SQL Server要求选用要求使用2005、2008或者2012版本，可以采用SQL Server Express、SQL Server Develop或SQL Server Enterprise等版本。实验要求：本实验内容比较繁多，要求同学一定要进行完全的实验，并做出详尽的记录。实验步骤及结果分析：一. 建表阶段1利用SQL语句，分别定义数据库中各基本表的主键、候选键、外键，实现实体完整性约束和参照完整性约束。2在数据库中选取两张或三张具有外键关联的表，利用语句foreign keyreferences创建外键，实现参照完整性约束。3根据实际背景，对某些表中的某些特定属性，定义空值、非空约束。create table Msc ( MscID char(256) not null, Mscname char(256) null, Msccompany char(256) null, Msclongitude float null, Msclatitude float null, Mscaltitude float null, primary key (MscID) create table Bsc ( BscID char(256) not null, Bscname char(256) null, Bscaltitude float null, Bsclatitude float null, Bsclongitude float null, Bsccompany char(256) null, MscID char(256) null, primary key (BscID), foreign key (MscID) references Msc)create table Bts ( BscID char(256) null, Btslatitude float null, Btsaltitude float null, Btslongitude float null, Btsname char(256) not null, Btscompany char(256) null, BtsPower int null, primary key (Btsname), foreign key (BscID) references Bsc)create table MS ( IMEI char(256) not null, MSISDN char(256) null, gsmMspHeight float null, gsmMspFout float null, gsmMspSense int null, username char(256) null, MScompany char(256) null, Mzone char(10) null, primary key (IMEI)create table Cell ( CellID char(256) not null, Btsname char(256) null, Areaname char(256) null, Raidious int null, Direction int null, BCCH int null, LAC char(256) null, Celllongitude float null, Celllatitude float null, primary key (CellID), foreign key (Btsname) references Bts)create table Antenna ( CellID char(256) not null, AntennaHeight float null, HalfPAngle float null, MaxAttenuation float null, Gain float null, AntTilt float null, Pt float null, MsPwr float null, foreign key (CellID) references Cell)create table Pingdian ( CellID char(256) not null, Freq int not null, primary key (CellID,Freq), check(Freq=1 and Freq10) begin print 1个MSC最多管理10个BSC。 rollback transactionend个BSC最多管理50个小区/扇区。GOcreate trigger Cell_to_Bsc on Cellafter insert as if (select count(Cell.Btsname) from Bsc,Bts,Cell,inserted where Bts.Btsname=inserted.Btsname and Bts.BscID=Bsc.BscID group by Bsc.BscID)50) begin print 1个BSC最多管理50个小区/扇区。 rollback transactionend个BTS可以连接至1-3个BSC。GOcreate trigger Bts_to_Bsc on Btsafter insert as if (select count(Bts.BscID) from Bts,inserted where Bts.Btsname=inserted.Btsname group by Bts.BscID)3) begin print 1个BTS可以连接至1-3个BSC。 rollback transactionend个MS可以位于1到6个BTS的覆盖范围内。GOcreate trigger MS_to_serverr on serverrafter insert as if (select count(serverr.IMEI) from serverr,inserted group by serverr.Btsname)6) begin print 1个MS可以位于1到6个BTS的覆盖范围内。 rollback transactionend小区/扇区所使用的频点号在，124之间。create table Pingdian ( CellID char(256) not null, Freq int not null, primary key (CellID,Freq), check(Freq=1 and Freq=124)5选取定义了主键的关系表，向该表插入在主属性上取值为空的元组，观察系统反应；选取表中某些或某个元组，修改这些元组在主属性上的取值，或插入新元组，使这些元组与表中已有其它元组的主属性取值相同，观察系统反应；(选定MSC表)insert into Msc(MscID,Mscname,Msccompany,Msclongitude,Msclatitude,Mscaltitude)values (NULL,BIG,NOKIA,117.21,40.22,30)在MSC表有数据的情况下（MscID=5214）insert into Msc(MscID,Mscname,Msccompany,Msclongitude,Msclatitude,Mscaltitude)values (5214,BIG,NOKIA,117.21,40.22,30)6 选取定义了候选键的关系表，向该表插入在候选键属性上取值为空的元组，观察系统反应；选取表中某些或某个元组，修改这些元组在候选键属性上的取值，或插入新元组，使这些元组与表中已有其它元组的候选键属性取值相同，观察系统反应；(选定tonghuadata表 主键为CellID 候选键为timee和Datee)insert into tonghuadata(CellID,traff,thtraff,rate,congsnum,callnum,callcongs,nTCH,Datee,Timee)values (9011,1,1,1,1,1,1,1,NULL,NULL)insert into tonghuadata(CellID,traff,thtraff,rate,congsnum,callnum,callcongs,nTCH,Datee,Timee)values (9011,1,1,1,1,1,1,1,2094-06-04 00:00:00.000,2395-12-18 00:00:00.000)7选取相互间定义了外键关联的一组表，分别在参照关系、被参照关系上，对表的主属性/外键属性作插入、删除、更新等操作，观察当1个表（如参照关系表、被参照关系表）在外键或主属性属性上的取值发生变化时，数据库管理系统对这些操作的反应，以及另外一个表（如被参照关系表、参照关系表）在主属性或外键属性上的取值的变化，并记录实验结果。 上述插入、删除、更新操作操作分为违反约束和不违反约束两种情况。插入参照关系不存在违反约束 可以直接插入Msc信息被参照关系违反约束（MscID为5214和5215，插入MscID不为该值会报错）insert into Bsc(BscID,Bscname,Bscaltitude,Bsclatitude,Bsclongitude,Bsccompany,MscID)values(42,Hello,40,121.2,40.321,Apple,2416)不违反约束insert into Bsc(BscID,Bscname,Bscaltitude,Bsclatitude,Bsclongitude,Bsccompany,MscID)values(42,Hello,40,121.2,40.321,Apple,5214)8 观察级联操作对外键约束的影响1）对相互间定义了外键关联关系的一组表，分别使用foreign keyreferences on delete cascadeon update cascade on insert cascade语句创建级联外键约束。 create table Bsc ( BscID char(256) not null, Bscname char(256) null, Bscaltitude float null, Bsclatitude float null, Bsclongitude float null, Bsccompany char(256) null, MscID char(256) null, primary key (BscID), foreign key (MscID) references Msc on delete cascade on update cascade )2) 分别在参照关系、被参照关系上，对表的主属性/外键属性作插入、删除、更新等操作，观察当1个表（如参照关系表、被参照关系表）在外键或主属性属性上的取值发生变化时，数据库管理系统对这些操作的反应，以及另外一个表（如被参照关系表、参照关系表）在主属性或外键属性上的取值的变化，并记录实验结果。 上述插入、删除、更新操作操作分为违反约束和不违反约束两种情况。（当输入on insert cascade之后系统一直报错，无法检验插入后是否违背约束）更新主属性 违反约束（无）不违反约束 Msc更新的时候Bsc同时更新update Msc set MscID=5244where MscID=5214更新外键属性 违反约束（Bsc更新的内容在Msc中不存在）update Bsc set MscID=5244where MscID=5214不违反约束 (Bsc更新的内容在Msc中存在)update Bsc set MscID=5215where MscID=5214删除主属性 违反约束（无） 不违反约束delete from Msc where MscID=5214Msc和对应的Bsc都删除删除外键属性 违反约束（无）不违反约束delete from Bsc where MscID=5215全部被删除（5214已经在上面操作中更改为5215了）9选取建表阶段定义的2个断言约束，对断所涉及的元组属性做插入、删除、更新操作，观察当违反断言、触发器约束条件时，DBMS的反映和关系表内元组数据的变化（对触发器）。插入（不违反断言）insert into BSC(BscID,Bscname,Bscaltitude,Bsclatitude,Bsclongitude,Bsccompany,MscID)values (42227,BELLBS,1,1,1,Nokia,5215)（违反断言）insert into BSC(BscID,Bscname,Bscaltitude,Bsclatitude,Bsclongitude,Bsccompany,MscID)values (42230,BELLBS,1,1,1,Nokia,5215)安全性实验内容与要求：1.以DBA身份（可以是SQL Server上的sa或者windows上的系统管理组的某个成员）登陆系统，在图形界面下创建新登录，新建登录用户可以使用Windows认证模式和SQL Server认证模式。由于Windows只有一个用户，且用户已经为SQL server 的默认登陆者 因此无法采用Windows认证模式SQL server 认证模式2使用T-SQL命令create login login-name 和create login login-name from windows重复第1步的内容。CREATE LOGIN asd WITH PASSWORD = 111无多余windows账户，无法操作create login from windows3 在服务器角色选项卡中赋予新建用户角色。完成后查找T-SQL中对应命令，在图形界面上撤消该权限后使用命令完成授予权限和收回权限的任务。删除权限：revoke create any database to asd设定权限：grant create any database to asd4 将新建用户映射到移动通信数据库某用户。执行相关SQL操作，检查该用户的权限。用户在权限范围内、超出权限范围访问相应数据对象，查看访问结果和DBMS的反映。设定映射：5对特定服务器对象设置权限，双击登录名以后选择安全对象，赋予该登录名对服务器内的某个对象的权限。执行相关SQL操作，检查该用户的权限。用户在权限范围内、超出权限范围访问相应数据对象，查看访问结果和DBMS的反映。