Use wildcard to grant rights to table with MySQL and MariaDB
The attribution of right in MySQL and MariaDB is sometimes a puzzle especially if one wishes to have a control by table for a user. It is possible to use a wildcard in the GRANT command, but its syntax is not very explicit.
Let's imagine that for my blog I work with an SEO team. This team needs access to the BDD to manage their data and to analyze stats. The team is not composed of developers, I do not want to grant them too much rights on tables.
All tables they need are prefixed with "seo_". The command that comes to mind is:
GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.seo_* TO 'seo_team'@'localhost';
You guessed it, it's too easy, it does not work. MySQL and MariaDB do not accept the notation "seo_ *" as the table name. So I could do one command per table by explicitly indicating the table, but it implies to maintain the rights as more tables are added or removed.
It is possible to work around the problem, MySQL and MariaDB accepts a wildcard (only the char %) on the first part of the table expression (before the point). The syntax to use becomes:
GRANT SELECT, INSERT, UPDATE, DELETE ON `blog.seo_%`.* TO 'seo_team'@'localhost';
Easy to use, but not the solution it comes to mind.
Replied of ulrich on Jan 17, 2024