[MXS-1958] User without SELECT privileges is not allowed to connect Created: 2018-07-04  Updated: 2020-03-13  Resolved: 2020-02-14

Status: Closed
Project: MariaDB MaxScale
Component/s: Authenticator
Affects Version/s: 2.2.11
Fix Version/s: 2.5.0

Type: Bug Priority: Minor
Reporter: markus makela Assignee: Esa Korhonen
Resolution: Fixed Votes: 0
Labels: 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.

 Comments   
Comment by markus makela [ 2020-01-02 ]

Most likely fixed by the authenticator changes in 2.5.0.

Generated at Thu Feb 08 04:10:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.