Tell me please - what minimum set of privileges the user should to have to get access to the "mysql" database on the server.
For example to get ability to execute query:
select * from mysql.help_topic
Thanks!
3 Answers
The very minimal permissions would be:
GRANT USAGE ON *.* TO [user@host] ;
GRANT SELECT ON mysql.help_topic TO [user@host] ;
Do not believe this answer is sufficient. Please check the manual first, because the privilege system in MySQL is far from being obvious.
-
I believe USAGE is automatically granted by CREATE USER– Peter WoosterJan 17, 2013 at 14:04
-
@PeterWooster Yes, it is, but is required nonetheless.– YaKJan 17, 2013 at 14:05
-
there is no need do grant it as it's already there and it is described as a 'server administration' right, so is a bit confusing to average users.– Peter WoosterJan 17, 2013 at 14:06
-
@PeterWooster You are right on the premises, but I disagree with the conclusion. I wish the average user will read the manual for more information on this topic. Fiddling with access rights should not be done lightly. I do not mean be sound arrogant, I firmly believe this.– YaKJan 17, 2013 at 14:09
-
1. The USAGE privilege is granted to any registered user. 2 The SELECT privilege is enough to get information from the "mysql" database. I thought, that needed additional administration privileges to get access. Thanks for the answer!– Yuriy VelichkoJan 17, 2013 at 14:17
If you have phpmyadmin then while creating user you can give privileges by following
Priviliges -> Add a New User -> Data , you should be able to grant privileges like SELECT, INSERT, UPDATE, DELTE, FILE
You need to grant the "object" rights if you want to permit CRUD access. These are SELECT,INSERT,UPDATE, and DELETE. You may need to add EXECUTE and SHOW VIEW. Beyond that you are granting permission to change the schema.
If all you want is select, just grant SELECT.
here's the documentation: http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html