网站导航
> 技术文档 > 【MySQL】用户管理和权限

【MySQL】用户管理和权限

网友: 技术文档 2025-08-08 05:57:13

欢迎拜访：雾里看山-CSDN博客
本篇主题：【MySQL】用户管理和权限
发布时间：2025.3.12
隶属专栏：MySQL

在这里插入图片描述

目录

  • 引言
  • 用户
    • 用户信息
    • 创建用户
    • 修改用户密码
      • 语法
      • 案例
    • 删除用户
      • 语法
      • 案例
  • 权限
    • 权限列表
    • 查看和刷新用户的权限
    • 给用户授权
      • 语法
      • 案例
    • 回收权限
      • 语法
      • 示例

引言

如果我们只能使用root用户，这样存在安全隐患。在多用户协同开发时，很容易因为新手的误操作，给数据库带来严重的安全问题。这时，就需要使用MySQL的用户管理。
【MySQL】用户管理和权限

用户

用户信息

MySQL中的用户，都存储在系统数据库mysql的user表中

mysql> show databases;+--------------------+| Database  |+--------------------+| information_schema || bit_index || database1 || index_db  || mysql  || performance_schema || scott  || sys || test  || test_db || user_db |+--------------------+11 rows in set (0.00 sec)mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+---------------------------+| Tables_in_mysql  |+---------------------------+| columns_priv  || db || engine_cost  || event  || func|| general_log  || gtid_executed || help_category || help_keyword  || help_relation || help_topic || innodb_index_stats || innodb_table_stats || ndb_binlog_index || plugin  || proc|| procs_priv || proxies_priv  || server_cost  || servers  || slave_master_info || slave_relay_log_info || slave_worker_info || slow_log  || tables_priv  || time_zone  || time_zone_leap_second || time_zone_name || time_zone_transition || time_zone_transition_type || user|+---------------------------+31 rows in set (0.00 sec)mysql> select host,user,authentication_string from user;+-----------+---------------+-------------------------------------------+| host | user | authentication_string  |+-----------+---------------+-------------------------------------------+| localhost | root |  || localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |+-----------+---------------+-------------------------------------------+3 rows in set (0.01 sec)

字段解释：

  • host： 表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆
  • user： 用户名
  • authentication_string： 用户密码通过password函数加密后的
  • *_priv： 用户拥有的权限
    我们还可以通过select * from user \\G查看每个用户的具体信息。
mysql> select * from user \\G*************************** 1. row ***************************  Host: localhost  User: root  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: YCreate_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: auth_socket authentication_string: password_expired: N password_last_changed: 2025-01-20 12:21:54 password_lifetime: NULL account_locked: N*************************** 2. row ***************************  Host: localhost  User: mysql.session  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: Y 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: NCreate_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: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE password_expired: N password_last_changed: 2025-01-20 12:21:54 password_lifetime: NULL account_locked: Y*************************** 3. row ***************************  Host: localhost  User: mysql.sys  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: NCreate_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: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE password_expired: N password_last_changed: 2025-01-20 12:21:54 password_lifetime: NULL account_locked: Y3 rows in set (0.00 sec)

创建用户

语法

create user \'用户名\'@\'登陆主机/ip\' identified by \'密码\';
如果登录主机被设置为%，则表示该用户可以在任何地方登陆user；
此设置方法需要谨慎使用

案例

mysql> create user \'wdd\'@\'localhost\' identified by \'123456\';Query OK, 0 rows affected (0.00 sec)mysql> select host,user,authentication_string from user;+-----------+---------------+-------------------------------------------+| host | user | authentication_string  |+-----------+---------------+-------------------------------------------+| localhost | root | *999FD3326F738172CF3B546D7B69779554E9719E || localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | wdd  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+---------------+-------------------------------------------+4 rows in set (0.00 sec)

此时，我们便可以使用新账号新密码进行登录了，但是，此时新用户的权限病灭有被设置，所以大部分库都是看不到的。

wdd@VM-20-16-ubuntu:~/mysql$ mysql -u wdd -p;Enter password: Welcome to the MySQL monitor. Commands end with ; or \\g.Your MySQL connection id is 7Server version: 5.7.29 MySQL Community Server (GPL)Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.mysql> show databases;+--------------------+| Database  |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)

修改用户密码

语法

自己改自己密码
set password=password(\'新的密码\');
root用户修改指定用户的密码
set password for \'用户名\'@\'主机名\'=password(\'新的密码\');

案例

自己修改自己的密码

mysql> set password=password(\'12321\');Query OK, 0 rows affected, 1 warning (0.00 sec)

root用户修改任意用户的密码

mysql> set password for \'wdd\'@\'localhost\'=password(\'1234abcd\');Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select host,user,authentication_string from user;+-----------+---------------+-------------------------------------------+| host | user | authentication_string  |+-----------+---------------+-------------------------------------------+| localhost | root | *999FD3326F738172CF3B546D7B69779554E9719E || localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | wdd  | *A28D6A233B76FC581A8E711B8966883C91C97612 |+-----------+---------------+-------------------------------------------+4 rows in set (0.00 sec)

删除用户

语法

drop user \'用户名\'@\'主机名\'

案例

mysql> select host,user,authentication_string from user;+-----------+---------------+-------------------------------------------+| host | user | authentication_string  |+-----------+---------------+-------------------------------------------+| localhost | root | *999FD3326F738172CF3B546D7B69779554E9719E || localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | wdd  | *A28D6A233B76FC581A8E711B8966883C91C97612 |+-----------+---------------+-------------------------------------------+4 rows in set (0.00 sec)mysql> drop user \'wdd\'@\'localhost\';Query OK, 0 rows affected (0.00 sec)mysql> select host,user,authentication_string from user;+-----------+---------------+-------------------------------------------+| host | user | authentication_string  |+-----------+---------------+-------------------------------------------+| localhost | root | *999FD3326F738172CF3B546D7B69779554E9719E || localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |+-----------+---------------+-------------------------------------------+3 rows in set (0.00 sec)

权限

权限列表

MySQL数据库提供的权限列表：
【MySQL】用户管理和权限

查看和刷新用户的权限

查看用户权限

mysql> show grants for \'wdd\'@\'localhost\';+-----------------------------------------------+| Grants for wdd@localhost|+-----------------------------------------------+| GRANT USAGE ON *.* TO \'wdd\'@\'localhost\' || GRANT SELECT ON `test`.* TO \'wdd\'@\'localhost\' |+-----------------------------------------------+2 rows in set (0.00 sec)mysql> show grants for \'root\'@\'localhost\';+---------------------------------------------------------------------+| Grants for root@localhost  |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'localhost\' WITH GRANT OPTION || GRANT PROXY ON \'\'@\'\' TO \'root\'@\'localhost\' WITH GRANT OPTION |+---------------------------------------------------------------------+2 rows in set (0.01 sec)

如果发现赋权限后，没有生效，执行如下指令：

mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

给用户授权

刚创建的用户没有任何权限。需要给用户授权。

语法

grant 权限列表 on 库.对象名 to \'用户名\'@\'登陆位置\' [identified by \'密码\']

说明：

  • 权限列表，多个权限用逗号分开
grant select on ...grant select, delete, create on ....grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限
  • *.* : 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）
  • 库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)
  • identified by可选。 如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户。

案例

终端A:(使用root账号)

mysql> show databases;+--------------------+| Database  |+--------------------+| information_schema || bit_index || database1 || index_db  || mysql  || performance_schema || scott  || sys || test  || test_db || user_db |+--------------------+11 rows in set (0.00 sec)mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| msg || tmp |+----------------+2 rows in set (0.00 sec)mysql> grant select on test.* to \'wdd\'@\'localhost\';Query OK, 0 rows affected (0.00 sec)

终端B:(使用wdd账号)

--没有设置查看权限前mysql> show databases;+--------------------+| Database  |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)设置查看权限以后mysql> show databases;+--------------------+| Database  |+--------------------+| information_schema || test  |+--------------------+2 rows in set (0.00 sec)mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| msg || tmp |+----------------+2 rows in set (0.00 sec)--可以查看mysql> select * from tmp;+----+------------+| id | birthday |+----+------------+| 1 | 1990-02-24 || 2 | 1980-03-05 || 3 | 2025-02-18 |+----+------------+3 rows in set (0.00 sec)--没有删除权限mysql> delete from tmp;ERROR 1142 (42000): DELETE command denied to user \'wdd\'@\'localhost\' for table \'tmp\'

回收权限

语法

revoke 权限列表 on 库.对象名 from \'用户名\'@\'登陆位置\'；

示例

终端A:(root 账号)

mysql> revoke all on test.* from \'wdd\'@\'localhost\';Query OK, 0 rows affected (0.00 sec)

终端B:(wdd 账号)

-- 回收权限之前mysql> show databases;+--------------------+| Database  |+--------------------+| information_schema || test  |+--------------------+2 rows in set (0.00 sec)--回收权限以后mysql> show databases;+--------------------+| Database  |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)

⚠️ 写在最后：以上内容是我在学习以后得一些总结和概括，如有错误或者需要补充的地方欢迎各位大佬评论或者私信我交流！！！

手机铃声下载

网络标签：

相关问题