[MDEV-18126] Connect-Table-Type MYSQL: access denied when create table Created: 2019-01-03  Updated: 2019-01-03  Resolved: 2019-01-03

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table, Storage Engine - Connect
Affects Version/s: 10.3.11
Fix Version/s: N/A

Type: Bug Priority: Trivial
Reporter: Karsten Budde Assignee: Olivier Bertrand
Resolution: Not a Bug Votes: 0
Labels: connect-engine
Environment:

CentOS Linux release 7.5.1804 (Core)


 Description   

Hello,

I want to use a connect-table between two mariaDB databases.
The target db-server is named host1 and there is a mariaDB db1 with user user1.
The source db-server is named host2 and there is a mariaDB db2 with user user2.
user1 is not in db2 and user2 is not in db1.

When I run the following statement as user1 in db1:

create or replace table test1
 
 engine = connect
 
 table_type = MYSQL
 
 dbname = db2
 
 tabname = testtab
 
 connection = 'mysql://user2:password2@host2';

in db1 table test1 is created.

When I run the following statement as user1 in db1:

create or replace table test2
 
 (column1 int not null,
 
  column2 varchar)
 
 engine = connect
 
 table_type = MYSQL
 
 dbname = db2
 
 tabname = testtab
 
 connection = 'mysql://user2:password2@host2';

I get the error

SQL-Fehler [1045] [28000]: (conn=95) Access denied for user 'user1'@'%' (using password: YES)

Then I create user user1 in db2 on host2 with all privileges and retry creating the conntect-table. But I get always the error

SQL-Fehler [1045] [28000]: (conn=95) Access denied for user 'user1'@'%' (using password: YES)

I don't know, what is wrong. Please help me.

Regards,
Karsten

 Comments   
Comment by Karsten Budde [ 2019-01-03 ]

Hello,

as root-user in db1 I can run the statement without errors:

create or replace table test2
 
 (column1 int not null,
 
  column2 varchar)
 
 engine = connect
 
 table_type = MYSQL
 
 dbname = db2
 
 tabname = testtab
 
 connection = 'mysql://user2:password2@host2';

Then I test all global priviliges to user1. On privileges FILE the statement runs also for user1 without errors.

In the documentation I found only for file-based connect-tables (https://mariadb.com/kb/en/library/inward-and-outward-tables/), that the privilege FILE is needed.

Is a connect-table MYSQL a file-based connet-table?

Regard,
Karsten

Comment by Olivier Bertrand [ 2019-01-03 ]

Is a connect-table MYSQL a file-based connect-table?
I am not sure (this checking function was not written by me) but it could be, probably when the target table is file based.

Generably speaking, this MYSQL table type was written principally for internal use by other types like TBL or partition tables when they target a non CONNECT table. In case of such trouble, you may want to use the FEDERATED(X) storage engine, which does the same things the CONNECT MYSQL type does.

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