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. 如果以上检查均失败，则系统拒绝执行操作

权限信息

权限	权限级别	权限说明
CREATE	数据库、表或索引	创建数据库、表或索引权限
DROP	数据库或表	删除数据库或表权限
GRANT OPTION	数据库、表或保存的程序	赋予权限选项
REFERENCES	数据库或表
ALTER	表	更改表，比如添加字段、索引等
DELETE	表	删除数据权限
INDEX	表	索引权限
INSERT	表	插入权限
SELECT	表	查询权限
UPDATE	表	更新权限
CREATE VIEW	视图	创建视图权限
SHOW VIEW	视图	查看视图权限
ALTER ROUTINE	存储过程	更改存储过程权限
CREATE ROUTINE	存储过程	创建存储过程权限
EXECUTE	存储过程	执行存储过程权限
FILE	服务器主机上的文件访问	文件访问权限
CREATE TEMPORARY TABLES	服务器管理	创建临时表权限
LOCK TABLES	服务器管理	锁表权限
CREATE USER	服务器管理	创建用户权限
PROCESS	服务器管理	查看进程权限
RELOAD	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT	服务器管理	复制权限
REPLICATION SLAVE	服务器管理	复制权限
SHOW DATABASES	服务器管理	查看数据库权限
SHUTDOWN	服务器管理	关闭数据库权限
SUPER	服务器管理	执行kill线程权限

用户管理

用户的组成

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;