MySQL中用户及权限管理（mysql8.0版本）-博客园

MySQL中用户及权限管理（mysql8.0版本）

2025-06-03 20:30:46 发布 48 浏览

页面报错/反馈

已收藏点赞

概述

在MySQL中，用户与权限管理属于关键的安全机制，能让你对数据库的访问进行精准控制

MySQL用户管理

创建用户信息

语法

CREATE USER username@'host' IDENTIFIED [WITH 密码插件] BY 'password' ;

参数解析：

username：表示要创建的用户名
host：指定用户登录时所用的主机，可以是具体的 IP 地址、网段、域名，也可以使用通配符%（表示允许从任何主机登录）。
密码插件：可选项，用来加密密码的插件
password：表示用户的登录密码

示例：

创建test01用户，指定密码为123456，并设置仅本地连接：

mysql> CREATE USER test01@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

# 或者
mysql> CREATE USER test01@'127.0.0.1' IDENTIFIED by '123456';
Query OK, 0 rows affected (0.00 sec)

创建test02用户，指定密码为123456，允许所有主机可远程连接：

mysql> CREATE USER test02@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

创建test03用户，指定密码123456，只允许10.0.0.1/24网段下的虚拟主机使用

mysql> CREATE USER test03@'10.0.0.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
#或者
mysql> CREATE USER test03@'10.0.0.1/24' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

查看用户信息

查看所有用户信息（查看用户表）

mysql> select * from mysql.userG
*************************** 1. row ***************************
                    Host: %
                    User: test02
             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: NULL
             x509_issuer: NULL
            x509_subject: NULL
           max_questions: 0
             max_updates: 0
         max_connections: 0
    max_user_connections: 0
                  plugin: caching_sha2_password
   authentication_string: $A$005$>]?]C!&XUelYK|JWRaSLg.3RlFiM6RKkC8/SBd65hUvJDZiSm2F6ZPwtZaB
        password_expired: N
   password_last_changed: 2025-05-01 21:19:02
       password_lifetime: NULL
          account_locked: N
        Create_role_priv: N
          Drop_role_priv: N
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: NULL
         User_attributes: NULL

字段解析：

Host：表示主机域，白名单信息
User：用户名
*_priv：以_priv结尾的都是用来表示被授予的权限，Y表示拥有该权限，N表示没有该权限
ssl_type：指定用户使用的 SSL 连接类型。
ssl_cipher：指定 SSL 连接使用的加密算法。
x509_issuer：指定用户 SSL 证书的颁发者信息。
x509_subject：指定用户 SSL 证书的主题信息。
max_questions：用户每小时允许执行的最大查询数量，0 表示无限制。
max_updates：用户每小时允许执行的最大更新操作数量，0 表示无限制。
max_connections：用户同时允许的最大连接数，0 表示无限制。
max_user_connections：该用户在服务器上允许的最大并发连接数，0 表示无限制。
plugin：指定用户使用的认证插件，这里使用的是 caching_sha2_password。
authentication_string：存储用户加密后的密码。
password_expired：表示用户密码是否已过期，Y 表示已过期，N 表示未过期。
password_last_changed：记录用户密码最后一次修改的时间。
password_lifetime：指定用户密码的有效期，NULL 表示使用全局设置。
account_locked：表示用户账户是否被锁定，Y 表示已锁定，N 表示未锁定。
Password_reuse_history：指定密码复用历史记录的数量限制。
Password_reuse_time：指定密码复用的时间限制。
Password_require_current：指定是否要求用户提供当前密码才能更改密码。
User_attributes：可以用于存储用户的额外属性信息。

其中我们主要关注User、Host、authentication_string、plugin、account_locked这几个字段即可

查看当前用户信息

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

删除用户信息

方式一：DROP(推荐！！！)

语法：

DROP USER username@'host'

示例：删除用户test01

mysql> DROP USER test01@'localhost';
Query OK, 0 rows affected (0.00 sec)

