Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.25, 10.11.16, 11.4.10, 11.8.6, 12.3.1
-
None
Description
Problem
If database name is near the maximum allowed limit (64 symbols) and contains _ symbols, it is impossible to make a functional privilege GRANT for this database.
Steps to reproduce
# mariadb
|
...
|
Server version: 11.8.6-MariaDB-ubu2404 mariadb.org binary distribution
|
|
|
MariaDB [(none)]> CREATE DATABASE very_long_database_name_with_underscore_symbols_inside_the_name;
|
Query OK, 1 row affected (0.000 sec)
|
|
|
MariaDB [(none)]> CREATE USER john@localhost IDENTIFIED BY 'password';
|
Query OK, 0 rows affected (0.010 sec)
|
|
|
MariaDB [(none)]> GRANT ALL ON `very\_long\_database\_name\_with\_underscore\_symbols\_inside\_the\_name`.* TO john@localhost;
|
Query OK, 0 rows affected (0.011 sec)
|
|
|
MariaDB [(none)]> SHOW GRANTS FOR john@localhost;
|
+----------------------------------------------------------------------------------------------------------------------------+
|
| Grants for john@localhost |
|
+----------------------------------------------------------------------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO `john`@`localhost` IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
|
| GRANT ALL PRIVILEGES ON `very\_long\_database\_name\_with\_underscore\_symbols\_inside\_the\_name`.* TO `john`@`localhost` |
|
+----------------------------------------------------------------------------------------------------------------------------+
|
The problem is that `FLUSH PRIVILEGES` query will reload the GRANTs with truncated database name:
MariaDB [(none)]> FLUSH PRIVILEGES;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [(none)]> SHOW GRANTS FOR john@localhost;
|
+--------------------------------------------------------------------------------------------------------------------+
|
| Grants for john@localhost |
|
+--------------------------------------------------------------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO `john`@`localhost` IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
|
| GRANT ALL PRIVILEGES ON `very\_long\_database\_name\_with\_underscore\_symbols\_inside\_t`.* TO `john`@`localhost` |
|
+--------------------------------------------------------------------------------------------------------------------+
|
2 rows in set (0.000 sec)
|
The grant is now very_long_database_name_with_underscore_symbols_inside_t instead of very_long_database_name_with_underscore_symbols_inside_the_name and user `john` can not access this database.
Problem is visible in the mysql.db table:
MariaDB [(none)]> SELECT Db, user FROM mysql.db;
|
+------------------------------------------------------------------+------+
|
| Db | user |
|
+------------------------------------------------------------------+------+
|
| very\_long\_database\_name\_with\_underscore\_symbols\_inside\_t | john |
|
+------------------------------------------------------------------+------+
|
1 row in set (0.000 sec)
|
Root cause
The mysql.db can not store the escaped name of the database because it is limited to the 64 symbols (maximum allowed database name). However the escaped database name will be longer than 64 symbols.
MariaDB [(none)]> describe mysql.db;
|
+--------------------------+---------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+--------------------------+---------------+------+-----+---------+-------+
|
| Host | char(255) | NO | PRI | | |
|
| Db | char(64) | NO | PRI | | | <--- problem
|
| User | char(128) | NO | PRI | | |
|
Scope
This problem affects all MariaDB releases.
Problem affects all databases with long names and _ symbols inside. It would affect % symbols as well but they are less often used in database names.
Expected behaviour
- The GRANT statement should have returned and error because it can not create functional GRANT with the given name
- DB engine should be updated to actually support GRANTS when escaped DB name exceeds 64 symbols.