[MDEV-29659] Got error 1227 when reading table and ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation on SELECT or HANDLER READ when using spider_internal_sql_log_off=0 Created: 2022-09-28  Updated: 2023-12-16  Resolved: 2023-12-08

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Alexey Botchkov
Resolution: Not a Bug Votes: 0
Labels: affects-tests

Issue Links:
Relates
relates to MDEV-33041 Improved documentation for spider_int... Confirmed
relates to MDEV-33042 Improve unclear 'Got error 1227' erro... Open
relates to MDEV-33043 Possible performance optimization whe... Open

 Description    

INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER Spider@localhost IDENTIFIED BY 'PWD123';
 
GRANT ALL ON test.* TO Spider@localhost;
 
CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock', DATABASE 'test', USER 'Spider', PASSWORD 'PWD123');
 
CREATE TABLE t (c INT) ENGINE=InnoDB;
 
SET spider_internal_sql_log_off=0;
 
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
 
SELECT LEFT('a', SUM(a)) FROM t1;

Leads to:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)

 
10.11.0-dbg>SELECT LEFT('a', SUM(a)) FROM t1;
 
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)

 
Version: '10.11.0-MariaDB-debug'  socket: '/test/MD190922-mariadb-10.11.0-linux-x86_64-dbg/socket.sock'  port: 12223  MariaDB Server
 
2022-09-28 20:34:12 4 [ERROR] Got error 1227 when reading table './test/t1'

Bug confirmed present in:
MariaDB: 10.4.27 (dbg), 10.4.27 (opt), 10.5.18 (dbg), 10.5.18 (opt), 10.6.10 (dbg), 10.6.10 (opt), 10.7.6 (dbg), 10.7.6 (opt), 10.8.5 (dbg), 10.8.5 (opt), 10.9.3 (dbg), 10.9.3 (opt), 10.10.0 (dbg), 10.10.0 (opt), 10.10.2 (dbg), 10.10.2 (opt), 10.11.0 (dbg), 10.11.0 (opt)

Perror for ERROR 1227:

$ ./bin/perror 1227
 
MariaDB error code 1227 (ER_SPECIFIC_ACCESS_DENIED_ERROR): Access denied; you need (at least one of) the %-.128s privilege(s) for this operation



 Comments   
Comment by Roel Van de Paar [ 2022-09-28 ]

Additional testcase

CREATE USER Spider@localhost IDENTIFIED BY 'PWD123';
 
CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock', DATABASE 'test', USER 'Spider', PASSWORD 'PWD123');
 
CREATE TABLE t1 (c1 CHAR(3)) DEFAULT CHARSET=sjis ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
 
SET spider_internal_sql_log_off=0;
 
HANDLER t1 OPEN;
 
HANDLER t1 READ FIRST;

Comment by Yuchen Pei [ 2023-12-08 ]

It looks like this is expected, see the kb[1]

Explicitly setting the spider_internal_sql_log_off system variable causes the Spider node to execute SET sql_log_off statements on the data nodes to enable or disable the General Query Log. When this is done, queries issued by users without the SUPER privilege raise an error.

[1] https://mariadb.com/kb/en/spider-use-cases/#enable-use-of-non-root-connections

Any opposition to closing this as not a bug?

Comment by Roel Van de Paar [ 2023-12-08 ]

Agreed, we can close based on this. Is there something we can do about the error log message - to make it clearer (like in the client)?

Comment by Yuchen Pei [ 2023-12-08 ]

Thanks Roel - I'm closing this ticket for now. Please feel free to create a task to improve the error message.

Comment by Roel Van de Paar [ 2023-12-16 ]

MDEV-33041 Improved documentation for spider_internal_sql_log_off

Comment by Roel Van de Paar [ 2023-12-16 ]

MDEV-33042 Improve unclear 'Got error 1227' error log message

Comment by Roel Van de Paar [ 2023-12-16 ]

MDEV-33043 Possible performance optimization when using spider_internal_sql_log_off=0/1

Generated at Thu Feb 08 10:10:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.