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

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

实验目的要求

  1. 掌握自主存取控制权限的定义和维护方法；

  2. 掌握实体完整性的定义和维护方法；

  3. 掌握参照完整性的定义和维护方法；

  4. 掌握用户自定义完整性的定义和维护方法。

实验主要内容

  1. 定义用户、角色，分配权限给用户、角色，回收权限，以相应的用户名登录数据库验证权限分配是否正确。选择一个应用场景，使用自主存取控制机制设计权限分配。可以采用两种方案。方案一：采用sa超级用户登录数据库，完成所有权限分配工作，然后用相应用户名登陆数据库以验证权限分配正确性；方案二：采用sa用户登陆数据库创建三个部门经理用户，并分配相应的权限，然后分别用三个经理用户名登陆数据库，创建相应部门的USER, ROLE，并分配相应权限。验证权限分配之前，请备份好数据库；针对不同用户所具有的权限，分别设计相应的SQL语句加以验证。

  2. 定义实体完整性，删除实体完整性。能够写出两种方式定义实体完整性的SQL语句：创建表时定义实体完整性、创建表后定义实体完整性。设计SQL语句验证完整性约束是否起作用。

  3. 定义参照完整性，定义参照完整性的违约处理，删除参照完整性。写出两种方式定义参照完整性的SQL语句：创建表时定义参照完整性、创建表后定义参照完整性。

  4. 针对具体应用语义，选择NULL/NOT NULL、DEFAULT，UNIQUE、CHECK等，定义属性上的约束条件。

实验仪器设备

  1. 学生每个一台PC机

  2. 已安装SQL Server环境

实验记录

安全性

创建和删除用户

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

  2. 打开【新建查询】,输入

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

    创建多个不用登录的用户

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

    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约束正常工作。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Weber-security/p/13168136.html