Shulou(Shulou.com)05/31 Report--

MySQL how to modify the IP restrictions of the account, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Create a test user LimitIP, which only allows access to the IP address of 192.168 segments. The specific permissions are as follows:

Mysql > GRANT SELECT ON MyDB.* TO LimitIP@'192.168.%' IDENTIFIED BY 'LimitIP'; Query OK, 0 rows affected (0.01 sec) mysql > GRANT INSERT, UPDATE,DELETE ON MyDB.kkk TO LimitIP@'192.168.%'; Query OK, 0 rows affected (0.00 sec) mysql > mysql > flush privileges; Query OK, 0 rows affected (0.00 sec) mysql > mysql > show grants for LimitIP@'192.168.%' +-+ | Grants for LimitIP@192 .168.% | + -- + | GRANT USAGE ON *. * TO 'LimitIP'@'192.168.%' IDENTIFIED BY PASSWORD' * 72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' | GRANT SELECT ON `MyDB`.* TO 'LimitIP'@'192.168.%' | | GRANT INSERT UPDATE DELETE ON `MyDB`.`kkk`TO 'LimitIP'@'192.168.%' | + -- + 3 rows in set (0.00 sec) mysql >

Suppose we now receive a requirement that this user only allows access to this IP address 192.168.103.17, so I intend to update the mysql.user table as follows:

Mysql > select user, host from mysql.user where user='LimitIP' +-+-+ | user | host | +-+-+ | LimitIP | 192.168.% | +-+-+ 1 row in set (0.00 sec) mysql > update mysql.user set host='192.168.103.17' where user='LimitIP' Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > flush privileges; Query OK, 0 rows affected (0.01 sec) mysql > select user, host from user where user='LimitIP'; ERROR 1046 (3D000): No database selected mysql > use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with-A Database changed mysql > select user, host from user where user='LimitIP' +-+-+ | user | host | +-+-+ | LimitIP | 192.168.103.17 | +-+-+ 1 row in set (0.00 sec) mysql > show grants For LimitIP@'192.168.103.17' +-+ | Grants for LimitIP@192.168.103.17 | +- -- + | GRANT USAGE ON *. * TO 'LimitIP'@'192.168.103.17' IDENTIFIED BY PASSWORD' * 72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' | +-- -+ 1 row in set (0.00 sec) mysql >

The above test found that if you only modify the mysql.user table in this way, the previous permissions will be lost, as shown below. If you query mysql.db and mysql.tables_priv, you will find that the field value of Host is still 192.168.%.

Mysql > select * from mysql.db where user='LimitIP'\ G * * 1. Row * * Host: 192.168.% Db: MyDB User: LimitIP Select_priv: y Insert_priv: n Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N 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: N1 row in set (0.00 sec) ERROR: No query specified mysql > select * from mysql.tables_priv where user='LimitIP'\ G * * 1. Row * * Host: 192.168.% Db: MyDB User: LimitIP Table_name: kkk Grantor: root@localhost Timestamp: 0000-00-0000: 00:00 Table_priv: Insert,Update Delete Column_priv: 1 row in set (0.00 sec) ERROR: No query specified

So I continue to modify the mysql.db and mysql.tables_priv tables, and then test and verify that finally OK (see the following test steps). Of course, if the permissions of the account are more than these levels, you may also have to modify tables such as mysql.columns_priv, mysql.procs_priv, etc.

Mysql > show grants for LimitIP@'192.168.%'; ERROR 1141 (42000): There is no such grant defined for user 'LimitIP' on host' 192.168% 'mysql > update mysql.db set host='192.168.103.17' where user='LimitIP'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > update mysql.tables_priv set host='192.168.103.17' where user='LimitIP' Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > flush privileges; Query OK, 0 rows affected (0.00 sec) mysql > show grants for LimitIP@'192.168.103.17' +-+ | Grants for LimitIP@192.168.103.17 | +- -- + | GRANT USAGE ON *. * TO 'LimitIP'@'192.168.103.17' IDENTIFIED BY PASSWORD' * 72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' | GRANT SELECT ON `MyDB`.* TO 'LimitIP'@'192.168.103.17' | | GRANT INSERT | UPDATE DELETE ON `MyDB`.`kkk`TO 'LimitIP'@'192.168.103.17' | + -- + 3 rows in set (0.00 sec) mysql >

If you need to modify the user's IP restrictions, it is not the best policy to update the mysql permission table. In fact, there is a better way, that is, RENAME USER Syntax.

Mysql > RENAME USER 'LimitIP'@'192.168.103.17' TO' LimitIP'@'192.168.103.18'; Query OK, 0 rows affected (0 sec) mysql > FLUSH PRIVILEGES; Query OK, 0 rows affected (0 sec) mysql > show grants for 'LimitIP'@'192.168.103.18' +-+ | Grants for LimitIP@192.168.103.18 | +- -- + | GRANT USAGE ON *. * TO 'LimitIP'@'192.168.103.18' IDENTIFIED BY PASSWORD' * 72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' | GRANT SELECT ON `MyDB`.* TO 'LimitIP'@'192.168.103.18' | | GRANT INSERT | UPDATE DELETE ON `MyDB`.`kkk`TO 'LimitIP'@'192.168.103.18' | + -- + 3 rows in set (0.00 sec) mysql > is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.