CODEWITHSUNDEEP
mysql
CMOS
CMOS

Reputation: 2917

How to enable MYSQL User access to all but one database

So I know questions similar to this have been asked but never anything exactly like this as far as I can find. Basically I have my main root user that has access to everything, and then I want a second user to have access to everything on localhost EXCEPT one database. Technically I want to remove their access to all databases with a prefix but I figured I would start with one. The issue I have found is that the databases I want restricted may change over time but they will always have a prefix. Anyways,

Normally to setup a user who has access to everything I do something like this

GRANT ALL PRIVILEGES ON *.* TO 'miniroot'@'localhost' WITH GRANT OPTION;

This way if I make a new database miniroot will always have access, this is great. However I have some tables with a prefix of 'PRIVATE'. I want miniroot to NOT have access to this.

I saw an answer on here on how to revoke access from a DB doing sometihng like this 

REVOKE all on PRIVATE.* from 'miniroot'@'localhost';

However this does not work and says ERROR 1141 (42000): There is no such grant defined for user 'miniroot' on host 'localhost' Which is true, I need my miniroot to have access to new DB's as they get created not a specific set. So I cant just remove one from the set. Any idea on how I might make this work?

Upvotes: 0

Views: 65

Answers (1)

Mazen Embaby
Mazen Embaby

Reputation: 1361

Well ,

you can use a wildcard% and _ for matching schemas :

You can use the opposite of what you want to accomplish 

CREATE USER 'jeffrey'@'%' IDENTIFIED BY 'password';
GRANT ALL ON  `public%`.* TO 'jeffrey'@'%';

so the user have access only schemas with prefix public any other name private or whatever named will not accessed

Upvotes: 1

Related Questions