Accidentially I came across the statement "SHOW GRANTS requires the SELECT privilege for the mysql database." in MySQL documentation (http://dev.mysql.com/doc/refman/5.1/en/show-grants.html).
It is not quite true. Any user can "SHOW GRANTS [FOR himself]" with no privileges at all. But more important: SELECT priviege is requried on database-level, Privilege to the privileges tables is not enough. See
SHOW GRANTS;
/*returns
Grants for me@%
—————————————————–
GRANT USAGE ON *.* TO 'me'@'%'
GRANT SELECT ON `mysql`.`user` TO 'me'@'%'
GRANT SELECT ON `mysql`.`tables_priv` TO 'me'@'%'
GRANT SELECT ON `mysql`.`procs_priv` TO 'me'@'%'
GRANT SELECT ON `mysql`.`db` TO 'me'@'%'
GRANT SELECT ON `mysql`.`columns_priv` TO 'me'@'%'*/
SHOW GRANTS FOR root@localhost;
/*returns
Error Code: 1044
Access denied for user 'me'@'%' to database 'mysql' */
Anybody having SELECT privilege to the mysql database can read logs if you use log tables. And unlike when logging to files (where you can specify logfile paths) there is no option to specify another database for the log tables.
I think it is a serious security flaw. The reason is that a log may contain data. That may be private data (email addresses, bank account numbers .. you name it). Consider a statement like
UPDATE `identity`SET `bank_account_no` = ….. WHERE social_security_id = ….. ;
You should have SELECT privilege to the `identity` table to see those data. But if you can read logs you need not.
I don't claim all applications would send such statements (but I believe that some will do). You can avoid it to some extent by using user variables, hashes, or by writing complex statements with JOINs and SUBQUERIES so that you don't need to 'ping-pong' data from the server to the application and back (but it may conflict with performance in particular if SUBQUERIES are used).
So you should be careful with logging to tables. Not only is performance not good, but there are security concerns. Who should be allowed to see the logs? (but still log tables may be convenient for a test/development scenario though.)
I posted this bug report: http://bugs.mysql.com/bug.php?id=64215