SELECT INTO OUTFILE permission denied but the user can write to the directory

Question

I have an issue trying to use SELECT INTO OUTFILE and using a directory other than /tmp.

My Linux user is named datam, my MySQL user is lea, and MySQL runs as mysql.

When datam runs mysql -u lea database and tries to do a SELECT INTO OUTFILE with a path of /home/datam/xfers/online/file.csv, I get an error code 13, permission denied. Using /tmp/file.csv works, so I'm fairly confident it is not an issue with permissions within MySQL.

I've added mysql to the datam group and have verified this with:

~$ sudo id mysql
uid=106(mysql) gid=114(mysql) groups=114(mysql),1001(datam)

I have /home/datam/ set as 775 recursively.

If I do sudo -u mysql /bin/bash and go to /home/datam/xfers/online/ and do touch file it writes a file.

What do I need to do to allow mysql to write a file from SELECT INTO OUTFILE?

I believe this is not a duplicate of other questions surrounding this subject, because I've looked at them and followed all of their instructions (setting execute on all directories leading up to the one I want, setting GRANT FILE ON, etc).

MySQL user lea grants:

+-----------------------------------------------------------------------------------------------------------+
| Grants for lea@localhost                                                                                  |
+-----------------------------------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO 'lea'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT ALL PRIVILEGES ON `database`.* TO 'lea'@'localhost'                                    |
+-----------------------------------------------------------------------------------------------------------+

I finally got MySQL to export to a file by following advice here https://dba.stackexchange.com/a/245383/18098 and here https://stackoverflow.com/a/16990452/470749 — Ryan, Aug 15 '19 at 00:11

score 7 · Accepted Answer · edited Apr 13 '17 at 12:42

7

This may be caused by mysql user permissions.

As stated here https://dba.stackexchange.com/questions/17029/cannot-output-mysql-data-to-file

To give yourself FILE privilege, do the following:

service mysql restart --skip-networking --skip-grant-tables
mysql <hit enter>
UPDATE mysql.user SET File_priv = 'Y' WHERE user='lea' AND host='localhost';
exit
service mysql restart

The linux user can write a file. But the mysql service may be blocked by apparmor.

Check this file: /etc/apparmor.d/usr.sbin.mysqld.

Add your project folder there:

/usr/sbin/mysqld {
    [...]
    /home/datam/xfers/online/ r,
    /home/datam/xfers/online/* rw
    [...]
}

Finally, do a

sudo /etc/init.d/apparmor reload

edited Apr 13 '17 at 12:42

Community

1
1

answered May 20 '15 at 20:19

Alex Tartan

6,736
10
34
45

The user does have the file privilege though. As stated in my question: `Using /tmp/file.csv works, so I'm fairly confident it is not an issue with permissions within MySQL` – nwalke May 20 '15 at 20:21
Did you try it? " Any file created by INTO OUTFILE or INTO DUMPFILE is writable by all users on the server host. The reason for this is that the MySQL server cannot create a file that is owned by anyone other than the user under whose account it is running. (You should never run mysqld as root for this and other reasons.) The file thus must be world-writable so that you can manipulate its contents." – Alex Tartan May 20 '15 at 20:23
And having a mysql user with file permissions is different from trying to write to a file using `mysql` operating system account – Alex Tartan May 20 '15 at 20:26
I'm not sure what your quote has to do with anything. The file that comes from `INTO OUTFILE` when I use `/tmp` is world writable. It is owned by `mysql`. No where did I say I was running MySQL as root. None of those things are my issue. Updating question to show grants. I didn't say "lea" was trying to write the file either. – nwalke May 20 '15 at 20:27
The user `mysql` is trying to write the file. Which is why I posted in my question about permissions that the `mysql` linux user has. – nwalke May 20 '15 at 20:29
1

My last update doesn't fit in a comment. Please see updated answer – Alex Tartan May 21 '15 at 10:03
1

Definitely was app armor. Thanks! – nwalke May 21 '15 at 15:04

score 6 · Answer 2 · answered Apr 04 '18 at 21:18

6

On modern systems mariaDB & mysql gets installed with systemd support. Among other things, it has this setting in /etc/systemd/system/mysql.service:

Prevent accessing /home, /root and /run/user

ProtectHome=true

That's what stops it from writing in /home.

answered Apr 04 '18 at 21:18

Tom Bille

430
3
5

That is not what prevented it in this case, as is shown by me already accepting an answer. You providing additional info for people dropping by from Google is fine, but stating this was my problem is incorrect. – nwalke Apr 05 '18 at 01:20
Can u throw some light on why these directories are protected? – Ayush Jun 12 '20 at 13:01

score -1 · Answer 3 · answered Jul 18 '19 at 06:40

-1

I found that changing the owner of the directory to the same user as the mysql daemon worked for me.

answered Jul 18 '19 at 06:40

Damien

1,140
15
22

SELECT INTO OUTFILE permission denied but the user can write to the directory

3 Answers3

Prevent accessing /home, /root and /run/user

Linked