数据库实验 数据控制-安全性部分+ 数据控制-完整性部分+数据备份和恢复

数据库实验 数据控制-安全性部分+ 数据控制-完整性部分+数据备份和恢复

一、实验环境
本实验的实验环境为SQL Sever 2008 R2。

二、实验内容与完成情况

一、实验目的

熟悉通过SQL对数据库进行数据控制，包括安全性、完整性和数据库恢复。

二、实验内容

实验一 数据控制-安全性部分

1.实验内容

SQL中使用GRANT和REVOKE语句向用户授予或收回对数据的操作权限。GRANT语句向用户授予权限，REVOKE语句收回已经授予用户的权限。

2.GRANT

GRANT语句的一般格式为
GRANT<权限>[,<权限>]…
ON<对象类型><对象名>[,<对象类型><对象名>]…
TO <用户>[,<用户>]…
[WITH GRANT OPTION];

其语义为：将对指定操作对象的指定操作权限授予指定的用户。发出该GRANT语句可以时数据库管理员，也可以该数据库对象创建者（即属主owner），还可以时已经拥有该权限的用户。接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用户。

如果指定了WITH GRANT OPTION 子句，则获得某种权限的用户还可以把这种权限再授予其他用户。如果没有指定EITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，不能传播该权限。

SQL标准允许拥有WITH GRANT OPTION的用户把相应权限或其子集授予其他用户，但不允许循环授权，即被授权者不能把权限再授回给授权者或其祖先。

我首先假定系统有U1、U2、U3 、U4、U5、U6六个登录用户，操作如图1。
Exec sp_addlogin ‘U1’,‘123456’
Exec sp_addlogin ‘U2’,‘123456’
Exec sp_addlogin ‘U3’,‘123456’
Exec sp_addlogin ‘U4’,‘123456’
Exec sp_addlogin ‘U5’,‘123456’
Exec sp_addlogin ‘U6’,‘123456’

然后使其成为当前数据库的合法用户，操作如图2。
Exec sp_grantdbaccess ‘U1’
Exec sp_grantdbaccess ‘U2’
Exec sp_grantdbaccess ‘U3’
Exec sp_grantdbaccess ‘U4’
Exec sp_grantdbaccess ‘U5’
Exec sp_grantdbaccess ‘U6’

（1）把查询Albums表的权限授给用户U1。

GRANT SELECT
ON Albums
TO U1

（2）把对Albums表的查询和插入权限授给U5用户，并允许将此权限再授予其他用户。
GRANT SELECT,INSERT
ON Albums
TO U5
WITH GRANT OPTION

下面登录U1。
在图5中可以看出U1没有获得访问select_course数据库的权限。

在图6中可以看出U1获得查询Albums表的权限。

在图7中可以看出U1没有获得更新Albums表的权限。

下面登录U5。
在图8中可以看出U5获得查询Albums表的权限。

在图9、10中可以看出U5获得插入Albums表的权限。

下面验证WITH GRANT OPTION。
U5将查询Albums表功能授予U6。

GRANT SELECT
ON Albums
TO U6

登录U6，执行查询功能。

U6并没有获得其他权限，比如UPDATE。

下面重新登录管理员用户，执行REVOKE。

授予用户的权限可以由数据库管理员或其他授权者用REVOKE语句收回，REVOKE语句的一般格式为

REVOKE <权限>[,<权限>]…
ON <对象类型><对象名>[,<对象类型><对象名>]…
FROM <用户>[,<用户>]…[CASCADE|RESTRICT];

（3）把用户U1查询Albums的权限收回。

REVOKE SELECT
ON Albums
FROM U1

（4）把用户U5查询、插入Albums的权限收回，级联收回U6的权限。
REVOLE SELECT,INSERT
ON Albums
FROM U5 CASCADE;

从图16、17、18中可以看出，U5、U6、U7均已无法访问Albums表。

实验二 数据控制-完整性部分

1.实体完整性

关系模型的试题完整性在CREATE TABLE中用PRIMARY KEY定义。对单属性构成的码有两种说明方法，一种是定义为列级约束条件，另一种是定义为表级约束条件。对多个属性构成的码只有一种说明方法，即定义为表级约束条件。

例：将Course表中的Cno属性定义为码。

CREATE TABLE Course
(Cno int PRIMARY KEY,
Cname CHAR(20) NOT NULL,
Ccredit int
)

下面呈现当操作违反了完整性约束条件时，系统是如何处理的。

2.参照完整性
关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。

例：定义SC中的参照完整性。

CREATE TABLE SC
(Sno nCHAR(10)NOT NULL,
Cno int NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY(Sno)REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno)
)

下面呈现当操作违反了参照完整性时，系统是如何处理的。
例如，删除Student表中Sno值为“201215121”的元组，则从要SC表中级联删除SC.Sno=201215121’的所有元组。

3.用户定义完整性

用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求。目前的关系数据库管理系统都提供了定义和检验这类完整性的机制，使用了和实体完整性、参照完整性相同的技术和方法来处理它们，而不必由应用程序承担这一功能。

例：不允许取空值

