当客户端连接 MySQL 服务器时,必须提供有效的身份认证,例如用户名和密码。当用户执行任何数据库操作时,服务器将会验证用户是否具有相应的权限,例如查询表需要 SELECT 权限,删除对象需要 DROP 权限。
为了方便用户权限的管理,MySQL 8.0 提供了角色的功能。角色(Role)是一组权限的集合。
本篇我们讨论 MySQL 中的账户和权限的管理。
5.1 管理用户
5.1.1 创建用户
MySQL 使用 CREATE USER 语句创建用户,基本语法如下:
CREATE USER [IF NOT EXISTS] account_name
IDENTIFIED BY 'password';
复制代码
其中,account_name 是账户名称;账户名称分为两个部分:用户名(user_name)和主机名(host_name),使用 % 连接。IDENTIFIED BY 用于指定用户的密码。IF NOT EXISTS 用于避免创建重名账户时产生错误信息。
以下语句创建一个新的用户 dev01,它可以从本机登录(localhost):
mysql> CREATE USER dev01@localhost IDENTIFIED BY 'Dev01@mysql';
Query OK, 0 rows affected (0.21 sec)
复制代码
MySQL 中的账户由用户名和主机名共同决定,主机 office.example.com 上的 dev01 和主机 home.example.com 上的 dev01 是两个账户。如果不指定主机名,表示用户可以从任何主机登录:
user_name
user_name@%
复制代码
% 是通配符,表示任何字符串;另外,_ 表示任意单个字符。
如果用户名或主机名中包含特殊字符,例如空格或者 - ,需要使用引号分别引用这两部分内容:
'user-name'@'host-name'
复制代码
除了单引号之外,也可以使用反引号(`)或者双引号(")。
MySQL 中的账户信息存储在系统数据库 mysql 的 user 表中:
mysql> select host, user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | dev01 |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
5 rows in set (0.00 sec)
复制代码
除了 dev01@localhost 之外,其他 4 个用户都是初始化创建的系统用户。
除了基本语法之外,创建用户时还可以指定更多选项:
resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
复制代码
resource_option 用于限制该用户对系统资源的使用:
- MAX_QUERIES_PER_HOUR,每小时允许执行的查询次数。默认为 0 ,表示没有限制;
- MAX_UPDATES_PER_HOUR,每小时允许执行的更新次数。默认为 0 ,表示没有限制;
- MAX_CONNECTIONS_PER_HOUR,每小时允许执行的连接次数。默认为 0 ,表示没有限制;
- MAX_USER_CONNECTIONS,该用户并发连接的数量。默认为 0 ,表示没有限制;此时用户的并发连接数由系统变量 max_user_connections 决定。
以下语句创建一个新的账户 dev02,允许从任何主机登录。同时限制该用户每小时最多执行 1000 次查询和 100 次更新:
mysql> CREATE USER 'dev02'@'%'
-> WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 100;
Query OK, 0 rows affected (0.01 sec)
复制代码
注意第二行的->
是客户端的提示符,不是输入的内容。查询系统用户表可以显示以上设置:
mysql> select host, user, max_questions, max_updates from mysql.user;
+-----------+------------------+---------------+-------------+
| host | user | max_questions | max_updates |
+-----------+------------------+---------------+-------------+
| % | dev02 | 1000 | 100 |
| localhost | dev01 | 0 | 0 |
| localhost | mysql.infoschema | 0 | 0 |
| localhost | mysql.session | 0 | 0 |
| localhost | mysql.sys | 0 | 0 |
| localhost | root | 0 | 0 |
+-----------+------------------+---------------+-------------+
6 rows in set (0.00 sec)
复制代码
以下是密码管理选项:
password_option: {
PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
| PASSWORD HISTORY {DEFAULT | N}
| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}
复制代码
密码管理选项可以用于设置密码的过期策略、重用策略和修改密码时的验证:
- PASSWORD EXPIRE,将密码立即设置为过期;PASSWORD EXPIRE DEFAULT,使用全局的密码过期策略,由系统变量 default_password_lifetime 决定;PASSWORD EXPIRE NEVER,密码永不过期;PASSWORD EXPIRE INTERVAL N DAY 密码每隔 N 天过期;
- PASSWORD HISTORY DEFAULT,使用全局的密码重用策略,由系统变量 password_history 决定;PASSWORD HISTORY N,新密码与最近 N 次密码不能重复;
- PASSWORD REUSE INTERVAL DEFAULT,使用全局的密码重用策略(按照时间间隔指定),由系统变量 password_reuse_interval 决定;PASSWORD REUSE INTERVAL N DAY,新密码与最近 N 天内的密码不能重复;
- PASSWORD REQUIRE CURRENT,用户修改密码时需要输入当前密码;PASSWORD REQUIRE CURRENT OPTIONAL,用户修改密码时不需要输入当前密码;PASSWORD REQUIRE CURRENT DEFAULT,使用全局策略,由系统变量 password_require_current 决定。
账户的密码选项同样可以通过 mysql.user 表查看:
mysql> select host,user,
-> password_expired, password_last_changed,
-> password_lifetime, password_reuse_history,
-> password_reuse_time, password_require_current
-> from mysql.user;
+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+
| host | user | password_expired | password_last_changed | password_lifetime | password_reuse_history | password_reuse_time | password_require_current |
+-----------+------------------+------------------+-----------------------+-------------------+------------------------+----------------