实验二——数据库安全性定义与检查

一、 实验题目

数据库安全性定义与检查

二、 实验内容和要求

安全性实验包含两个实验项目，其中1个为必修，1个为选修。自主存取控制实验为设计型实验项目，审计实验为验证型实验项目。两个实验的实验目的和内容如下：

1. 掌握自主存取控制权限的定义和维护方法。
2. 定义用户、角色，分配权限给用户、角色，回收权限，以相应的用户名登录数据库验证权限分配是否正确。选择一个应用场景，使用自主存取控制机制设计权限分配。
3. 掌握数据库审计的设置和管理方法，以便监控数据库操作，维护数据库安全。
4. 打开数据库审计开关。以具有审计权限的用户登陆数据库，设置审计权限，然后以普通用户登录数据库，执行相应的数据操纵SQL语句，验证相应审计设置是否生效，最后再以具有审计权限的用户登录数据库，查看是否存在相应的审计信息。

三、 实验步骤

（一） 自主存取控制实验实验

定义用户、角色，分配权限给用户、角色，回收权限，以相应的用户名登录数据库验证权限分配是否正确。选择一个应用场景，使用自主存取控制机制设计权限分配。
实验重点：定义角色，分配权限和回收权限。
实验难点：实验方案二实现权限的再分配和回收。

1. 方案一

采用SYSTEM超级用户登录数据库，完成所有权限分配工作，然后用相应用户名登陆数据库以验证权限分配正确性；
1.1. 查看SYSTEM超级用户‘root’的权限


SYSTEM超级用户root含有所有的权限；

1.2. 定义用户与删除用户

使用CREATE USER进行用户的创建，使用IDENTIFIED BY语句设定用户的登陆密码。
使用SHOW GRANTS FOR语句来进行用户权限的检查，结果如下，新创建的用户U1没有任何权限。

删除用户的语句为DROP USER <用户名>，如下：


注：对创建数据库模式一类的数据库对象的授权由管理员在创建用户时指定，未指定时默认为CONNECT权限，无法创建模式和基本表（因为没必要创建基本表因此这里默认使用CONNECT权限）。
1.3. 定义角色、给角色授权与角色权限收回

使用CREATE ROLE语句来进行角色的创建，使用GRANT语句来将权限给予角色；
使用SHOW GRANTS来展示角色的权限：


使用REVOKE语句可以收回赋予给ROLE的权限：

可以看到此时INSERT权限从ROLE中回收；

1.4. 赋予用户权限（授予权限与授予角色）

授权可以通过两种方式，分别为直接授权以及授予角色，其中角色是权限的集合；
执行第11条语句后，观察U1用户的权限：

执行第12条语句后，观察U1用户的权限：

1.5. 验证权限分配是否正确
我们已知将R1角色（含UPDATE权限）以及权限SELECT赋予给了U1用户；此时登录用户U1进行测试：

由于我们仅给U1对于student表的相关权限，因此用户U1仅能看到表student的信息，其余表都是透明的；

1.5.1. 验证权限是否分配正确
U1用户具有权限SELETE、角色R1，分别验证如下；
① SELECT验证


② 角色R1验证
由于角色R1中仅含有UPDATE权限，因此直接验证即可：

然而此时出现错误如下：

可以看到，UPDATE权限并没有给用户U1！
经过仔细的查找资料，最终在手册中找到原因，MySQL的角色创建后是需要进行激活，然后里面的权限才可以使用！
因此，进行角色R1的激活（需要使用角色的用户激活）

此时我们再次进行更新：

同时可以看出更新操作成功！


1.5.2. 验证用户是否会越权
我们的U1仅仅含有SELECT与UPDATE权限，因此，如果进行插入与删除将会无法执行；
① INSERT


从结果可以看出U1没有插入权限！
② DELETE


从结果可以看出U1没有删除权限！
③ 建表
正如刚才所提的，创建的用户默认为CONNECT模式，该模式下我们的用户无法创建模式、基本表。


无法创建，可以看出用户U1的数据库模式正常。
1.6. 权限回收测试
U1此时含有R1角色（含UPDATE权限），以及SELETE权限；
① 回收R1角色中的UPDATE权限：

需要超级用户进行
再次进行更新年龄操作：

此时用户U1进行UPDATE将不会成功：

② 回收权限SELECT

再次使用SELECT操作：

2. 方案二

采用SYSTEM用户登录数据库创建三个部门经理用户，并分配相应的权限，然后分别用三个经理用户名登录数据库，创建相应部门的USER, ROLE，并分配相应权限。

根据实际情况，使用Mysql的一个示例数据库employees，其中包含多个表，是一个公司员工数据库的简单示例（但有大量数据），我们下面仅仅使用其中的salaries（工资表），employees（员工表）；
我们假设有两个部门，财务部（后面缩写为Finance）专门管理工资表，有修改工资的权限；人事部（HR）管理员工表，有删除员工、加入员工、更新员工信息的权限；
2.1. 创建部门经理用户
这里因为部门经理需要创建用户，因此需要创建用户的权限；
给与创建用户的权限有两种方式，一种是在创建用户时使用With DBA模式，DBA模式下的用户为超级模式（书中介绍的方法），另一种方法是在创建用户后，使用Grant语句更新该用户的权限列表中的Create_user_priv权限。

如图，使用的更改权限方法，相比超级模式DBA安全一些。
2.2. 创建角色并角色授予部门经理

如图，将插入、更新工资表的权限给与角色R_FIN后给财务部门经理FINANCE，将插入、更新、删除员工表的权限给角色R_Hr后给与人事部门经理HR；
为了使得该权限可以由经理赋给用户，需要将上with grant option选项。

展示两者此时权限：


同样的，注意激活两个角色！

2.3. 部门经理创建相应的用户


两个部门分别创建一个普通Hr_U、Fin_U；

2.4. 部门经理给与自己创建的用户权限


其中，Fin_U对表salaries拥有SELECT、INSERT权，但是没有DELETE权，且UPDATE的权利仅能在字段salary上；
Hr_U拥有对employees的SELECT、INSERT、DELETE以及UPDATE字段first_name、last_name的权限。
检查最后两个用户的权限：

2.5. 检查权限
① Employees—HR_U检查


（SELETE正常）

（UPDATE字段正常）


（DELETE正常）

（INSERT正常）
给予的权限均正常；


当我们越权更新（不能更新的字段）会失败。

② Salaries—FIN_U检查

除了我们给予的权限外，还有其它的操作是无权进行的：

更新to_date、delect、select employees表都是非法的；


2.6. 回收权限
这里直接将经理的权限进行回收：

此处回收update；
此时，HR将无法将update给与HR_U：


此时权限正常；
由于我们刚开始HR给HR_U权限的方式为直接授权，此时HR_U的update权限并没有被拿走！

（二） 审计实验

打开数据库审计开关。以具有审计权限的用户登陆数据库，设置审计权限，然后以普通用户登录数据库，执行相应的数据操纵SQL语句，验证相应审计设置是否生效，最后再以具有审计权限的用户登录数据库，查看是否存在相应的审计信息。
mysql本身并没有操作审计的功能，需要采用general log方法记录sql操作。网上的插件都是基于MySQL5.7版本的，由于我使用的是最新版8.21所以不是使用插件，而是使用mysql8.x提供的general log来实现审计功能。但是开启它有以下几个缺点

• 无论sql有无语法错误，只要执行了就会记录，导致记录大量无用信息，后期的筛选有难度。
• Sql并发量很大时，log的记录会对io造成一定的印象，是数据库效率降低。
• 日志文件很容易快速膨胀，不妥善处理会对磁盘空间造成一定影响。

1. 查询审计配置情况
   
   
2. 暂时打开全局审计
   
   
3. 找到审计文件AFWZ.log
   C:\ProgramData\MySQL\MySQL Server 8.0\Data目录下；
   
4. Testdb数据库中的student表测试
   
   进行四种SELECT查询后观察log文件：
   
   可以观察到审计文件中的记录。
5. 关闭审计