实验三

前言

实验内容和要求

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

具体内容如下：

设置SQL Server服务器身份验证模式（Windows或SQL Server和Windows(S) 认证模式）
登录的管理

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

数据库用户的管理

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

角色的管理

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

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

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

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

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

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

参考教材《数据库系统概论第五版》==P141-P151 数据加密 P158P167 数据库完整性==
实验前,先熟悉常用的语句,方便理解以下代码(个人整理，如有错误，欢迎指正)

SQL知识点

一、授权：授予与收回

列权限	含义
SELECT	查询表
REFERENCES	约束表
INSERT	用户可以插入一个元组
UPDATE	修改表
DELETE	删除表
ALL PRIVILEGES	所有权限

1.GRANT

GRANT <权限>,<权限>.....
ON <对象类型>,<对象名>,....
TO <用户>,....
WITH GRANT OPTION;/*允许此权限授予其他用户*/

2.REMOVE

GRANT <权限>,<权限>.....
ON <对象类型>,<对象名>,....
FROM <用户>,<用户>...

二、数据库的完整性

1.参照完整性

NO ACTION: 不允许该操作执行，该策略一般设置为默认策略

CASCADE：当删除或者修改被参照表中的一个元组导致与参照表不一致时，删除或修改参照表中的所有导致不一致的元组

设为空值：当删除或者修改被参照表中的一个元组导致与参照表不一致时，将造成不一致的元组设为空值

CREATE TABLE SC(
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno),/*在表级定义实体完整性，Sno，Cno都不能为空值*/
    FOREIGN KEY(Sno) REFERENCES Student(Sno)/*在表级定义参照完整性*/++
    	ON DELETE CASCADE/*当删除Sttudent表中的元组时，级联删除SC表中相应的元组*/
    	ON UPDATE CASCADE,/*当更新Student表中的sno时，级联更新SC表中相应的元组*/
    FOREIGN KEY(Cno) REFERENCES Course(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/
    	ON DELETE NO ACTION/*当删除Course表中的元组造成与SC表不一致时，拒绝删除*/
    	ON UPDATE CASCADE/*当更新Course表中的Cno时，级联更新SC表中相应的元组*/
);

2.用户定义的完整性

2.1属性约束条件

NOT NULL: 列值非空

UNIQUE: 列值唯一

CHECK: 检查列值是否满足一个表达式

CREATE TABLE SC(
    Sno CHAR(9) UNIQUE NOT NULL,/*Sno属性不允许取空值，且唯一*/
    Cno CHAR(4) NOT NULL,/*Cno属性不能为空*/
    Grade SMALLINT CHECK(Grade>=0 AND Grade<=100),/*Grade取值范围时0-100*/
    };

2.2元组约束条件

与属性约束条件相比，元组级的约束可以设置不同属性之间的取值的相互约束条件

CREATE TABLE Student(
    Sno CHAR(9),
    Sname CHAR(20),
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
    PRIMARY KEY(Sno),
    CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
    /*定义了元组中Sname和Ssex两个属性值之间的约束条件。性别为女通过CHECK，性别为男时，名字不能以MS.开头*/
);

3.完整性约束命名子句

以上的完整性约束条件都在CREATE TABLE语句中定义，SQL还在CREATE TABLE语句中提供了完整性约束命名子句CONSTRAINT,从而灵活的增加、删除一个完整性约束条件

CONSTRAINT <完整性约束条件名><完整性约束条件>

3.1子句

CREATE TABLE Student(
    Sno NUMEIC(9),
    	CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
    	/*学号在9000-99999*/
    Sname CHAR(20),
    	CONSTRAINT C2 NOT NULL,
		/*姓名不能为空*/
    Sage NUMERIC(3),
    	CONSTRAINT C3 CHECK(Sage<20),
		/*年龄小于20*/
    Ssex CHAR(2),
    	CONSTRAINT C4 CHECK(Ssex IN('男','女')),
    	CONSTRAINT C5 StudentKEY PRIMARY KEY(Sno)
		/*性别只能是男或女*/
);

3.2删除约束

ALTER TABLE Student
	DROP CONSTRAINT C1;

实验内容

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

1.1键鼠操作

首先打开新建登录名

按下图依次填写

1.2SQL语句

---创建SQL Server登录名
CREATE login "user" with password='gczf',default_database=学籍管理;

2.登录的管理

2.1键鼠操作

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

首先打开数据库用户窗口

按下图，将user用户加入学籍管理数据库中，并命名用户名为U1(也可以为user)

创建好后，刷新

2.2SQL语句

---创建数据库用户
CREATE user U1 for login user;/*第二个user代表登录名称*/

3.数据库用户的管理

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

---授权U1查询和插入权限
GRANT SELECT,INSERT
ON Student
to U1

---授权所有权限给U1
GRANT ALL PRIVILEGES
ON Student
to U1

4.角色的管理

角色同样可以使用键鼠，方法与上面相同，不做赘述

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

---创建角色R1,授权查询，更新，插入权限
CREATE ROLE R1
GRANT SELECT,UPDATE,INSERT
ON Student
to R1

---授权R1删除权限
GRANT DELETE
ON Student
to R1

---移除R1查询权限
REVOKE SELECT
ON Student
FROM R1;

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

CREATE TABLE Course1
(Cno char(4) PRIMARY KEY,
 Cname char(40) UNIQUE,
 Cpno char(4),
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES Course (Cno)
);

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

CREATE TABLE SC1
(Sno char(9),
 Cno char(4),
 GRADE SMALLINT CHECK(Grade>=0 AND Grade<=100),
 PRIMARY KEY(Sno,Cno),
 FOREIGN KEY (Sno) REFERENCES Student (Sno),
 FOREIGN KEY (Cno) REFERENCES Course (Cno)
);

7．

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

CREATE TABLE SC1
(Sno char(9),
Cname char(40) UNIQUE,
 Cpno char(4),
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES Course (Cpno)
);

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

CRATE TABLE Student
(Sno char(9) PRIMARY KEY,
 Sname char(20) UNIQUE,
 Ssex char(2),
 CCONSTRAIN C1 CHECK (Ssex in ('男','女')),
 Sage SMALLINT,
 Sdept char(20),
 FOREIGN KEY (Sdept) REFERENCES Dept(Dno)
);

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

Alter TABLE Student1 ADD birthday DATE DEFAULT getdate();

思考题

1.角色的作用是什么？

数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合，因此以一组具有相同权限的用户创建一个角色，使用角色管理数据库权限可以简化授权的过程

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

windows用户是通过windows登录账号的相关权限登录，数据库用户是通过sqlserver程序自身创建的用户登录