Can't grant all privileges to a user as Admin on Azure MySQL server

Question

I'm having a MySQL server on Azure and I'm connecting as an admin, I would like to grant all privileges to some user (possibly the admin user) with:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

but I get: 1045 (28000): Access denied for user '<my_admin_username>'@'%' (using password: YES)

The reason i want to grant all privileges is, because i can't use this:

LOAD DATA INFILE 'somefile.csv'
INTO TABLE sometable
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; 

will give the same error, even though i'm connected as an admin user: 1045 (28000): Access denied for user '<my_admin_username>'@'%' (using password: YES)

I also tried this to achieve this goal, same error: GRANT FILE ON *.* TO '<my_admin_username>'@'%';

These are the permissions that an admin has as from SHOW GRANTS FOR '<my_admin_username>'@'%':

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON *.* TO `<my_admin_username>`@`%` WITH GRANT OPTION
GRANT APPLICATION_PASSWORD_ADMIN,REPLICATION_APPLIER,ROLE_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,XA_RECOVER_ADMIN ON *.* TO `<my_admin_username>`@`%` WITH GRANT OPTION

There is no "root" user. Just an admin with a nickname as defined on Azure website.

I also tried to do Microsoft Entra Authentication, which made a user in server, when i logged in as that user I still got the same error as for admin.

Answer 1

1045 (28000): Access denied for user '<my_admin_username>'@'%' (using password: YES)

The error generally comes for the wrong password for the user you are using to authenticate.

I also ran into same issue. I had to add LOCAL to my SQL statement.

For example, this gives the permission problem:

LOAD DATA INFILE 'csv1.csv'
INTO TABLE dometable
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Add LOCAL to your SQL statement and the permissions issue should go away. Like so:

LOAD DATA INFILE 'csv1.csv'
INTO TABLE dometable
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Also, while connecting to MySQL use connection string like below

mysql -h servername.mysql.database.azure.com -u pratik -p --local-infile=1