实验二 数据库安全性与完整性设计与实践
20201331黄文刚
一、实验目的
1．系统梳理常规的数据库安全性与完整性技术；
2．了解所选用DBMS的安全性控制和完整性约束机制；
3．能够在特定的DBMS上进行具体实践。
二、实验要求
1．能够根据特定的应用进行基于应用场景的安全性与完整性设计，并落实在具体的DBMS中；
2．能够进行用户、角色的创建及管理；
3．能够进行用户、角色的权限授予及回收；
4．能够利用视图进行数据库安全性设计；
5．能够对特定应用的数据库完整性有明确的设计；
6．能够在特定的DBMS中设计并实现触发器。
三、实验步骤
1．针对实验一中的数据库进行存取访问控制设计，设计用户与角色，并进行权限授予，在openGauss上实现；
2．根据特定需求（自拟明确的需求）进行视图设计并在openGauss上实现；
3．适当考虑其他的数据库安全性设计方案，并在openGauss上验证；
4．针对特定的数据库进行完整性分析与设计，补充必要的完整性设计与基于openGauss的实现，其中至少应包括一个触发器；
5．撰写实验报告。
四、实验报告内容
1．数据库描述
根据需求描述和ER图：
转化后：（主码用加粗标识出）
staff（name、birth、num、departmentnum、gender、password）
department（departmentnum、department_departmentnum、departmentname、number、departmenttype）
duty（dutynum、dutyname）
rank （ranknum、rankname）
employ（num、ranknum、employtime）
serve（num、dutynum、worktime）
have（departmentnum、ranknum）
have2（departmentnum、dutynum）
中文版：
员工（ 姓名、出生年月、编号、部门编号、性别、口令）
部门（部门编号、部门_部门编号、部门名称、编制人数、部门类型）
职务（职务编号、职务名称）
职级（职级编号、职级名称）
聘用（编号、职级编号、聘用时间）
担任（编号、职务编号、任职时间）
拥有（部门编号、职级编号）
拥有2（部门编号、职务编号）
随后，根据实验一一系列复杂操作（此处不再赘述），建好了数据库系统
2．数据库安全性设计描述
给出针对上述数据库的安全性设计，描述分成哪些用户、有哪些角色、授予哪些权限等，是否有视图的设计、如何设计等；
一．安全性设计：
系统整体安全性设计
对于以员工部门为管理需求的数据库应用系统来说，95%的用户和访问来自公司内部，所以从应用安全性角度考虑，我们将根据IP地址进行甄别，仅允许公司内部的连接。
应用前端安全性设计
应用前端角色分类：普通员工，总管理员，人事处，考勤处，部门管理处
1.普通员工：拥有查看自己的员工信息的权限,以及查看所有其他员工姓名、编号的权限
2.总管理员：拥有所有权限
3.人事处：拥有员工表+聘用表+担任表的查阅插入修改删除权限、部门表+职务表+职级表查阅权限
4.考勤处：拥有查看员工表+部门表的权限，以及对未到考勤的员工的视图+考勤状态视图的查阅插入修改删除权限
5.部门管理处：拥有对部门表的查阅插入修改删除权限
二．视图的设计
视图设计：
1.查看当前员工的所有信息
2.查看所有员工的编号、姓名
3.查看当前员工的签到记录（签到成功或未签到）
4.查看未打考勤的员工
5.查看所有员工任职状态
我设计的系统需要新建一个关系：timecard，签到表
create table timecard (
name VARCHAR(256) not null,
num VARCHAR(256) not null,
tcstate VARCHAR(256) null,
constraint PK_timecard primary key (num,name )
);
create unique index timecard_PK on timecard (
name);
alter table timecard
add constraint FK_timecard_RELATIONS_staff foreign key (num)
references staff (num)
on delete restrict on update restrict;
并为其设计好数据：
INSERT INTO timecard VALUES('liuyan','111111111','1');
INSERT INTO timecard VALUES('liuzhang','222222222','0');
INSERT INTO timecard VALUES('liubiao','333333333','1');
INSERT INTO timecard VALUES('liubei','444444444','1');
INSERT INTO timecard VALUES('liushan','555555555','0');
INSERT INTO timecard VALUES('liuchen','666666666','1');
INSERT INTO timecard VALUES('liuyao','777777777','1');
INSERT INTO timecard VALUES('liuba','888888888','0');
三．创建相关用户、角色
创建角色：
1.普通员工：
CREATE ROLE normalworker PASSWORD 'fxyloveh14!';
2.总管理员：
CREATE ROLE root PASSWORD 'fxyloveh14!';
3.人事处：
CREATE ROLE staffdepartment PASSWORD 'fxyloveh14!';
4.考勤处：
CREATE ROLE checker PASSWORD 'fxyloveh14!';
5.部门管理处：
CREATE ROLE departmentmanage PASSWORD 'fxyloveh14!';
创建用户：
CREATE USER superadmin PASSWORD 'fxyloveh14!';
CREATE USER liuyan PASSWORD 'fxyloveh14!';
CREATE USER liubiao PASSWORD 'fxyloveh14!';
CREATE USER liuba PASSWORD 'fxyloveh14!';
CREATE USER staffer PASSWORD 'fxyloveh14!';
CREATE USER departmentadmin PASSWORD 'fxyloveh14!';
CREATE USER checkeradmin PASSWORD 'fxyloveh14!';
四．创建相关视图
创建视图：
1.查看当前员工的所有信息（以编号为标准）
CREATE VIEW staff_look AS
SELECT staff.*
FROM staff
WHERE
staff.num = CURRENT_USER;
2.查看所有员工的姓名、编号
CREATE VIEW staff_look_normal AS
SELECT staff.name,staff.num
FROM staff;
3.查看当前员工的签到状态
CREATE VIEW timecard_look AS
SELECT timecard.*
FROM timecard
WHERE timecard.num = CURRENT_USER;
4.查看未打考勤（签到状态为0）的员工姓名、编号
CREATE VIEW timecard_look AS
SELECT timecard.*
FROM timecard
WHERE timecard.tcstate = 0;
五．赋予角色权限
赋予角色权限：
1.赋予普通员工权限：
GRANT SELECT
ON staff_look
To normalworker;
GRANT SELECT
ON staff_look_normal
To normalworker;
2.赋予总管理员权限：
GRANT ALL PRIVILEGES
ON ALL TABLES
IN SCHEMA public
To root;
3.赋予人事处权限：
GRANT ALL PRIVILEGES
ON TABLE staff
to staffdepartment;
GRANT ALL PRIVILEGES
ON TABLE employ
to staffdepartment;
GRANT ALL PRIVILEGES
ON TABLE serve
to staffdepartment;
GRANT SELECT
ON TABLE department,duty,rank
to staffdepartment;
（原来可以一次赋予多个表的权限呀，我都不知道）
4.赋予考勤处权限
GRANT SELECT
ON TABLE staff,department
to checker;
GRANT ALL PRIVILEGES
ON timecard_look_0,timecard_look
to checker;
5.赋予部门管理处权限
GRANT ALL PRIVILEGES
ON department
to departmentmanage;
3．安全性的实现过程和验证过程
按照安全性设计给出用户、角色、权限、视图等的创建语句，并给出验证效果的截图；
一．普通员工：拥有查看自己的员工信息的权限,以及查看所有其他员工姓名、编号的权限
先将角色normalworker赋予用户liuyan，liubiao，liuba：
GRANT normalworker
To liuba,liuyan,liubiao WITH ADMIN OPTION:
首先先看下能不能查看表staff而不是视图staff_look：
SELECT *
from staff;
不能就对辣,因为没权限，完美
再试下能不能查看视图staff_look，结果应该只有liuyan自己的全部员工信息:
SELECT *
from staff_look;
完美！
最后试下能不能看staff_look_normal，并且结果应该是显示所有其他员工的姓名和编号：
SELECT *
from staff_look_normal;
泰裤辣！
看下角色权限表：
SELECT grantee, string_agg( privilege_type,', ' ) as privilege_type
FROM information_schema.role_table_grants
WHERE table_name='staff_look' #另一个改成staff_look_normal
即可
group by grantee
二．总管理员：拥有所有权限
先把角色赋予superadmin：
GRANT root
TO superadmin with admin option;
然后看下角色权限表：
SELECT grantee, string_agg( privilege_type,', ' ) as privilege_type
FROM information_schema.role_table_grants
WHERE table_name='staff'
group by grantee
这句命令只能查看某个表的哪些角色拥有什么权限
三．人事处：拥有员工表+聘用表+担任表的查阅插入修改删除权限、部门表+职务表+职级表查阅权限
GRANT staffdepartment
TO staffer with admin option;
接下来看下他的权限都能不能用、可不可以越权：
SELECT *
FROM staff;
INSERT
INTO staff
VALUES
('huangwengang','2002.6.16','20201331','001','man','616');
插入后：
UPDATE staff
SET password='161'
WHERE num='20201331';
修改后：
DELETE
FROM staff
WHERE num='20201331';
删除后：
剩下两个表权限一致，测试省略。再试下对department表的操作：
SELECT *
FROM department;
INSERT
INTO department
VALUES ('005','005','xiandai','2023','zhongguo');
可以查询，但不能插入，同理想必也不能修改、删除
其余两个表也同理，测试步骤省略
最后看下表的权限：
SELECT grantee, string_agg( privilege_type,', ' ) as privilege_type
FROM information_schema.role_table_grants
WHERE table_name='staff'
group by grantee
四．考勤处：拥有查看员工表+部门表的权限，以及对未到考勤的员工的视图的查阅插入修改删除权限
先把角色checker的权限赋予用户checkeradmin
GRANT checker
TO checkeradmin with admin option;
接下来看下他的权限都能不能用、可不可以越权。
先试下对视图timecard_look的权限：
SELECT *
FROM timecard_look;
接下来试图插入、删除视图，但是，
好吧，视图是不能修改的（应该是），所以待会我们看下用户权限表即可
再试下对表staff的权限：
SELECT *
FROM staff;
INSERT
INTO staff
VALUES
('huangwengang','2002.6.16','20201331','001','man','616');
也没问题，可以查询，别的都不行
最后查下表的用户权限分配：
SELECT grantee, string_agg( privilege_type,', ' ) as privilege_type
FROM information_schema.role_table_grants
WHERE table_name='timecard_look' #下一个改成staff
group by grantee
都没有问题
五．部门管理处：拥有对部门表的查阅插入修改删除权限
先把角色departmentmanage的权限赋予用户departmentadmin：
GRANT departmentmanage
TO departmentadmin with admin option;
Ok，接下来测试权限：
SELECT *
FROM department;
插入数据：
INSERT
INTO department
VALUES ('005','005','xiandai','2023','zhongguo');
插入数据后：
更新数据：
UPDATE department
SET number='2023.5.22'
WHERE department_departmentnum='005';
更新数据后：
删除操作：
DELETE
FROM department
WHERE department_departmentnum='005';
删除后数据：
权限没问题
再试下能不能越权：
访问员工表：
明显越权也是不可以的
最后看一下department表的用户权限分配：
没问题，顺带一提，要是查询没有权限的表的权限分配，就是这结果：
至此，安全性的实现过程全部呈现
4．数据库完整性设计描述
实体完整性：
主键不能为空或者未知值，在本次实验中所有表的主键都有值不为空并且没有重复，因此实体完整性没问题。
参照完整性：
参照的关系中的属性值必须能够在被参照关系找到或者取空值，否则不符合数据库的语义。在本次实验中所有用到其他表内属性的值都有值或者为空，因此参照完整性没问题。
用户定义完整性:
因为在本次实验中，本数据库内并没有特别设定的数值限制，因此用户定义体完整性没问题。
5．完整性设计的具体实现和验证过程
在本次实验中，对于完整性的设计主要体现在主键上，基本上每个表的主键都是不重复的。
因为每个表都未取空值，列值唯一，因此完整性是得以保证的。前面已多次做过完整性验证，在此处便不多加以赘述。
触发器的实现：
创建函数：
CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
IF (new.departmentnum='002') AND(new.password < 1000) THEN
new.password :=1000;
END IF;
return new;
END
$$ LANGUAGE PLPGSQL;
创建触发器：
CREATE TRIGGER insert_trigger
BEFORE INSERT ON staff
FOR EACH ROW
EXECUTE PROCEDURE tri_insert func();
这里出问题是因为触发器和函数不能写到一起
分开就可以了
运行测试
INSERT INTO staff(name,birth,num,departmentnum,gender,password) VALUES('liudehua','1961.9','999999999','002','man','999');
SELECT *
FROM staff;
6．问题分析
1.前端应用权限控制和数据库用户权限控制混淆
一开始进行实验时，我立马投入到应用中各个角色的权限设计中，并开始思考将哪些表的哪些字段暴露给某个角色。但当我想到为每一个应用用户创建一个数据库用户时，我意识到我这里概念理解上出现了错误，数据库用户应该只是对数据进行操作时进行权限控制，如查或增删改，而不去考虑应用中存在的几类参与者，前端应用应根据具体业务和相应的安全性需求通过数据库用户获取数据再根据应用内的角色划分进行权限控制。最终实现我的上述方案。
2.对于我上述数据库安全性设计来说用户权限有部分重叠。这里的权限重叠实际上是无可避免的，更高权限的admin管理员需通过认证，实际上可能造成的安全危害要写很多。
3.关于数据库管理员的问题
在上述方案中，数据库管理员对应前端应用超级管理员，他应该具备调整业务逻辑的能力（也就是创建表、修改表、删除表），并且能够对两类业务管理员进行管理。所以这里的数据库管理员应对应数据库的拥有者，应该是全局唯一的。
4.本方案中前端应用和数据库关系的思考
在本方案中实际上是把从数据库返回的数据在应用前端进行权限控制，绝大多数工作再前端应用，应用所对应的数据库用户只有两个。应用按照用户权限划分和具体业务通过对应的数据库用户进行操作，再对返回的数据进行分割将用户权限范围内的信息进行返回。
7．实验感想
现实中绝大多数的应用系统都使用数据库，本以为一些应用系统本身没什么价值，但系统中的数据和用户数据才是价值所在，这些信息都被存储在数据库中。那么数据库安全性就显得尤为重要了，一般保护数据库的方式有两种一种是访问控制，一种是数据加密。通过本实验我们深入学习和探索了应用系统中的访问控制，但我们一系列的设计仅仅实现了自主存取控制，所有的权限是面向用户的。这种级别的访问控制可能仍不能满足一些应用场景，比如电子政务系统，军事保密系统，这些实行分级保护的信息，我们应该对信息本身进行访问控制，也就是实施强制访问控制，通过上都下写的原则保护信息本身，达到更高等级的保护。未来我们或许将AI技术融入数据库中，甄别正常业务操作，对于破坏性操作和非法操作发出告警和拦截。
在本次实验中我们应该把应用当作数据库的对接对象，而不是应用中的用户，更多的工作应该转移到前端应用上（比如权限划分，用户最终的操作和显示等），这样才能达到应用系统的高效运转，也避免了对数据库的过多连接，极大的避免了由数据库连接过多产生的错误。