方式二：DELETE方式

语法：

DELETE FROM mysql.user WHERE User = 'username' and Host = 'host'

示例：

mysql> DELETE FROM mysql.user WHERE User='test01' AND Host = 'localhost';
Query OK, 0 rows affected (0.00 sec)

修改用户信息

修改用户密码

语法：

ALTER mysql.user @'' identified by 'newpasswd'

示例：

ALTER mysql.user root@'localhost' identified by '123456'

修改用户密码插件

密码插件介绍
密码插件一共有两种
caching_sha2_password：新型加密方式
mysql_native_password：老版加密方式

语法：

alter user 用户名@‘主机清单’ identified with 密码插件 by '密码'

示例：

alter user root@'localhost' identified with caching_sha2_password by 'huangsir';

锁定用户

应用场景：删除用户之前，可以先锁定用户信息一段时间，确定用户不再使用之后，再进行删除。

语法：

alter user @'' account lock;

示例：

alter user test02@'localhost' account lock;

解锁用户

语法：

alter user @'' account lock;

示例：

alter user test02@'localhost' account unlock;

MySQL权限管理

mysql中的权限是对用户进行授权，使其用户能够对某一个资源进行访问、修改、删除等。

查看MySQL中有哪些权限

列字段说明
Privilege：表示可以对用户授权的所有权限名称
Context：表示设置的权限可以对数据库服务的哪些资源进行操作
Comment：对权限的用途进行解释说明

mysql> show privileges;
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege                  | Context                               | Comment                                               |
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                      | Tables                                | To alter the table                                    |
| Alter routine              | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                     | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine             | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create role                | Server Admin                          | To create new roles                                   |
| Create temporary tables    | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view                | Tables                                | To create new views                                   |
| Create user                | Server Admin                          | To create new users                                   |
| Delete                     | Tables                                | To delete existing rows                               |
| Drop                       | Databases,Tables                      | To drop databases, tables, and views                  |
| Drop role                  | Server Admin                          | To drop roles                                         |
| Event                      | Server Admin                          | To create, alter, drop and execute events             |
| Execute                    | Functions,Procedures                  | To execute stored routines                            |
| File                       | File access on server                 | To read and write files on the server                 |
| Grant option               | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                      | Tables                                | To create or drop indexes                             |
| Insert                     | Tables                                | To insert data into tables                            |
| Lock tables                | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                    | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                      | Server Admin                          | To make proxy user possible                           |
| References                 | Databases,Tables                      | To have references on tables                          |
| Reload                     | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client         | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave          | Server Admin                          | To read binary log events from the master             |
| Select                     | Tables                                | To retrieve rows from table                           |
| Show databases             | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view                  | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                   | Server Admin                          | To shut down the server                               |
| Super                      | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                    | Tables                                | To use triggers                                       |
| Create tablespace          | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                     | Tables                                | To update existing rows                               |
| Usage                      | Server Admin                          | No privileges - allow connect only                    |
| XA_RECOVER_ADMIN           | Server Admin                          |                                                       |
| SHOW_ROUTINE               | Server Admin                          |                                                       |
| RESOURCE_GROUP_USER        | Server Admin                          |                                                       |
| REPLICATION_APPLIER        | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ENABLE     | Server Admin                          |                                                       |
| GROUP_REPLICATION_ADMIN    | Server Admin                          |                                                       |
| FLUSH_USER_RESOURCES       | Server Admin                          |                                                       |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin                          |                                                       |
| ROLE_ADMIN                 | Server Admin                          |                                                       |
| BACKUP_ADMIN               | Server Admin                          |                                                       |
| CONNECTION_ADMIN           | Server Admin                          |                                                       |
| SET_USER_ID                | Server Admin                          |                                                       |
| SESSION_VARIABLES_ADMIN    | Server Admin                          |                                                       |
| RESOURCE_GROUP_ADMIN       | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ARCHIVE    | Server Admin                          |                                                       |
| BINLOG_ENCRYPTION_ADMIN    | Server Admin                          |                                                       |
| REPLICATION_SLAVE_ADMIN    | Server Admin                          |                                                       |
| SYSTEM_VARIABLES_ADMIN     | Server Admin                          |                                                       |
| SYSTEM_USER                | Server Admin                          |                                                       |
| APPLICATION_PASSWORD_ADMIN | Server Admin                          |                                                       |
| TABLE_ENCRYPTION_ADMIN     | Server Admin                          |                                                       |
| SERVICE_CONNECTION_ADMIN   | Server Admin                          |                                                       |
| AUDIT_ADMIN                | Server Admin                          |                                                       |
| BINLOG_ADMIN               | Server Admin                          |                                                       |
| ENCRYPTION_KEY_ADMIN       | Server Admin                          |                                                       |
| CLONE_ADMIN                | Server Admin                          |                                                       |
| FLUSH_OPTIMIZER_COSTS      | Server Admin                          |                                                       |
| FLUSH_STATUS               | Server Admin                          |                                                       |
| FLUSH_TABLES               | Server Admin                          |                                                       |
+----------------------------+---------------------------------------+-------------------------------------------------------+
62 rows in set (0.00 sec)

