MySQL系列：11 用户管理之1 认证

开门见山

与其他服务软件类似，MySQL的用户管理用于控制不同用户的不同权限，用以实现不同用户的不同数据访问需求，同时保证数据的安全性。

MySQL使用双层验证模式来响应用户的连接和查询请求，即认证Authentication和授权Authorization，具体验证过程如下：

MySQL系列：11 用户管理之1 认证-LMLPHP

认证Authentication：即验证用户的身份。MySQL客户端每次连接至服务器时，都需要进行认证，除非服务端开启免认证模式。

授权Authorization：即验证用户的权限。MySQL对用户的每次请求进行权限验证，只有当用户的相关权限验证成功，才允许执行相关操作。

本文将就MySQL的认证Authentication进行详细描述。

MySQL认证杂货

用户信息查看

MySQL自带mysql数据库中user表用于存储用户信息。包括用户名user、用户主机host(客户端主机)、用户权限、资源限制等。查看user表的建表语句如下：

mysql> 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 '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `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,
  `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',
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
  `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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

查看所有用户信息

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| common           | %         |
| root             | %         |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

注意：

1. user表中的host列是客户端host名（不是server端host名），可以为localhost（本机），可以为%等通配符，还可以是IP地址；

2. 若客户端与服务端为同一主机，使用mysql连接时可以不指定服务端主机名；若客户端与服务端不为同一主机，则mysql连接时需要指定server端主机名或IP地址，如下：

mysql -u<username> -p<password> -h<server_host>

Creating a User Account创建用户账户

MySQL使用CREATE USER...IDENTIFIED BY语句来创建相关账户信息，如下：

MySQL [192.168.124.12] SQL> create user 'common'@'%' identified by 'common';
Query OK, 0 rows affected (0.0523 sec)

注意：

账户名包含用户名和用户主机两部分，分别通过单引号包围；
从MySQL8.0.4开始，创建用户时默认的密码认证插件从之前的mysql_native_password修改为caching_sha2_password，所以若需要使用如上原始密码验证，需要修改创建语句如下：

create USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

Host Name Patterns主机名模式

MySQL支持丰富主机名格式，包括hostname（如localhost）、Qualified host name（如localhost.example.com）、IP地址/掩码、通配符（’%’、‘_’）等。其中通配符’%’匹配任意长度字符串、通配符’_’匹配单个字符；

此外，可创建匿名的MySQL用户，如下：

mysql> CREATE USER ''@'localhost';

注意：5.7.24-0ubuntu0.16.04.1-log版本创建匿名用户后，导致有名用户不能用。具体原因不明。

设置/修改用户密码

官方推荐使用alter user XXX语句修改用户密码，如下：

alter user 'root'@'localhost'  IDENTIFIED BY 'root';

若因为忘记密码采用skip-grant-tables选项启动的mysqld守护进程，直接执行此命令可能会报错，如下：

需要先flush privileges;刷新权限系统相关表。

强制使密码到期

DBA在某些场合可以强制使应用用户密码到期，如下：

ALTER USER 'common'@'%' PASSWORD EXPIRE;

到期后的用户还是可以登录到mysql，只是不能执行任何操作，如下：

MySQL系列：11 用户管理之1 认证-LMLPHP

其他

MySQL还支持其他插件模块进行认证授权，如Linux系统的PAM（Pluggable Authentication Modules）认证，此认证方式下，MySQL本身并不存储用户密码信息，而是使用OS的认证机制对密码进行认证。同时，客户端也是用使用mysql_clear_password实现用户密码的纯文本发送，如：

jwlLinux jwllinux # cat /etc/mysql/conf.d/mysql.cnf
[client]
enable-cleartext-plugin

# add by zavier 20190720
user=root
password=root
show-warnings

总结

MySQL的所有的用户认证/授权都记录在mysql.user表中，合理的配置用户认证方式与权限是DBA的主要职责之一。合理的用户认证/授权可防止应用用户非故意的破坏操作，也可防止恶意用户的非法破坏。

那个少年