Grant MySQL/MariaDB privileges on all prefixed databases

Here we are, you reached this page while looking for how to grant MySQL/MariaDB privileges on some databases sharing the same table prefix like phabricator_asd, phabricator_lol or mediawiki_asd mediawiki_lol or whatever name prefixed with an underscore and you want to do it in the right way because you remember that something may be not correct in your syntax.

Well:

CREATE DATABASE mediawiki_asd;
CREATE DATABASE mediawiki_lol;

CREATE USER mediawiki_admin@localhost IDENTIFIED BY 'whoooo';

GRANT ALL PRIVILEGES ON `mediawiki\_%`.* TO mediawiki_admin@localhost;

In short you have to adopt \_% remembering to escape your underscores. Why? Because in the GRANT command you should respect the syntax expressed in the MariaDB LIKE specifications and that’s why your damn underscore, without being escaped, is a reserved char meaning whatever single character. The risk of not remembering this, is to forget the underscore escape, allowing all privileges also on unrelated databases like mediawikiasd or mediawikilol etc.
How could you sleep with such unuseful mistake? Uh?

Yep. I’ve written this dummy post because I’m definitely sick of wasting 5 minutes a week looking for this. Hoping to see this post in my DuckDuckGo results soon.

That’s all. asd

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *