MySQL-用户与权限管理

用户与权限管理

权限控制

权限控制数据字典

mysql.user表

记录允许连接到服务器的用户帐号信息

mysql.db

记录各个帐号在各个数据库上的操作权限

mysql.tables_priv

记录数据表级的操作权限

mysql.columns_priv

记录数据列级的操作权限

mysql.host

配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

权限验证过程

自顶向下逐层下查的方式验证用户的权限

1. MySQL Server 启动时，将上面的权限表信息加载到内存中
2. 先检查mysql.user表中的全局权限，如果满足条件，则执行操作
3. 若失败，再检查mysql.db表中是否有满足条件的权限，如果满足，则执行操作
4. 若失败，再则检查mysql.table_priv和mysql.columns_priv(如果是存储过程操作则检查mysql.procs_priv)，如果满足，则执行操作
5. 如果以上检查均失败，则系统拒绝执行操作

权限信息

用户管理

用户的组成

MySQL的授权用户由两部分组成： 用户名和登录主机名，其格式为：‘username‘@‘hostname‘。单引号不是必须的，若包含有特殊字符，则必须使用单引号包围起来。

hostname 项内容

• 允许使用%和_两个通配符。
  • %：表示匹配所有
  • _：表示匹配单个字符
• 本机地址
  • localhost 代表本机
  • 127.0.0.1 代表ipv4本机地址
  • ::1 代表ipv6的本机地址

创建账户

CREATE USER ‘biu‘@‘localhost‘ IDENTIFIED  BY  ‘_password_‘;

删除用户

drop user ‘biu‘@‘localhost‘;

权限管理

刷新权限

对权限表user、db、host等做了update或者delete更新的时候，需要执行FLUSH PRIVILEGES命令来刷新内存中权限数据。

flush privileges;

查看用户权限

-- 查看当前用户的权限
show grants;

-- 查看指定用户的权限
show grants for ‘biu‘@‘localhost‘;

授权用户系统权限

grant all privileges on *.* to ‘biu‘@‘localhost‘;

回收用户权限

revoke PROCESS ON *.* from ‘biu‘@‘localhost‘;

revoke delete on *.* from ‘biu‘@‘localhost‘;

账户密码管理

更改密码

ALTER  USER  ‘biu‘@‘localhost‘  IDENTIFIED  BY  ‘_password_‘;

直接更改当前账户(非匿名)

ALTER  USER  USER()  IDENTIFIED  BY  ‘_password_‘;

SET PASSWORD = PASSWORD(‘_password_‘);

命令行更改帐户密码

mysqladmin -u  _user_name_  -h _host_name_   -p "_password_"

使用mysqladmin设置密码应该被认为是不安全的。

复制用户密码限制

使用MySQL复制，需要注意 CHANGE MASTER TO 语句限制 32 个字符；如果密码太长会被截断。

密码过期

# 手动使帐户密码失效

ALTER USER ‘biu‘@‘localhost‘ PASSWORD EXPIRE;

密码过期策略

default_password_lifetime = 180

默认为 0 ，永不过期

要求每90天更改一次密码

CREATE  USER  ‘biu‘@‘localhost‘  PASSWORD  EXPIRE  INTERVAL  90 DAY; 
ALTER  USER  ‘biu‘@‘localhost‘  PASSWORD  EXPIRE  INTERVAL  90 DAY;

禁用密码有效期

CREATE  USER  ‘biu‘@‘localhost‘  PASSWORD  EXPIRE  NEVER;
ALTER  USER  ‘biu‘@‘localhost‘  PASSWORD  EXPIRE  NEVER;

密码重用限制【8.0】

• 要禁止重复使用最近6个密码或365天以内的密码，请在服务器my.cnf文件中添加以下行 ：

[mysqld]
password_history=6
password_reuse_interval=365
或
SET GLOBAL password_history = 6;
SET GLOBAL password_reuse_interval = 365;

• 在允许重复使用之前，至少需要更改5次密码：

