MySQL的一些小tip
- 2019 年 11 月 6 日
- 筆記
1
Create user和grant 的几点说明
我们都知道Create user和grant for都可以用来创建一个用户，那么这两个语句的处理上有什么区别呢？这里我们从MySQL 5.5和MySQL 5.7两个版本来看。
首先使用MySQL 5.5版本，我们假定需要创建一个用户yeyz，它的host是本地localhost，分别使用这两种创建语句来创建这个用户，首先是grant语句，grant语句一般直接跟一些给定的权限：
[email protected]:(none) ::>>select version(); +------------+ | version() | +------------+ | 5.5.19-log | +------------+ row in set (0.00 sec) [email protected]:(none) ::>>drop user yeyz@localhost; Query OK, rows affected (0.00 sec) [email protected]:(none) ::>>grant select,create on *.* to yeyz@localhost identified by '123456'; Query OK, rows affected (0.00 sec) [email protected]:(none) ::>>show grants for yeyz@localhost; +----------------------------------------------------------------------------------------------------------------------+ | Grants for yeyz@localhost | +----------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, CREATE ON *.* TO 'yeyz'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +----------------------------------------------------------------------------------------------------------------------+ row in set (0.00 sec)
上面我们给定了create和select权限，我们使用新的用户登录，可以发现：
mysql--yeyz@localhost:(none) ::>>create database yeyz; Query OK, row affected (0.00 sec) mysql--yeyz@localhost:(none) ::>>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | yeyz | +--------------------+ rows in set (0.01 sec)
这个用户可以创建一个新的数据库。
我们删掉前面的yeyz用户，再来看看create语句：
[email protected]:(none) ::>>select version(); +------------+ | version() | +------------+ | 5.5.19-log | +------------+ row in set (0.00 sec) [email protected]:(none) ::>>create user yeyz@localhost identified by '123456'; Query OK, rows affected (0.00 sec)
然后我们查询这个用户的相关权限：
show grants for yeyz@localhost; +-------------------------------------------------------------------------------------------------------------+ | Grants for yeyz@localhost | +-------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'yeyz'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +-------------------------------------------------------------------------------------------------------------+ row in set (0.00 sec)
可以看到，已经给出了usage权限，这里解释一下，这个usage权限只能连接数据库，什么也不能做，我们验证一下，用这个用户连接数据库，结果如下：
[dba_mysql@tk-dba-mysql-stat-10-104 ~]$ /usr/local/mysql/bin/mysql -uyeyz --socket=/data/mysql_4306/tmp/mysql.sock --port= -p -hlocalhost Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is Server version: 5.5.19-log MySQL Community Server (GPL) Copyright (c) , , 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--yeyz@localhost:(none) ::>>show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ row in set (0.01 sec) mysql--yeyz@localhost:(none) ::>>create database yeyz; ERROR (): Access denied for user 'yeyz'@'localhost' to database 'yeyz' mysql--yeyz@localhost:(none) ::>>
我们可以看到这个用户只能连接数据库，查看数据库里面的表，连最基本的创建数据库的权限都没有。如果想要create user这种方式也可以用户创建数据库的权限，需要额外使用grant的方式分配相关权限，如下：
[email protected]:(none) ::>>show grants for yeyz@localhost; +-------------------------------------------------------------------------------------------------------------+ | Grants for yeyz@localhost | +-------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'yeyz'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +-------------------------------------------------------------------------------------------------------------+ row in set (0.00 sec) [email protected]:(none) ::>>grant select,create on *.* to yeyz@localhost; Query OK, rows affected (0.00 sec) [email protected]:(none) ::>>show grants for yeyz@localhost; +----------------------------------------------------------------------------------------------------------------------+ | Grants for yeyz@localhost | +----------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, CREATE ON *.* TO 'yeyz'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +----------------------------------------------------------------------------------------------------------------------+ row in set (0.00 sec) [email protected]:(none) ::>>create database yeyz; Query OK, row affected (0.00 sec)
在上面的基础上，我们使用grant语句重新给定select和create权限，发现已经可以创建数据库了。
结论：
1.create user方法创建用户相当于执行了grant usage，它创建的用户没有任何的权限，只能登陆到服务上，没有任何权限。若想分配相关权限，需要使用grant语句重新分配。
2.grant语法创建的用户可以直接赋予相应权限，用户创建成功之后即可使用相关权限。
大家可能发现了，前面说了两个版本，MySQL5.5和MySQL5.7，上面的结论实在MySQL5.5的基础上得到的，那么5.7又是怎么回事呢？这里解释一下，MySQL 5.7版本中create user和grant的区别跟上面的相同，但是有一个细节需要注意：
mysql> create user test@localhost identified by '123456'; Query OK, rows affected (0.03 sec) mysql> show grants for test@localhost; +------------------------------------------+ | Grants for test@localhost | +------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | +------------------------------------------+ row in set (0.00 sec) mysql> show create user test@localhost; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER for test@localhost | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ row in set (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 5.7.22-log | +------------+ row in set (0.00 sec)
从上面的测试我们可以看出，在MySQL5.7版本中，当我们创建用户使用create user的方法时，使用show grant for语句是没法看到用户的密码的，而使用show create user方法便可以看到用户的密码。而在MySQL5.5版本中，我们使用show create user的方式时会直接报错。
[email protected]:(none) ::>>show create user yeyz@localhost; ERROR (): 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 'user yeyz@localhost' at line [email protected]:(none) ::>>show grants for yeyz@localhost; +----------------------------------------------------------------------------------------------------------------------+ | Grants for yeyz@localhost | +----------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, CREATE ON *.* TO 'yeyz'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +----------------------------------------------------------------------------------------------------------------------+ row in set (0.00 sec) [email protected]:(none) ::>>select version(); +------------+ | version() | +------------+ | 5.5.19-log | +------------+ row in set (0.00 sec)
这里又有一个问题，既然show create user方法创建的用户无法使用show grants for看到密码，那么grant 语法创建的用户呢？是不是也看不到？来看实验：
mysql> grant select on *.* to test1@localhost identified by '123456'; Query OK, rows affected, warning (0.02 sec) mysql> show grants for test1@localhost; +--------------------------------------------+ | Grants for test1@localhost | +--------------------------------------------+ | GRANT SELECT ON *.* TO 'test1'@'localhost' | +--------------------------------------------+ row in set (0.00 sec)
这样，这个问题一目了然，显然也无法看到。
简单总结：
- MySQL5.5不支持show create user语句，只支持show grants for语句；
- MySQL5.7中支持show create user语句和show grants for语句，但是show grants for语句无法查看所创建的用户的密码；
2
MySQL5.5和MySQL5.7的user表区别
上面讲到了MySQL5.5和MySQL5.7的创建用户的区别，这里我们说说MySQL5.5和MySQL5.7里面的user表的区别。
这个问题是由线上的一个工单引出的，前两天在处理一个工单的时候，需要查询查询账户创建时候的密码，于是直接使用下面的语句进行查询：
[email protected]:(none) ::>>select user,host,password from mysql.user; ERROR (S22): Unknown column 'password' in 'field list'
发现这个user表里面居然没有password这个选项，然后desc查询了一下mysql.user表里面的字段：
[email protected]:(none) ::>>desc mysql.user; +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char() | NO | PRI | | | | User | char() | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int() unsigned | NO | | | | | max_updates | int() unsigned | NO | | | | | max_connections | int() unsigned | NO | | | | | max_user_connections | int() unsigned | NO | | | | | plugin | char() | NO | | mysql_native_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | | password_last_changed | timestamp | YES | | NULL | | | password_lifetime | smallint() unsigned | YES | | NULL | | | account_locked | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ rows in set (0.00 sec)
发现password字段在MySQL5.7版本中已经被拿掉了，取而代之的是authentication_string,查看authentication_string可以发现：
[email protected]:(none) ::>>select user,host,authentication_string from mysql.user; +---------------------+---------------+-------------------------------------------+ | user | host | authentication_string | +---------------------+---------------+-------------------------------------------+ | root | localhost | *B66B5AD56Exxxxxxxxxxxx9AF81952D7F403 | | mysql.sys | localhost | *THISISNOTAVALIxxxxxxxxxxxATCANBEUSEDHERE | | dba_admin | 127.0.0.1 | *ExxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxE | +---------------------+---------------+-------------------------------------------+ rows in set (0.00 sec)
而在MySQL 5.5的版本中，我们可以看到password的字段是存在的：
[email protected]:(none) ::>>select user,host,password,authentication_string from mysql.user; +------------------+-----------------+-------------------------------------------+-----------------------+ | user | host | password | authentication_string | +------------------+-----------------+-------------------------------------------+-----------------------+ | root | localhost | *xxxxxxxxxxx881A495C5B7C199DCB2DE15A740FA | | | dba_yeyz | localhost | *xxxxxxxxxxxxx9105EE4568DDA7DC67ED2CA2AD9 | NULL | | yeyz | localhost | *xxxxxxxxxxxx29105EE4568DDA7DC67ED2CA2AD9 | NULL | +------------------+-----------------+-------------------------------------------+-----------------------+ rows in set (0.00 sec)
结论：
MySQL5.5中存在password字段和authentication_string字段，但是后者的值为空值。
MySQL5.7版本中的已经不存在password字段，取而代之的是authentication_string字段，该字段存储加密的密码值；
