MySQL用户管理完整教程
在实际项目中，一个数据库会由多个用户同时使用，此时需要创建多个用户并赋予不同的管理权限。一般来说，用户权限可以分为超级管理员权限，管理员权限，读写权限，只读权限等。
一般情况下，MySQL安装配置成功后，会自动设置一个root用户，root用户是数据库的超级管理员用户，root用户拥有对MySQL数据库操作的所有权限，为了便于管理，需要创建其他用户同时授予不同的权限。MySQL用户的格式如下：
username@hostname说明：hostname：表示该用户可以通过哪些客户端主机登录当前服务器上的MySQL服务。
（1）hostname可以是主机名，也可以是IP地址。但是在MySQL中，主机名和IP地址属于不同的主机，比如：127.0.0.1和localhost表示不同的主机。
（2）hostname可以使用通配符。比如：root@‘10.1.1.%’，表示root用户可以通过10.1.1网段的所有主机登录MySQL。
一、查看用户
MySQL的用户及权限信息存储在系统数据库mysql中的 user 表和db中。
1、使用DESC USER命令查看user表结构如下：
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)2、使用DESC USER命令查看db表（某个用户针对某个数据库的权限）结构如下：
mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)二、创建用户
创建用户常用的有两种方式：（1）使用create user命令；（2）使用grant命令。
1、使用create user命令，语法如下：
create user '用户名'@'主机名' identified by '密码';例如：
create user 'wang'@'localhost' identified by 'Wgx123456.';
mysql> select host,user,authentication_string from user;
+-------------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-------------+---------------+-------------------------------------------+
| localhost | root | *D042427D51E98841EFCD0C8A58186C56D7A1CFCD |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| 192.168.1.% | repl | *D042427D51E98841EFCD0C8A58186C56D7A1CFCD |
| localhost | admin | *C71BFC2E4C2341D35621FEDE96DA1A174D197A52 |
| localhost | wang | *D042427D51E98841EFCD0C8A58186C56D7A1CFCD |
+-------------+---------------+-------------------------------------------+
6 rows in set (0.00 sec)查看新建的用户权限，可以看出该用户wang没有任何权限。
mysql> select * from user where user='wang'\G
*************************** 1. row ***************************
Host: localhost
User: wang
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: *D042427D51E98841EFCD0C8A58186C56D7A1CFCD
password_expired: N
password_last_changed: 2019-12-25 21:45:12
password_lifetime: NULL
account_locked: N
1 row in set (0.03 sec)使用grant命令给该用户授权：
mysql> grant select,insert,delete,update on my_db.* to 'wang'@'localhost';
mysql> select * from user where user='wang'\G
*************************** 1. row ***************************
Host: localhost
User: wang
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
mysql> select * from db where user='wang'\G
*************************** 1. row ***************************
Host: localhost
Db: my_db
User: wang
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y使用grant命令重新给该用户授权：
grant select,insert,delete,update on *.* to 'wang'@'localhost';
mysql> grant select,insert,delete,update on *.* to 'wang'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user where user='wang'\G
*************************** 1. row ***************************
Host: localhost
User: wang
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y2、使用grant命令
使用grant命令时，如果用户已存在，则给该用户授权，如果用户不存在，则创建用户同时授权，格式如下：
grant 权限 on 数据库.数据表 to '用户名'@'访问主机' identified by '密码' with grant option;说明：
（1）常用的用户权限有CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、SELECT等，也可以使用ALL PRIVILEGES表示所有权限。
（2）数据库.数据表：指定对哪个数据库的哪个表授权，可以使用【*.*】表示所有数据库中的所有表。
（3）如果需要创建新用户并授权，可以通过【 ‘用户名’@‘访问主机’】来表示用户可以从哪些主机登录，并使用【 identified by ‘密码’】创建登录密码。
（4）with grant option：表示该用户可以再给其他用户授权。
grant all on *.* to 'admin'@'localhost' identified by 'Admin123456.' with grant option;（1）创建一个用户admin，代码如下：
mysql> grant all on *.* to 'admin'@'localhost' identified by 'Admin123456.' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select * from user where user='admin'\G
*************************** 1. row ***************************
Host: localhost
User: admin
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
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: *7673FF697A1C23B2FAF5ACE9D881BB808F4B8B61
password_expired: N
password_last_changed: 2019-12-25 22:06:28
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)可以看到admin用户拥有所有权限。
（2）创建一个用户zhao，代码如下：
mysql> grant select,insert,delete,update,create,drop on my_db.*
-> to 'zhao'@'localhost' identified by 'Zhao123456.' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant select,insert,delete,update,create,drop on test.* to 'zhao'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where user='zhao'\G
*************************** 1. row ***************************
Host: localhost
User: zhao
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: *79305B23C99E198F62C995D01B0039AEE9C993E8
password_expired: N
password_last_changed: 2019-12-25 22:11:53
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
mysql> select * from db where user='zhao'\G
*************************** 1. row ***************************
Host: localhost
Db: my_db
User: zhao
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: Y
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
*************************** 2. row ***************************
Host: localhost
Db: test
User: zhao
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
2 rows in set (0.00 sec)（3）创建一个用户gao，代码如下：
mysql> grant select,delete,update,insert,drop,alter on my_db.student to 'gao'@'localhost' identified by 'Gao123456.';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant select on my_db.course to 'gao'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select,delete,update,insert on my_db.score to 'gao'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from tables_priv where user='gao'\G
*************************** 1. row ***************************
Host: localhost
Db: my_db
User: gao
Table_name: student
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Drop,Alter
Column_priv:
*************************** 2. row ***************************
Host: localhost
Db: my_db
User: gao
Table_name: course
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select
Column_priv:
*************************** 3. row ***************************
Host: localhost
Db: my_db
User: gao
Table_name: score
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete
Column_priv:
3 rows in set (0.00 sec)（4）创建一个用户liu，代码如下：
mysql> grant select(s_id,s_name,age) on my_db.student to 'liu'@'localhost' identified by 'Liu123456.';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant update(s_id,s_name,age) on my_db.student to 'liu'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from columns_priv where user='liu';
+-----------+-------+------+------------+-------------+---------------------+---------------+
| Host | Db | User | Table_name | Column_name | Timestamp | Column_priv |
+-----------+-------+------+------------+-------------+---------------------+---------------+
| localhost | my_db | liu | student | s_name | 0000-00-00 00:00:00 | Select,Update |
| localhost | my_db | liu | student | s_id | 0000-00-00 00:00:00 | Select,Update |
| localhost | my_db | liu | student | age | 0000-00-00 00:00:00 | Select,Update |
+-----------+-------+------+------------+-------------+---------------------+---------------+
3 rows in set (0.00 sec)三、查看用户权限
show grants for '用户名'@'主机名';例如：
mysql> show grants for 'admin'@'localhost';
+----------------------------------------------------------------------+
| Grants for admin@localhost |
+----------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'zhao'@'localhost';
+---------------------------------------------------------------------------------------------------------+
| Grants for zhao@localhost |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhao'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test`.* TO 'zhao'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `my_db`.* TO 'zhao'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show grants for 'gao'@'localhost';
+---------------------------------------------------------------------------------------------+
| Grants for gao@localhost |
+---------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'gao'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `my_db`.`score` TO 'gao'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE, DROP, ALTER ON `my_db`.`student` TO 'gao'@'localhost' |
| GRANT SELECT ON `my_db`.`course` TO 'gao'@'localhost' |
+---------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)四、设置用户密码
修改账户密码有以下几种方式：
1、使用mysqladmin命令修改用户密码
该命令不需要登录MySQL，格式如下：
shell> mysqladmin -u user_name -p password "newpwd";例如：修改root用户的密码，并使用新密码登录：
[root@bogon ~]# mysqladmin -uroot -p password 'Zhao@123456'
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@bogon ~]# mysql -uroot -pZhao@123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.27-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.2、登录root账户，修改其他用户密码
set password for '用户名'@'主机名' = PASSWORD('密码');例如：
mysql> set password for 'gao'@'localhost' = password('Gao123456!');
Query OK, 0 rows affected, 1 warning (0.01 sec)3、登录root账户，使用update命令修改user表
mysql> update mysql.user set authentication_string=password('Gao@123456')
where user='gao' and host='localhost';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)4、登录自己的账户并修改密码
set password = password('Gao@@123456');五、删除用户
1、使用drop user命令删除
drop user '用户名'@'主机名';例如：
mysql> drop user 'gao'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> drop user 'zhao'@'localhost';
Query OK, 0 rows affected (0.00 sec)2、直接删除user表中的用户记录
mysql> select host,user,authentication_string from mysql.user;
+-------------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-------------+---------------+-------------------------------------------+
| localhost | root | *D042427D51E98841EFCD0C8A58186C56D7A1CFCD |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| 192.168.1.% | repl | *D042427D51E98841EFCD0C8A58186C56D7A1CFCD |
| localhost | liu | *06865F7F3361C772744A3481C7024CFA6F7D487C |
| localhost | admin | *7673FF697A1C23B2FAF5ACE9D881BB808F4B8B61 |
| localhost | wang | *D042427D51E98841EFCD0C8A58186C56D7A1CFCD |
+-------------+---------------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> delete from mysql.user where user='liu' and host='localhost';
Query OK, 1 row affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user,authentication_string from mysql.user;
+-------------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-------------+---------------+-------------------------------------------+
| localhost | root | *D042427D51E98841EFCD0C8A58186C56D7A1CFCD |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| 192.168.1.% | repl | *D042427D51E98841EFCD0C8A58186C56D7A1CFCD |
| localhost | admin | *7673FF697A1C23B2FAF5ACE9D881BB808F4B8B61 |
| localhost | wang | *D042427D51E98841EFCD0C8A58186C56D7A1CFCD |
+-------------+---------------+-------------------------------------------+
6 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
