[MDEV-32984] Update federated table and column privileges Created: 2023-12-11  Updated: 2024-01-10  Resolved: 2024-01-10

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Federated
Affects Version/s: 10.11.6, 11.3.1
Fix Version/s: 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Major
Reporter: Vladimir Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None


 Description   

Side A:

create database my_user;
 
create user 'my'@'x.x.x.x' identified by '1qaz2wsx';
 
use my_user;
 
CREATE TABLE `users` (
 
  `id` int(6) NOT NULL AUTO_INCREMENT,
 
  `name` varchar(50) DEFAULT NULL,
 
  `password` varchar(50) DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `name` (`name`)
 
);
 
GRANT INSERT, SELECT (`id`, `name`, `password`), UPDATE (`password`) ON `my_user`.`users` TO `my`@`x.x.x.x`;

Side B:

create database prod_db;
 
create user 'prod_user' identified by '2wsx3edc';
 
GRANT ALL PRIVILEGES ON prod_db.* TO 'prod_user' WITH GRANT OPTION;
 
CREATE TABLE `prod_f` (
 
  `id` int(6) NOT NULL AUTO_INCREMENT,
 
  `name` varchar(50) DEFAULT NULL,
 
  `password` varchar(50) DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `name` (`name`)
 
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://my:1qaz2wsx@a.a.a.a:3306/my_user/users';
 
insert into prod_f (name,password) values ('test','123');


select * from prod_f\G
 
      id: 1
 
    name: test
 
password: 123
 
 
 
UPDATE prod_f SET password='123456' WHERE name='test';
 
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1143: UPDATE command denied to user 'my'@'x.x.x.x' for column 'id' in table 'users'' from FEDERATED

Why does mariadb need permissions on the 'id' field?
In this case, update will work if grant update permissions on the 'id' field. In mysql 5.7 this works, no permissions for 'id' are required, but in 10.x it does not.

 Comments   
Comment by Sergei Golubchik [ 2023-12-11 ]

what "federated" are you using? what does 

select * from information_schema.plugins where plugin_name like '%fede%'

show?

Comment by MG [ 2023-12-12 ]

I can reproduce this:

           PLUGIN_NAME: FEDERATED
 
        PLUGIN_VERSION: 2.1
 
         PLUGIN_STATUS: ACTIVE
 
           PLUGIN_TYPE: STORAGE ENGINE
 
   PLUGIN_TYPE_VERSION: 101106.0
 
        PLUGIN_LIBRARY: ha_federatedx.so
 
PLUGIN_LIBRARY_VERSION: 1.15
 
         PLUGIN_AUTHOR: Patrick Galbraith
 
    PLUGIN_DESCRIPTION: Allows one to access tables on other MariaDB servers, supports transactions and more
 
        PLUGIN_LICENSE: GPL
 
           LOAD_OPTION: ON
 
       PLUGIN_MATURITY: Stable
 
   PLUGIN_AUTH_VERSION: 2.1

On the remote side:

231212  1:14:45	     6 Query	set time_zone='+00:00'
 
		     6 Query	SET AUTOCOMMIT=0
 
		     6 Query	set time_zone='+00:00'
 
		     6 Query	SAVEPOINT save1
 
		     6 Query	set time_zone='+00:00'
 
		     6 Query	SHOW TABLE STATUS LIKE 'users'
 
		     6 Query	set time_zone='+00:00'
 
		     6 Query	SELECT `id`, `name`, `password` FROM `users` WHERE  ( (`name` = 'test') ) AND ( (1=1) )
 
		     6 Query	set time_zone='+00:00'
 
		     6 Query	UPDATE `users` SET `id` = 1, `password` = '123456' WHERE `id` = 1 AND `name` = 'test'
 
		     6 Query	set time_zone='+00:00'
 
		     6 Query	ROLLBACK

Comment by Vladimir [ 2023-12-12 ]

The problem was initially observed on old version of federated, then the plugin was upgraded to federatedx, which did not solve the problem.

before update:

MariaDB [prod_db]> select * from information_schema.plugins where plugin_name like '%fede%'\G
 
PLUGIN_NAME: FEDERATED
 
PLUGIN_VERSION: 1.0
 
PLUGIN_STATUS: ACTIVE
 
PLUGIN_TYPE: STORAGE ENGINE
 
PLUGIN_TYPE_VERSION: 100612.0
 
PLUGIN_LIBRARY: ha_federated.so
 
PLUGIN_LIBRARY_VERSION: 1.14
 
PLUGIN_AUTHOR: Patrick Galbraith and Brian Aker, MySQL AB
 
PLUGIN_DESCRIPTION: Allows accessing tables on other MariaDB servers
 
PLUGIN_LICENSE: GPL
 
LOAD_OPTION: ON
 
PLUGIN_MATURITY: Gamma
 
PLUGIN_AUTH_VERSION: 1.0
 
1 row in set (0.003 sec)

after:

MariaDB [prod_db]> select * from information_schema.plugins where plugin_name like '%fede%'\G
 
PLUGIN_NAME: FEDERATED
 
PLUGIN_VERSION: 2.1
 
PLUGIN_STATUS: ACTIVE
 
PLUGIN_TYPE: STORAGE ENGINE
 
PLUGIN_TYPE_VERSION: 100612.0
 
PLUGIN_LIBRARY: ha_federatedx.so
 
PLUGIN_LIBRARY_VERSION: 1.14
 
PLUGIN_AUTHOR: Patrick Galbraith
 
PLUGIN_DESCRIPTION: Allows one to access tables on other MariaDB servers, supports transactions and more
 
PLUGIN_LICENSE: GPL
 
LOAD_OPTION: ON
 
PLUGIN_MATURITY: Stable
 
PLUGIN_AUTH_VERSION: 2.1
 
1 row in set (0.005 sec)

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