Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.11.6, 11.3.1
-
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.
Attachments
Activity
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
|
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)
|
what "federated" are you using? what does
show?