Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.14
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?