Grant SELECT permission on some databases to a user using wildcards, then modify the permissions on one specific database for the same user, resulting in only SELECT permission

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 使用通配符把一此库的select权限给某用户,再把其中某一库给相同用户修改权限,最终只有select权限

| username: devopNeverStop

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.1
[Reproduction Path] Operations performed that led to the issue
Grant SELECT ON testDB_%.* to ‘testUser’@‘%’;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON testDB_db01.* TO ‘testUser’@‘%’;
[Encountered Issue: Problem Description and Impact]
The testUser user only has select permission for testDB_db01
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

| username: 我是咖啡哥 | Original post link

Can this be executed successfully?

| username: devopNeverStop | Original post link

Sure, the database name has backticks, but they are not displayed.

| username: 啦啦啦啦啦 | Original post link

I can indeed reproduce this issue in version 6.5.

| username: 裤衩儿飞上天 | Original post link

In previous tests, assigning permissions was not very friendly to the wildcard %, so it had to be done one by one :crazy_face:

| username: devopNeverStop | Original post link

In a SaaS environment, there are quite a few databases.

| username: 裤衩儿飞上天 | Original post link

You can concatenate strings in SQL.

| username: devopNeverStop | Original post link

If % doesn’t work, there’s no other choice. I originally thought it might be some variable controlling the permission stacking strategy, but I couldn’t find any relevant information.

| username: tidb菜鸟一只 | Original post link

I also tried, both tidb5.4.3 and 6.5.0 have this issue, but it did not occur on mysql5.7…

| username: wzf0072 | Original post link

Grant SELECT ON testDB_% .* to ‘testUser’@‘%’;

| username: devopNeverStop | Original post link

Is there anyone from the manufacturer who can provide a good solution? Thanks!

| username: Min_Chen | Original post link

Hello,
This issue has been verified as a bug and is being tracked in this issue. Please keep an eye on the progress of the fix. In the meantime, please use the workaround provided by the previous commenter. Thank you.

| username: devopNeverStop | Original post link

Got it, thanks!

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.