小白学习MySQL - 不同版本创建用户的些许区别

bisal的个人杂货铺 2022-04-19

349

MySQL创建用户有很多种方法，例如常规create user，再通过grant，授予权限，还可直接grant连带创建用户和授权一起做了。最近创建过程中，发现不同版本操作有些区别。

MySQL 5.7官方手册，给出了创建用户的操作步骤，

https://dev.mysql.com/doc/refman/5.7/en/grant.html

但实际上，grant带着identified by是可以的，

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.32-log |
+------------+
1 row in set (0.02 sec)

mysql> create user test identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on `mysql`.* to 'test'@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

MySQL 8.0官方手册上，给出的操作步骤基本和5.7是相同的，

https://dev.mysql.com/doc/refman/8.0/en/grant.html

但是执行的时候，grant如果加上identified by，就会提示语法存在错误，

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

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

mysql> grant all privileges on `mysql`.* to 'test'@'%' identified by 'test';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'test'' at line 1
复制

其实这对create user和grant分开执行的场景没什么影响，无非是多个少个identified by的问题，但是如果通过grant将创建用户和授权一起执行的场景，就会有些影响，例如5.7中，可以通过grant达到创建用户和授权的效果，当然，这个受sql_mode中的参数NO_AUTO_CREATE_USER的控制，如果指定了，就是允许如下的操作，

mysql> grant all privileges on `mysql`.* to 'testdb'@'%' identified by 'testdb';
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

但是8.0中，这个操作，就会提示错误，

mysql> grant all privileges on `mysql`.* to 'testdb'@'%' identified by 'testdb';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'testdb'' at line 1
复制