其中有几个较为核心的权限需要特别关注一下

权限	授权资源	解释说明
Select	Tables	对表进行操作，查询表中的数据信息
Insert	Tables	对表进行操作，添加表中数据
Update	Tables	对表进行操作，修改表中数据
Delete	Tables	对表进行操作，删除表中数据
Alter	Tables	对表进行操作，修改表中结构
Index	Tables	对表进行操作，修改表中索引信息
Create	Databases,Tables	对表和库进行操作，创建数据库和表
Drop	Databases,Tables	对表和库进行操作，删除数据库和表
Grant option	Databases,Tables,Functions,Procedures	是否给予 root 的超级权限，能否给其它用户授权
Usage	Server Admin	没有任何权限，只允许连接数据权限

数据库中的特殊权限

除了上述的权限之外，mysql还有三个特殊的权限

Usage：所有用户都有的一个权限，远程连接的权限。
all：将管理员身份的所有权限赋予其它用户，但是Grant option权限没有给与

mysql> grant all on *.* to test01@'%';
Query OK, 0 rows affected (0.00 sec)

Grant option：root用户给普通用户赋予权限的能力，需要单独赋予，all权限没有包含此权限。

mysql> grant grant option on *.* to test01@'%';
Query OK, 0 rows affected (0.00 sec)

查看用户的权限

应用场景：查看指定用户的权限

语法：

show grants for @''

示例：

mysql> show grants for root@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                                                                                                    |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

给用户添加权限

语法：

# 库名可以用*代替，表示所有的库，表名也可以用*代替，表示库中所有的表
grant  on (*).(*) to @'';

示例：

# 创建test01用户
mysql> CREATE USER test01@'%' IDENTIFIED BY 'huangsir';
Query OK, 0 rows affected (0.01 sec)

# 授权
mysql> grant select on *.* to test01@'%';
Query OK, 0 rows affected (0.00 sec)

# 验证
mysql> show grants for test01@'%';
+-------------------------------------+
| Grants for test01@%                 |
+-------------------------------------+
| GRANT SELECT ON *.* TO `test01`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

取消用户的权限

语法：

库名可以用*代替，表示所有的库，表名也可以用*代替，表示库中所有的表
revoke 权限 on (*).(*) from @'';

示例：

# 回收权限
mysql> revoke select on *.* from test01@'%';
Query OK, 0 rows affected (0.01 sec)

# 查看权限，USAGE是每个用户都用的权限
mysql> show grants for test01@'%';
+------------------------------------+
| Grants for test01@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO `test01`@`%` |
+------------------------------------+
1 row in set (0.00 sec)

