MySQL 用户账号管理（Accounts Management）

用户需要通过账号连接到MySQL Server，本文总结了MySQL账号的常用管理操作。

目录

一、用户账号简介

二、账号创建

三、账号权限管理

3.1 权限赋予与回收

3.1.1 库级赋权

3.1.2 表级赋权

3.1.3 列级赋权

3.1.4 存储过程和函数赋权

3.1.5 权限查询

3.1.6 权限回收

3.2 通过角色集中管理权限

四、账号密码管理

4.1 修改账号密码

4.2 强制用户修改密码

4.3 强制密码复杂度

4.3.1 安装控制插件

4.3.2 设置强度控制变量

4.3.3 强度控制测试

---

一、用户账号简介

MySQL的账号信息存储在mysql.user表中，该表中包含了账号名称，密码，权限等相关信息，可以通过desc mysql.user命令来查看表的结构：

desc mysql.user;

注意该表的主键是Host，User组成的复合主键，这也代表了MySQL账号的组成结构，MySQL的账号由User和Host两部分组成，其格式为'User'@'Host'，其中Host部分限制了用户可以登录的地址。这也意味着用户名相同并不代表是同一账号，'abc'@'host1'和'abc'@'host2'是两个完全无关的账号。

在登录的时候，MySQL需要对我们的账号进行验证，这个验证的操作是由专门的认证插件来完成的。通过show plugins我们可以看到MySQL的认证插件（Type为Authentication）：

show plugins;

同时在我们创建账号时，认证插件会采用哈希算法对密码进行加密，并存储到mysql.user的authentication_string字段（如果是旧的MySQL版本，这个字段也可能是password）：

select user,host,plugin,authentication_string from mysql.user where user='root';

上面查询中，plugin字段代表该账号登录时使用的验证插件。

MySQL的3种认证插件简介如下：

• mysql_native_password：MySQL 8.0以前的默认认证插件，使用默认的哈希算法。
• sha256_password：使用sha-256哈希算法，比mysql_native_password安全性更高。
• caching_sha2_password：MySQL 8.0开始的默认认证插件，sha256_password的升级版，客户端必须配置使用SSL加密连接。

注意：如果MySQL从低版本升级到8.0时，由于默认认证插件的变化，可能导致之前的应用无法连接到数据库，此时可以配置SSL连接，或者将账号的认证插件降级到mysql_native_password保持兼容。

二、账号创建

用create user语句创建新的账号，通过identified by子句设置密码：

create user 'vincent'@'localhost' identified by 'password';

上述语句创建了一个用户名为vincent的账号，并且限制只能从本地（localhost）登录。这里的主机名也可以用IP地址、网段、域名等替代：

create user 'vincent'@'192.168.3.8' identified by 'password';
 
create user 'vincent'@'192.168.1.%' identified by 'password';
 
create user 'vincent'@'%.example.com' identified by 'password';

虽然账号名都是vincent，但他们都是独立的账号，只能从限定的Host登录，其中%代表通配符，如果主机名用%代替，那么则代表该用户可以从任何地方登录。

如果在创建账号时忽略了Host部分，则MySQL默认该账号可以从任何地方登录（host被设置为%）：

create user 'vincent' identified by 'password';
 
select user,host from mysql.user where user='vincent';

在创建账号时，账号的认证插件会使用系统的默认设置（由参数 default_authentication_plugin控制）：

show variables like 'default_authentication_plugin';

你也可以在创建账号时使用with ‘plugin’子句显式指定认证插件：

create user 'vincent'@'192.168.3.8' identified with 'sha256_password' by 'password';

上面显示指定了sha256_password作为改账号的认证插件。

三、账号权限管理

当账号刚创建时，默认只有一个USAGE权限，即仅可以连接到服务器。你还需要为账号进行赋权才可以使用，赋权时要遵循最小适用原则，即仅对用户赋予满足其需求的最小权限。

MySQL的权限有很多，可以通过show privileges命令查看所有的权限，权限后面有相应的注释：

show privileges;

