0

I need to add an user able to read all my databases. So I create:

create user 'reader' identified by 'password';
grant select on *.* to 'reader';

The problem with this is that the user is able to read also system tables like mysql.user, which I think is a security problem. I tried to revoke the select privilege on that table:

revoke SELECT ON mysql.* from 'reader';

But I get

ERROR 1141 (42000): There is no such grant defined for user 'reader';

What am I doing wrong? Is there another way to achieve what I want? Or manipulating the grant tables is the only way?

1
  • You can revoke only right existed as standalone one (remove record from grants table). Grant SELECT ON mysql.* not exist (no such record), so operation fails. it's revoke, not deny. – Akina May 24 '18 at 10:06
0

may i correct what you have posted above..

"create user 'reader' identified by 'password'; grant select on . to 'reader';"

try below

create user reader@whatEverIPAddressOfUser identified by 'password'; grant select on . to reader@whatEverIPAddressOfUser; flush privileges;

then issue your revoke statement

revoke SELECT ON mysql.* from reader@whatEverIPAddressOfUser; flush privileges;

Not the answer you're looking for? Browse other questions tagged or ask your own question.