Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
2.2.11
-
None
Description
If a user does not have a SELECT privilege on any database or table, authentication via MaxScale will always fail.
One solution is to use the following SQL fragment in the user construction query.
SELECT CASE LOCATE('Y', CONCAT( |
Select_priv, Insert_priv, Update_priv, Delete_priv,
|
Create_priv, Drop_priv, Reload_priv, Shutdown_priv,
|
Process_priv, File_priv, Grant_priv, References_priv,
|
Index_priv, Alter_priv, Show_db_priv, Super_priv,
|
Create_tmp_table_priv, Lock_tables_priv, Execute_priv,
|
Repl_slave_priv, Repl_client_priv, Create_view_priv,
|
Show_view_priv, Create_routine_priv, Alter_routine_priv,
|
Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv
|
)) WHEN 0 THEN 'N' ELSE 'Y' END AS priv FROM mysql.user; |
The list of fields is most likely version specific. To accommodate changes in the list of fields, it needs to be constructed with the following query.
SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'user' AND COLUMN_NAME LIKE '%priv'; |
The same process must be repeated for mysql.db as well as mysql.tables_priv.