上述命令查看的是权限明细，你也可以用关键字all来替代所有权限，all的权限非常高，慎用。即使要使用，也应限制在一定的范围内。

3.1 权限赋予与回收

权限的赋予是通过grant语句完成的，语句格式为：grant '权限' on '对象' to '账号';

我们先建一个测试数据库和表：

create database mydb;
 
use mydb;
 
create table mytable(id int primary key, name varchar(32));

下面演示几种常用的赋权操作：

3.1.1 库级赋权

可以用db_name.*来对某数据库下所有对象统一赋权：

grant select on mydb.* to 'vincent'@'localhost';

mydb.* 代表了mydb数据下所有的对象，上面语句赋予了查询该数据库下所有对象的权限。

如果有多项权限需要赋予，可以用逗号分隔：

grant insert,delete,update on mydb.* to 'vincent'@'localhost';

上述语句赋予了mydb数据库下所有对象的增、删、改权限。

给用户赋予数据库下所有对象（mydb.*）上的所有权限（all）：

grant all on mydb.* to 'vincent'@'localhost';

权限all代表了数据库mydb下的所有权限，使用时要小心。

如果在赋权的语句后面跟上with grant option，则该用户可以继续为其他用户赋权，可能导致权限泛滥，不推荐使用：

grant all on mydb.* to 'vincent'@'localhost' with grant option;

3.1.2 表级赋权

有些时候，我可能想限制用户只能查询特定的表，我们可以用db_name.table来将权限限制在表级别：

给用户'vincent'@'localhost'赋予数据库mydb下mytable表的读取权限：

grant select on mydb.mytable to 'vincent'@'localhost';

3.1.3 列级赋权

如果我想将用户的选项限定到列，可以在相应的权限后指定列名：

给用户'vincent'@'localhost'赋予数据库mydb下mytable表name列的查询和更新权限：

grant select(id,name), update(name) on mydb.mytable to 'vincent'@'localhost';

上述select(id,name), update(name)，将查询权限限定在id，name列，将更新权限限定在name列。

3.1.4 存储过程和函数赋权

如果要赋予存储过程或函数的相关权限，只需要带上procedure或function关键字即可：

grant execute on procedure sys.execute_prepared_stmt to 'vincent'@'localhost';
 
grant execute on function sys.version_patch to 'vincent'@'localhost';

3.1.5 权限查询

赋权后，我们可以通过show grants for '账号'，来查询某账号被赋予权限：

show grants for 'vincent'@'localhost';

如果仅执行show grants; 命令（没有for子句），那么就是查询自己的权限。

3.1.6 权限回收

权限回收是通过revoke语句完成的，格式和赋权相同，只是将grant关键字替换为revoke，to关键字替换为from。格式为：revoke '权限' on '对象' from '账号';

revoke execute on procedure sys.execute_prepared_stmt from 'vincent'@'localhost';

3.2 通过角色集中管理权限

上面的示例都是针对账号直接赋权，如果有很多用户有相似的权限，那么为每个用户独立赋权就很麻烦了，这种场景可以利用角色（role）来集中管理权限。

角色（role）是权限的集合，你可以将权限赋给角色，然后将角色赋给账号，这会方便权限的集中管理，如果涉及通用权限调整，只需要调整角色的权限即可，部分用户如果需要特别的权限，可以单独赋予。

下面创建2个角色，分别对应普通用户组和管理员组：

create role user_group, admin_group;

普通组只有mydb下查询权限，管理员组具有所有权限：

grant select on mydb.* to user_group;
 
grant all on mydb.* to admin_group;

最后只要将角色赋给相应的账号即可：

grant user_group to 'vincent'@'localhost';

四、账号密码管理

在建立账号时，我们会指定密码，有时候这可能是一个初始密码，需要用户自己去修改。

4.1 修改账号密码

修改自己的密码通过set password语句来修改自己的密码，语句格式为：set password='密码';

下面的语句将自己账号的密码修改为vincent：

set password='vincnet';

