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?

Hyeonil Jeong · Accepted Answer · 2015-10-12 07:36:01Z

2

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

answered Oct 12, 2015 at 7:36

Hyeonil Jeong

2283 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rick James · Accepted Answer · 2015-06-24 19:12:21Z

0

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.

answered Jun 24, 2015 at 19:12

Rick James

144k15 gold badges144 silver badges254 bronze badges

Comments

user12976268 · Accepted Answer · 2020-02-27 23:28:19Z

0

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.

answered Feb 27, 2020 at 23:28

user12976268

1

Collectives™ on Stack Overflow

grant to multiple db using one command

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related