CODEWITHSUNDEEP
mysqlmariadbdatabase-administrationsql-grant
DongHoon Kim
DongHoon Kim

Reputation: 386

grant to multiple db using one command

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?

Upvotes: 1

Views: 2207

Answers (3)

user12976268
user12976268

Reputation: 1

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.

Upvotes: 0

Hyeonil Jeong
Hyeonil Jeong

Reputation: 228

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

Upvotes: 2

Rick James
Rick James

Reputation: 142298

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.

Upvotes: 0

Related Questions