mysqlprivilegesmysqladminmysqladministrator

Revoke privileges from user in mySQL

For every new user we create in mySQL using the statement

CREATE USER newuser@localhost IDENTIFIED BY 'password';

"SHOW GRANTS" is showing only "USAGE ON *.* " privilege.

But the user is able to select,insert,.. on "test" and "information_schema" databases and I'm unable to revoke these privileges on "test" using the revoke statement given below.

REVOKE ALL ON test.* FROM newuser@localhost;
ERROR 1141 (42000) : There is no such grant defined for user 'guest' on host 'localhost'

I just don't want the newuser to access the test and information_schema databases.

Solution

  • http://dev.mysql.com/doc/refman/5.6/en/default-privileges.html

    By default, the mysql.db table contains rows that permit access by any user to the test database and other databases with names that start with test_. (...) This means that such databases can be used even by accounts that otherwise possess no privileges. If you want to remove any-user access to test databases, do so as follows:

    mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';
mysql> FLUSH PRIVILEGES;

    (...) With the preceding change, only users who have global database privileges or privileges granted explicitly for the test database can use it.

    The information_schema database is a read-only pseudo database built on-the-fly on request. Users will always be able to consult this database, but it only presents entries to which they already have access to otherwise.