Permission denied mysql outfile

Question

I am trying to understand which user is called when I execute a MySQL procedure because my procedure tries to create a file but the file can not be created because of a permission problem.

Here is my procedure:

CREATE PROCEDURE `EXPORT_TEST`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN

DECLARE VAR_INS INT(10);
DECLARE VAR_DT_DEBUT DATETIME;


SET VAR_DT_DEBUT = CURRENT_TIMESTAMP;

INSERT INTO BC_ALIM (NOM_TABLE, DATE_DEBUT)
  VALUES('EXPORT_TEST', VAR_DT_DEBUT);

BEGIN

SELECT * INTO OUTFILE '/home/bureau/IMPORTS/fichierSortie3.csv'
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM db_baby_concept.BC_TYPE_LIVRAISON;
END;
END

Thank you for help.

When you run a procedure it uses the permissions of the creator by default. — Barmar, Dec 06 '18 at 21:28

score 0 · Accepted Answer · answered Dec 06 '18 at 21:36

0

MySQL stored procedures have a SQL SECURITY characteristic, which specifies security contact in which the procedure code runs. This can be either DEFINER or INVOKER, and the default is DEFINER. So when you run your stored procedure, it executes with the permissions of the user who defined the procedure; if they don't have the FILE privilege, it won't be able to create the file.

You can set the procedure's security to INVOKER instead. Then it will use your permissions rather than the creator's.

CREATE PROCEDURE procName()
SQL SECURITY INVOKER
BEGIN 
    ...
END;

Also, the file is written by the MySQL server process, so the Linux userid used for the server needs to have write permission to the directory containing the file. So you probably need to make your IMPORTS directory world-writable. And if the secure_file_priv system variable is set, you can only write to the directory named there.

answered Dec 06 '18 at 21:36

Barmar

741,623
53
500
612

I have changed the procedure but I still have access denied (See my post) Thx – Chris99391 Dec 06 '18 at 21:51
Is the IMPORTS directory world-writable? – Barmar Dec 06 '18 at 22:01
Yes chmod -R 777 – Chris99391 Dec 06 '18 at 22:03
See https://stackoverflow.com/questions/30359005/select-into-outfile-permission-denied-but-the-user-can-write-to-the-directory/30359605 – Barmar Dec 06 '18 at 22:09
This does not impact anything on the database? – Chris99391 Dec 06 '18 at 22:14
Not sure what you mean. If you just make changes that affect writing files, it shouldn't affect anything else. – Barmar Dec 06 '18 at 22:16
Not work :'( All users of the database have the File_priv at Y – Chris99391 Dec 06 '18 at 22:29
There are lots of other questions about this topic. – Barmar Dec 06 '18 at 22:31
its working with this Prevent accessing /home, /root and /run/user ProtectHome=true – Chris99391 Dec 06 '18 at 22:37

Permission denied mysql outfile

1 Answers1