Description:
In MySQL, both "_" and "%" are considered special characters in privilege statements. These characters are known as "wildcards."
The underscore ("_") represents any single character.
We have the following two databases:
```
fander_1
fander11
```
Currently, my account has the following grant:
```
grant select on `fander_1`.* to fander;
```
As I mentioned earlier, the underscore ("_") is a wildcard, which means it's a special character. Therefore, this grant effectively gives select privileges on both of the above databases. In cases where there are many similar databases, this type of grant is simple and convenient. Everything works perfectly and operates as expected.
However, one day, I wanted to give the fander_1 database an additional insert privilege (without granting this to the other databases). How can this be done?
The answer, as I understand it, is to escape the underscore ("_"), which has a special meaning as a wildcard.
The command I used is:
```
grant insert on `fander\_1`.* to fander;
```
After granting, the result is as follows:
```
mysql> show grants for fander;
+-----------------------------------------------+
| Grants for fander@% |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `fander`@`%` |
| GRANT INSERT ON `fander\_1`.* TO `fander`@`%` |
| GRANT SELECT ON `fander_1`.* TO `fander`@`%` |
+-----------------------------------------------+
3 rows in set (0.00 sec)
```
At this point, a normal user would reasonably assume that fander should still have select privileges on all databases with names that match the pattern fander?1 (where "?" represents any character), which would obviously include the fander_1 database itself. Additionally, the user should only have insert privileges on the fander_1 database.
However, the test results show that the user no longer has any privileges on the fander_1 database, meaning the permissions are lost.
This is a serious bug, and I hope you agree with my assessment.
How to repeat:
1.Execute the following commands as the root user:
```
mysql> create database fander_1;
Query OK, 1 row affected (0.01 sec)
mysql> create database fander11;
Query OK, 1 row affected (0.01 sec)
mysql> create user fander identified by 'MySQL@2024';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select on `fander_1`.* to fander;
Query OK, 0 rows affected (0.01 sec)
mysql> create table fander_1.t (a int);
Query OK, 0 rows affected (0.04 sec)
```
2. Then, as the fander user:
mysql -ufander -pMySQL@2024 -h127.0.0.1
```
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| fander11 |
| fander_1 |
| information_schema |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
# At this point, `fander` has SELECT privileges on the `fander_1.t` table.
mysql> select * from fander_1.t;
Empty set (0.00 sec)
```
3. Next, as the root user, add the INSERT privilege for fander\_1:
```
mysql> grant insert on `fander\_1`.* to fander;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for fander;
+-----------------------------------------------+
| Grants for fander@% |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `fander`@`%` |
| GRANT INSERT ON `fander\_1`.* TO `fander`@`%` |
| GRANT SELECT ON `fander_1`.* TO `fander`@`%` |
+-----------------------------------------------+
3 rows in set (0.00 sec)
```
4. Finally, as the fander user again, you will notice that while the INSERT privilege works, the previously granted SELECT privilege is now lost:
mysql -ufander -pMySQL@2024 -h127.0.0.1
```
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| fander11 |
| fander_1 |
| information_schema |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> select * from fander_1.t;
ERROR 1142 (42000): SELECT command denied to user 'fander'@'127.0.0.1' for table 't'
mysql> insert into fander_1.t values(1);
Query OK, 1 row affected (0.01 sec)
```
```
Suggested fix:
This bug occurs because mixing grants for database names containing wildcards with grants for database names that contain escaped wildcards can lead to permission loss. The lost privileges are related to how the grants are ordered in the mysql.db metadata. MySQL only recognizes one of the grants. Although in my simulation, the SELECT privilege was lost, I could manipulate the order of entries in the mysql.db table through specific operations, which would result in the loss of the INSERT privilege instead of SELECT. I won't go into further details regarding this.
Description: In MySQL, both "_" and "%" are considered special characters in privilege statements. These characters are known as "wildcards." The underscore ("_") represents any single character. We have the following two databases: ``` fander_1 fander11 ``` Currently, my account has the following grant: ``` grant select on `fander_1`.* to fander; ``` As I mentioned earlier, the underscore ("_") is a wildcard, which means it's a special character. Therefore, this grant effectively gives select privileges on both of the above databases. In cases where there are many similar databases, this type of grant is simple and convenient. Everything works perfectly and operates as expected. However, one day, I wanted to give the fander_1 database an additional insert privilege (without granting this to the other databases). How can this be done? The answer, as I understand it, is to escape the underscore ("_"), which has a special meaning as a wildcard. The command I used is: ``` grant insert on `fander\_1`.* to fander; ``` After granting, the result is as follows: ``` mysql> show grants for fander; +-----------------------------------------------+ | Grants for fander@% | +-----------------------------------------------+ | GRANT USAGE ON *.* TO `fander`@`%` | | GRANT INSERT ON `fander\_1`.* TO `fander`@`%` | | GRANT SELECT ON `fander_1`.* TO `fander`@`%` | +-----------------------------------------------+ 3 rows in set (0.00 sec) ``` At this point, a normal user would reasonably assume that fander should still have select privileges on all databases with names that match the pattern fander?1 (where "?" represents any character), which would obviously include the fander_1 database itself. Additionally, the user should only have insert privileges on the fander_1 database. However, the test results show that the user no longer has any privileges on the fander_1 database, meaning the permissions are lost. This is a serious bug, and I hope you agree with my assessment. How to repeat: 1.Execute the following commands as the root user: ``` mysql> create database fander_1; Query OK, 1 row affected (0.01 sec) mysql> create database fander11; Query OK, 1 row affected (0.01 sec) mysql> create user fander identified by 'MySQL@2024'; Query OK, 0 rows affected (0.01 sec) mysql> grant select on `fander_1`.* to fander; Query OK, 0 rows affected (0.01 sec) mysql> create table fander_1.t (a int); Query OK, 0 rows affected (0.04 sec) ``` 2. Then, as the fander user: mysql -ufander -pMySQL@2024 -h127.0.0.1 ``` mysql> show databases; +--------------------+ | Database | +--------------------+ | fander11 | | fander_1 | | information_schema | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) # At this point, `fander` has SELECT privileges on the `fander_1.t` table. mysql> select * from fander_1.t; Empty set (0.00 sec) ``` 3. Next, as the root user, add the INSERT privilege for fander\_1: ``` mysql> grant insert on `fander\_1`.* to fander; Query OK, 0 rows affected (0.01 sec) mysql> show grants for fander; +-----------------------------------------------+ | Grants for fander@% | +-----------------------------------------------+ | GRANT USAGE ON *.* TO `fander`@`%` | | GRANT INSERT ON `fander\_1`.* TO `fander`@`%` | | GRANT SELECT ON `fander_1`.* TO `fander`@`%` | +-----------------------------------------------+ 3 rows in set (0.00 sec) ``` 4. Finally, as the fander user again, you will notice that while the INSERT privilege works, the previously granted SELECT privilege is now lost: mysql -ufander -pMySQL@2024 -h127.0.0.1 ``` mysql> show databases; +--------------------+ | Database | +--------------------+ | fander11 | | fander_1 | | information_schema | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) mysql> select * from fander_1.t; ERROR 1142 (42000): SELECT command denied to user 'fander'@'127.0.0.1' for table 't' mysql> insert into fander_1.t values(1); Query OK, 1 row affected (0.01 sec) ``` ``` Suggested fix: This bug occurs because mixing grants for database names containing wildcards with grants for database names that contain escaped wildcards can lead to permission loss. The lost privileges are related to how the grants are ordered in the mysql.db metadata. MySQL only recognizes one of the grants. Although in my simulation, the SELECT privilege was lost, I could manipulate the order of entries in the mysql.db table through specific operations, which would result in the loss of the INSERT privilege instead of SELECT. I won't go into further details regarding this.