Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 数据库对库授权 _不能识别
[TiDB Usage Environment] /Test/
[TiDB Version] 6.1.1
[Reproduction Path] GRANT ALL PRIVILEGES ON test_%.* TO ‘test’@‘%’;
[Encountered Problem: When granting privileges to the database, if you only write “test”, it can recognize the “test_1” database. However, if you write “test_”, it cannot find this database. This syntax works in MySQL. Tried escaping with GRANT ALL PRIVILEGES ON test\_%.* TO ‘test’@‘%’; but it still doesn’t work. This bug indicates that this syntax is not supported.]
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]
There is nothing in here that can solve my problem. My issue is with test_%, where the underscore is not recognized. test% works fine.
CREATE USER test@‘%’ IDENTIFIED BY ‘Asia_6796’;
GRANT ALL PRIVILEGES ON z_%.* TO test@‘%’;
I’ll give it a try, it should be doable.
According to your syntax, I got a syntax error. What version are you using?
Why can other databases be displayed? Shouldn’t it only show the “z_1” database?
My syntax is like this: GRANT ALL PRIVILEGES ON 'ds_%'.* TO dspirate@'%';
You should first create the user with CREATE USER, then execute GRANT. Directly using GRANT cannot create a user and will result in an error.
What I understand now is that this _ is a wildcard, but it doesn’t match the underscore string. How do I escape this to an underscore? I tried \ but it didn’t work.
This is an issue with Chinese quotation marks, right?
% matches any sequence of characters_ matches any single character
I suggest referring to this article: https://mp.weixin.qq.com/s/s0DsJhghy2GWrHZt9xu87g
The issue is still unresolved. I found that the underscore here is a wildcard. How can I escape it to an underscore?
I tried this method: GRANT ALL PRIVILEGES ON \ds\_%`.* TO ‘test’@‘%’;`.
GRANT ALL PRIVILEGES ON s\_%.* TO test@‘%’; Pay attention to the ` symbol.
It seems to be a forum issue, this symbol is not displayed, you need to wrap s\_% with two backticks.
Tested it, and it seems like there might be a bug.
Official website description:
Test: Version 6.5.0
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.5.0 |
+--------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| fl |
| mysql |
| test |
| test_ |
| test_1 |
| test_2 |
| test_2222 |
| tpch |
+--------------------+
11 rows in set (0.01 sec)
mysql> create user lf@'%' identified by 'lf';
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> grant all on `test\%`.* to lf@'%';
Query OK, 0 rows affected (0.06 sec)
mysql> show grants for lf@'%';
+----------------------------------------------+
| Grants for lf@% |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'lf'@'%' |
| GRANT ALL PRIVILEGES ON test\%.* TO 'lf'@'%' |
+----------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> exit
Bye
[tidb@qa-fenglei-01 ~]$ mysql -h10.18.13.224 -P3399 -ulf -plf
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 721
Server version: 5.7.25-TiDB-v6.5.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
+--------------------+
1 row in set (0.01 sec)
mysql>
My version is 5.4.3. By granting permissions to test as shown in the red box above, test can see u_1 but cannot see universe, which is correct. I haven’t tried version 6.5.
Currently using version 6.1, there are definitely issues. However, I don’t know the reason. I have performed REVOKE and grant permission operations multiple times.
It is certain that the two users have the same permissions but see different content. Later, I deleted the problematic user and then recreated the user and granted permissions. It worked… Could it be cached? This is also very difficult to reproduce.
|
username: TiDBer_30hewbUu
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 