mysql使用者建立觸發器許可權不足跟引數log_bin_trust_function_creators

我愛睡蓮發表於2021-05-10

問題描述

有業務反饋當前使用者無法建立觸發器和儲存過程，讓使用者自己測試，該使用者進行對錶的增刪改查等其他許可權沒有問題，這邊用root使用者查證，該使用者擁有對當前庫的所有許可權，但是為什麼就是建立不了觸發器呢？建立語句不涉及其他庫，只是對當前庫進行建立觸發器，下面自己進行測試。

處理過程：將引數log_bin_trust_function_creators設定為ON即可

現有一下疑問？

1.使用者擁有對當前庫的所有許可權，但是為什麼建立不了觸發器呢？

2.log_bin_trust_function_creators引數開啟或關閉對建立觸發器有什麼影響呢？

3.如果對使用者授予一個對所有庫的建立觸發器，儲存過程許可權，會不會有效呢？

報錯：

ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

測試過程：

1.建立測試相關用例

建立測試用例：
mysql> create database gwhdgl;
mysql> create user gwhdgl@'%' identified by '123';
mysql> grant all privileges on gwhdgl.* to gwhdgl@'%';
mysql> grant select on mysql.* to gwhdgl@'%';
mysql> show grants for gwhdgl@'%';
+----------------------------------------------------+
| Grants for gwhdgl@%                                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'gwhdgl'@'%'                 |
| GRANT ALL PRIVILEGES ON `gwhdgl`.* TO 'gwhdgl'@'%' |
| GRANT SELECT ON `mysql`.* TO 'gwhdgl'@'%'          |
+----------------------------------------------------+
3 rows in set (0.00 sec)

建立測試表：
mysql> select * from t1;
+------+--------+-----------+
| id   | name   | start_url |
+------+--------+-----------+
|    1 | 張三   | Y         |
|    2 | 李四   | Y         |
|    3 | 王五   | Y         |
|    4 | 馬六   | Y         |
|    5 | 是無   | Y         |
|    6 | 虧劉   | Y         |
+------+--------+-----------+
6 rows in set (0.01 sec)

mysql> 
mysql> select * from t2;
+------+--------+-----------+
| id   | name   | start_url |
+------+--------+-----------+
|    1 | 張三   | Y         |
|    2 | 李四   | Y         |
|    3 | 王五   | Y         |
|    4 | 馬六   | Y         |
|    5 | 是無   | Y         |
|    6 | 虧劉   | NULL      |
+------+--------+-----------+
6 rows in set (0.00 sec)

2.建立觸發器，報錯ERROR 1442

mysql -ugwhdgl -p123 -h192.168.163.21 -P13306

DELIMITER ||
create trigger gwhdgl_t2_triggers before insert
on t2 for each row
begin
update gwhdgl.t1 set start_url='Y';
END
||

ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

從mysql.db檢視到的許可權

mysql> select * from mysql.db where user='gwhdgl' and host='%'\G;
*************************** 1. row ***************************
Host: %
Db: gwhdgl
User: gwhdgl
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: mysql
User: gwhdgl
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
2 rows in set (0.00 sec)

ERROR:
No query specified

從mysql.user看到的許可權

mysql> select * from mysql.user where user='gwhdgl' and host='%'\G;
*************************** 1. row ***************************
Host: %
User: gwhdgl
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
password_expired: N
password_last_changed: 2021-05-10 10:17:28
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)

對當前庫的許可權具備完全，但是對其他庫的許可權沒有，如果將對其他庫的建立觸發器許可權給到gwhdgl使用者，就是將mysql.user表的許可權更改過來，會不會正常呢？

3.授予使用者mysql.user表的許可權

User表：存放使用者賬戶資訊以及全域性級別（所有資料庫）許可權，決定了來自哪些主機的哪些使用者可以訪問資料庫例項，如果有全域性許可權則意味著對所有資料庫都有此許可權
Db表：存放資料庫級別的許可權，決定了來自哪些主機的哪些使用者可以訪問此資料庫
Tables_priv表：存放表級別的許可權，決定了來自哪些主機的哪些使用者可以訪問資料庫的這個表
Columns_priv表：存放列級別的許可權，決定了來自哪些主機的哪些使用者可以訪問資料庫表的這個欄位
Procs_priv表：存放儲存過程和函式級別的許可權

