1. The problem I'm encountering
There are 2 root user entries in the mysql.user table, one is a specific ip 10.0.0.25, and the other is a subnet 10.0.0.0/16, which covers the first one. Like the following table shows:
mysql> select User,Host,authentication_string from user;
| User | Host | authentication_string |
|---|---|---|
| root | 10.0.0.25 | *971179048117239D3088559F0B1345BE582FCD44 |
| root | 10.0.0.0/16 | *971179048117239D3088559F0B1345BE582FCD44 |
The first one(root@10.0.0.25) is the original root user which has all privileges, but the second one(root@10.0.0.0/16) is new created, and has no privileges. My problem is I can't get back to the first one with full privileges. When I connect it from the host 10.0.0.25, the server matches the second one primarily.
So, what should I do?
2. How I got here?
After the MySql instance started, there was a root@%, which means it could be connected from any where.
| User | Host | authentication_string |
|---|---|---|
| root | % | *971179048117239D3088559F0B1345BE582FCD44 |
I thought it's insecure, so I changed it to root@10.0.0.25.
| User | Host | authentication_string |
|---|---|---|
| root | 10.0.0.25 | *971179048117239D3088559F0B1345BE582FCD44 |
But it's also not safe enough, considering the 10.0.0.25 maybe shutdown someday. So I created the second one, like the first table shows.
I did flush privileges before I granting all privileges to the second one. Then I can never get back to the original root user with full privileges.
3. My efforts
I've tried to search a solution, but all is in vain. I also refer to the official documentation, and found the matching rules the server does from [6.2.5 Access Control, Stage 1: Connection Verification](https://dev.mysql.com/doc/refman/5.7/en/connection-access.html）
When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:
- Whenever the server reads the user table into memory, it sorts the rows.
- When a client attempts to connect, the server looks through the rows in sorted order.
- The server uses the first row that matches the client host name and user name.
The server uses sorting rules that order rows with the most-specific Host values first:
- Literal IP addresses and host names are the most specific.
- The specificity of a literal IP address is not affected by whether it has a netmask, so 198.51.100.13 and 198.51.100.0/255.255.255.0 are considered equally specific.
- The pattern '%' means “any host” and is least specific.
- The empty string '' also means “any host” but sorts after '%'.
Anyone please help me.
Many thanks!