MySQL new user PRIVILEGES not working

Question

I created a user to only select/Read data from MySQL but I can still drop tables from database. what is wrong in this SQL script.

create user 'test'@'%' Identified by 'test!';
grant SELECT ON * . * TO 'test'@'%';
FLUSH PRIVILEGES;

Answer 1

You need to connect to the database as root.

mysql -u root -p  

I also notice that the proper command should be

GRANT SELECT ON *.* TO 'test'@'%';

Meanwhile yours is

grant SELECT ON * . * TO 'test'@'%';

That is, you got too many spaces (this could be the dealbreaker).

Then just use the test user

mysql -u test -p

And run an update query.

You should receive an error

command denied to user

You could also try to tell what exactly you're using:

GRANT SELECT ON databasename.viewname TO 'test'@'identifythehost';

Answer 2

Most probably your test database is modifiable by everyone. Try this:

DELETE FROM mysql.db WHERE Db IN('test', 'test\_%');
FLUSH PRIVILEGES;