一、背景

最近在搭建mysql主從，為了防止用戶對從庫進行寫操作，導致主從不一致的情況出現。我將用戶的super權限進行了回收，但是發現用戶仍然能在設置了read_only的庫上面進行寫操作。這是為什么呢？

二、實驗

設置數據庫只讀

(root@localhost)[(none)]> set global read_only = 1;

創建用戶，并回收super權限

(root@localhost)[(none)]> create user scott@'%' identified by 'tiger';
(root@localhost)[(none)]> grant all on *.* to scott;
(root@localhost)[(none)]> revoke super on *.* from scott;

新開一個窗口

[root@xbz ~]# mysql -uscott -ptiger

(scott@localhost)[(none)]> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

(scott@localhost)[(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for scott@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `scott`@`%`                                                                                                                                                                                                                           |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `scott`@`%` |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

(scott@localhost)[(none)]> insert into hello.t1 values(100,'abc');
Query OK, 1 row affected (0.00 sec)

可以看到用戶已經沒有super權限，但是仍然能在設置了read_only參數的庫上進行寫操作。通過查閱官方文檔，發現這是由于在8.0.18之后，mysql添加了CONNECTION_ADMIN這個權限導致的。官方文檔搜索“CONNECTION_ADMIN”

為了驗證，再用root回收scott的CONNECTION_ADMIN權限

(root@localhost)[(none)]> revoke CONNECTION_ADMIN on *.* from scott;

回到scott用戶窗口，發現執行insert就報錯了

(scott@localhost)[(none)]> insert into hello.t1 values(100,'abc');
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

***************** 實驗到此就結束了 *****************

以下面的方式創建一個用戶

(root@localhost)[(none)]> create user tom@'%' identified by 'tom';
(root@localhost)[(none)]> grant all on hello.* to scott;

新開一個窗口

[root@xbz ~]# mysql -utom -ptom

(tom@localhost)[(none)]> insert into hello.t1 values(100,'abc');
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

可以看到如果以庫名.*去賦權限用戶的話，用戶是沒辦法對設置了read_only參數的庫進行修改的，這一點與*.*賦權不同。

三、總結

1. read_only對擁有super和connection_admin這兩個權限的用戶無效。
2. 盡量用庫名.*去賦權，而非*.*