CODEWITHSUNDEEP
mysqlschemaprivileges
Gidget
Gidget

Reputation: 73

How to prevent a user from accessing a specific schema?

I have a server, that was created for me. On it there is a schema called "test".

I've logged in with root user, and created a new schema called "WEB".

create schema WEB;

What I want to do now, is to have a user, that can only see that new schema.

So I created a user like so:

create user webtestuser identified by 'webtestuser';

grant select, insert, update, delete on WEB.* to webtestuser;

The problem is that when I log in with the new user, I can still see the "test" schema. Even when I 'revoke all' on the user, that schema is still visable.

Anything I'm missing here?

Thanks !

Upvotes: 4

Views: 2695

Answers (1)

James C
James C

Reputation: 14169

By default mysql comes shipped with some grants foro the test schemas defined. They are ALL ON test to all users and also ALL ON test\_% to all users (test\_% matches stuff like test_foo, test_123)

Because of the way they're defined I can't see a way of removing these grants using the REVOKE ... FROM syntax so you'll have to use the following:

DELETE FROM mysql.db WHERE Db IN("test","test\_%") AND User="" AND Host="%";
FLUSH PRIVILEGES;

Upvotes: 3

Related Questions