专栏首页杨建荣的学习笔记MySQL中如何得到权限信息

MySQL中如何得到权限信息

最近在做一次MySQL数据迁移的时候,突然发现自己遗漏了一个地方,那就是权限信息没有导出,如果我们使用mysqldump --all-databases的时候没有添加--flush-privileges的时候,导出的数据中是不会包含mysql数据库的。

而我其实是比较懒的,不想因为这个重新导出一次,那么我就有几种方式选择。

如果在MySQL 5.5,5.6的版本中,我可以直接导出mysql.user的数据即可。

如果使用脚本化完成,基本是这样的形式即可,本意其实就是show grants for 'xxx'的组合形式,不断拼接解析。

mysql -e "SELECT DISTINCT CONCAT('show grants for ','''',user,'''@''',host,'''',';') AS query FROM mysql.user where user!='root'" | grep -v query >/tmp/showgrants.sql && mysql </tmp/showgrants.sql | egrep -v 'Grants for|query'

运行后的语句大体是如下的形式:

GRANT ALL PRIVILEGES ON *.* TO 'adm'@'localhost' IDENTIFIED BY PASSWORD '*3DCFB64FE0CB05D63B9AF64492B5CD6269D82EE8' GRANT ALL PRIVILEGES ON `Cyou_DAS`.* TO 'adm'@'localhost' GRANT USAGE ON *.* TO ''@'mysqlactivity'

这一招在5.5,5.6中都是可行的,但是迁移的数据库是5.7的,看到下面导出的语句,我感觉不对劲,难道都不要密码,如果确实没有,这是一个多么大的坑。

GRANT USAGE ON *.* TO 'phplamp'@'localhost' GRANT ALL PRIVILEGES ON `phplampDB`.* TO 'phplamp'@'localhost' 我知道5.7做了一些改进,本身对于show grants也有一些限制,没想到真碰上这种情况,教训是如此的深刻。

所以回到问题,如果现在要解决,就有大体的三种方式来同步权限; 方法1:重新导出导入整个数据库 不评论,我绝对不会这么做,只是看起来是一个完整的过程,但是无用功太多,很容易被鄙视 方法2:导出mysql的权限配置 如果是在5.5,5.6的环境,直接导出mysql.user表数据即可,但是在5.7中可不行,一种方式就是导出整个mysql库的数据 方法3:pt工具导出 使用自定义脚本或者pt工具来导出权限信息 当然解决方法很多,我就说说方法2,方法3

我对比了5.6和5.7 的表结构情况。不看不知道,一看差别还真不小。

MySQL 5.7的mysql.user表含有45个字段 MySQL 5.6的mysql.user表含有43个字段

这是表面现象,不是5.7多两个字段这么简单,真实情况如下:

1) MySQL5.7中多了下面的3个字段,字段和数据类型如下:

password_last_changed | timestamp password_lifetime | smallint(5) unsigned account_locked | enum('N','Y') 2)这么一看总数对不上,这是因为MySQL 5.7相比5.6少了password字段

3)还有个细节可能被忽略,那就是MySQL 5.7的字段user相比MySQL 5.6长度从16字符增长到了32字符。

这就奇怪了,为什么没有了password字段呢,没有了password字段,这个功能该怎么补充呢

MySQL5.6中查看mysql.user的数据结果如下:

> select user,password,authentication_string from mysql.user; | user | password | authentication_string | | app_live_im | *E96DB97255EF3ED52454A10EDA1AE7BABC8D3700 | | | mysqlmon | *0571D080430BC7B60A3F4D41A8D71501E6B8FDAA | |

而在MySQL 5.7中,结果却有所不同

+-----------------+-------------------------------------------+ | user | authentication_string | +-----------------+-------------------------------------------+ | gym | *0CD6502815166F2C7E17B630C3248B900065FCEA | | actv_test | *82A4DC7B3F5E73E822529E9EF4DE8C042253445A |

一个重要差别就在于mysql.user表的字段值 plugin

max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: password_expired: N password_last_changed: 2016-11-09 11:38:39 password_lifetime: 0

基于这个安全策略,可以做很多的事情,5.7默认就是这种模式。

看起来之前的那种show grants得到的信息很有限,那么我们来看看pt工具的效果,直接运行./pt-show-grants即可

