专栏首页DBA随笔MySQL权限控制部分一些细节

MySQL权限控制部分一些细节

//

MySQL权限控制部分回顾

//

今天周天,早上懒了一会儿,起的有点儿晚,中午没事儿干,重新看了看MySQL里面的权限控制模块,再次回头看,还是有很多收获的细节,这里记录一下,方便自己后续查看。

关于权限部分的内容,之前3月11号的文章中有写过一些,今天的内容,我们使用一个一个的细节知识点来撰写(本文中所使用的MySQL版本是5.7.16),在写这些知识点之前,我们首先介绍一下MySQL的权限控制粒度、然后了解一下MySQL中客户端发起请求的时候,服务端所做的核实工作,先来看权限控制粒度:

1、全局层级

全局权限使用于给一个给定服务器中的所有数据库,这些权限存储在mysql.user表中,使用grant all on *.*的方法和revoke all on *.*的方法授予或者回收权限。

2、数据库层级

数据库权限适用于一个给定数据库中的所有目标,包含表对象和存储过程,这些权限存储在mysql.db表中,使用grant all on db_name.*或者对应的revoke方法可以授予和回收数据库权限

3、表层级

表权限适用于一个给定表中的所有列,这些权限存储在mysql的tables_priv表中,一般使用grant all on db_name.tbl_name和对应的revoke语句来授予或者撤销权限。

4、列层级的权限

列层级的权限适用于一个给定表中的指定列,这些权限存储在mysql.columns_priv的表中,由于这个权限不常用,这里给出它的授权方法示例,如下:

首先我们创建一个用户,拥有yeyztest.test1这个表的select权限:

mysql:mysql 19:35:38>>show grants for  dba_yeyz@'192.168.18.%' ;
+------------------------------------------------------------------------------+
| Grants for dba_yeyz@192.168.18.%                                             |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba_yeyz'@'192.168.18.%'                              |
| GRANT SELECT ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%'               |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

然后我们多test1中的一个字段id进行update操作,结果如下:

mysql> select * from test1;
+---------------+
| id            |
+---------------+
|            22 |
| 3333333333333 |
+---------------+
2 rows in set (0.00 sec)

mysql> update test1 set id=2 where id=22;
ERROR 1142 (42000): UPDATE command denied to user 'dba_yeyz'@'192.168.18.**' for table 'test1'

当然,我们是不能进行update的,这个时候,我们使用root账号给这个dba_yeyz的账号一个id列的权限,然后再看它的结果:

mysql:mysql 19:38:38>>show grants for  dba_yeyz@'192.168.18.%' ;
+------------------------------------------------------------------------------+
| Grants for dba_yeyz@192.168.18.%                                             |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dba_yeyz'@'192.168.18.%'                              |
| GRANT SELECT ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%'              |
| GRANT SELECT, UPDATE (id) ON `yeyztest`.`test1` TO 'dba_yeyz'@'192.168.18.%' |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

这里需要注意给字段添加权限的语句,也就是:

grant update (id) on yeyztest.test1 to XXXXXX

也就是在权限后面跟上字段的名称。

这个时候,我们查询一下columns_priv的表,可以看到里面的记录是:

mysql:mysql 19:39:46>>select * from columns_priv;
+--------------+----------+----------+------------+-------------+---------------------+-------------+
| Host         | Db       | User     | Table_name | Column_name | Timestamp           | Column_priv |
+--------------+----------+----------+------------+-------------+---------------------+-------------+
| 192.168.18.% | yeyztest | dba_yeyz | test1      | id          | 0000-00-00 00:00:00 | Update      |
+--------------+----------+----------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)

再次用dba_yeyz进行update操作,可以看到结果:

mysql> update test1 set id=2 where id=22;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from test1;
+---------------+
| id            |
+---------------+
|             2 |
| 3333333333333 |
+---------------+
2 rows in set (0.00 sec)

将id=22的列成功改成了id=2。

