[TiDB Version]
v5.4.1, v6.1.0, etc.
[Encountered Problem]
Two identical usernames with different host ranges and different permissions. When logging in, the correct host is verified, but during permission verification, the permissions of the other host are used.
[Reproduction Steps]
Create two identical usernames with different hosts and different passwords:
create user dba_test@'%' identified by '123456';
GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'dba_test'@'%';
create user dba_test@'192.168.%' identified by '654321';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON test.* TO 'dba_test'@'192.168.%';
Log in from a server within the 192.168.X range and perform a delete operation:
mysql -udba_test -p654321 -h192.168.13.15 -P4000
MySQL [(none)]> use test;
Database changed
MySQL [test]> DROP TABLE IF EXISTS a;
ERROR 1142 (42000): DROP command denied to user 'dba_test'@'192.168.%' for table 'a'
After deleting the user dba_test@'%', re-execute the delete statement, and the delete operation can be performed.
Under normal circumstances, the production environment does not use @‘%’. It is evident that TiDB matched the permissions for “@‘%’” but did not match “@‘192.168.%’”. However, it needs to be verified whether it matches based on the allowed host access range or based on the database’s own permissions.
Yes, the production environment does not use @‘%’, but this issue was occasionally discovered in the testing environment. The result obviously does not match the permission allocation.
I tested it, and the performance of MySQL 5.7 and 8.0 is indeed different from TiDB. The second user can delete directly upon logging in, but according to the test results, it seems that MySQL does not implement it with the least privilege.
First, access control is performed, matching based on the priority of user and host. Once matched, it then checks whether the corresponding permissions exist. According to MySQL’s official documentation, specific IP and hostname selections take the highest priority, above ‘%’.
In TiDB, the access control matches the correct user and host, but during the permission check, it uses ‘%’ for the host, which is clearly incorrect.
Is TiDB correct? I haven’t found any information yet, but during MySQL permission verification, if the user table does not have global permissions, it will check the db table. The db table is also sorted by host, db, and user (sorted according to the server’s collation rules, first by hostname field value—the more specific the value, the higher it is, with string hostnames and IP addresses being the most specific), and then uses the first matching item.
Looking at the MySQL official website, it says that the minimum privilege should be within the scope of the user at login. For example, the scope of % is relatively large, and specifying a specific host is relatively small, so it will use the specific host for verification. Then, the table operation permissions are also verified according to this logic. As a result, the permissions corresponding to the logged-in user will be matched, and other user permissions will not be used.
In the user table, the username is only db_test, and the IP in the back is the value of the host field, which is only used to restrict the login client and not the user’s operation permissions. Therefore, I think the database will treat this user as the same user.
You can create root@% and root@x.x.x.x users separately, assign different permissions, and then log in from the x.x.x.x host to test what the root permissions are.
According to MySQL’s verification rules, the more precise one should take precedence. Check if there are user() and current_user() functions to see the logged-in user.
|
username: Hacker_ojLJ8Ndr
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: