Shulou(Shulou.com)06/01 Report--

How does MySQL modify the IP restrictions of an account? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Recently, I encountered a requirement in my work: to modify the permissions of MySQL users, it is necessary to restrict specific IP addresses to access. I encountered this kind of demand for the first time. As a result, some problems were found in the use of updating the system permissions report during the testing process, as demonstrated below.

Note: the following test environment is MySQL 5.6.20. If there is any discrepancy between other versions and the test results below, please refer to the actual environment.

Let's first create a test user LimitIP, which only allows access to the IP address of segment 192.168. 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 you now receive a demand 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 selectedmysql > use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-A Database changedmysql > 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: NCreate_tmp_table_priv: n Lock_tables_priv: NCreate_ view_priv: n Show_view_priv: NCreate_ 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,DeleteColumn_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 > Thank you for reading! After reading the above, do you have a general understanding of how MySQL modifies the IP restrictions of the account? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.