author：sufei
版本：MySQL8.0.18
说明：本文主要是测试初步在MySQL 8.0.18中实现的行级别强制访问控制
数据库强制访问控制采用三权分立，也就是有特定的安全员账号，源码中采用动态权限MAC_ADMIN来表明使用是否为安全员。下面是详细的实验步骤。
一、数据安全员进行MAC配置
为满足数据安全，提供了一个动态安全权限MAC_ADMIN，只有具有该权限的用户才能够配置相关MAC表（添加的强制访问控制相关表）以及打开或者关闭mac_enable全局变量。
1.1 安全用户测试
下面是有关安全管理员测试情况：
mysql> select * from mysql.global_grants;
+---------------+-----------+----------------------------+-------------------+
| USER | HOST | PRIV | WITH_GRANT_OPTION |
+---------------+-----------+----------------------------+-------------------+
| mysql.session | localhost | BACKUP_ADMIN | N |
| mysql.session | localhost | CLONE_ADMIN | N |
| mysql.session | localhost | CONNECTION_ADMIN | N |
| mysql.session | localhost | PERSIST_RO_VARIABLES_ADMIN | N |
| mysql.session | localhost | SESSION_VARIABLES_ADMIN | N |
| mysql.session | localhost | SYSTEM_USER | N |
| mysql.session | localhost | SYSTEM_VARIABLES_ADMIN | N |
| root | localhost | APPLICATION_PASSWORD_ADMIN | Y |
| root | localhost | AUDIT_ADMIN | Y |
| root | localhost | BACKUP_ADMIN | Y |
| root | localhost | BINLOG_ADMIN | Y |
| root | localhost | BINLOG_ENCRYPTION_ADMIN | Y |
| root | localhost | CLONE_ADMIN | Y |
| root | localhost | CONNECTION_ADMIN | Y |
| root | localhost | ENCRYPTION_KEY_ADMIN | Y |
| root | localhost | GROUP_REPLICATION_ADMIN | Y |
| root | localhost | INNODB_REDO_LOG_ARCHIVE | Y |
| root | localhost | MAC_ADMIN | Y |
| root | localhost | PERSIST_RO_VARIABLES_ADMIN | Y |
| root | localhost | REPLICATION_APPLIER | Y |
| root | localhost | REPLICATION_SLAVE_ADMIN | Y |
| root | localhost | RESOURCE_GROUP_ADMIN | Y |
| root | localhost | RESOURCE_GROUP_USER | Y |
| root | localhost | ROLE_ADMIN | Y |
| root | localhost | SERVICE_CONNECTION_ADMIN | Y |
| root | localhost | SESSION_VARIABLES_ADMIN | Y |
| root | localhost | SET_USER_ID | Y |
| root | localhost | SYSTEM_USER | Y |
| root | localhost | SYSTEM_VARIABLES_ADMIN | Y |
| root | localhost | TABLE_ENCRYPTION_ADMIN | Y |
| root | localhost | XA_RECOVER_ADMIN | Y |
| sf | % | APPLICATION_PASSWORD_ADMIN | Y |
| sf | % | AUDIT_ADMIN | Y |
| sf | % | BACKUP_ADMIN | Y |
| sf | % | BINLOG_ADMIN | Y |
| sf | % | BINLOG_ENCRYPTION_ADMIN | Y |
| sf | % | CLONE_ADMIN | Y |
| sf | % | CONNECTION_ADMIN | Y |
| sf | % | ENCRYPTION_KEY_ADMIN | Y |
| sf | % | GROUP_REPLICATION_ADMIN | Y |
| sf | % | INNODB_REDO_LOG_ARCHIVE | Y |
| sf | % | PERSIST_RO_VARIABLES_ADMIN | Y |
| sf | % | REPLICATION_APPLIER | Y |
| sf | % | REPLICATION_SLAVE_ADMIN | Y |
| sf | % | RESOURCE_GROUP_ADMIN | Y |
| sf | % | RESOURCE_GROUP_USER | Y |
| sf | % | ROLE_ADMIN | Y |
| sf | % | SERVICE_CONNECTION_ADMIN | Y |
| sf | % | SESSION_VARIABLES_ADMIN | Y |
| sf | % | SET_USER_ID | Y |
| sf | % | SYSTEM_USER | Y |
| sf | % | SYSTEM_VARIABLES_ADMIN | Y |
| sf | % | TABLE_ENCRYPTION_ADMIN | Y |
| sf | % | XA_RECOVER_ADMIN | Y |
+---------------+-----------+----------------------------+-------------------+
54 rows in set (0.00 sec)
可以看到root有MAC_ADMIN权限（是安全员），而sf用户并没有（不是安全员）。下面分别使用root和sf用户操作mac配置表
## 使用没有MAC_ADMIN权限的用户（非安全员）,此时既无法修改权限表，也无法关停mac_enable
mysql> select current_user();insert into mysql.subject values('sf','mac_level_3'),('sf1','mac_level_2');set global mac_enable = OFF;
+----------------+
| current_user() |
+----------------+
| sf@% |
+----------------+
1 row in set (0.00 sec)
ERROR 1148 (42000): The used command is not allowed with this MySQL version
ERROR 1227 (42000): Access denied; you need (at least one of) the MAC_ADMIN privilege(s) for this operation
下面则是使用安全员，进行操作
mysql> select current_user();insert into mysql.subject values('sf','mac_level_3'),('sf1','mac_level_2');set global mac_enable = OFF;
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
1.2 安全员配置MAC系统表
# 配置主体规则表如下
mysql> select * from mysql.subject;
+------+-------------+
| User | Agent |
+------+-------------+
| sf | mac_level_3 |
| sf1 | mac_level_2 |
+------+-------------+
2 rows in set (0.00 sec)
mysql> select * from mysql.agent;
+-------------+-------+-------+
| Agent | Level | Cate |
+-------------+-------+-------+
| mac_level_2 | 2 | (1,2) |
| mac_level_3 | 3 | (1,2) |
+-------------+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from mysql.object;
+----------+-------------+-------+-------+-------+------+
| Db | Object_name | Type | Level | Cate | flag |
+----------+-------------+-------+-------+-------+------+
| mac_test | test | TABLE | 2 | (1,2) | N |
+----------+-------------+-------+-------+-------+------+
1 row in set (0.00 sec)
// 其中flag为N, 则说明现在是表级别的强制访问控制。
从上述配置表示：
主体：用户sf行级别为3，sf1行级别为2
客体：表mac_test.test，默认flag参数为N，表示进行表级别的强制访问控制
1.3 针对特定表开启行级别强制访问控制
开启前测试表情况如下：
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | dog |
+----+------+
1 row in set (0.00 sec)
开启行级别强制访问控制（其中第一个参数为库名，第二个参数为表名）
mysql> call mac_enable_row('mac_test','test');
+---------+
| success |
+---------+
| 0 |
+---------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
开启后，可以看到flag行级别已经打开，而且表增加了一个安全等级字段
mysql> select * from test;
+----+------+-----------+
| id | name | mac_level |
+----+------+-----------+
| 1 | dog | 0 |
+----+------+-----------+
1 row in set (0.00 sec)
mysql> select * from mysql.object;
+----------+-------------+-------+-------+-------+------+
| Db | Object_name | Type | Level | Cate | flag |
+----------+-------------+-------+-------+-------+------+
| mac_test | test | TABLE | 2 | (1,2) | Y |
+----------+-------------+-------+-------+-------+------+
1 row in set (0.00 sec)
由于强制访问控制，存在一个全局开关，我们需要开启，如果没有打开，可以执行如下命令： set global mac_enable = ON;
二、测试
通过三个客户端分别使用sf（安全等级为3），sf1（安全等级为2），sf2（没有配置，默认安全等级为0，最低）用户进行登入测试
image.png
image.png
image.png
