Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15697

Remote user used by Spider needs SUPER privilege

    XMLWordPrintable

    Details

      Description

      I'm trying to test out Spider, but it doesn't seem to want to connect to the remote server if the remote user doesn't have SUPER privileges. Why is this necessary?

      e.g. if I do the following on the remote server:

      CREATE DATABASE spider_test;
       
      GRANT ALL PRIVILEGES ON spider_test.* TO spider_test@'%' IDENTIFIED BY 'password';
       
      USE spider_test;
       
      CREATE TABLE local_tab (
         id int auto_increment primary key,
         str varchar(50)
      ) ENGINE=InnoDB;
      

      And then the following on the Spider server:

      CREATE SERVER srv1 
        FOREIGN DATA WRAPPER mysql 
      OPTIONS( 
        HOST '172.30.0.249', 
        DATABASE 'spider_test',
        USER 'spider_test',
        PASSWORD 'password'
      );
       
      CREATE DATABASE spider_test;
       
      USE spider_test;
       
      CREATE TABLE spider_tab (
         id int auto_increment primary key,
         str varchar(50)
      ) ENGINE=Spider COMMENT='wrapper "mysql", srv "srv1", table "local_tab"';
      

      I am not able to query the spider table:

      MariaDB [spider_test]> SELECT * FROM spider_tab;
      ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
      

      Even though I am able to connect to the remote server and query the table using the regular mysql client:

      $ mysql -h 172.30.0.249 -u spider_test -ppassword spider_test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 15
      Server version: 10.1.31-MariaDB 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 [spider_test]> SELECT * FROM local_tab;
      Empty set (0.00 sec)
      

      The spider table works if I give the user the SUPER privileges.

      remote server:

      GRANT SUPER ON *.* TO spider_test@'%';
      

      Spider server:

      MariaDB [spider_test]> SELECT * FROM spider_tab;
      Empty set (0.01 sec)
      

      Why would the remote user need SUPER privileges?

        Attachments

          Activity

            People

            Assignee:
            jacob-mathew Jacob Mathew (Inactive)
            Reporter:
            GeoffMontee Geoff Montee
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: