Unable to use CONNECT engine table

[bugeaud]

According to https://github.com/MariaDB/mariadb-docker/blob/master/10.10/docker-entrypoint.sh#L362-L364 ALL privileges has been given to MARIADB_USER.

But accessing with MARIADB_USER on MARIADB_DATABASE to a CONNECT defined table will result into #1227 - Access denied; you need (at least one of) the FILE privilege(s) for this operation as use of CONNECT engine database requires FILE privilege.

Those CONNECT tables are created with a line like :

RUN ls *.dbf | sed 's/\(.*\)\.dbf/create table \1 engine=CONNECT table_type=DBF CHARSET=cp850 file_name=\"\/app\/API\/data\/&\" option_list="Accept=1";/' > api-ccam-create.sql

and later will be transfered to the startup folder

RUN cp api-ccam-create.sql /docker-entrypoint-initdb.d/04-api-ccam.sql

Using MARIADB_USER all non CONNECT table are able to be accessed other table (say InnoDB engine) but not the one using CONNECT engine. Attemps to perform GRANT FILE have all failled. Only workaround to date is to use MARIADB_ROOT user that can access both kind of tables without issues.

[grooverdan]

It comes down to that grant all gives all the database privileges but none of the global privileges which includes FILE.

A solution is that the sql files in init are processed by MARIADB_ROOT so can be used to give additional grants:

$ cat init/01.sql 
GRANT FILE ON *.* TO bob@'%'

And a creation script like yours in the initdb.d to create the DBF connect tables:

$ cat init/02.sh
#!/bin/sh
for f in /app/API/data/*.dbf; do
    n=${f##*/}
    n=${n%.dbf}
    echo "create table $n engine=CONNECT table_type=DBF CHARSET=cp850 file_name=\"$f\" option_list=\"Accept=1\";"
done | mariadb -u "$MARIADB_USER" -p"$MARIADB_PASSWORD" "$MARIADB_DATABASE"