保姆级喂饭教程MySQL修改用户对应IP范围

JAY.LIN 收录于未分类

2025-09-16 约 802 字预计阅读 2 分钟

https://bing.ee123.net/img/rand?artid=151754830

【保姆级喂饭教程】MySQL修改用户对应IP范围

[ VibeCoding·九月创作之星挑战赛 10w+人浏览 1.3k人参与

]( )

前言

之前创建了一个本地MySQL用户，后续需要分享给同事，所以就需要修改对应ip限制，做个小总结

一、创建用户

-- 创建本地用户
CREATE USER 'python'@'localhost' IDENTIFIED BY 'password';

权限相关表介绍：
MySQL中有三张用户权限相关表，都在基本的mysql数据库中

user：用户权限表
db：数据库权限表
tables_priv：数据表权限表

二、设置权限

设置访问某个数据库的权限

-- 授权常用权限
GRANT CREATE, DROP, ALTER, INDEX, SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'python'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;

所有权限：

Privilege	Grant Table Column	Context
ALL [PRIVILEGES]	Synonym for “all privileges”	Server administration
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for “no privileges”	Server administration

三、修改IP

1. 直接更新权限表

UPDATE mysql.user SET host='%' WHERE user='python' AND host='localhost';
FLUSH PRIVILEGES;

这样更新完以后，同事进行登录，只能看到一个information_schema数据库，看不到其他数据库，因为db表中python的host还是localhost
需要再修改db表：

UPDATE mysql.db SET host='%' WHERE user='python' AND host='localhost';
FLUSH PRIVILEGES;

如果设置了单张表的权限还需要再修改tables_priv表

UPDATE mysql.tables_priv SET host='%' WHERE user='python' AND host='localhost';
FLUSH PRIVILEGES;

2. 使用 RENAME USER 命令

-- 更规范的修改方式（MySQL 5.0.2+支持）
RENAME USER 'python'@'localhost' TO 'python'@'%';

参考文献

喜欢的点个关注吧><！祝你永无bug~

/*
                   _ooOoo_
                  o8888888o
                  88" . "88
                  (| -_- |)
                  O\  =  /O
               ____/`---'\____
             .'  \\|     |//  `.
            /  \\|||  :  |||//  \
           /  _||||| -:- |||||-  \
           |   | \\\  -  /// |   |
           | \_|  ''\---/''  |   |
           \  .-\__  `-`  ___/-. /
         ___`. .'  /--.--\  `. . __
      ."" '<  `.___\_<|>_/___.'  >'"".
     | | :  `- \`.;`\ _ /`;.`/ - ` : | |
     \  \ `-.   \_ __\ /__ _/   .-` /  /
======`-.____`-.___\_____/___.-`____.-'======
                   `=---='
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
            佛祖保佑       永无BUG
*/