定义Course表时，Cname不允许取空值。

CREATE TABLE Course
(Cno int PRIMARY KEY,
Cname CHAR(20) NOT NULL,
Ccredit int
)

下面呈现当操作违反了用户定义完整性时，系统是如何处理的。

4.CHECK短语
例：Teacher表的Tsex只允许取“男”或“女”。
CREATE TABLE Teacher
(Tno CHAR(6) PRIMARY KEY,
Ssex CHAR(6) CHECK(Ssex in (‘男’,‘女’))
)

下面呈现当操作违反了CHECK短语用户定义完整性时，系统是如何处理的。

5.CONSTRANT子句

SQL还在CREATE TABLE语句中提供了完整性约束命名子句CONSTRAINT，用来对完整性约束条件命名，从而可以灵活地增加、删除一个完整性约束条件。

例：建立学生登记表SSStudent，要求学号在1~999之间，姓名不能取空值，年龄小于20，性别只能是“男”或“女”。

CREATE TABLE SSStudent
(Sno NUMERIC(6)
CONSTRAINT CI CHECK(SnO BETWEEN 1 AND 999),Sname CHAR(20)
CONSTRAINT C2 NOT NULL,Sage NUMERIC(3)
CONSTRAINT C3 CHECK(Sage <20),Ssex CHAR(2)
CONSTRAINT C4 CHECK(Ssex IN(‘男’,‘女’)),CONSTRAINT StudentKey PRIMARY KEY(Sno)
)

下面呈现当操作违反了CONSTRAINT子句时，系统是如何处理的。

实验三 数据备份和恢复

尽管数据库系统中采取了各种保护措施来防止数据库的安全性和完整性被破坏，保证并发事务的正确执行，但是计算机系统中硬件的故障、软件的错误、操作员的失误以及恶意的破坏仍是不可避免的，这些故障轻则造成运行事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中全部或部分数据丢失。因此数据库管理系统必须具有把数据库从错误状态恢复到某一已知的正确状态（亦称为一致状态或完整状态）的功能，这就是数据库的恢复。恢复子系统是数据库管理系统的一个重要组成部分，而且还相当庞大，常常占整个系统代码的10%以上。数据库系统所采用的恢复技术是否行之有效，不仅对系统的可靠程度起着决定性作用，而且对系统的运行效率也有很大影响，是衡量系统性能优劣的重要指标。

1.创建一个备份设备
USE [master]
GO
EXEC master.dbo.sp_addumpdevice @devtype = N’disk’, @logicalname = N’beifen’, @physicalname = N’D:\beifen.bak’
GO

2.完整备份实验数据库到备份设备

–备份数据库
USE master
GO
BACKUP DATABASE select_course TO beifen

3.向实验数据库中某个表插入若干条记录
INSERT
INTO Sstudent
VALUES (‘Bob’,‘Database’,‘1’);
INSERT
INTO Sstudent
VALUES (‘Bob’,‘java’,‘2’);
INSERT
INTO Sstudent
VALUES (‘Bob’,‘C++’,‘3’);

4.备份数据库事务日志到备份设备，并查看日志格式。

（1） 备份数据库事务日志到备份设备

–备份事务日志
USE master
GO
BACKUP LOG select_course TO beifen;

（2）查看日志格式

5.利用第2步所得的完整备份，恢复到插入记录前的状态。
use master
go
—声明变量
declare @dbName nvarchar(max)=‘select_course’;
declare @dbFullName nvarchar(max)=‘D:\beifen.bak’;
–1.1修改为单用模式
exec(N’ALTER DATABASE ‘+@dbName+’ SET SINGLE_USER WITH ROLLBACK IMMEDIATE’);
–1.2结束链接进程
DECLARE @kid varchar(max)
SET @kid=’’
SELECT @kid=@kid+'KILL ‘+CAST(spid as Varchar(10)) FROM master…sysprocesses
WHERE dbid=DB_ID(@dbName) ;
EXEC(@kid) ;
–2.执行还原语句
restore database @dbName from disk=@dbFullName
with replace --覆盖现有的数据库
–3.重置数据库为多用户模式
exec(N’ALTER DATABASE ‘+@dbName+’ SET MULTI_USER WITH ROLLBACK IMMEDIATE’);

可以看到表中内容已经没有了（刚开始时的表就是空表）。

6.利用第4步所得的事务日志，恢复到插入记录后的状态

ALTER DATABASE select_course SET OFFLINE WITH ROLLBACK IMMEDIATE
RESTORE DATABASE select_course FROM DISK=’D:\beifen.bak’ WITH REPLACE,NORECOVERT
RESTORE LOG select_course FROM DISK=’D:\beifen.bak’ WITH RECOVERY, STOPAT=N’12/1/2020 20:36:00 PM’
ALTER database select_course set online

三、出现的问题及解决方法
1.在写授权语句时，系统报错，在“TO”附近有语法错误，应为DOUBLECOLON，或ID.。这时把TABLE删除就可以了。
2.在备份数据库时，刚开始备份了一个名字全是中文的数据库，在写代码时无法写出“-”该符号，只能换成备份英文名字的数据库。