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

The following mainly brings you to understand its user management by modifying MySQL user authorization IP. I hope this content can bring you practical use, which is also the main purpose of my editor to understand its user management by modifying MySQL user authorization IP. All right, don't talk too much nonsense, let's just read the following.

Recently, the authorized IP of database users has been adjusted from IP to specific IP, which means that when troubleshooting problems, you can locate the specific application CVM, or make some specific settings.

It was thought that a UPDATE could be done, but after testing, it was found that the modified user had only USAGE permission. The demonstration 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 zzzz_acc@192.168.4.%: GRANTUSAGE ON *. * TO 'zzzz_acc'@'192.168.4.%' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34'

* * 2. Row**

Grants for zzzz_acc@192.168.4.%: 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 manually modifying the permission table, you need to FLUSH:

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 zzzz_acc@192.168.4.14: GRANTUSAGE ON *. * TO 'zzzz_acc'@'192.168.4.14' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34'

1 row in set (0.00 sec)

The reason is that the user permission is at the database level, and the mysql.db table needs 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, the permissions are back at this time:

Mysql > SHOW GRANTS FOR'zzzz_acc'@'192.168.4.14'\ G

* 1. Row**

Grants for zzzz_acc@192.168.4.14: GRANTUSAGE ON *. * TO 'zzzz_acc'@'192.168.4.14' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34'

* * 2. Row**

Grants for zzzz_acc@192.168.4.14: GRANTSELECT, INSERT, DELETE, EXECUTE ON `zzzz`. * TO 'zzzz_acc'@'192.168.4.14'

2 rows in set (0.00 sec)

To sum up, it is generally not recommended to manually modify the permission table directly, and this is not recommended in official documents. MySQL provides corresponding commands for user management, such as creating users, adjusting permissions, changing passwords, etc. Using RENAME USER here, you can achieve it in 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 some workarounds. I think of a problem encountered before. The administrative user of the database, 'root'@'localhost', has permission to ALL PRIVILEGES, but no permission to GRANT OPTION (don't ask me why). That is, the administrative user cannot grant or revoke the permissions of other users, which also means that an actual valid user cannot be created, as demonstrated below:

Mysql > SHOW GRANTS\ G

* 1. Row**

Grants for root@localhost: 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 should we do? we can only modify the grant_priv field of the mysql.user table directly:

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 root@localhost: GRANT ALLPRIVILEGES ON *. * TO 'root'@'localhost' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34' WITH GRANTOPTION

1 row in set (0.00 sec)

Note below that although you have seen the GRANT OPTION permission, you have to log in again before the permission can take effect, which is similar to modifying the global parameters and reconnecting, otherwise you will encounter the error above.

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 zzzz_acc2@192.168.4.15: GRANTUSAGE ON *. * TO 'zzzz_acc2'@'192.168.4.15' IDENTIFIED BY PASSWORD'*0CF81DDE0A7213E4AB18F2925316C049BCF3F2E8'

* * 2. Row**

Grants for zzzz_acc2@192.168.4.15: GRANTSELECT, INSERT, DELETE, EXECUTE ON `zzzz`. * TO 'zzzz_acc2'@'192.168.4.15'

2 rows in set (0.00 sec)

The above two examples involve user and db in the MySQL permission table. The former records the user's global permissions and some non-permission information, while the latter records database-level permissions; related to permissions are table_priv, columns_priv and proc_priv tables, corresponding to table, column, and stored procedure (function) level permissions (proxies_priv proxy user rights are not mentioned for the time being).

Finally, let's take a look at the imperfections in the part of managing users' passwords in MySQL version 5.6. due to temporary debugging of the project, it is necessary to open the online database for a specific temporary read-only permission (on the slave library), but version 5.6 only provides password expiration settings and does not limit the duration of expiration. There are many improvements in these details in version 5.7, such as password expiration, user locking, SSL encrypted connection settings, etc.

For the above about through the modification of MySQL user authorization IP to understand its user management, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.