实验4数据库的安全性、完整性

2022-09-15 10:00:24

实验4数据库的安全性、完整性

实验目的要求

掌握自主存取控制权限的定义和维护方法；
掌握实体完整性的定义和维护方法；
掌握参照完整性的定义和维护方法；
掌握用户自定义完整性的定义和维护方法。

实验主要内容

定义用户、角色，分配权限给用户、角色，回收权限，以相应的用户名登录数据库验证权限分配是否正确。选择一个应用场景，使用自主存取控制机制设计权限分配。可以采用两种方案。方案一：采用sa超级用户登录数据库，完成所有权限分配工作，然后用相应用户名登陆数据库以验证权限分配正确性；方案二：采用sa用户登陆数据库创建三个部门经理用户，并分配相应的权限，然后分别用三个经理用户名登陆数据库，创建相应部门的USER, ROLE，并分配相应权限。验证权限分配之前，请备份好数据库；针对不同用户所具有的权限，分别设计相应的SQL语句加以验证。
定义实体完整性，删除实体完整性。能够写出两种方式定义实体完整性的SQL语句：创建表时定义实体完整性、创建表后定义实体完整性。设计SQL语句验证完整性约束是否起作用。
定义参照完整性，定义参照完整性的违约处理，删除参照完整性。写出两种方式定义参照完整性的SQL语句：创建表时定义参照完整性、创建表后定义参照完整性。
针对具体应用语义，选择NULL/NOT NULL、DEFAULT，UNIQUE、CHECK等，定义属性上的约束条件。

实验仪器设备

学生每个一台PC机
已安装SQL Server环境

实验记录

安全性

创建和删除用户

使用sa超级用户登录数据库

打开【新建查询】,输入

CREATE USER U1 WITHOUT LOGIN 
EXECUTE AS USER = ‘U5‘
REVERT

创建多个不用登录的用户

查看当前用户授权情况的语句为

SELECT pr.principal_id, pr.name, pr.type_desc,
    pr.authentication_type_desc, pe.state_desc,
    pe.permission_name, s.name + ‘.‘ + o.name AS ObjectName
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
JOIN sys.objects AS o
    ON pe.major_id = o.object_id
JOIN sys.schemas AS s
    ON o.schema_id = s.schema_id;

4.定义多个用户

CREATE USER U1 WITHOUT LOGIN ;
CREATE USER U2 WITHOUT LOGIN ;
CREATE USER U3 WITHOUT LOGIN ;
CREATE USER U4 WITHOUT LOGIN ;
CREATE USER U5 WITHOUT LOGIN ;

5.删除用户

--声明数据库引用
use database_name;
go
 
--判断是否存在用户自定义用户，如果存在则删除。
if exists(select * from sys.database_principals where name=user_name)
--把架构所有者修改回来架构自身
alter authorization on schema::[Architecture_name] to Architecture_name;
--删除角色拥有的成员
alter role [[Architecture_name] drop member user_name;
--删除扩展属性
exec sys.sp_dropextendedproperty @name=N‘tests_description‘, @level0type=N‘user‘,@level0name=N‘user_name‘
--删除用户架构
drop user user_name;
go

            
--database_name
--数据库名称
--user_name
--用户名称
--Architecture_name
--架构名称
--tests_description
--扩展属性名称

执行完命令之后，点击刷新便能够看到用户被删除。

给用户授权

授权、查，增，改的方法

use 学生
go
--分配权限
grant select,insert,Update on Students to U1
grant create table to U1

把对 Student表和 Course表的全部操作权限授予用户U2和U3。

SQL server的授权同时只能操作一个数据库对象（Object）

GRANT ALL PRIVILEGES ON OBJECT::S_T.Student TO U2,U3
GRANT ALL PRIVILEGES ON OBJECT::S_T.Course TO U2,U3

把对表SC的查询权限授予所有用户

GRANT SELECT ON SC TO PUBLIC

把查询Student表和修改学生学号权限授给用户U4

GRANT UPDATE(SNO),SELECT
ON Student
TO U4

验证有效性

BEGIN TRANSACTION;
EXECUTE AS USER=‘U4‘;
UPDATE student
SET SNO=‘1234‘
WHERE SNO=‘201215125‘;
REVERT;
SELECT * FROM student;
ROLLBACK;

把对表SC的 INSERT权限授予U5用户，并允许将此权限再授予其他用户。

GRANT INSERT
ON SC
TO U5
WITH GRANT OPTION

验证有效性

EXECUTE AS USER=‘U5‘;
INSERT INTO SC(SNO,CNO,GRADE) VALUES (201215125,4,98);
REVERT;
SELECT * FROM SC;

U5将INSERT的权限传播给U6，U6也可将INSERT的权力授予U7

EXECUTE AS USER =‘U5‘
GRANT INSERT
ON SC
TO U6
WITH GRANT OPTION
REVERT

EXECUTE AS USER =‘U6‘
GRANT INSERT
ON SC
TO U7
WITH GRANT OPTION
REVERT

权限回收

--把用户U4修改学生学号的权限收回
REVOKE UPDATE(SNO)
ON student
FROM U4

--收回所有用户对表SC的查询权限
REVOKE SELECT
ON SC
FROM PUBLIC

--把用户U5,U6,U7对SC表的INSERT权限收回
--使用CASCADE
REVOKE INSERT
ON SC
FROM U5 CASCADE

验证权限是否收回成功

EXECUTE AS USER=‘U4‘;
UPDATE student
SET SNO=‘1234‘
WHERE SNO=‘201215125‘;
REVERT;

EXECUTE AS USER=‘U1‘;
SELECT * FROM SC
REVERT;

EXECUTE AS USER=‘U5‘;
INSERT INTO SC(SNO,CNO,GRADE) VALUES (201215125,4,98);
REVERT;

定义实体完整性，删除实体完整性

实体完整性
- 实体完整性定义
  - CREAT TABLE中用PRIMARY KEY定义
  - 单属性构成的码有两种说明方法（PRIMARY KEY的位置和语法不同）：
    - 定义为列级约束条件
    - 定义为表记约束条件
  - 对多个属性构成的码只有一种说明方法
    - 定义为表级约束条件（PRIMARY KEY(Sno,Cno))
