数据库--数据库安全与完整性

一、实验项目要求

实验目的

使学生加深对数据安全性和完整性的理解。并掌握 SQL Server中有关登录、用户、角色及操作权限的管理方法。熟悉通过SQL语句对数据进行完整性控制。

实验内容

数据库的安全性实验，在 SQL Server中，设置 SQL Server的安全认证模式，实现对SQL Server的用户和角色管理，设置和管理数据操作权限。

具体内容如下：

1. 设置SQL Server服务器身份验证模式（Windows或SQL Server和Windows(S) 认证模式）。

2. 登录的管理

创建一个SQL Server登录名，系统会自动生成同登录名的用户名。

3. 数据库用户的管理

给用户授权，授权时必须是超级用户（在windows账户或者xiaoli账户下）才可以进行。给用户授予某些表的相关操作权限。

4. 角色的管理

创建一个角色，使创建的用户成为该角色的成员，并授予一定的操作权限。

5．在课程表的“课程名”字段上定义唯一约束。

6．在选课表的“成绩”字段上定义check约束，使之必须大于等于0且小于等于100。“课程号”字段只能输入数字字符。

7．（1）在学生表中定义主键、外键约束。

（2）定义完整性约束命名子句，限制学生表的“性别”字段，使之只能取“男、女”值。

（3）在学生表中增加出生日期字段，使出生年月的默认值取当前日期。

二、理论分析或算法分析

alter table 表名 add constraint 约束名（随意起） unique(列名)

1.角色的创建：

GRANT ROLE<角色名>

2.给角色授权：

GRANT <权限><权限>

ON <对象类型>对象名

TO<角色>

3.将一个角色授予其他的角色或用户

GRANT <角色>

TO<角色>

[WITH ADMIN OPTION]

4.将权限收回：

REVOKE <权限>

ON<对象类型><对象名>

FROM<用户>

三、实现方法

1. 设置SQL Server服务器身份验证模式（Windows或SQL Server和Windows(S) 认证模式）。

我选择的是Windows；

2. 登录的管理

创建一个SQL Server登录名，系统会自动生成同登录名的用户名。

我设置的登录名为xiaosun,

use ST;
CREATE login "xiaoli" with password='123456',

用户的创建：

use ST;
CREATE user xiaoli for login xiaoli;

3. 数据库用户的管理

给用户授权，授权时必须是超级用户（在windows账户或者xiaoli账户下）才可以进行。给用户授予某些表的相关操作权限。

给用户xiaoli授予查询权，对于学生信息的查询；

use ST;
GRANT SELECT
ON STUDENT
TO xiaoli;

给用户xiaoli授予插入权

use ST;
GRANT INSERT
ON STUDENT
TO xiaoli;

给用户xiaoli授予修改权

use ST;
GRANT UPDATE
ON STUDENT
TO xiaoli;

对于xiaoli的查询权收回

use ST;
REVOKE SELECT
ON STUDENT
TO xiaoli;

对于xiaoli的插入权收回

use ST;
REVOKE INSERT
ON STUDENT
TO xiaoli;

对于xiaoli的修改权收回

use ST;
REVOKE UPDATE
ON STUDENT
TO xiaoli;

4. 角色的管理

创建一个角色，使创建的用户成为该角色的成员，并授予一定的操作权限。

创建一个角色为adimination为管理者；

use ST;
CREATE ROLE adimination

给角色授权：

use ST;
GRANT SELECT,UPDATE
ON STUDENT
TO adimination

将一个角色授予xiaoli用户

use ST;
exec sp_addrolemember 'adimination','xiaoli';

5．在课程表的“课程名”字段上定义唯一约束。

use ST;
ALTER TABLE COURSE ADD CONSTRAINT uqname UNIQUE (Cname)

6．在选课表的“成绩”字段上定义check约束，使之必须大于等于0且小于等于100。“课程号”字段只能输入数字字符。

use ST;
CREATE TABLE SC1(
	Sno CHAR(9),
	Cno CHAR(8),
	Grade SMALLINT CHECK (Grade>=90 AND Grade<=100),
	PRIMARY KEY(Sno,Cno),
	FOREIGN KEY(Sno) REFERENCES STUDENT(Sno),
	FOREIGN KEY(Cno) REFERENCES COURSE(Cno)
	);

7．（1）在学生表中定义主键、外键约束。

CREATE TABLE STUDENT(
	Sno CHAR(9)PRIMARY KEY,
	Sname CHAR(20),
	Ssex CHAR(8),
	Sage SMALLINT,
	Sdept CHAR(20),
	Foreign key (Sdept) references SEDPT (SSno)
);

（2）定义完整性约束命名子句，限制学生表的“性别”字段，使之只能取“男、女”值。

use ST;
CREATE TABLE STUDENT1(
	Sno CHAR(9)PRIMARY KEY,
	Sname CHAR(20),
	Ssex CHAR(8) CHECK (SSex IN('男','女')),
	Sage SMALLINT,
	Sdept CHAR(20),
Foreign key (Sdept) references SEDPT (SSno)
);

（3）在学生表中增加出生日期字段，使出生年月的默认值取当前日期。

use ST;
alter table STUDENT
add 入学时间 datetime

四、实验结果分析

1. 设置SQL Server服务器身份验证模式（Windows或SQL Server和Windows(S) 认证模式）。

2. 登录的管理

创建一个SQL Server登录名，系统会自动生成同登录名的用户名

用户的创建xiaoli;

3. 数据库用户的管理

给用户授权，授权时必须是超级用户（在windows账户或者xiaoli账户下）才可以进行。给用户授予某些表的相关操作权限。

给用户xiaoli授予查询权

给用户xiaoli授予插入权；

4. 角色的管理

创建一个角色，使创建的用户成为该角色的成员，并授予一定的操作权限。

创建了一个角色为admination;

将一个角色授予xiaoli用户

6．在选课表的“成绩”字段上定义check约束，使之必须大于等于0且小于等于100。“课程号”字段只能输入数字字符。

（2）定义完整性约束命名子句，限制学生表的“性别”字段，使之只能取“男、女”值。

（3）在学生表中增加出生日期字段，使出生年月的默认值取当前日期。

实验总结

在这次实验中，明显感觉这次的出现的错误比上次多，书上有些语句在SQL sever中不可以执行，就是会出现错误，在网上搜索一下，找出错误，不能只是一味着看书上的代码，要在理解的基础上进行操作。

思考题

1.角色的作用是什么？

角色是一个强大的工具，使您得以将用户集中到一个单元中，然后对该单元应用权限。对一个角色授予、拒绝或废除的权限也适用于该角色的任何成员。可以建立一个角色来代表单位中一类工作人员所执行的工作，然后给这个角色授予适当的权限。当工作人员开始工作时，只须将他们添加为该角色成员，当他们离开工作时，将他们从该角色中删除。而不必在每个人接受或离开工作时，反复授予、拒绝和废除其权限。权限在用户成为角色成员时自动生效。

2.数据库用户和windows用户有何不同？

一个是通过windows登录账号的相关权限登录
一个是通过sqlserver程序自身创建的用户登录
个人用当然一般都windows身份验证了，应用程序连接数据库基本都用sql身份验证