Modify MySQL user authorization IP from one time to see its user management

recent authorization of database users IP by IP segment , adjust to specific IP the , the intention is to troubleshoot the problem , can be targeted to a specific application server , or targeted to make certain settings .

thought a UPDATE You can handle it . , after testing, it was found that the modified user was left with only USAGE permission. , The demo is as follows :

mysql> SELECT User, host, password from mysql.userwhere user = ' Zzzz_acc ' \g

1. row***************************

User:zzzz_acc

host:192.168.4.%

Password:*2b979ed0716e5fcb08ca97c284fe270b65991f34

1 row in Set (0.00 sec)

mysql> SHOW GRANTS for ' zzzz_acc ' @ ' 192.168.4.% ' \g

1. row***************************

Grants for [e-mail protected]%: grantusage on * * to ' ZZZZ_ACC ' @ ' 192.168.4.% ' identified by PASSWORD ' *2b979ed0716e5fcb08ca 97c284fe270b65991f34 '

2. row***************************

Grants for [e-mail protected]%: Grantselect, INSERT, DELETE, EXECUTE on ' zzzz '. * to ' ZZZZ_ACC ' @ ' 192.168.4.% '

2 rows in Set (0.00 sec)

Mysql> UPDATE mysql.user SET host = ' 192.168.4.14 ' WHERE user = ' ZZZZ_ACC ' and host = ' 192.168.4.% ';

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

After you manually modify the permissions table , to be FLUSH under :

mysql> FLUSH privileges;

Query OK, 0 rows Affected (0.00 sec)

mysql> SHOW GRANTS for ' zzzz_acc ' @ ' 192.168.4.14 ' \g

1. row***************************

Grants for [e-mail protected]: grantusage on * * to ' ZZZZ_ACC ' @ ' 192.168.4.14 ' identified by PASSWORD ' *2b979ed0716e5fcb08ca 97c284fe270b65991f34 '

1 row in Set (0.00 sec)

Why? , is that the user right is at the database level , Mysql.db the table has to be modified accordingly. :

Mysql> SELECT Host, DB, user from Dbwhere host = ' 192.168.4.% ' \g

1. row***************************

host:192.168.4.%

Db:zzzz

User:zzzz_acc

1 row in Set (0.00 sec)

Mysql> UPDATE mysql.db SET host = ' 192.168.4.14 ' WHERE user = ' ZZZZ_ACC ' and host = ' 192.168.4.% ';

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)

Look , then the privilege is back. :

mysql> SHOW GRANTS for ' zzzz_acc ' @ ' 192.168.4.14 ' \g

1. row***************************

Grants for [e-mail protected]: grantusage on * * to ' ZZZZ_ACC ' @ ' 192.168.4.14 ' identified by PASSWORD ' *2b979ed0716e5fcb08ca 97c284fe270b65991f34 '

2. row***************************

Grants for [e-mail protected]: Grantselect, INSERT, DELETE, EXECUTE on ' zzzz '. * to ' ZZZZ_ACC ' @ ' 192.168.4.14 '

2 rows in Set (0.00 sec)

summarized under , is generally do not manually directly modify the permissions table , official documentation does not recommend this . MySQL in the management of users , such as create users , Adjust permissions , change passwords, etc. , are provided with the corresponding commands . use here RENAME USER, can one step :

mysql> RENAME USER ' zzzz_acc ' @ ' 192.168.4.% ' to ' ZZZZ_ACC ' @ ' 192.168.4.14 ';

Query OK, 0 rows affected (0.01 sec)

But there are also adaptations., This reminds me of a problem I've encountered before., administrative users of the database' root ' @ ' localhost 'have aAll Privilegesthe Permissions, but no.GRANT OPTIONPermissions(Don't ask me why this is so). that is, the administrative user, cannot grant and revoke permissions from other users, It also means that you cannot create a user who is actually valid ., The demo is as follows:

Mysql> SHOW Grants\g

1. row***************************

Grants for [e-mail protected]: GRANT allprivileges on * * to ' root ' @ ' localhost ' identified Bypassword ' *2b979ed0716e5fcb08ca97c284fe270b65991f34 '

1 row in Set (0.00 sec)

mysql> CREATE USER ' zzzz_acc2 ' @ ' 192.168.4.15 ' identified by ' ZZZZ_ACC2 ';

Query OK, 0 rows affected (0.15 sec)

Mysql> GRANT SELECT, INSERT, Delete,execute on ' zzzz '. * to ' zzzz_acc2 ' @ ' 192.168.4.15 ';

ERROR 1044 (42000): Access denied for user ' root ' @ ' localhost ' to database ' zzzz '

What to do? , can only be modified directly Mysql.user of the table Grant_priv field. :

mysql> SELECT User, host, password,grant_priv from mysql.user WHERE user = ' root ' and host = ' localhost ' \g

1. row***************************

User:root

Host:localhost

Password: *2b979ed0716e5fcb08ca97c284fe270b65991f34

Grant_priv:n

1 row in Set (0.00 sec)

mysql> UPDATE mysql.user SET grant_priv= ' Y ' WHERE user = ' root ' and host = ' localhost ';

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\g

1. row***************************

Grants for [e-mail protected]: GRANT allprivileges on * * to ' root ' @ ' localhost ' identified by PASSWORD ' *2B979ED0716E5FC B08ca97c284fe270b65991f34 ' with grantoption

1 row in Set (0.00 sec)

here's the note. , Although I saw the GRANT OPTION Permissions , We need to get back on the landing . , This permission will take effect , similar to modifying the global parameters , to reconnect the same , Otherwise you will encounter the above error .

mysql> GRANT SELECT, Insert,delete, EXECUTE on ' zzzz '. * to ' zzzz_acc2 ' @ ' 192.168.4.15 ';

Query OK, 0 rows Affected (0.00 sec)

mysql> SHOW GRANTS for ' zzzz_acc2 ' @ ' 192.168.4.15 ' \g

1. row***************************

Grants for [e-mail protected]: grantusage on * * to ' zzzz_acc2 ' @ ' 192.168.4.15 ' identified by PASSWORD ' *0CF81DDE0A7213E4AB1 8f2925316c049bcf3f2e8 '

2. row***************************

Grants for [e-mail protected]: Grantselect, INSERT, DELETE, EXECUTE on ' zzzz '. * to ' zzzz_acc2 ' @ ' 192.168.4.15 '

2 rows in Set (0.00 sec)

the above two examples involveMySQLin the Permissions tableUser,and thedb.the former records the user's global permissions and some non-privileged information, the latter records database-level permissions; There are also permissions related toTable_priv, Columns_privand theProc_privTable, corresponds to the table, respectively, column, and stored Procedures(function)level of permissions(Proxies_privdelegate user Rights not mentioned).

and finally, look at theMySQL 5.6The version is not well-established in the Manage User passwords section, because the project is temporarily debugged, requires open online database temporary read-only permissions for a specific length of time(On the from library), but5.6The version only provides password expiration settings, there is no limit to the length of expiration. These details are5.7There are many improvements in the version, such as when the password expires, User Lock, SSLEncrypt connection settings, etc..

This article is from the "Move Yourself" blog, please be sure to keep this source http://coveringindex.blog.51cto.com/12533373/1954587

Modify MySQL user authorization IP from one time to see its user management