CREATE  USER  ‘biu‘@‘localhost‘  PASSWORD  HISTORY  5;
ALTER  USER  ‘biu‘@‘localhost‘  PASSWORD  HISTORY  5;

• 要将两种类型的重用限制结合使用

CREATE  USER  ‘biu‘@‘localhost‘  PASSWORD  HISTORY  5  PASSWORD  REUSE  INTERVAL  365 DAY;
ALTER  USER  ‘biu‘@‘localhost‘  PASSWORD  HISTORY  5  PASSWORD  REUSE  INTERVAL  365 DAY;

密码修改验证【8.0.13】

可以设置在当前用户修改自己密码时候需要输入原密码，由参数 password_require_current 控制，默认不需要

-- 修改方式
ALTER  USER  USER()  IDENTIFIED  BY  ‘_auth_string_‘  REPLACE  ‘_current_auth_string_‘;
ALTER  USER  ‘biu‘@‘localhost‘  IDENTIFIED  BY  ‘_auth_string_‘  REPLACE  ‘_current_auth_string_‘;

支持双密码【8.0.14】

从MySQL 8.0.14开始，允许用户帐户具有双重密码，分别指定为主要和次要密码。

可以无缝的进行更改密码，而不用停止应用。

示例：

1.在主上修改帐号的密码，设置新密码

ALTER  USER  ‘appuser1‘@‘host1.example.com‘  IDENTIFIED  BY  ‘_password_b_‘  RETAIN  CURRENT  PASSWORD;

2.等待密码复制到从库

3.修改应用程序的密码，使用新的密码进行连接

4.删除旧密码

ALTER  USER  ‘appuser1‘@‘host1.example.com‘  DISCARD  OLD  PASSWORD;

RETAIN CURRENT PASSWORD : 保留帐户的当前密码作为其辅助密码。客户端使用新旧密码都可以连接。如果使用 alter user 或 set password 设置新密码为空，则旧密码也会变为空
设置辅助密码需要权限 APPLICATION_PASSWORD_ADMIN

生成随机密码【8.0.18】

mysql> CREATE USER ‘y1‘@‘localhost‘ IDENTIFIED BY RANDOM PASSWORD,‘y2‘@‘%‘ IDENTIFIED BY RANDOM PASSWORD;
+------+-----------+----------------------+
| user | host      | generated password   |
+------+-----------+----------------------+
| y1   | localhost | :ir5:D]*cbi}V3aP68/r |
| y2   | %         | i2Zi/T+HpEw%MK:j{E5r |
+------+-----------+----------------------+
2 rows in set (0.01 sec)

# 修改为随机密码
mysql>  ALTER  USER  ‘y1‘@‘localhost‘  IDENTIFIED  BY  RANDOM  PASSWORD,  ‘y2‘@‘%‘ IDENTIFIED  BY  RANDOM  PASSWORD;

生成的密码长度默认为 20 ，长度由参数 generated_random_password_length控制[5 - 255]

登录失败跟踪和临时帐户锁定【8.0.19】

对账户设置登陆失败次数和锁定时间

CREATE  USER  ‘u1‘@‘localhost‘  IDENTIFIED  BY  ‘_password_‘  FAILED_LOGIN_ATTEMPTS  3  PASSWORD_LOCK_TIME  3;

设置账户资源限制

可以对账户进行如下限制：

• 帐户每小时可发出的查询数量
• 帐户每小时可以发布的更新次数
• 帐户每小时可以连接到服务器的次数
• 帐户同时连接到服务器的数量

-- 设置资源限制
CREATE USER ‘francis‘@‘localhost‘ IDENTIFIED BY ‘frank‘
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;

-- 修改
mysql>  ALTER  USER  ‘francis‘@‘localhost‘  WITH  MAX_QUERIES_PER_HOUR  100;

-- 删除限制
mysql>  ALTER  USER  ‘francis‘@‘localhost‘  WITH  MAX_CONNECTIONS_PER_HOUR  0;

MySQL-用户与权限管理

原文：https://www.cnblogs.com/binliubiao/p/15233804.html