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

Need more GRANTs to be documented for connecting to xpand direct using xpandmon

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5.9
    • Component/s: Documentation, xpandmon
    • Labels:
      None
    • Environment:

      Description

      mariadb-maxscale-25-xpand-monitor/
      mentions that the grants required for maxscale user are:

      CREATE USER 'maxscale'@'maxscalehost' IDENTIFIED BY 'maxscale-password';
      GRANT SELECT ON system.membership TO 'maxscale'@'maxscalehost';
      GRANT SELECT ON system.nodeinfo TO 'maxscale'@'maxscalehost';
      GRANT SELECT ON system.softfailed_nodes TO 'maxscale'@'maxscalehost';
      

      But if you want to connect to xpand direct through maxscale using a user other than the one specified in maxscale.cnf, you also need following permissions:

      GRANT SELECT ON `system`.`users` TO 'maxscale'@'%';
      GRANT SELECT ON `system`.`user_acl` TO 'maxscale'@'%';
      

      I used 'maxscale'@'%' here, but could use 'maxscale'@'maxscalehost'

      Setup:
      Config file on maxscale server:

      [root@karma040 ~]# cat /etc/maxscale.cnf
      [maxscale]
      log_info=1
      logdir=/data/clustrix/log
      threads=auto
       
      # ------------------------------
      # Config to connect to backend directly:
       
      [karma055-backend]
      type=server
      #address=10.2.12.214
      address=karma055.colo.sproutsys.com
      port=3306
      protocol=MariaDBBackend
       
      # Backend specific monitor and router:
      [Backend-Monitor]
      type=monitor
      module=xpandmon
      servers=karma055-backend
      user=maxscale
      password=maxscale_pw
      monitor_interval=10000
       
      [Backend-Read-Only-Service]
      type=service
      router=readconnroute
      user=maxscale
      password=maxscale_pw
      router_options=running
      cluster=Backend-Monitor
       
      [Backend-Read-Only-Listener]
      type=listener
      service=Backend-Read-Only-Service
      protocol=MariaDBClient
      address=0.0.0.0
      port=4008
      

      On xpand direct:

      CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale_pw';
          GRANT SELECT ON system.membership TO 'maxscale'@'%';
          GRANT SELECT ON system.nodeinfo TO 'maxscale'@'%';
          GRANT SELECT ON system.softfailed_nodes TO 'maxscale'@'%';
      

      Create another user on xpand direct that you want to use to connect to xpand direct through maxscale:

      MySQL [system]> CREATE USER 'rahul'@'%' IDENTIFIED BY 'maxscale_pw';
      Query OK, 0 rows affected (0.02 sec)
      

      Restart maxscale service and try:

      [root@karma111 ~]# mysql -h karma040 -u rahul -p'maxscale_pw' -P4008
      ERROR 1045 (28000): Access denied for user 'rahul'@'10.2.15.53' (using password: YES)
      

      Now provide the new permissions to maxscale user on xpand direct:

      MySQL [system]> SHOW GRANTS FOR 'maxscale'@'%';
      +---------------------------------------------------------------+
      | Grants for maxscale@%                                         |
      +---------------------------------------------------------------+
      | GRANT SELECT ON `system`.`membership` TO 'maxscale'@'%'       |
      | GRANT SELECT ON `system`.`nodeinfo` TO 'maxscale'@'%'         |
      | GRANT SELECT ON `system`.`softfailed_nodes` TO 'maxscale'@'%' |
      +---------------------------------------------------------------+
      3 rows in set (0.00 sec)
       
      MySQL [system]> GRANT SELECT ON `system`.`users` TO 'maxscale'@'%';
      Query OK, 0 rows affected (0.05 sec)
       
      MySQL [system]> GRANT SELECT ON `system`.`user_acl` TO 'maxscale'@'%';
      Query OK, 0 rows affected (0.06 sec)
      

      [root@karma040 ~]# service maxscale restart
      

      Now try connecting with the new user, that succeeds:

      [root@karma111 ~]# mysql -h karma040 -u rahul -p'maxscale_pw' -P4008
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MySQL connection id is 1
      Server version: 5.0.45-Xpand-mainline1-16876
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MySQL [(none)]> SHOW GRANTS FOR 'maxscale'@'%';
      +---------------------------------------------------------------+
      | Grants for maxscale@%                                         |
      +---------------------------------------------------------------+
      | GRANT SELECT ON `system`.`membership` TO 'maxscale'@'%'       |
      | GRANT SELECT ON `system`.`nodeinfo` TO 'maxscale'@'%'         |
      | GRANT SELECT ON `system`.`softfailed_nodes` TO 'maxscale'@'%' |
      | GRANT SELECT ON `system`.`users` TO 'maxscale'@'%'            |
      | GRANT SELECT ON `system`.`user_acl` TO 'maxscale'@'%'         |
      +---------------------------------------------------------------+
      5 rows in set (0.00 sec)
      

        Attachments

          Activity

            People

            Assignee:
            johan.wikman Johan Wikman
            Reporter:
            rahul.joshi@mariadb.com Rahul Joshi
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration