MySQL 用户账号管理（Accounts Management）

最新推荐文章于 2024-09-12 17:24:50 发布

V1ncent Chen

最新推荐文章于 2024-09-12 17:24:50 发布

阅读量1.3k

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/frostlulu/article/details/132907997

版权

MySQL 专栏收录该内容

41 篇文章 17 订阅

订阅专栏

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

一、用户账号简介

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个小写字母，一个特殊字符，满足强度要求，修改成功。

V1ncent Chen

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录