实验二 数据库安全性设计

一、实验要求

基于openGauss或GaussDB（for MySQL）完成特定数据库的安全性设计。

二、实验目的

了解并学会运用数据库的安全性技术

掌握用户、角色的创建及管理；

掌握用户权限的授予及回收；

掌握用户安全策略如何设置；

掌握用户视图的设计与实现方法。

三、实验步骤

1.针对特定的数据库进行用户与角色设计，并进行权限的授予

2.根据特定需求（自拟明确的需求）进行视图设计

3.撰写实验报告

四、实验报告内容

1.数据库描述

应用场景

本次实验使用的数据库是图书馆管理系统。

要求记录学生和图书的基本信息以及借阅图书、预定图书等相关信息，还要记录学生和其对应的类型、可借阅数量。在上次实验的基础上，针对本次实验的需求，进行了一些修改。

概念模型

逻辑模型

• 学生（学号，姓名，班级，类型号，用户名）

• 类型（类型号，类型名，可借数量）

• 书籍（在学校书库的编号，ISBN号，书名）

• 借阅（在学校书库的编号，借出时间，学号，归还时间）

• 预定（学号，ISBN号，预定时间，到库时间）

2.数据库安全性设计描述

角色权限设计：

• 借阅者：拥有查看book表的权限，以及查看学生自己的信息和没有借阅出去的书籍的权限，查阅当前用户的借阅的书籍以及预定的书籍视图的权限

• 超级管理员：拥有所有权限

• 图书馆前台：拥有book表的查阅，borrow表的查阅插入修改，reserve表的查阅插入权限，以及没有借出去的书籍的视图权限

• 图书管理员：拥有book表的查阅插入，reserve表的查阅插入权限

• 人员管理：student表的查阅插入修改删除权限

视图设计：

• 查看没有被借完的书籍
• 查看当前用户的所有信息
• 查看当前用户已经借阅的书籍记录
• 查看当前用户的已经预定的书籍记录

3.安全性的实现过程和验证过程

由于实际验证截图太多，在这里不多截取，具体权限可以通过命令：

SELECT * FROM information_schema.table_privileges WHERE table_schema='library';进行查看，因此在实验报告中验证用户权限部分，我放置的只有各个角色对应用户的权限表。

用户角色创建

CREATE ROLE borrower PASSWORD 'OpenGauss@123';
CREATE ROLE pub PASSWORD 'OpenGauss@123';
CREATE ROLE root PASSWORD 'OpenGauss@123';
CREATE ROLE bookmanage PASSWORD 'OpenGauss@123';
CREATE ROLE humanmanage PASSWORD 'OpenGauss@123';
CREATE USER superadmin PASSWORD 'OpenGauss@123';
CREATE USER lzh PASSWORD 'OpenGauss@123';
CREATE USER ssh PASSWORD 'OpenGauss@123';
CREATE USER pub1 PASSWORD 'OpenGauss@123';
CREATE USER bookadmin PASSWORD 'OpenGauss@123';
CREATE USER humanadmin PASSWORD 'OpenGauss@123';

GRANT ALL PRIVILEGES
ON SCHEMA library
TO borrower,pub,root,bookmanage,humanmanage;

GRANT ALL PRIVILEGES
ON SCHEMA library
TO lzh,admin,pub1,bookadmin,humanadmin,ssh,root;

赋予角色借阅者查看book表的权限，以及查看学生自己的信息和没有借阅出去的书籍的权限，查阅当前用户的借阅的书籍以及预定的书籍视图的权限。

GRANT SELECT
ON TABLE library.book
TO borrower;

GRANT SELECT 
ON library.not_borrowed_book,library.user_info,library.user_borrow,library.user_reserve
to borrower;

赋予角色超级管理员所有权限

GRANT ALL PRIVILEGES 
ON ALL TABLES 
IN SCHEMA library 
to root;

GRANT SELECT
ON library.not_borrowed_book,library.user_info,library.user_borrow,library.user_reserve
to root;

赋予图书馆前台，book表的查阅，borrow表的查阅插入，修改return_time，reserve表的查阅插入权限，以及没有借出去的书籍的视图权限

