2

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.

Roronoa
  • 281
  • 3
  • 10
  • What about deleting the user and creating it again with `GRANT ... ON .*` instead of `*.*` ? – Nir Alfasi Jun 22 '13 at 05:46
  • I tried " grant usage on database.* to newuser@localhost IDENTIFIED BY 'password'; " but still its granting for all databases ie "usage on \*.*" for the new user – Roronoa Jun 22 '13 at 06:01

1 Answers1

7

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.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87