Description:
After the modification of the account host, show grants for the account has no authority.The steps are as follows.
When I logged in with the modified account, I found that all permissions were available.
I tried again to give all the permissions to all the libraries, and when I show grants for account permissions, nothing will change.Complete the following steps
How to repeat:
root@localhost:(none) 11:24:06>select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| dba | % |
| sqltest | 192.168.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)
root@localhost:(none) 11:24:08>show grants for sqltest@'192.168.%';
+-----------------------------------------------------------------------------+
| Grants for sqltest@192.168.% |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sqltest'@'192.168.%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'sqltest'@'192.168.%' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
root@localhost:(none) 11:24:20>update mysql.user set host='192.168.11.%' where user='sqltest';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:(none) 11:24:45>show grants for sqltest@'192.168.%';
+-----------------------------------------------------------------------------+
| Grants for sqltest@192.168.% |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sqltest'@'192.168.%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'sqltest'@'192.168.%' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
root@localhost:(none) 11:24:49>show grants for sqltest@'192.168.11.%';
ERROR 1141 (42000): There is no such grant defined for user 'sqltest' on host '192.168.11.%'
root@localhost:(none) 11:25:04>flush privileges;
Query OK, 0 rows affected (0.00 sec)
root@localhost:(none) 11:25:14>show grants for sqltest@'192.168.11.%';
+------------------------------------------------+
| Grants for sqltest@192.168.11.% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'sqltest'@'192.168.11.%' |
+------------------------------------------------+
1 row in set (0.00 sec)
root@localhost:(none) 11:25:15>flush privileges;
Query OK, 0 rows affected (0.00 sec)
root@localhost:(none) 11:29:33>show grants for sqltest@'192.168.11.%';
+------------------------------------------------+
| Grants for sqltest@192.168.11.% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'sqltest'@'192.168.11.%' |
+------------------------------------------------+
1 row in set (0.00 sec)
root@localhost:testdb 11:49:16>show grants for sqloper@'192.168.%';
+----------------------------------------------------------------------+
| Grants for sqloper@192.168.% |
+----------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'sqloper'@'192.168.%' |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost:testdb 11:49:25>update mysql.user set host='192.168.11.%' where user='sqloper';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:testdb 11:49:46>show grants for sqloper@'192.168.%';
+----------------------------------------------------------------------+
| Grants for sqloper@192.168.% |
+----------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'sqloper'@'192.168.%' |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost:testdb 11:49:58>show grants for sqloper@'192.168.11.%';
ERROR 1141 (42000): There is no such grant defined for user 'sqloper' on host '192.168.11.%'
root@localhost:testdb 11:50:07>flush privileges;
Query OK, 0 rows affected (0.00 sec)
root@localhost:testdb 11:50:10>show grants for sqloper@'192.168.11.%';
+-------------------------------------------------------------------------+
| Grants for sqloper@192.168.11.% |
+-------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'sqloper'@'192.168.11.%' |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@localhost:testdb 11:50:12>select version();
+------------+
| version() |
+------------+
| 5.7.27-log |
+------------+
1 row in set (0.00 sec)