Mysql: privileges to get access to “thesql” database

MySQL

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!

Best Answer

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.

Related Solutions

MySQL – Minimum Privileges Required for Database Schema Backup

According to the MySQL Documentation

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

Thus, to run

mysqldump --no-data --routines --triggers ...

the privileges required are

SELECT
SHOW VIEW (If any database has Views)
TRIGGER (If any table has one or more triggers)
LOCK TABLES (If you use an explicit --lock-tables)

You will also need the SELECT privilege on mysql.proc in order to dump Stored Procedures.

MySQL: What exact commands does each privilege level allow

show and set are commands of the MySQL client; you can't prevent users from using them.

set global is only allowed if the user has the super system privilege. See dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges

Best Answer

Related Solutions

MySQL – Minimum Privileges Required for Database Schema Backup

MySQL: What exact commands does each privilege level allow

Related Question