5、子程序层级

create routine、alter routine、execute和grant权限适用于已经存储的子程序,这些权限可以被授予为全局层级和数据库层级,可以被存储在mysql.procs_priv中。

客户端发起请求的时候,MySQL服务器核实请求时候的流程图如下:

我将今天看到的一些细节的知识点一个一个罗列出来,希望对大家有点儿帮助:

1、在MySQL5.7.16版本中,mysql系统库中已经没有host表了,跟权限控制相关的表只有5个,分别是user、db、table_priv、proc_priv、column_priv。

2、mysql.user表的主键是用user和host联合起来组成的,且看表结构:

mysql--dba_admin@127.0.0.1:mysql 19:44:56>>show create table mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
---------------权限字段(29个)--------------
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  ......此处省略

---------------安全字段(4个)---------------
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
---------------资源控制字段(4个)--------------
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
--------------插件字段(1个)---------------
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
--------------密码字段(5个)--------------
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `password_last_changed` timestamp NULL DEFAULT NULL,
  `password_lifetime` smallint(5) unsigned DEFAULT NULL,
  `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)  -------------联合主键,host在前-----------
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

3、tables_priv表中只有以下权限,也就是关于表的权限:

select、insert、update、delete、drop、create、alter、grant、references、index、create view、show view、trigger;

columns_priv表中的权限只有下面四个:select、insert、update,references

4、修改一个用户的密码,一般有以下几种方式:

set password for user@host = password('newpassword');
update mysql.user set authentication_string=password('pwd') where user='username' and host='hostname';
alter user user@host identified by 'newpassword';
mysqladmin -u username -h hostname -p password "new password";

最好的方式是alter user的方法,事实上,在新的版本8.0中,set password的方法已经不能使用了,所以建议使用alter user的方法设置新的密码。

除此之外,还有一种方法,不太常用,就是使用grant的方法覆盖掉之前的密码,这里我们简单实验一般,看看效果:

mysql:mysql 20:01:05>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%'  identified by '111111';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql:mysql 20:01:29>>select user,host,concat(user,'@','\'',host,'\''),authentication_string from mysql.user;
+------------------+----------------+-----------------------------------+-------------------------------------------+
| user             | host           | concat(user,'@','\'',host,'\'')   | authentication_string                     |
+------------------+----------------+-----------------------------------+-------------------------------------------+
| dba_yeyz         | 192.168.18.%   | dba_yeyz@'192.168.18.%'           | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
+------------------+----------------+-----------------------------------+-------------------------------------------+
11 rows in set (0.00 sec)

mysql:mysql 20:01:31>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%'  identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql:mysql 20:01:57>>select user,host,concat(user,'@','\'',host,'\''),authentication_string from mysql.user;
+------------------+----------------+-----------------------------------+-------------------------------------------+
| user             | host           | concat(user,'@','\'',host,'\'')   | authentication_string                     |
+------------------+----------------+-----------------------------------+-------------------------------------------+
| dba_yeyz         | 192.168.18.%   | dba_yeyz@'192.168.18.%'           | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------------+----------------+-----------------------------------+-------------------------------------------+
1 rows in set (0.00 sec)

上面的测试可以看到,当我们使用grant的方法给一个指定的用户重新设置密码的时候,之前的老密码会被覆盖,所以这个操作在线上应该慎用,在每次grant的时候看看有没有已经存在的账号,确认没有之后,再进行grant操作

5、如果我们不慎忘记了mysql的root密码,可以重新启动mysql服务,加上--skip-grant-tables这个参数来启动mysql服务,这样就可以直接免除了在权限表里面的匹配工作,直接登陆进mysql服务中,从而修改root账号的密码。

6、如果使用update或者insert记录到mysql.user表中的方法创建账户或者修改密码,在执行完语句之后,必须使用flush privileges的操作刷新权限表,否则该操作无法产生效果。

7、有几个权限会影响mysqladmin工具的执行,分别是

reload权限:影响flush操作

shutdown权限:影响shutdown操作

process权限:影响processlist操作

super权限:影响kill操作

8、之前提到了mysql.user表中的资源控制的字段,分别是

max_questions每小时最大请求数、max_updates每小时最大更新数、max_connections每小时最大连接数、max_user_connections单个用户可同时建立的最大连接数。

如果我们想给一个用户设置这个参数,可以使用如下的SQL来进行设置:

mysql:mysql 20:01:58>>GRANT SELECT ON `yeyztest`.`test` TO 'dba_yeyz'@'192.168.18.%' with max_queries_per_hour 1000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql:mysql 20:13:13>>select user,host,max_questions from mysql.user where user='dba_yeyz';    
+----------+--------------+---------------+
| user     | host         | max_questions |
+----------+--------------+---------------+
| dba_yeyz | 192.168.18.% |          1000 |
+----------+--------------+---------------+
1 row in set (0.00 sec)

注意到,这里的grant语句中使用了with这个选项,with后面可以跟的选项有5个,分别是:

grant option:被授权的用户可以将这些权限赋予别的用户

max_queries_per_hour count:每个小时可以执行count次查询;

max_updates_per_hour count:每个小时可以执行count次更新;

max_connections_per_hour count:每个小时可以建立count个连接;

max_user_connections count:设置单个用户可以同时建立count个连接

9、设置全局变量:

SET GLOBAL default_password_lifetime = 180;

SET GLOBAL default_password_lifetime = 0;

可以设置密码的生命周期为6个月,6个月之后失效,如果设置为0,则一直有效。

当然,还可以在创建用户的时候就指定密码的修改周期或者禁用密码的修改周期:

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

9、有时候我们似乎已经删除了账号密码,但是却还可以通过账号密码进行访问,这个时候,需要检查一个设置,就是看看user表中是否有空记录:

select user,host from mysql.user where user='';

很有可能是你设置了user为空的记录,这样导致所有的用户都可以直接登陆。如果有,最好直接干掉它,因为它违背了安全的宗旨。

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

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

原始发表时间:2019-08-04

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL之delete user和drop user 的区别

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

    AsiaYe
  • MySQL的一些小tip

    我们都知道Create user和grant for都可以用来创建一个用户,那么这两个语句的处理上有什么区别呢?这里我们从MySQL 5.5和MySQL 5....

    AsiaYe
  • MySQL学习过程阶段总结

    今天是公司夏游结束后的第一天,去了趟杭州回来,总体感觉很好,尝试了很多有意思的事情,觉得生活又重新变得有意思起来,总之,一切很好。

    AsiaYe
  • 连接虚拟机mysql无法访问,报错编号1130的解决方法

    新装一台虚拟机mysql的时候,往往会出现win无法连接的情况,报错信息1130,是因为没有权限的问题,解决方案如下: mysql -u root -p mys...

    生活创客
  • flask第二十二篇——模板【4】过滤器

    用户2149234
  • Numpy 修炼之道 (10)—— 结构化数组

    之前我们操作Numpy的数组时,都是通过索引来操作的。针对二维数组,使用索引可以完成对行、列的操作。但是这是非常不直观的。可以把二维数组想象成一个excel表格...

    abs_zero
  • MacOs安装mysql与修改root密码

    mysql-5.7.21-1-macos10.13-x86_64.dmg下载地址是

    超蛋lhy
  • ubuntu循环登录

    Xserver 启动时,读文件~/.Xauthority,读入对应其display的记录.当一个需要显示的客户程序启动调用XOpenDisplay()也读这个文...

    墨文
  • 深入了解Infor Factory Track:8份帮助文档

    在www.inforxtreme.com搜索Factory Track,记录还不多,目前之后141条记录,大概50条都是Manual或者Solution,从历史...

    崔文远TroyCui
  • 天御反欺诈服务,让「天下无贼」

    腾讯云安全

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动