Clever Geek Handbook

Provide multiple db's with one command

I have many databases mariaDB

that share the same prefix. For example,

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

And I want to create a new user USER

that SELECT

databases can have a prefix apple

. Therefore, I provide the SELECT

new user USER

using the following command below.

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 grant of a solution for multiple databases, specify the prefix using a single command, like the wildcard ( %

) operator LIKE

?

+1


source to share


2 answers 


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


+2


source


The real problem is with thousands of databases. You will probably be slowing things down with so much. (This is an OS issue, as MySQL creates each database through a directory.)



Write a stored procedure to query for information_schema

to discover all databases (using LIKE

) and generate (using SELECT ... CONCAT

) the statements you want GRANT

. Then manually copy these output to the mysql command line tool to execute them.

0


source






More articles:


All Articles