author：sufei
版本：MySQL8.0.18
說(shuō)明：本文主要是測(cè)試初步在MySQL 8.0.18中實(shí)現(xiàn)的行級(jí)別強(qiáng)制訪問(wèn)控制
數(shù)據(jù)庫(kù)強(qiáng)制訪問(wèn)控制采用三權(quán)分立噩凹，也就是有特定的安全員賬號(hào)，源碼中采用動(dòng)態(tài)權(quán)限MAC_ADMIN來(lái)表明使用是否為安全員。下面是詳細(xì)的實(shí)驗(yàn)步驟航瞭。
一汇跨、數(shù)據(jù)安全員進(jìn)行MAC配置
為滿足數(shù)據(jù)安全拒炎，提供了一個(gè)動(dòng)態(tài)安全權(quán)限MAC_ADMIN，只有具有該權(quán)限的用戶才能夠配置相關(guān)MAC表（添加的強(qiáng)制訪問(wèn)控制相關(guān)表）以及打開(kāi)或者關(guān)閉mac_enable全局變量歧斟。
1.1 安全用戶測(cè)試
下面是有關(guān)安全管理員測(cè)試情況：
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權(quán)限（是安全員），而sf用戶并沒(méi)有（不是安全員）偏形。下面分別使用root和sf用戶操作mac配置表
## 使用沒(méi)有MAC_ADMIN權(quán)限的用戶（非安全員）,此時(shí)既無(wú)法修改權(quán)限表静袖，也無(wú)法關(guān)停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
下面則是使用安全員，進(jìn)行操作
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系統(tǒng)表
# 配置主體規(guī)則表如下
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, 則說(shuō)明現(xiàn)在是表級(jí)別的強(qiáng)制訪問(wèn)控制俊扭。
從上述配置表示：
主體：用戶sf行級(jí)別為3队橙，sf1行級(jí)別為2
客體：表mac_test.test，默認(rèn)flag參數(shù)為N萨惑，表示進(jìn)行表級(jí)別的強(qiáng)制訪問(wèn)控制
1.3 針對(duì)特定表開(kāi)啟行級(jí)別強(qiáng)制訪問(wèn)控制
開(kāi)啟前測(cè)試表情況如下：
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | dog |
+----+------+
1 row in set (0.00 sec)
開(kāi)啟行級(jí)別強(qiáng)制訪問(wèn)控制（其中第一個(gè)參數(shù)為庫(kù)名捐康，第二個(gè)參數(shù)為表名）
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)
開(kāi)啟后，可以看到flag行級(jí)別已經(jīng)打開(kāi)庸蔼，而且表增加了一個(gè)安全等級(jí)字段
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)
由于強(qiáng)制訪問(wèn)控制解总，存在一個(gè)全局開(kāi)關(guān)，我們需要開(kāi)啟姐仅，如果沒(méi)有打開(kāi)花枫，可以執(zhí)行如下命令： set global mac_enable = ON;
二、測(cè)試
通過(guò)三個(gè)客戶端分別使用sf（安全等級(jí)為3）掏膏，sf1（安全等級(jí)為2）劳翰，sf2（沒(méi)有配置，默認(rèn)安全等級(jí)為0馒疹，最低）用戶進(jìn)行登入測(cè)試
image.png
image.png
image.png