MySQL5.7与MySQL8.0授权管理区别

旧版本中：

# 会创建用户并授予权限。
grant select on *.* from test02@'%';

新版本中：需要先创建用户再授予权限

create user oldboy@'%' identified by '123456';
grant 权限信息 on . to oldboy@'%';

数据库中关于用户及权限的表

user表

该表在mysql库中，主要存储用户、主机域（白名单），密码，加密插件等信息，还存储了.级别的权限(全局权限)

mysql> select * from mysql.user;
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host      | User             | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher             | x509_issuer              | x509_subject               | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                                                  | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| %         | test01           | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          | NULL                   | NULL                     | NULL                       |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$"r@'&D6z|QdzYq;g+@
                                                                                     >a5...cJHw.6.zMa56QSDCyLg62NqULpNlALJQ8SPn3. | N                | 2025-06-03 19:41:32   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | mysql.infoschema | Y           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          | NULL                   | NULL                     | NULL                       |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2025-05-26 14:45:26   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | mysql.session    | N           | N           | N           | N           | N           | N         | N           | Y             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          | NULL                   | NULL                     | NULL                       |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2025-05-26 14:45:26   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | mysql.sys        | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          | NULL                   | NULL                     | NULL                       |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2025-05-26 14:45:26   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
| localhost | root             | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          | NULL                   | NULL                     | NULL                       |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$7U%wi[K4LRr"'1bwP%wKqAeufFhd1JQICjzr5jodTjX7EI3MHfA.J2Gm6X/O/ | N                | 2025-05-26 15:02:07   |              NULL | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
5 rows in set (0.00 sec)

db表

该表在mysql库中，主要存储用户、主机域等信息，同时存储了单库级别的权限（局部权限）

mysql> select * from mysql.db;
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db                 | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| localhost | performance_schema | mysql.session | Y           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
| localhost | sys                | mysql.sys     | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | Y            |
+-----------+--------------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)

tables_priv

该表在mysql库中，主要存储用户、主机域等信息，同时存储了单表级别的权限（局部权限）

mysql> select * from mysql.tables_priv;
+-----------+-------+---------------+------------+----------------+---------------------+------------+-------------+
| Host      | Db    | User          | Table_name | Grantor        | Timestamp           | Table_priv | Column_priv |
+-----------+-------+---------------+------------+----------------+---------------------+------------+-------------+
| localhost | mysql | mysql.session | user       | boot@          | 0000-00-00 00:00:00 | Select     |             |
| localhost | sys   | mysql.sys     | sys_config | root@localhost | 2025-05-26 14:45:26 | Select     |             |
+-----------+-------+---------------+------------+----------------+---------------------+------------+-------------+
2 rows in set (0.00 sec)

MySQL角色管理（MySQL8.0新特性）

mysql中的角色管理是mysql8.0的新特性，整体命令语法和上述的用户和权限类似。下面是一个使用角色的案例。

角色使用步骤

创建角色

语法：

# host不填时，默认为%
create role @''

示例，创建读、写两个角色

mysql> create role test_w@'%',test_r@'%';
Query OK, 0 rows affected (0.01 sec)

对角色授权

语法：

grant 权限信息 on 库名(*).表名(*) to 角色名称

示例：

mysql> grant select on *.* to test_r@'%';
Query OK, 0 rows affected (0.01 sec)

角色中添加成员

语法：

grant 角色名 to 用户名@'白名单'

示例：

mysql> grant test_r@'%' to test01@'%';
Query OK, 0 rows affected (0.00 sec)

激活用户角色功能

手动激活：

set default role all to 用户名@'白名单';

自动激活：

#开启自动激活功能
set global activate_all_roles_on_login=on;

本文来自博客园，作者：huangSir-devops，转载请注明原文链接：https://www.cnblogs.com/huangSir-devops/p/18856928，微信Vac666666，欢迎交流

登录查看全部

参与评论