GRANT SELECT
ON TABLE book,borrow,reserve
TO pub;

GRANT INSERT
ON TABLE borrow,reserve
TO pub;

GRANT UPDATE
ON TABLE borrow
TO pub;

GRANT ALL PRIVILEGES
ON library.not_borrowed_book
to pub;

赋予图书管理员，book表的查阅插入，reserve表的查阅插入权限

GRANT SELECT,INSERT
ON TABLE book,reserve
TO bookmanage;

赋予人员管理，student表的查阅插入修改删除权限

GRANT ALL PRIVILEGES
ON TABLE student
TO humanmanage;

视图创建

查看没有被借完的书籍

CREATE VIEW not_borrowed_book AS
select * 
from book 
except(
	select book.* from borrow,book 
	WHERE book.book_id=borrow.book_id and borrow.return_time is null 
	GROUP BY book.book_id
	HAVING COUNT(book.book_id)=book.qty);

查看当前用户的所有信息

CREATE VIEW user_info AS
SELECT
 student.stu_id,
 student.stu_name,
 student.class_id,
 stutype.stu_type_name,
 stutype.borrow_qty 
FROM
 student,
 stutype 
WHERE
 student.stu_name = CURRENT_USER 
 AND student.stu_type_id = stutype.stu_type_id;

查看当前用户已经借阅的书籍记录

CREATE VIEW user_borrow AS
SELECT
 borrow.book_id,
 borrow.borrow_time,
 borrow.return_time 
FROM
 student,
 borrow 
WHERE
 student.stu_name = CURRENT_USER 
 AND borrow.stu_id = student.stu_id;

查看当前用户的已经预定的书籍记录

CREATE VIEW user_reserve AS
SELECT
 reserve.isbn,
 reserve.reserve_time,
 reserve.arrival_time 
FROM
 student,
 reserve 
WHERE
 student.stu_name = CURRENT_USER 
 AND reserve.stu_id = student.stu_id;

赋予用户admin超级管理员角色

GRANT root
TO admin with admin option;

• 超级用户拥有对所有表的所有权限

超级用户权限表：

grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy wpy root myteach library stutype INSERT NO NO wpy root myteach library stutype SELECT NO YES wpy root myteach library stutype UPDATE NO NO wpy root myteach library stutype DELETE NO NO wpy root myteach library stutype TRUNCATE NO NO wpy root myteach library stutype REFERENCES NO NO wpy root myteach library stutype TRIGGER NO NO wpy root myteach library stutype ALTER NO NO wpy root myteach library stutype DROP NO NO wpy root myteach library stutype COMMENT NO NO wpy root myteach library stutype INDEX NO NO wpy root myteach library stutype VACUUM NO NO wpy root myteach library book INSERT NO NO wpy root myteach library book SELECT NO YES wpy root myteach library book UPDATE NO NO wpy root myteach library book DELETE NO NO wpy root myteach library book TRUNCATE NO NO wpy root myteach library book REFERENCES NO NO wpy root myteach library book TRIGGER NO NO wpy root myteach library book ALTER NO NO wpy root myteach library book DROP NO NO wpy root myteach library book COMMENT NO NO wpy root myteach library book INDEX NO NO wpy root myteach library book VACUUM NO NO wpy root myteach library reserve INSERT NO NO wpy root myteach library reserve SELECT NO YES wpy root myteach library reserve UPDATE NO NO wpy root myteach library reserve DELETE NO NO wpy root myteach library reserve TRUNCATE NO NO wpy root myteach library reserve REFERENCES NO NO wpy root myteach library reserve TRIGGER NO NO wpy root myteach library reserve ALTER NO NO wpy root myteach library reserve DROP NO NO wpy root myteach library reserve COMMENT NO NO wpy root myteach library reserve INDEX NO NO wpy root myteach library reserve VACUUM NO NO wpy root myteach library borrow INSERT NO NO wpy root myteach library borrow SELECT NO YES wpy root myteach library borrow UPDATE NO NO wpy root myteach library borrow DELETE NO NO wpy root myteach library borrow TRUNCATE NO NO wpy root myteach library borrow REFERENCES NO NO wpy root myteach library borrow TRIGGER NO NO wpy root myteach library borrow ALTER NO NO wpy root myteach library borrow DROP NO NO wpy root myteach library borrow COMMENT NO NO wpy root myteach library borrow INDEX NO NO wpy root myteach library borrow VACUUM NO NO wpy root myteach library student INSERT NO NO wpy root myteach library student SELECT NO YES wpy root myteach library student UPDATE NO NO wpy root myteach library student DELETE NO NO wpy root myteach library student TRUNCATE NO NO wpy root myteach library student REFERENCES NO NO wpy root myteach library student TRIGGER NO NO wpy root myteach library student ALTER NO NO wpy root myteach library student DROP NO NO wpy root myteach library student COMMENT NO NO wpy root myteach library student INDEX NO NO wpy root myteach library student VACUUM NO NO wpy root myteach library not_borrowed_book INSERT NO NO wpy root myteach library not_borrowed_book SELECT NO YES wpy root myteach library not_borrowed_book UPDATE NO NO wpy root myteach library not_borrowed_book DELETE NO NO wpy root myteach library not_borrowed_book TRUNCATE NO NO wpy root myteach library not_borrowed_book REFERENCES NO NO wpy root myteach library not_borrowed_book TRIGGER NO NO wpy root myteach library not_borrowed_book ALTER NO NO wpy root myteach library not_borrowed_book DROP NO NO wpy root myteach library not_borrowed_book COMMENT NO NO wpy root myteach library not_borrowed_book INDEX NO NO wpy root myteach library not_borrowed_book VACUUM NO NO wpy root myteach library user_info INSERT NO NO wpy root myteach library user_info SELECT NO YES wpy root myteach library user_info UPDATE NO NO wpy root myteach library user_info DELETE NO NO wpy root myteach library user_info TRUNCATE NO NO wpy root myteach library user_info REFERENCES NO NO wpy root myteach library user_info TRIGGER NO NO wpy root myteach library user_info ALTER NO NO wpy root myteach library user_info DROP NO NO wpy root myteach library user_info COMMENT NO NO wpy root myteach library user_info INDEX NO NO wpy root myteach library user_info VACUUM NO NO

赋予用户lzh,ssh借阅者权限以及视图权限

GRANT borrower
TO lzh,ssh with admin option;

• 可以正常查阅book表

• 没有借完的书籍视图

• 当前用户信息视图

• 自己预定以及借阅的书籍视图

• 对剩下的其他表没有操作权限

借阅者用户权限表：

grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy wpy borrower myteach library book SELECT NO YES wpy borrower myteach library not_borrowed_book INSERT NO NO wpy borrower myteach library not_borrowed_book SELECT NO YES wpy borrower myteach library not_borrowed_book UPDATE NO NO wpy borrower myteach library not_borrowed_book DELETE NO NO wpy borrower myteach library not_borrowed_book TRUNCATE NO NO wpy borrower myteach library not_borrowed_book REFERENCES NO NO wpy borrower myteach library not_borrowed_book TRIGGER NO NO wpy borrower myteach library not_borrowed_book ALTER NO NO wpy borrower myteach library not_borrowed_book DROP NO NO wpy borrower myteach library not_borrowed_book COMMENT NO NO wpy borrower myteach library not_borrowed_book INDEX NO NO wpy borrower myteach library not_borrowed_book VACUUM NO NO wpy borrower myteach library user_info SELECT NO YES wpy borrower myteach library user_borrow SELECT NO YES wpy borrower myteach library user_reserve SELECT NO YES

赋予用户pub1图书馆前台权限

GRANT pub
TO pub1 with admin option;

pub1权限表：

grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy wpy pub myteach library book SELECT NO YES wpy pub myteach library reserve INSERT NO NO wpy pub myteach library reserve SELECT NO YES wpy pub myteach library borrow INSERT NO NO wpy pub myteach library borrow SELECT NO YES wpy pub myteach library borrow UPDATE NO NO wpy pub myteach library not_borrowed_book INSERT NO NO wpy pub myteach library not_borrowed_book SELECT NO YES wpy pub myteach library not_borrowed_book UPDATE NO NO wpy pub myteach library not_borrowed_book DELETE NO NO wpy pub myteach library not_borrowed_book TRUNCATE NO NO wpy pub myteach library not_borrowed_book REFERENCES NO NO wpy pub myteach library not_borrowed_book TRIGGER NO NO wpy pub myteach library not_borrowed_book ALTER NO NO wpy pub myteach library not_borrowed_book DROP NO NO wpy pub myteach library not_borrowed_book COMMENT NO NO wpy pub myteach library not_borrowed_book INDEX NO NO wpy pub myteach library not_borrowed_book VACUUM NO NO

赋予用户humanadmin人员管理权限

GRANT humanmanage
TO humanadmin with admin option;

humanadmin权限表：

grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy wpy humanmanage myteach library student INSERT NO NO wpy humanmanage myteach library student SELECT NO YES wpy humanmanage myteach library student UPDATE NO NO wpy humanmanage myteach library student DELETE NO NO wpy humanmanage myteach library student TRUNCATE NO NO wpy humanmanage myteach library student REFERENCES NO NO wpy humanmanage myteach library student TRIGGER NO NO wpy humanmanage myteach library student ALTER NO NO wpy humanmanage myteach library student DROP NO NO wpy humanmanage myteach library student COMMENT NO NO wpy humanmanage myteach library student INDEX NO NO wpy humanmanage myteach library student VACUUM NO NO

赋予用户bookadmin图书管理权限

GRANT bookmanage
TO bookadmin with admin option;

bookadmin权限表：

grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy wpy bookmanage myteach library book INSERT NO NO wpy bookmanage myteach library book SELECT NO YES wpy bookmanage myteach library reserve INSERT NO NO wpy bookmanage myteach library reserve SELECT NO YES

4.问题分析

​ 实验过程中出现了关于视图设计的内容，这一部分在构思的时候，一开始想的是，让每一个学生在登陆图书借阅系统之后，都可以查看自己借书的信息，预定书籍信息，以及自己的学生信息。但是一开始的思路是，每一个学生单独创建一个视图，但是这不仅复杂，而且麻烦，所以经过查阅资料，发现了一个很有用的current_user，即当前数据库登录用户，所以我们基于这个current_user，将数据库登录用户名与学生姓名进行联系，最终实现了每一个学生都只能查看自己的学生信息，自己的书籍预约和借阅信息。但是问题就是opengauss数据库创建用户好像不支持中文，而且current_user的用法也不支持中文，所以只能使用姓名首字母缩写来代替学生姓名以及创建数据库用户名称。

​ 还有就是我觉得是Nevicat的本身存在的bug，在我使用timestamp类型作为borrow表中的borrow_time和return_time时，一开始是不管在where后面加入什么样的条件，都能查出所有结果，但是后面重新连接之后，就又发现正常了，到现在也不明白到底怎么回事。

5.实验感想

​ 这次实验多少还是有点难度，难度具体体现在需要自己构思角色，并且根据自己对这个图书馆借阅管理系统的理解为每个角色设置权限。还要自己设计视图，因此实验的大部分时间都在构思，整个的角色创建，用户授权，视图创建大概只花了不到一个小时。其次就是对权限进行验证，虽然不难，但是很麻烦，因为如果从严谨的角度来说，肯定是要多建几个新的用户，进行授权之后，还要登录每个新用户，测试自己设置的权限到底有没有生效。我觉的这很麻烦，于是我只是在登录每个用户之后，使用权限查询语句单独查询每个用户的权限进行验证，这样会方便很多。

​ 这次实验，我们使用的数据库只是一个，非常简陋的，非常简单的数据库，简单到其实视图都没有存在的必要，只需要设置权限就可以实现自己构思出的大部分需求。但是这也让我想到，仅仅是这么简单的一个数据库，我们在构思用户，构思权限的时候，就已经花费了非常多的时间，如果真的在现实生活中的中小型数据库，一个库里面有几十上百张表，一张表里有几十个属性，成千上万条记录。我们又应该如何构思角色，如何构思权限。