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?
Attachments
Activity
Also documented in https://mariadb.com/kb/en/library/spider-use-cases/#enable-use-of-non-root-connections , but I have seen it before and was looking for it again, but 'Use cases' seems to be a weird place to put this; it probably belongs in the FAQ?
To fix the problem, I have changed the spider_internal_sql_log_off configuration setting so that if it is NOT SET, which is the most likely case, the Spider node DOES NOT SEND the 'SET SQL_LOG_OFF' statement to the data nodes. However if the spider_internal_sql_log_off setting IS EXPLICITLY SET to either 0 or 1, then the Spider node DOES SEND the 'SET SQL_LOG_OFF' statement, requiring a remote user with the SUPER privilege. The Spider documentation will be updated to reflect this change.
See https://mariadb.com/kb/en/library/spider-server-system-variables/#spider_remote_sql_log_off
Setting this to 1 ('set GLOBAL spider_remote_sql_log_off=0;') results in spider not trying to 'set session sql_log_off = 1;' on the remote databases.
Maybe the default could be changed?