root使用者授權
mysql> grant create routine,execute,alter routine,trigger on *.* to gwhdgl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show grants for gwhdgl@'%';
+------------------------------------------------------------------------------+
| Grants for gwhdgl@%                                                          |
+------------------------------------------------------------------------------+
| GRANT EXECUTE, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON *.* TO 'gwhdgl'@'%' |
| GRANT ALL PRIVILEGES ON `gwhdgl`.* TO 'gwhdgl'@'%'                           |
| GRANT SELECT ON `mysql`.* TO 'gwhdgl'@'%'                                    |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select * from mysql.user where user='gwhdgl' and host='%'\G;
*************************** 1. row ***************************
Host: %
User: gwhdgl
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: Y
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: N
Event_priv: N
Trigger_priv: Y
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
password_expired: N
password_last_changed: 2021-05-10 10:17:28
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)

ERROR:



gwhdgl使用者看到對trigger的許可權

mysql> DELIMITER ||
mysql> create trigger gwhdgl_t2_triggers before insert
-> on t2 for each row
-> begin
-> update gwhdgl.t1 set start_url='Y';
-> END
-> ||

ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

經過測試，將mysql.db和mysql.user的許可權都改回來，仍然沒有用

4.在所有許可權都保持的情況下，開啟引數log_bin_trust_function_creators

mysql> set global log_bin_trust_function_creators=1;

gwhdgl使用者

mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ||
mysql> create trigger gwhdgl_t2_triggers before insert
-> on t2 for each row
-> begin
-> update gwhdgl.t1 set start_url='Y';
-> END
-> ||
Query OK, 0 rows affected (0.00 sec)

mysql> use gwhdgl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql>
mysql> drop trigger if exists gwhdgl_t1_triggers;
Query OK, 0 rows affected, 1 warning (0.00 sec)

經測試，開啟log_bin_trust_function_creators引數可以讓使用者擁有建立觸發器選項，此時使用者的許可權還是

| GRANT EXECUTE, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON *.* TO 'gwhdgl'@'%' |
| GRANT ALL PRIVILEGES ON `gwhdgl`.* TO 'gwhdgl'@'%' |
| GRANT SELECT ON `mysql`.* TO 'gwhdgl'@'%'

log_bin_trust_function_creators這個引數到底是什麼意思？

當二進位制日誌啟用後，這個變數就會啟用。它控制是否可以信任儲存函式建立者，不會建立寫入二進位制日誌引起不安全事件的儲存函式。如果設定為0（預設值），使用者不得建立或修改儲存函式，除非它們具有除CREATE ROUTINE或ALTER ROUTINE特權之外的SUPER許可權。設定為0還強制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性宣告函式的限制。如果變數設定為1，MySQL不會對建立儲存函式實施這些限制。

所以這個引數是跟隨log-bin的，控制開啟log-bin模式後，開啟主從複製模式，對寫入二進位制日誌引起不安全事件的儲存函式信任關係的一個限制，所以下面測試將log-bin關閉，mysql使用者建立觸發器需要什麼許可權呢？

開啟這個引數就相當於允許主從複製這些函式

5.關閉log-bin，目前使用者擁有的許可權是對當前庫，和其他庫的建立觸發器的許可權，測試成功

mysql> DELIMITER ||
mysql> create trigger gwhdgl_t2_triggers before insert
    -> on t2 for each row
    -> begin
    -> update gwhdgl.t1 set start_url='Y';
    -> END
    -> ||
Query OK, 0 rows affected (0.00 sec)

6.授予使用者對其他庫的觸發器許可權，目前是對當前庫擁有建立觸發器的許可權，建立成功

將對其他庫的許可權收回來
mysql> revoke create routine,execute,alter routine,trigger on *.* from gwhdgl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for gwhdgl@'%';
+----------------------------------------------------+
| Grants for gwhdgl@%                                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'gwhdgl'@'%'                 |
| GRANT ALL PRIVILEGES ON `gwhdgl`.* TO 'gwhdgl'@'%' |
| GRANT SELECT ON `mysql`.* TO 'gwhdgl'@'%'          |
+----------------------------------------------------+
3 rows in set (0.00 sec)

