Cannot grant user privilege to create REFERENCES in MySQL 8

Question

I tried to create the quartz table with a user that has privilege as below.

Privileges: [Select, Insert, Update, Delete, Create, Drop, File, Index, Alter, Show databases, Create temporary tables, Lock tables, Create view, Show view, Create routine, Alter routine, Event, Trigger]

it had error while running the following create table SQL, from quartz github

chosen the shortest create table SQL as below

CREATE TABLE QRTZ_BLOB_TRIGGERS
  (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    BLOB_DATA BLOB NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);

the error was SQL Error [1142] [42000]: (conn=16) REFERENCES command denied to user 'user'@'192.168.0.2' for table 'schema.qrtz_job_details'

then I used root user to do GRANT CREATE REFERENCES ON schema.* TO user@%

I also tried REFERENCE instead of REFERENCES, which should not have been an issue anyway, mysql reference

then the error became SQL Error [1064] [42000]: (conn=20) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCE ON schema.* TO user' at line 1

the [email protected]/255.255.255.0 user was just created fresh a few minutes ago, with

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.0/255.255.255.0';
FLUSH PRIVILEGES;

I had to resort to creating the tables using the root user. root ran those create table statements without any issue.

I am using DBeaver as SQL client, I do notice some past issues when I try to create / copy users using SQL clients like HeidiSQL / MySQL workbench or DBeaver.

Does any one see what has gone wrong why I can't grant the REFERENCES privilege?

Jimmy Chi Kin Chau · Accepted Answer · 2024-08-28 22:59:23Z

0

dug a bit through the users UI in Dbeaver, turns out I need to Grant grant to the new [email protected]/255.255.255.0 user

GRANT Grant option ON *.* TO 'root'@'192.168.0.0/255.255.255.0' WITH GRANT OPTION;
FLUSH PRIVILEGES;

may be there is a security reason to this, GRANT ALL PRIVILEGES does not grant the grant.

and also properly quote the domain by '%' GRANT REFERENCES ON schema.* TO user@'%';

answered Aug 28, 2024 at 22:59

Jimmy Chi Kin Chau

1851 silver badge16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot grant user privilege to create REFERENCES in MySQL 8

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related