专栏首页DBA随笔MySQL的一些小tip

MySQL的一些小tip

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)

这样,这个问题一目了然,显然也无法看到。

简单总结:

  1. MySQL5.5不支持show create user语句,只支持show grants for语句;
  2. 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字段,该字段存储加密的密码值;

本文分享自微信公众号 - DBA随笔(gh_acc2bbc0d447),作者:AsiaYe

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-11-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL之delete user和drop user 的区别

    首先,我们看看delete from mysql.user的方法。我们创建两个用户用来测试,测试环境是MySQL5.5版本,用户名分别为yeyz@'%'和y...

    AsiaYe
  • Python之面向对象简介

    Python之面向对象 面向过程的程序设计把计算机程序视为一系列的命令集合,即一组函数的顺序执行。为了简化程序设计,面向过程把函数继续切分为子函数,即把大块函...

    AsiaYe
  • Python之函数编程(1)

    第一次接触到这个知识点的时候,我还不是特别适应,内置的函数怎么可以用一个变量来代替?但是python确实是这么做的,这也不妨碍它的顺利执行。举个例子说明...

    AsiaYe
  • spark中 map和reduce理解及与hadoop的map、reduce区别

    问题导读 1.你认为map函数可以做哪些事情? 2.hadoop中map函数与Scala中函数功能是否一致? 3.Scala中reduce函数与hadoop中...

    用户1410343
  • rjb.com被用建站

    近日有国外媒体报道,三声母域名rjb.com已经被终端建站,暂时停止流通。

    躲在树上的域小名
  • Git 子模块应用简介

    大家做自动化的时候, 常常碰见这样的情况:你自己的项目,UI自动化需要一些前置条件,而这些前置条件正好是你API自动化项目已经完成了的功能,或者说你的项目有一些...

    iTesting
  • C++ 模板学习

    1. 模板的概念。 我们已经学过重载(Overloading),对重载函数而言,C++的检查机制能通过函数参数的不同及所属类的不同。正确的调用重载函数。例如,为...

    猿人谷
  • 变分の美

    变分法(Variational method)已经成为微积分后主流的分析工具, 在物理和应用数学有着极大的功能。 变分法的诞生起源于最强大的数学家家族两个兄弟之...

    史博
  • iOS开发:运行多个模拟器之后出现Unable to boot device due to insu...提示

    在iOS开发中,在运行代码之后可能会做多个屏幕尺寸的对比,来查看界面控件的布局和位置,但是如果同时打开多个Xcode里面的模拟器手机尺寸之后,就不能再打开手机模...

    三掌柜
  • 安全漏洞公告

    Red Hat JBoss Portal GateIn Portal不正确URL转义存在多个反射型跨站脚本漏洞发布时间:2013-12-19漏洞编号:BUGT...

    安恒信息

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动