MySQL授权认证

1. MySQL权限系统介绍

权限系统的作用是授予来自某个主机的某个用户可以查询、插入、修改、删除等数据库操作的权限。
不能明确的指定拒绝某个用户的具体某个连接
权限控制(授权与回收)的执行语句, 包括create user,grant,revoke
授权后的权限都会存放在MySQL的内部数据库中(数据库名叫mysql),并在数据库启动之后把权限信息复制到内存中
MySQL用户的认证信息不光包括用户名，还要包含连接发起的主机

sql

# 以下两个joe被认为不是同一个用户
SHOW GRANTS FOR 'joe'@'office.example.com'
SHOW GRANTS FOR 'ioe'@'home.example.com'

2. 权限分类

2.1 权限级别

权限级别	存储表
所有库权限	mysql.user
指定库权限	mysql.db
指定表权限	mysql.tables_priv
指定列权限	mysql.columns_priv
在MySQL5.7中还有proxies_priv表，它通过代理用户实现用户角色的功能，但是在MySQL8.0之后内部已经有相关Role的角色功能，不再使用proxies_priv表。

权限管理规范

删除所有用户名为空的用户
不允许密码为空的用户存在
管理员用户可以有所有库的权限
开发应用只需给相应库的权限

2.2 常用的权限

SQL语句: SELECT、INSERT、UPDATE、DELETE、INDEX
存储过程: CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
管理权限：SUPER RELOAD、SHOW DATABASE、SHUTDOWN、GRANT OPTION
更多权限请参考：https://dev.mysql.com/doc/refman/8.4/en/privileges-provided.html

3. 创建用户

创建用户jack,允许任何机器可以登录：

sql

(root@localhost)[performance_schema]>create user 'jack'@'%' identified by 'jack';
Query OK, 0 rows affected (0.01 sec)

4. 删除用户

sql

(root@localhost)[performance_schema]>drop user 'jack'@'%';
Query OK, 0 rows affected (0.00 sec)

5. 查看用户权限

比如查看root用户的权限：

(root@localhost)[performance_schema]>show grants for 'jack'@'%';
+----------------------------------+
| Grants for jack@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'jack'@'%' |
+----------------------------------+
1 row in set (0.00 sec)

可以看到jack用户只有基本的连接权限。

6. 授予权限

sql

(root@localhost)[performance_schema]>grant insert,delete,select,update on test.* to 'jack'@'%';
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[performance_schema]>show grants for 'jack'@'%';
+----------------------------------------------------------------+
| Grants for jack@%                                              |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jack'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'jack'@'%' |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)
-- 如果还要添加权限，还是使用grant
(root@localhost)[performance_schema]>grant create,drop,index on test.* to 'jack'@'%';
Query OK, 0 rows affected (0.01 sec)

还有一种方式创建用户同时授予权限(过时)：

sql

(root@localhost)[performance_schema]>grant insert,delete,select,update on test.* to 'amy'@'%' identified by 'amy';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost)[performance_schema]>show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

经过测试发现8.X版本已经不再支持这种方式。

7. 修改用户密码

sql

(root@localhost)[performance_schema]>alter user 'jack'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

8. 回收权限

sql

(root@localhost)[performance_schema]>revoke index on test.* from 'jack'@'%';
Query OK, 0 rows affected (0.00 sec)

9. 得到授权的权限

在grant的语句后面末尾加上with grant option:

sql

-- 授予amy用户查询权限并只能自己用
(root@localhost)[performance_schema]>grant select on test.t_student to 'amy'@'%' with grant option MAX_USER_CONNECTIONS 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

10. 查询权限元数据表

10.1 查询全局权限

sql

(root@localhost)[mysql]>SELECT *  FROM user where user='jack'\G
*************************** 1. row ***************************
                  Host: %
                  User: jack
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
      password_expired: N
 password_last_changed: 2024-11-30 11:05:00
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

由于当时授予jack的数据库级别的权限，所以在user表中看到都是N(无权限)。另外authentication_string字段在5.7之前是叫password，存放的加密后的字符串，密文不可逆得到原密码，是使用password()函数处理后的结果。

10.2 查看数据库级别权限

sql

(root@localhost)[mysql]>SELECT *  FROM db where user='jack'\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: jack
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.00 sec)

10.3 查看表级别权限

amy用户是授予t_student表权限的，查询表权限信息：

sql

(root@localhost)[mysql]>SELECT *  FROM tables_priv where user='amy'\G
*************************** 1. row ***************************
       Host: %
         Db: test
       User: amy
 Table_name: t_student
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Grant
Column_priv: 
1 row in set (0.00 sec)

10.4 查看字段级别权限

目前还没有grant某个用户字段级别的权限，所以表为空

sql

(root@localhost)[mysql]>SELECT *  FROM columns_priv\G
Empty set (0.00 sec)

危险

不建议直接修改user,db,tables_priv,columns_priv去维护用户权限信息, 推荐使用前面的create user,grant等命令，因为操作mysql系统数据库的库表有很大风险出错导致不可想象的问题。

11. 配置密码插件

在MySQL安装目录下，支持密码验证的插件是component_validate_password.so和validate_password.so，在MySQL8.0及更高版本中推荐使用component_validate_password.so。

11.1 通过配置文件启用

编辑my.cnf配置文件启用验证密码插件：

ini

[mysqld]  
validate_password_component=validate_password

重启MySQL服务以使更改生效。

11.2 通过命令启用

在MySQL命令行客户端中，您还可以直接加载该插件：

sql

-- mysql8.X
INSTALL COMPONENT 'file://component_validate_password';
-- mysql5.7
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

11.3 检查插件状态

使用以下命令确认哪些插件已经被加载：

sql

SHOW PLUGINS; 
-- 或者查询plugins表
SELECT * FROM information_schema.plugins where plugin_name LIKE '%password%';

11.4 查看生效的默认密码规则

Alt text 此时如果创建用户密码或者修改密码会要求密码符合规则：

提示

默认是中级密码规则，不同密码规则如下：

规则	验证规则
`0`或者`LOW`	Length
`1`或者`MEDIUM`	Length; numeric, lowercase/uppercase, and special characters
`2`或者`STRONG`	Length; numeric, lowercase/uppercase, and special characters; dictionary file

可以看出高级验证规则多了一个字典文件的验证。

11.5 使用高级校验密码规则

在data目录下面创建dic.file文件：

[root@hadoop104 data]# echo 'Password123_' >> dic.file
[root@hadoop104 data]# chown mysql:mysql dic.file

设置密码字典文件路径

mysql> set global validate_password.dictionary_file='/opt/module/mysql-8.0.39/data/dic.file';
Query OK, 0 rows affected (0.01 sec)

设置密码级别为最高

mysql> set global validate_password.policy=2;

验证设置

mysql> alter user 'jack'@'%' identified by 'Password123_';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

可以发现虽然符合密码大小写、含有数字、特殊字符的密码规范也不能通过，因为不满足字典文件。此时查看系统变量发现变量有所变化：

mysql> show variables like 'validate%';
+-------------------------------------------------+----------------------------------------+
| Variable_name                                   | Value                                  |
+-------------------------------------------------+----------------------------------------+
| validate_password.changed_characters_percentage | 0                                      |
| validate_password.check_user_name               | ON                                     |
| validate_password.dictionary_file               | /opt/module/mysql-8.0.39/data/dic.file |
| validate_password.length                        | 8                                      |
| validate_password.mixed_case_count              | 1                                      |
| validate_password.number_count                  | 1                                      |
| validate_password.policy                        | STRONG                                 |
| validate_password.special_char_count            | 1                                      |
+-------------------------------------------------+----------------------------------------+
8 rows in set (0.00 sec)

其中validate_password.check_user_name被开启了，表示密码中不能包含用户名。
5. 如需要卸载当前的密码插件：

sql

(root@localhost)[(none)]>UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected (0.00 sec)

12. 用户密码过期

使用password expire可以强制用户修改密码：

mysql> alter user 'jack'@'%' password expire;
Query OK, 0 rows affected (0.00 sec)

Alt text 用户还是能够登录，可以在登录进去后设置密码。

13. 角色管理

在MySQL8.0之后，原生支持了角色管理的功能。

13.1 创建Role

sql

CREATE ROLE senior_dba, app_dev;
GRANT ALL ON *.* to senior_dba WITH GRANT OPTION;
GRANT SELECT,DELETE,UPDATE,INSERT,CREATE,DROP ON wp.* TO app_dev;

13.2 角色和用户绑定

sql

CREATE USER 'jack'@'%' identified BY 'jack';
GRANT senior_dba TO 'jack'@'%' ;

查看jack用户的权限信息：

mysql> SHOW grants FOR 'jack'@'%' ;
+--------------------------------------+
| Grants for jack@%                    |
+--------------------------------------+
| GRANT USAGE ON *.* TO `jack`@`%`     |
| GRANT `senior_dba`@`%` TO `jack`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)

并没有显示出来我们想看的具体权限内容，需要在命令后面加上using 角色名称：
Alt text

MySQL授权认证 ​

1. MySQL权限系统介绍 ​

2. 权限分类 ​

2.1 权限级别 ​

2.2 常用的权限 ​

3. 创建用户 ​

4. 删除用户 ​

5. 查看用户权限 ​

6. 授予权限 ​

7. 修改用户密码 ​

8. 回收权限 ​

9. 得到授权的权限 ​

10. 查询权限元数据表 ​

10.1 查询全局权限 ​

10.2 查看数据库级别权限 ​

10.3 查看表级别权限 ​

10.4 查看字段级别权限 ​

11. 配置密码插件 ​

11.1 通过配置文件启用 ​

11.2 通过命令启用 ​

11.3 检查插件状态 ​

11.4 查看生效的默认密码规则 ​

11.5 使用高级校验密码规则 ​

12. 用户密码过期 ​

13. 角色管理 ​

13.1 创建Role ​

13.2 角色和用户绑定 ​