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

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

概述

在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.user\G
*************************** 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_C++ipher：指定 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)

修改用户信息

修改用户密码

语法：

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)

0

示例：

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)

1

修改用户密码插件

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

语法：

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)

2

示例：

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)

3

锁定用户

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

语法：

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)

4

示例：

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)

5

解锁用户

语法：

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)

4

示例：

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)

7

MySQL权限管理

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

查看MySQL中有哪些权限

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

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)

8

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

权限	授权资源	解释说明
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> 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)

9

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

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

查看用户的权限

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

语法：

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

示例：

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

给用户添加权限

语法：

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

示例：

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

取消用户的权限

语法：

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

示例：

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

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

旧版本中：

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

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

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

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

user表

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

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

db表

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

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)
0

tables_priv

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

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)
1

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

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

角色使用步骤

创建角色

语法：

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)
2

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

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)
3

对角色授权

语法：

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)
4

示例：

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)
5

角色中添加成员

语法：

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)
6

示例：

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)
7

激活用户角色功能

手动激活：

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)
8

自动激活：

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)
9