简介 前段时间,研究怎么去提升数据库安全,例如禁止执行不带条件的update操作,于是就想到了去启用sql_safe_updates参数,这个参数Mysql默认是不启用的,而且还不能加入到my.cnf配置里。因此就想到了用init_connect参数,将sql_safe_updates=1放到init_connect参数里,这样每个用户会话连接的时候,就会启用sql_safe_updates参数了。

可是用普通连接数据库之后,使用某个库之后,就会报错

mysql> use information_schema;
No connection. Trying to reconnect...
Connection id:    16
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 16 to db: 'unconnected' user: 'jim' host: 'localhost' (init_connect command failed)

** 报错分析** 分析报错之前,先复盘一下操作步骤

1.创建普通用户

mysql> create user 'jim'@'%' identified by 'jim'; 
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| jim              | %         |
| repl             | %         |
| root             | %         |
| tony             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
8 rows in set (0.10 sec)

2.使用root用户登录数据库,并设置init_connect参数


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

mysql> show variables like 'init_connect';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| init_connect  | sql_safe_updates=1 |
+---------------+--------------------+
1 row in set (0.00 sec)

3.使用普通用户jim连接测试

root@18374a493e56:~# mysql -ujim -pjim
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.21

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use information_schema
No connection. Trying to reconnect...
Connection id:    19
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 19 to db: 'unconnected' user: 'jim' host: 'localhost' (init_connect command failed)

4.使用root用户连接测试


root@18374a493e56:~# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use information_schema;
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

从上面的报错信息也能很快的判断出,是由于init_connect设置不合理导致的,可是这里很奇怪的是,普通用户会报错,root用户操作没有报错。弄不清楚为什么,于是就去看官方文档,看看官方文档是怎么描述的。

init_connect参数描述


For users that have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege), the content of init_connect is not executed. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the CONNECTION_ADMIN or SUPER privilege enables them to open a connection and fix the init_connect value.

这段话的大概意思是,当用户具有CONNECTION_ADMIN,SUPER权限用户登录时,是不需要执行init_connect参数的内容的,而不具备这些权限的用户登录时,需要执行init_connect参数的内容,当init_connect参数的内容语句有问题时,就会报错了,这就解释了为什么root用户没有问题,而普通用户发生了问题。

了解报错原因之后,需要修改init_connect的内容了,init_connect里的内容复制出来,如果在mysql command命令行里执行没有问题就可以了。

5.重新设置init_connect参数值

mysql> show variables like 'init_connect';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| init_connect  | sql_safe_updates=1 |
+---------------+--------------------+
1 row in set (0.01 sec)

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

mysql> set global init_connect='set session sql_safe_updates=1';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'init_connect';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| init_connect  | set session sql_safe_updates=1 |
+---------------+--------------------------------+
1 row in set (0.00 sec)

6.使用普通用户jim再次连接测试


root@18374a493e56:~# mysql -ujim -pjim
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use information_schema;
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> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set (0.01 sec)

从测试结果可以看到,已经可以正常使用Mysql数据库了,而且参数sql_safe_updates也设置正确了。

总结 总之,生产操作无小事,大家在生产上执行任何操作时,一定要在测试环境充分验证之后,了解影响范围之后,方可上线操作,如文中操作,很可能会导致一次线上故障。