測試下有沒有對trigger的許可權？
mysql> select user();
+-------------+
| user()      |
+-------------+
| gwhdgl@mha4 |
+-------------+
1 row in set (0.00 sec)

mysql> show grants for gwhdgl@'%';
+----------------------------------------------------+
| Grants for gwhdgl@%                                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'gwhdgl'@'%'                 |
| GRANT ALL PRIVILEGES ON `gwhdgl`.* TO 'gwhdgl'@'%' |
| GRANT SELECT ON `mysql`.* TO 'gwhdgl'@'%'          |
+----------------------------------------------------+
3 rows in set (0.00 sec)


mysql> DELIMITER ||
mysql> create trigger gwhdgl_t2_triggers before insert
    -> on t2 for each row
    -> begin
    -> update gwhdgl.t1 set start_url='Y';
    -> END
    -> ||
Query OK, 0 rows affected (0.00 sec)

結論：

1.建立觸發器其實只需要使用者對當前庫的create routine,execute,alter routine,trigger相關許可權就可以了

2.如果關閉log_bin_trust_function_creators模式，即便擁有了對當前庫和其他庫的建立觸發器許可權，仍然不能夠建立，除非擁有SUPER privilege

3.我使用的都是單機，並不是主從模式，但是也是受log_bin_trust_function_creators這個引數限制的

MySQL建立使用者，配置許可權、密碼策略
2019-01-25
MySql密碼
mysql使用者許可權管理
2021-09-06
MySql
建立MySQL觸發器
2021-06-04
MySql觸發器
MySQL使用者及許可權管理
2019-02-28
MySql
MySql查詢使用者許可權
2024-04-08
MySql
MySQL 使用者及許可權管理?
2019-09-12
MySql
mysql8下建立使用者遠端登陸許可權
2024-07-26
MySql
mysql許可權
2018-12-08
MySql
MYSQL學習筆記13: DCL許可權控制(使用者許可權操作)
2024-03-09
MySql筆記
mysql 8.0.21使用者及許可權操作
2020-11-05
MySql
入門MySQL——使用者與許可權
2020-07-03
MySql
MySQL入門--使用者與許可權
2019-05-29
MySql
mysql 給了使用者所有許可權ALL PRIVILEGES，但是該使用者沒有grant許可權
2024-08-11
MySql
MySQL許可權管理
2024-05-26
MySql
MySQL使用者許可權控制一例
2018-08-05
MySql
mysql 使用者及許可權管理小結
2019-03-25
MySql
授權|取消授權MYSQL資料庫使用者許可權
2024-11-26
MySql資料庫
MySQL新增新使用者、為使用者建立資料庫、為新使用者分配許可權
2018-09-14
MySql資料庫
MySQL-03.使用者管理和許可權管理
2024-03-17
MySql
MySQL 給使用者新增 ALTER VIEW 的許可權
2024-05-15
MySqlView
mysql8.0新增使用者，並指定許可權
2019-11-29
MySql
mysql 新增、刪除使用者和許可權分配
2021-09-09
MySql
mysql許可權參考
2020-01-21
MySql
MySQL 許可權詳解
2024-06-02
MySql
MySQL許可權問題
2020-12-13
MySql
linux使用者許可權
2024-09-09
Linux
Mysql學習筆記4--使用者許可權
2024-11-01
MySql筆記
mysql 開放遠端連線許可權連不上
2019-06-11
MySql
關於mysql許可權管理
2024-05-30
MySql
MySQL許可權管理實戰
2021-04-19
MySql
canal mysql select許可權粒度
2021-03-06
MySql
mysql連線無許可權
2020-12-09
MySql
報表如何透過引數控制資料許可權
2020-07-30
Linux使用者與許可權
2024-03-14
Linux
MongoDB建立只讀使用者並授權指定集合的查詢許可權
2024-11-14
MongoDB
Oracle資料庫scott使用者建立view檢視許可權
2021-01-02
Oracle資料庫View
mysql 床架新資料庫並分配使用者許可權
2024-12-07
MySql資料庫
CDB和PDB關於使用者建立和使用者許可權區別
2020-02-08