在上一期《权限系统表|全方位认识 mysql 系统库》中,我们针对mysql 系统库中的权限表做了一个简单的认识,本期我们将在上一期的基础上详细介绍MySQL 的访问权限控制系统,下面请跟随我们一起开始 mysql 系统库的系统学习之旅吧。
什么是访问权限控制系统?
MySQL访问权限系统的用户界面由几个SQL语句组成:如CREATE USER、GRANT和REVOKE。
在Server内部,MySQL 将权限信息存储在mysql数据库的权限表中。MySQL Server在启动时将这些表的内容读入内存,后续针对用户的访问控制决策基于权限表的内存副本实现。
MySQL访问权限系统可以确保只有被允许的(与用户权限匹配的)操作才能够在Server中执行。当一个用户连接到MySQL Server时,用户的认证身份由"请求连接的主机名和用户名"确定,MySQL使用主机名+用户名的方式来识别和区分"相同主机不同用户"和"不同主机相同用户"发出的请求(例如:从office.example.com连接的用户joe和从home.example.com连接的用户joe在MySQL Server中实际上是被当作两个不同的连接者来处理的,所以可以设置不同的密码、不同的权限),例如:
root@localhost : mysql 01:03:04> show grants for test_a@'localhost';
+---------------------------------------------+
| Grants for test_a@localhost |
+---------------------------------------------+
| GRANT SELECT ON *.* TO 'test_a'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)
root@localhost : mysql 01:03:22> show grants for test_a@'%';
+---------------------------------------------+
| Grants for test_a@% |
+---------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'test_a'@'%' |
+---------------------------------------------+
1 row in set (0.00 sec)
当用户使用客户端程序连接到MySQL Server时,MySQL的访问控制分为如下两个阶段:
如果某用户在已经建立连接期间,权限发生了变更(自身修改或者其他用户修改),那么对于该用户执行下一条语句时,这些权限变更不一定会立即生效。如果未生效需要执行flush privileges;
MySQL 提供的权限列表如下(其中,all或者all privileges代表了如下列表中除了grant option权限之外的所有权限):
root@localhost : (none) 11:55:05> show privileges;
+-------------------------+---------------------------------------
+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+--------------------------------------- +-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+--------------------------------------- +-------------------------------------------------------+
31 rows in set (0.00 sec)
以上权限列表中,Context字段显示了该权限的一个使用环境(或者说是权限的作用域),根据Context的不同,分为如下三类:
通常,按照我们使用经验还可以按照如下方式划分。
下面,我们挨个解释每个权限的作用。
3、 帐号命名规则
MySQL的帐户由用户名和主机名两部分组成(例如:user_name@host_name)。采用这种方式Server就可以区分相同用户来自不同主机的连接,本小节将介绍如何编写有效的帐户名称(包括特殊值和通配符规则),对于使用SQL语句CREATE USER、GRANT和SET PASSWORD来操作用户的,都遵循以下规则:
MySQL 中的帐号名在mysql系统字典库中的权限表user 表中存储时,会将user_name和host_name分开存储在user和host两列中:
关于帐号用户名和主机名中某些特殊值或通配符约定,如下:
* 主机名或IP地址值中都允许使用通配符%和_。这些与LIKE运算符中的通配符含义相同。例如,“%”表示匹配任意主机名,而“%.mysql.com”表示匹配mysql.com域中的任何主机,'192.51.100.%'表示匹配C类私有网络192.51.100中的任意主机,由于主机名允许使用IP+通配符值(例如:“192.51.100.%”匹配192.51.100子网上的任意主机),为了阻止有人通过192.51.100.somewhere.com 格式的主机名字符串构造来尝试扫描存活主机,MySQL不会在以数字和点开头的主机名上执行匹配动作。例如:如果主机名部分为1.2.example.com,则直接被MySQL忽略, IP地址只能使用通配符组合,而不能与主机名进行组合,否则也会被忽略 。 * 对于指定为IPv4地址的主机名,可以结合子网掩码来控制子网IP数量(注意:子网掩码不使用IPV6),格式:host_ip/netmask。例如:CREATE USER 'david'@'192.51.100.0/255.255.255.0';,表示用户名为david,主机名为192.51.100.0子网下的任意主机,满足此条件的客户端主机IP地址范围从192.51.100.0到192.51.100.255。
MySQL Server使用DNS解析时,需要注意以下问题:
4、MySQL 帐号访问控制两阶段
当您尝试连接MySQL Server时,Server根据如下条件来决定是否需要接受或拒绝连接:
当MySQL Server接收到一个新的连接请求时,Server首先检查用户凭证(帐号+密码),然后检查帐户的锁定状态。任意一个步骤检查失败则拒绝连接发访问。如果两个步骤都通过检查,则进入第2阶段并等待执行请求。
root@localhost : (none) 12:43:38> select host,user,authentication_string,account_locked from mysql.user;
+-----------+---------------+---------------------------------------- ---+----------------+
| host | user | authentication_string | account_locked |
+-----------+---------------+---------------------------------------- ---+----------------+
| localhost | root | *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y |
| % | admin | *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N |
| % | repl | *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N |
| % | qbench | *1966B10B87AA6A1F8E1215A1C81DDD5FBBA6B0D0 | N |
| % | program | *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N |
+-----------+---------------+---------------------------------------- ---+----------------+
7 rows in set (0.00 sec)
# 帐户锁定状态可以通过ALTER USER语句进行更改
ALTER USER [IF EXISTS]
user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
......
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}
上文中提到过,用户的身份信息基于两部分组成(user_name和host_name),对于身份信息的两个组成部分,有如下认证规则:
下表列举了一些user_name和host_name常用的组合:
客户端传入Server中的身份标识(主机名和用户名)可能与用户表中的多个行记录匹配成功。当一个用户尝试连接Server时,如果在Server的user表中匹配到多个行记录的身份认证信息,则Server必须确定要能够确定使用哪一行记录进行许可(不同的身份信息行记录可能对应着不同的权限):
Server 使用的排序规则中,先排序主机列值(越精确的值越靠前,字符串主机名和IP地址是最具体的,另外,IP地址的精确性不会受到掩码的影响,例如:192.51.100.13和192.51.100.0/255.255.255.0被视为具有相同的精确度。通配符'%'表示“任何主机”,被视为精确度较差的主机名。空字符串“'也意味着”任何主机“,但精确度比'%'更差,所以排序在'%'之后)。然后再按照用户列值进行排序(排序规则跟主机列值类似),host和user两列的排序规则有点类似与多列索引中的排序规则。
示例一:假设用户表中记录的内容如下所示:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
# 当Server将表中的内容读入内存时,会使用刚刚描述的规则在内存中对用户信息行进行排序。排序后的结果如下所示:
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | jeffrey | ...
| % | root | ...
+-----------+----------+-
# 当客户端尝试连接时,Server 会查看在内存中已排好序的用户身份认证信息,并使用第一个匹配项进行许可。如:对于用户jeffrey的localhost的主机连接,首先,精确匹配localhost主机列,有两列匹配,然后再匹配用户名列,也有两列(空值和jeffrey),两列交集最终确定匹配行为:host=localhost,user='',即''@'localhost'身份
示例二:假设用户表中记录的信息如下所示:
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| % | jeffrey | ...
| h1.example.net | | ...
+----------------+----------+-
# 在内存中排序之后的内容如下所示:
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| h1.example.net | | ...
| % | jeffrey | ...
+----------------+----------+-
# 来自h1.example.net主机的jeffrey用户的连接与第一行记录匹配成功,而来自任何主机的jeffrey用户的连接与第二行匹配成功
注意:
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+
当客户端与MySQL Server 建立连接之后,Server 进入权限访问控制的第2阶段。在第2阶段中,客户端发送给服务端的每个请求,服务端都会检查请求操作的类型,然后检查是否有足够的访问权限来执行请求操作。该检查工作依赖于mysql schema下的user、db、tables_priv、columns_priv、procs_priv、proxies_priv权限表中存放的权限信息。
user:该表中的权限作用范围是全局的,所以该表中相应权限类型列值为'Y'时,就表示表示对数据库实例中的所有数据库表都有该权限,所以,在大多数时候,我们需要根据具体的业务环境需求来给定需要访问的数据库对应的权限,而不是投方便直接给所有库所有表的权限(关于如何给定权限,请参考上文提到的权限分类)。
db:该表中的权限作用范围是数据库级别,对应数据库内的所有对象:
tables_priv,columns_priv和procs_priv:这三张表中记录着表级别权限、列级别权限、线程级别权限:
当一个客户端连接在进行第二阶段权限验证时,首先检查user表,如果所检查权限是user表特有的(其他权限表没有的权限类别),则user表中允许执行则Server 授予客户端访问权限,否则直接拒绝而不会继续检查其他权限表(因为其他权限表不具备该权限列表,无需检查),如果所检查权限类别除了在user表之外,在其他权限表中也具有该权限类别(例如:DML权限),则即时在user表中不允许(毕竟user表中的权限是表示是否具有全局权限的意思),也会继续往下检查db表,然后再检查tables_priv表,以此类推。
* grant语句在授予用户权限时,授予库级别权限时,数据库不需要事先存在即可授权成功,但如果是对表级别对象授权,则表需要事先存在,否则授权失败,提示表不存在的报错信息 。 * 对于存储程序的请求操作,Server 使用procs_priv表检查权限,而不是tables_priv和columns_priv表。
上文中提及的权限检查逻辑,可以使用如下布尔型的伪代码来表示:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges
PS:某些类型的一个语句可以需要请求多个类型的权限,例如:INSERT...SELECT,该语句需要请求INSERT和SELECT两个权限,而这两个权限可能在授予用户的时候授予范围不同,假如INSERT授予的是全局范围权限,而SELECT是授予的db级别的权限,此时,INSERT权限是保存在user表中的,SELECT权限是保存在db表中的,那么也就是说,这个时候Server需要分两次查询之后将两个表中记录的权限信息进行组合,然后再用于判断用户是否具INSERT...SELECT语句的访问请求权限,并返回相应的请求结果。如果任意一个权限不满足,则拒绝访问。
当mysqld启动时,将读取所有权限表的内容到内存中。后续所有用户对MySQL Server的访问的权鉴都是基于内存中保存的这些值进行。
对于权限表的重载,需要注意如下事项:
如果Server 启动时使用了--skip-grant-tables选项,则Server不会读权限表,也不会进行任何访问权限控制,这个时候任何人都可以免密码登录数据库并可以做任何事情,这种情况除非维护时间窗口,否则禁止使用,在这种情况下,如果要重新加载权限表,无需重新启动,只需要执行flush privileges;语句即可。
客户端无法连接服务器的问题
PS:MySQL 访问权限系统有如下限制:
本期内容就介绍到这里,本期内容参考链接如下:
| 作者简介
《千金良方——MySQL性能优化金字塔法则》、《数据生态:MySQL复制技术与生产实践》作者之一。
熟悉MySQL体系结构,擅长数据库的整体调优,喜好专研开源技术,并热衷于开源技术的推广,在线上线下做过多次公开的数据库专题分享,发表过近100篇数据库相关的研究文章。
全文完。