如果你有权限替别人修改密码，可以用set password for或者alter user语句来修改别人的密码：

set password for 'vincent'@'localhost'='password';
 
alter user 'vincent'@'localhost' identified by 'password';

上面两个语句的效果是相同的，任意选择一种即可。

4.2 强制用户修改密码

某些场景我们需要强制用户修改密码，只需要将用户密码的状态设置为"过期"，当密码的状态为过期时，账号允许连接至数据库，但是在修改密码前不会允许其他操作，由此来强制用户修改密码。

将单一账号密码状态设置为过期：

alter user 'vincent'@'localhost' password expire;

如果需要将一批账号同时设置为过期，可以通过update语句将myql.user表的password_expired字段批量设置为'Y'，效果是一样的：

update mysql.user set password_expired='Y' where user='vincent' and host='localhost';
 
flush privileges;

update之后记得flush privileges重载权限表，否则不会生效。如果用alter user则不需要。

'vincent'@'localhost'密码失效后，执行其他语句报错（提示修改密码），只有修改密码后，限制才解除：

show databases;
 
set password='password';
 
show databases;

4.3 强制密码复杂度

通常情况下，MySQL是不会要求密码强度的，用户可以任意输入简单密码。如果要限制密码满足一定的强度规则，我们可以利用MySQL自带的validate_password插件来控制。

4.3.1 安装控制插件

默认该插件是没有安装的，我们要先安装该插件：

install plugin validate_password soname 'validate_password.so';

插件的目录可以通过变量plugin_dir来查看，在操作系统的该目录下，我们可以搜到该插件：

select @@plugin_dir;

cd /usr/local/mysql/lib/plugin/
 
ll | grep validate_password

4.3.2 设置强度控制变量

插件安装好之后，我们就可以查看相关的控制变量了，通过修改变量的值，可以控制修改密码的强度要求：

show variables like 'validate_password%';

各个变量含义说明如下：

• validate_password_check_user_name：检查密码是否和用户名相似，默认就是打开的。
• validate_password_dictionary_file：密码字典值，用来排除部分密码。
• validate_password_length: 密码的最小长度。
• validate_password_mixed_case_count：密码字母大小写混合数量，默认1代表至少1个大小字母和1个小写字母。
• validate_password_mixed_number_count：密码必须包含的数字数量，默认1代表至少1个数字。
• validate_password_special_char_count：密码至少包含的特殊字符数量，默认1代表至少1个特殊字符。
• validate_password_policy：强度检测等级，有low, medium, strong共3个等级，默认为medium。

值为low时，仅检测一项密码长度。

值为medium时，检查密码长度，混合大小写，数字数量和特殊字符4项。

值为strong时，还会额外增加限制，密码不能与字典值文件（validate_password_dictionary_file）中密码相同，用来排除设置某些密码。

我们可以根据自己的需要修改这些，变量的值：

set global validate_password_length=10;
 
set global validate_password_mixed_case_count=2;
 
set global validate_password_number_count=2;

这里我修改了密码最小长度为10，必须有2个大小写混合，2个数字。

为了防止重启后丢失，推荐将其放到配置文件[mysqld]模块中：

[mysqld]
plugin-load-add=validate_password.so
validate_password_length=10
validate_password_mixed_case_count=2
validate_password_number_count=2
validate_password_special_char_count=1

4.3.3 强度控制测试

完成上面的配置后，密码强度控制就已经生效了。这些规则只会影响未来修改密码或者新建用户的操作，并不影响已有账户。

给vincent账号改一个简单的密码，提示密码不满足当前策略：

set password for 'vincent'@'localhost'='weakpassword';

可以通过函数validate_password_strength来评估密码的强度（0最弱，100最强）：

select validate_password_strength('abc');
 
select validate_password_strength('Asda@#9asC7U');

只能设置满足强度的密码：

set password for 'vincent'@'localhost'='12aaAA@890';

密码'12aaAA@890'，长度10位，包含5个数字，2个小写字母，2个小写字母，一个特殊字符，满足强度要求，修改成功。