Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-3303

A user with just EXECUTE on PROCEDURE privileges to the database, failing to connect via maxscale with database name mentioned.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.4.13, 2.5.5
    • Fix Version/s: 2.5.7
    • Component/s: Authenticator
    • Labels:
      None
    • Sprint:
      MXS-SPRINT-120

      Description

      This is with the MaxScale 2.4.13/2.5.5 and MariaDB server 10.5.5, a user with only EXECUTE to PROCEDURE privileges unable to connect via maxscale with database name mentioned explicitly.

      Same connection to the Direct MariaDB server IP, is able to access and execute the SP.

      The customer reported this saying after upgrading to the latest MaxScale 2.4.13, their application running on Python/Nodejs are not able to connect the database via MaxScale. They have the below mentioned user with similar privileges of EXECUTE to PROCEDURE only and in their application uses the database name explicitly while making the connection.

      As the connection to direct MariaDB server is working fine, raised this bug to fix the case, so that via maxscale as well it should connect the database.

      Here are the repro steps:

      1. Setup MaxScale 2.4.13/2.5.5 and MariaDB server 10.5.5, create following database/table/SP

      create database db1;
      use db1
      CREATE TABLE foo (a int);
      DELIMITER //
      CREATE PROCEDURE foobar()
      BEGIN
      SELECT * FROM foo;
      END //
      DELIMITER ;

      2. At the MariaDB server, create a user with below privileges

      create user 'user1'@'%' identified by 'suresh';
      create role user1_role;
      grant user1_role to 'user1'@'%';
      GRANT EXECUTE ON PROCEDURE `db1`.`foobar` TO `user1_role`;
      SET DEFAULT ROLE user1_role FOR 'user1'@'%';

      Or (without role)
      create user 'user1'@'%' identified by 'suresh';
      GRANT EXECUTE ON PROCEDURE `db1`.`foobar` TO 'user1'@'%';

      3. Try to connect the database via MaxScale with database name specified

      mysql -h<maxscale ip> -P <port> -u user1 -p suresh <database name> ## This gives error as access denied.

      4. Try to connect the database directly to the server with database name specified

      mysql -h<server ip> -P <port> -u user1 -p suresh <database name> ## This connects and can execute SP.

      Note: We can connect via MaxScale as well without mentioning the database name

      mysql -h<maxscale ip> -P <port> -u user1 -p suresh ## Database name not specified while connecting, it works.

      Test Results:
      =============
      [root@node4 ~]# maxctrl --version
      2.5.5

      [root@node4 ~]# maxctrl list servers
      ┌────────┬────────────────┬──────┬─────────────┬─────────────────────────┬──────┐
      │ Server │ Address │ Port │ Connections │ State │ GTID │
      ├────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ pidb01 │ 192.168.81.193 │ 3306 │ 0 │ Slave, Synced, Running │ │
      ├────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ pidb02 │ 192.168.81.192 │ 3306 │ 0 │ Master, Synced, Running │ │
      ├────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ pidb03 │ 192.168.81.194 │ 3306 │ 0 │ Slave, Synced, Running │ │
      └────────┴────────────────┴──────┴─────────────┴─────────────────────────┴──────┘
      [root@node4 ~]#

      [root@node4 ~]# maxctrl list services
      ┌────────────────────┬────────────────┬─────────────┬───────────────────┬────────────────────────┐
      │ Service │ Router │ Connections │ Total Connections │ Servers │
      ├────────────────────┼────────────────┼─────────────┼───────────────────┼────────────────────────┤
      │ Read-Write-Service │ readwritesplit │ 0 │ 0 │ pidb01, pidb02, pidb03 │
      └────────────────────┴────────────────┴─────────────┴───────────────────┴────────────────────────┘

      [root@node4 ~]# maxctrl list listeners Read-Write-Service
      ┌─────────────────────┬──────┬────────────────┬─────────┬────────────────────┐
      │ Name │ Port │ Host │ State │ Service │
      ├─────────────────────┼──────┼────────────────┼─────────┼────────────────────┤
      │ Read-Write-Listener │ 3306 │ 192.168.81.152 │ Running │ Read-Write-Service │
      └─────────────────────┴──────┴────────────────┴─────────┴────────────────────┘
      [root@node4 ~]#

        1. Connection to database via MaxScale(192.168.81.152) without specifying the database name:

      [root@node4 ~]# mysql -h192.168.81.152 -uuser1 -psuresh
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 3
      Server version: 10.5.5-MariaDB-log MariaDB Server

      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

      MariaDB [(none)]> use db1
      Database changed
      MariaDB [db1]> call foobar();
      ------

      a

      ------

      1

      ------
      1 row in set (0.002 sec)

      Query OK, 0 rows affected (0.002 sec)

      MariaDB [db1]> exit
      Bye

        1. Connection to database via MaxScale(192.168.81.152) specifying explicitly the database name: (Throws access denied error and maxscale logs noted user not found)

      [root@node4 ~]# mysql -h192.168.81.152 -uuser1 -psuresh db1
      ERROR 1044 (42000): Access denied for user 'user1'@'192.168.81.152' to database 'db1'
      [root@node4 ~]#

      maxscale logs:

      At Maxscale 2.5.5
      ==
      2020-11-20 06:42:17 warning: (4) [mariadbclient] Authentication failed for user 'user1'@[192.168.81.152] to service 'Read-Write-Service'. Originating listener: 'Read-Write-Listener'. MariaDB error: 'Access denied for user 'user1'@'192.168.81.152' to database 'db1''.

      At Maxscale 2.4.13
      ===
      2020-11-20 07:01:19 warning: (1) [MariaDBAuth] Read-Write-Service: login attempt for user 'user1'@[192.168.81.152]:58176 to database 'db1', authentication failed. User not found.

        1. Connection to database direct to the server IP (192.168.81.194) mentioning database name (Works fine, can execute the SP)

      [root@node4 ~]# mysql -h192.168.81.194 -uuser1 -psuresh db1
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 195
      Server version: 10.5.5-MariaDB-log MariaDB Server

      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

      MariaDB [db1]> call foobar();
      ------

      a

      ------

      1

      ------
      1 row in set (0.058 sec)

      Query OK, 0 rows affected (0.058 sec)

      MariaDB [db1]> exit
      Bye

        Attachments

          Activity

            People

            Assignee:
            esa.korhonen Esa Korhonen
            Reporter:
            suresh.ramagiri@mariadb.com suresh ramagiri
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration