Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: GRANT 授权，是否能反选对应表 或者 表正则匹配？
|
username: lzb666
Currently, there is a database with 800 tables. A new account needs to be created and granted access to most of the tables, except for a few sensitive data tables. Additionally, for newly added tables, it should automatically comply with rules such as test database dp_xxxxxx.
It seems that this is not supported. MySQL should also support regular expressions for database names, but not for individual table names.
I have the same requirement. My approach is to create a role, write a script to grant table permissions to the role, set up a detection for new tables, and then assign the role’s permissions to developers and others when granting permissions.
Is it possible to first grant access to the entire database, and then revoke access to the relevant sensitive tables?
This won’t work. The database and table correspond to different tables in the MySQL privilege system, so this cannot be achieved.
How about first granting query permissions for the entire database, and then revoking permissions for a few specific tables?
However, you can also use a script to first query the table names and then execute them in batches.
Provide a convenient approach: can we extract the sensitive tables from this database and place them in another database on the current machine, such as a configuration database? This way, by granting permissions separately, we won’t have to worry.
Of course, this is possible~~~ but this is equivalent to sharding~~ if there are join table operations, code modifications will be needed~~ it’s quite a bit of work.
At present, it seems to be a relatively convenient solution.
Okay, we will take this solution into consideration. However, due to historical baggage, there will be quite a lot of code changes.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: