mysql限制ip访问某个数据库_限制账号从指定IP地址访问/操作数据库

示例

创建新用户test001并授权管理rds001数据库，允许从42.120.74.119访问数据库。CREATE USER `test001`@`42.120.74.119`IDENTIFIED BY 'passwd';

GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test001'@'42.120.74.119';

GRANT ALL PRIVILEGES ON `rds001`.* TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`help_topic` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`func` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`time_zone` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`slow_log` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`time_zone_transition` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`event` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`proc` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`help_category` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`help_relation` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`help_keyword` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`general_log` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'test001'@'42.120.74.119';

GRANT SELECT ON `mysql`.`time_zone_name` TO 'test001'@'42.120.74.119';

说明

如果将所有的42.120.74.119更改为%，就和通过控制台创建的账号一样了，也可以在控制台看见此账号的所属数据库。

如果想要修改IP为42.120.74.120，可以使用如下命令： RENAME USER `test001`@`42.120.74.119` TO `test001`@`42.120.74.120`;