|
Permission sorting issues in MySQL
Experiment: The matching operation encounters issues after you sort permissions, as the permission table includes wildcards. Wildcards in the authorization table 1. In the "user" table, the User column with null values matches any user names or anonymous users. 2. In the Host column of the "user" table, you can use the "%" and "_" wildcards in "host name" or "IP address". These wildcards have the same meaning as the pattern matching operator LIKE. 3. In the "db" table, the Host and User columns have the same representations as those in the "user" table. Also, the Db column can include wildcards. 4. In tables_priv, columns_priv and procs_priv tables, only the Host column can include wildcards. Account login matching: By using wildcards, connecting to the server as one user may match multiple accounts. For example, if you have created two accounts "@localhost and 'yz'@'%', logging in as the user 'yz'@localhost matches both accounts. Because of this, you need to determine which account will be used as the login account. To address this issue, MySQL introduces sorting to the items in the "user" table. The sorting method is described as follows: First, most-specific Host values are sorted. Literal "host_name" and "IP address" are most-specific items (The specificity of IP addresses is independent from subnet masks, and 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered as of the same specificity.) The wildcard '%' is least specific. Also, the null character matches any hosts but has a lower specificity than '%'. Rows with the same Host value are sorted by most-specific User values (A null User value matches "any users" and is least specific.) However, sorting is indeterminate for Host and User values with the same specificity. By using the method described above, logging in as 'yz'@localhost matches the account "@localhost. If you attempt to log in with the password of the account 'yz'@'%', you may see the prompt "Access denied for user 'yz'@'localhost'" as MySQL actually selected the login account "@localhost. However, you may also see the following output by executing "select user()" after the login in MySQL: +--------------+ | user() | +--------------+ | yz@localhost | +--------------+ By executing "show grants", you will see the following output where the selected account is "@localhost. +------------------------------------------------+ | Grants for @localhost | +------------------------------------------------+ | GRANT USAGE ON *.* TO ''@'localhost' | | GRANT CREATE ON `privtest`.* TO ''@'localhost' | +------------------------------------------------+ Issue 1 – the issue resulting from different db_name authorization information for the same account Generally, account issues impose minor impacts. However, sorting issues in the db table may result in different outputs in MySQL and lost user permissions, which prevents users from working properly and causes service failures. In the db table, the sorting of Host, Db and User columns is also executed by specificity (from the top to lowest specificity), and the sorting will be returned once the first matching item is found. On one hand, this sorting is an indeterminate sorting and will insert an irrelevant item, which changes the sorting order of other items and results in the loss of certain original permissions. On the other hand, this sorting may still be stable, however missing certain permissions that should logically exist even it is determinate. For example: grant select on aaa.* to yz@localhost; grant create on `aa%` to yz@localhost; grant insert on `aaa%` to yz@localhost; During the permission check in MySQL, only the first matching item will be returned; namely the user yz@localhost has only the "select" but not "create" and "insert" permissions to the aaa database. From this perspective, inserting new permission information may result in the loss of existing permissions even if the sorting is determinate. Relevant knowledge points: 1. MySQL has multiple permission verification dimensions including global, db, table and column. 2. For the global and db dimensions, MySQL reads all authorization information by internal weight (from maximum to minimum) and retrieves the first matching item (with the maximum weight) for internal authorization verification. 3. The weight calculation method for the db dimension is as follows: 3.1 Weights are calculated by hostname, dbname and username respectively. 3.2 The weight of a single string without wildcards is 0x80. If the string includes wildcards, the weight would be the position where the first wildcard appears, which is 0x7F at the maximum. 3.3 For example, the weight of the "GRANT CREATE ON `tt_`.* TO 'tt'@'%'" authorization information is 0x010380 where '%' is 0x01, 'tt_' is 0x03 and 'tt' is 0x80. 4. Currently, most sorting cases in MySQL are quick sorting, which is an indeterminate sorting algorithm. Issue 2 – the issue resulting from the authorization information for different accounts Experiment: Test if MySQL would match each account permission information in the db table when verifying permissions at the db layer. Conclusion: From the effect perspective, MySQL matches each authorization information in the db table and returns the first matching item found, resulting in potential permission inconsistency between the permission information from "show grants" and actual permissions. (This is also true for the table layer.) The sorting order in MySQL is Host, Db and User. When matching with the "user" table, the system may match multiple items, and this introduces the following question: during the matching at the db layer, would matched accounts be rematched at the db layer? Experiment: Create both the 'yz'@'%' and 'yz'@localhost accounts and grant a permission to 'yz'@'%': grant create on yzdb to 'yz'@'%'; Log in as yz@localhost and execute the following statement. Then, you will see Usage only in the permission information. In this condition, if you attempt to create the database yzdb, you will find that you have the permission for creating yzdb. mysql> show grants; +----------------------------------------+ | Grants for yz@localhost | +----------------------------------------+ | GRANT USAGE ON *.* TO 'yz'@'localhost' | +----------------------------------------+ mysql> create database yzdb; Query OK, 1 row affected (0.02 sec) If you revoke the permission for creating yzdb from 'yz'@'%', 'yz'@'localhost' would lose this permission as well. During the test, the "@localhost account does not have the aforementioned permission of 'yz'@'%'. In this condition, 'yz' is nonequivalent to ''. In addition, the aforementioned situation would also lead to permission "loss", where the permissions from "show grants" may be lost due to other account authorization information. |
|
