grant to multiple db using one command

Question

I have many mariaDB databases that has same prefix. Such as

apple1
apple2
apple3
....
apple5000
apple5001
banana1
banana2
...
banana100

And I want create new user USER who can SELECT databases has apple prefix. So I grant SELECT to new user USER using multiple command below to.

GRANT SELECT ON `apple1` TO 'USER'@'%';
GRANT SELECT ON `apple2` TO 'USER'@'%';
GRANT SELECT ON `apple3` TO 'USER'@'%';
...
GRANT SELECT ON `apple5001` TO 'USER'@'%';

Is there any solution grant to multiple databases has specify prefix using one command like wildcard(%) of LIKE statement?

Answer 1

GRANT SELECT ON `apple%`.* TO 'USER'@'192.168.0.227';

Answer 2

The real problem is having thousands of databases. You are likely to be slowing things down by having so many. (This is an OS problem, since MySQL instantiates each database via a directory.)

Write a Stored Procedure to query against information_schema to discover all the databases (using LIKE) and generate (using SELECT ... CONCAT) the desired GRANT statements. Then manually copy that output into the mysql commandline tool to execute them.

Answer 3

as this came up on a google search, I ended up doing a for loop & piping it into mysql:

for i in var1 var2 var3 ; do echo "grant all on db_$i.* to user@host;" | mysql ; done

if using numbers then for i in 1..1000 if you have no login details in mycnf file & you have to login to mysql mysql -u user -password <Password> Only issue with the above is your password being in the command history.

Hope this helps anyone looking for a solution.