MySQL security settings ignore accounts limits?

Question

After several years of using MySQL, I've encountered some security issue that quite baffle me.

I create a new database, and a new user, then grant CRUD rights only to the new DB, to this user.

But when I log in either via some MySQL client, or via CLI mysql, I can read and change data in all databases.

I double-checked myself and verified that I should have access only to single DB - still I can access every DB.

Is there any setting for MySQL that just cause it to ignore the security, and I have accidentally turned it on?

Thanks.

Answer 1

Yes; if you start MySQL with --skip-grant-tables it allows free and easy access to everything by everyone. However, it's far more likely that you've made a mess of your auth and perms (it's really easy to do); post (probably in a new question is best) the set of queries/GRANT statements that have caused the problems (test on a scratch server that they actually do cause the problems you think they do) and someone can explain what's gone wrong.

Answer 2

i don't suppose you did grant create,update,read,delete on *.* to 'user'@'localhost'; ?

also check to make sure you're not using implicit user/password from a ~/.my.cnf file, and are starting the mysql CLI with mysql -u user -p (i mean, you may be 'logging in' as root@localhost or something...)

show grants; should show all the permissions you have, and which 'user' you are.

Answer 3

After restart it went away - strange stuff, will keep monitoring.

Thanks to everyone who answered.