How to enable MYSQL User access to all but one database

Question

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?

Mazen Embaby · Accepted Answer · 2018-04-20 02:20:43Z

1

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

answered Apr 20, 2018 at 2:20

Mazen Embaby

1,37111 silver badges18 bronze badges

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

2 Comments

CMOS Over a year ago

Hmmm, that is helpful information. However the biggest issue is that the whole reason I have to do this is because the existing system has thousands (not kidding) of databases that the primary account still needs access to. I basically need these two accounts to have inverse permissions and I am not sure it is possible :/

Mazen Embaby Over a year ago

Unfortunately, I am not sure also it's possible to inverse permission. Here what I am thinking to do it's not the perfect solution but hope to work select only one grant which is not important and let user access them eg (show view) specially if you don't have views in private schemas. GRANT show view ON private%.* TO 'jeffrey'@'%'; this will let user access only show view on any schemas begins with private This will be very helpful if you don't have views in private schemas that's mean it's privileged useless

Collectives™ on Stack Overflow

How to enable MYSQL User access to all but one database

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related