- 实体完整性检查和违约处理
  - 插入和更新时DBMS按照实体完整性规则进行自动检查
    - 由于全表扫描效率太低，便使用索引（B+树）

--创建表
CREAT TABLE COURSE(
CNO CHAR(4) NOT NULL
	PRIMARY KEY,
CNAME NCHAR(50),
CPNO CHAR(4),
ccredit smallint
)
--添加实体完整性
ALTER TABLE COURSE
	ADD CONSTRAINT COURSE_PK
	PRIMARY KEY(Cno);
--删除实体完整性
ALTER TABLE Course DROP CONSTRAINT PK__Course__C1fe6373BC8AAD0E

定义参照完整性，参照完整性违约处理，删除参照完整性

参照完整性
- 参照完整性定义
  - 一个关系的外码或者取空值或者等于它所参照的关系的主码值
  - 在CREAT TABLE中用FOREIGN KEY短语定义哪些列为外码
  - 用REFERENCES短语指明这些外码参照哪些表的主码
  - 表级定义、列级定义参照完整性，具体代码实例看资料
- 在参照完整性检查和违约处理
  - 对参照表和被参照表进行增删改操作时进行完整性检查和违约处理
  - 被参照表参照表违约处理
    
    插入元组拒绝
    
    修改外码值拒绝
    
    删除元组拒绝/级联删除/设置为空值
    
    修改主码值拒绝/级联修改/设置为空值
  - 拒绝执行（NO ACTION)
  - 级联操作（CASCADE)
  - 设置空值（SET-NULL)
  - ```
  示例代码
  CREAT TABLE SC
  (Sno CHAR(9) NOT NULL,
   Cno CHAR(4) NOT NULL,
   Grade SMALLINT,
   PRIMARY KEY(Sno,Cno),
   FOEREIGN KEY(Sno)REFERENCES Student(Sno)
   ON DELETE CASCADE  /*级联删除SC表中的相应的元组*/
   ON UPDATE CACADE,  /*级联更新相应的元组*/
   FOREIGN KEY (Cno)REFERENCES Course(Cno)
   ON DELETE NO ACTION/*当删除course表中的元组造成了与SC表不一致时拒绝删除*/
   ON UPDATE CASCADE
   /*当更新coourse表中的Cno时，级联更新SC表中相应的元组*/
   ）;
```

被参照表	参照表	违约处理
	插入元组	拒绝
	修改外码值	拒绝
删除元组		拒绝/级联删除/设置为空值
修改主码值		拒绝/级联修改/设置为空值

用户定义完整性

针对某一具体应用的数据必须满足的语义要求
CREAT TABLE 时定义属性上的约束条件
- 列值非空（NOT NULL)
- 列值唯一（UNIQUE)
- 检查列值是否满足一个条件表达式（CHECK)
```
CREAT TABLE DEPT
(
Deptno NUMERIC(2),
Dname CHAR(9) UNIQUE NOT NULL,
/*要求Dname列值唯一，并且不能取空值*/
PRIMARY KEY(Deptno)
);
```
  CHECK (条件)
属性上的约束条件检查和违约处理（只涉及单个属性）
元组上的约束条件定义（涉及多个属性）
- CHECK位置不同

	实体完整性	参照完整性	用户定义完整性
定义方法	CREAT TABLE	CREAT TABLE	CREAT TABLE
检查时机	执行插入、修改操作	参照表、插入/修改被参照表：删除/修改	执行插入、修改操作
违约处理	拒绝执行	拒绝执行/级联操作/设置为空值	拒绝执行

完整性约束名子句
- CONSTRAINT语句的用法，详见课本资料

CHECK约束

通过限制一个或多个列可接受的值，CHECK 约束可以强制域完整性。可以通过任何基于逻辑运算符返回 TRUE 或 FALSE 的逻辑（布尔）表达式创建 CHECK 约束。

用成绩来举例，成绩都有最大和最小值，为了避免出现负分，超分，应该给成绩列添加上CHECK约束

ALTER TABLE SC
ADD CONSTRAINT Check_for_grade
CHECK(0<= Grade AND Grade <=100)

验证约束是否生效

INSERT INTO SC(SNO,CNO,Grade) VALUES(201215125,6,120)

CHECK约束正常工作。

实验4数据库的安全性、完整性

码农公寓

实验4数据库的安全性、完整性

实验目的要求

实验主要内容

实验仪器设备

实验记录

安全性

创建和删除用户

给用户授权

授权、查，增，改的方法

把对 Student表和 Course表的全部操作权限授予用户U2和U3。

把对表SC的查询权限授予所有用户

把查询Student表和修改学生学号权限授给用户U4

把对表SC的 INSERT权限授予U5用户，并允许将此权限再授予其他用户。

U5将INSERT的权限传播给U6，U6也可将INSERT的权力授予U7

权限回收

定义实体完整性，删除实体完整性

定义参照完整性，参照完整性违约处理，删除参照完整性

相关文章