-- Grants for 'webadmin'@'10.127.8.207' CREATE USER IF NOT EXISTS 'webadmin'@'10.127.8.207'; ALTER USER 'webadmin'@'10.127.8.207' IDENTIFIED WITH 'mysql_native_password' AS '*DA43F144DD67A3F00F086B0DA1288C1D5DA7251F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK; GRANT ALL PRIVILEGES ON *.* TO 'webadmin'@'10.127.xx.xx';

这样的语句相对来说就是完整的,使用show grants的结果少了很多,只包含基本的权限信息。

> show grants for 'webadmin'@'10.12.20.133'; | GRANT ALL PRIVILEGES ON *.* TO 'webadmin'@'10.12.xx.xxx' |

为什么使用pt工具能够得到更多,不是这个工具有多神奇,而是里面充分利用了新特性的东西。

pt-show-grants里面是这样写的,对于MySQL 5.7的处理方式。

# If MySQL 5.7.6+ then we need to use SHOW CREATE USER my @create_user; if ( VersionCompare::cmp($version, '5.7.6') >= 0 ) { eval { @create_user = @{ $dbh->selectcol_arrayref("SHOW CREATE USER $user_host") }; }; if ( $EVAL_ERROR ) { PTDEBUG && _d($EVAL_ERROR); $exit_status = 1; } PTDEBUG && _d('CreateUser:', Dumper(\@create_user)); # make this replication safe converting the CREATE USER into # CREATE USER IF NOT EXISTS and then doing an ALTER USER my $create = $create_user[0]; my $alter = $create; $create =~ s{CREATE USER}{CREATE USER IF NOT EXISTS}; $create =~ s{ IDENTIFIED .*}{}; $alter =~ s{CREATE USER}{ALTER USER}; @create_user = ( $create, $alter ); PTDEBUG && _d('AdjustedCreateUser:', Dumper(\@create_user)); }

简化一下就是使用show create user这种方式,在这个基础上额外补充一下,使得这个语句更加健壮。

我们使用show create user 'webadmin'@'10.12.20.133'得到的结果如下:

| CREATE USER 'webadmin'@'10.12.20.133' IDENTIFIED WITH 'mysql_native_password' AS '*DA43F144DD67A3F00F086B0DA1288C1D5DA7251F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |

语句看起来丰满了很多,但是似乎还是少了些权限的信息,

这是因为5.7里面完整的信息是通过show create user和show grants for 'xx'这两种方式完成的,而在5.6中只需要通过show grants for 'xxx’即可。 明白了原委和解决方法,这个问题处理起来其实就很简单了。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes),作者:r13笔记第49天

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

原始发表时间:2017-08-11

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 不能轻视的mysql重启过程 (r7笔记第55天)

    数据库的重启看似是一件非常简单,没有技术含量的活,这是我以前说的话。而这句话简直是戳中了我的痛点。这种活真是太有技术含量了,高深到让人需要注意太多的东西,需要做...

    jeanron100
  • CentOS7压力测试MGR

    最近在规划CentOS7版本中的MySQL测试情况,于是找了公司内部的虚拟机来做下模拟测试。

    jeanron100
  • MySQL关于数据字典的一个疑问

    今天看着MySQL的数据字典,突然想到一个问题:为什么MySQL数据字典 information_schema中的表名是大写,而performance_sche...

    jeanron100
  • Ubuntu 修改 mysql 密码

    ppjun
  • 玩得一手好注入之order by排序篇

    看了之前Gr36_前辈在先知上的议题,其中有提到排序注入,最近经常遇到这样的问题,所以先总结下order by 排序注入的知识。 环境信息 测试环境:操作...

    漏斗社区
  • LAMP架构介绍,MySQL、MariaDB介绍,MySQL安装

    叶瑾
  • MYSQL my.ini 配置

    用户2657851
  • MySQL5.5版本部署的一个问题

    目前公司部署MySQL是通过平台化操作的,周五的时候,平台暂时出了点儿问题,手上有个需求比较着急,就直接手动的部署了一下,由于好长时间没有部署环境了,...

    AsiaYe
  • Linux基础(day55)

    13.4 mysql用户管理 mysql用户管理目录概要 grant all on . to 'user1' identified by 'passwd'; g...

    运维小白
  • Linux 安装mysql

    使用 yum 安装源码包(必须有网络) 1、输入如下安装命令,安装过程中输入 y yum install mysql-server -y 2、查看mysql 服...

    IT可乐

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动