MySQL的一些小tip
1
Create user和grant 的几点说明
我们都知道Create user和grant for都可以用来创建一个用户，那么这两个语句的处理上有什么区别呢？这里我们从MySQL 5.5和MySQL 5.7两个版本来看。
首先使用MySQL 5.5版本，我们假定需要创建一个用户yeyz，它的host是本地localhost，分别使用这两种创建语句来创建这个用户，首先是grant语句，grant语句一般直接跟一些给定的权限：
mysql--dba_admin@127.0.0.1:(none) ::>>select version();
+------------+
| version() |
+------------+
| 5.5.19-log |
+------------+
row in set (0.00 sec)
mysql--dba_admin@127.0.0.1:(none) ::>>drop user yeyz@localhost;
Query OK, rows affected (0.00 sec)
mysql--dba_admin@127.0.0.1:(none) ::>>grant select,create on *.* to yeyz@localhost identified by '123456';
Query OK, rows affected (0.00 sec)
mysql--dba_admin@127.0.0.1:(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语句：
mysql--dba_admin@127.0.0.1:(none) ::>>select version();
+------------+
| version() |
+------------+
| 5.5.19-log |
+------------+
row in set (0.00 sec)
mysql--dba_admin@127.0.0.1:(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的方式分配相关权限，如下：
mysql--dba_admin@127.0.0.1:(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)
mysql--dba_admin@127.0.0.1:(none) ::>>grant select,create on *.* to yeyz@localhost;
Query OK, rows affected (0.00 sec)
mysql--dba_admin@127.0.0.1:(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)
mysql--dba_admin@127.0.0.1:(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的方式时会直接报错。
mysql--dba_admin@127.0.0.1:(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
mysql--dba_admin@127.0.0.1:(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)
mysql--dba_admin@127.0.0.1:(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表的区别。
这个问题是由线上的一个工单引出的，前两天在处理一个工单的时候，需要查询查询账户创建时候的密码，于是直接使用下面的语句进行查询：
mysql--dba_admin@127.0.0.1:(none) ::>>select user,host,password from mysql.user;
ERROR (S22): Unknown column 'password' in 'field list'
发现这个user表里面居然没有password这个选项，然后desc查询了一下mysql.user表里面的字段：
mysql--dba_admin@127.0.0.1:(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可以发现：
mysql--dba_admin@127.0.0.1:(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的字段是存在的：
mysql--dba_admin@127.0.0.1:(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字段，该字段存储加密的密码值；
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2018-11-16，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。