概述
在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_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 <username>@'<host>' 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 <username>@'<host>' account lock;
示例：
alter user test02@'localhost' account lock;
解锁用户
语法：
alter user <username>@'<host>' account unlock;
示例：
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 <username>@'<host>'
示例：
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 <username>@'<host>';
示例：
```sql
创建test01用户
mysql> CREATE USER test01@'%' IDENTIFIED BY 'huangsir';
Query OK, 0 rows affected (0.01 sec)
授权
mysql> grant select on *
文章整理自互联网，只做测试使用。发布者：Lomu，转转请注明出处：https://www.it1024doc.com/12606.html
