Search code examples
mysqlsqlt-sqlprivilegessql-grant

Command denied for table 'session_variables'

After updating mysql version 5.7.8-rc-log, I granted privileges like this:

GRANT select ON test_db.* TO 'test'@'host';

and getting following error:

SELECT command denied to user 'test'@'host' for table 'session_variables'

but when I grant privileges like this:

GRANT select ON *.* TO 'test'@'host';

it works. Can anybody help?

Solution

  • Here are the article1, article2, article3 related to this issue. As per these articles, Workaround is setting show_compatibility_56 = on in /etc/my.cnf and restart mysql server.

    MySQL 5.7 introduces a change in the way we query for global variables and status variables: the INFORMATION_SCHEMA.(GLOBAL|SESSION)_(VARIABLES|STATUS) tables are now deprecated and empty. Instead, we are to use the respective performance_schema.(global|session)_(variables|status) tables.

    But the change goes farther than that; there is also a security change.

    So non-root user gets:

    mysql> show session variables like 'tx_isolation';
ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'session_variables'

    Solutions?

    The following are meant to be solutions, but do not really solve the problem:

    • SHOW commands. SHOW GLOBAL|SESSION VARIABLES|STATUS will work properly, and will implicitly know whether to provide the results via information_schema or performance_schema tables. But, aren't we meant to be happier with SELECT queries? So that I can really do stuff that is smarter than LIKE 'variable_name%'? And of course you cannot use SHOW in server side cursors. Your stored routines are in a mess now. This does not solve the GRANTs problem.
    • show_compatibility_56: an introduced variable in 5.7, boolean. It truly is a time-travel-paradox novel in disguise, in multiple respects. Documentation introduces it, and says it is deprecated. time-travel-paradox :O But it actually works in 5.7.8 (latest) time-travel-paradox plot thickens Your automation scripts do not know in advance whether your MySQL has this variable Hence SELECT @@global.show_compatibility_56 will produce an error on 5.6 But the "safe" way of SHOW GLOBAL VARIABLES LIKE 'show_compatibility_56' will fail on a privilege error on 5.7 time-travel-paradox :O Actually advised by my colleague Simon J. Mudd, show_compatibility_56 defaults to OFF. I support this line of thought. Or else it's old_passwords=1 all over again. show_compatibility_56 doesn't solve the GRANTs problem. This does not solve any migration path. It just postpones the moment when I will hit the same problem. When I flip the variable from "1" to "0", I'm back at square one.

    Suggestion

    I claim security is not the issue, as presented above. I claim Oracle will yet again fall into the trap of no-easy-way-to-migrate-to-GTID in 5.6 if the current solution is unchanged. I claim that there have been too many changes at once. Therefore, I suggest one of the alternative two flows:

    • Flow 1: keep information_schema, later migration into performance_schema In 5.7, information_schema tables should still produce the data. No security constraints on information_schema Generate WARNINGs on reading from information_schema ("...this will be deprecated...") performance_schema also available. With security constraints, whatever. In 5.8 remove information_schema tables; we are left with performance_schema only.
    • Flow 2: easy migration into performance_schema: In 5.7, performance_schema tables should not require any special privileges. Any user can read from them. Keep show_compatibility_56 as it is. SHOW commands choose between information_schema or performance_schema on their own -- just as things are done now. In 5.8, performance_schema tables will require SELECT privileges.

    Hope this will help you.