Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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'                                    |
+-----------------------------------------------------------------------------------------------------------+
like image 245
nwalke Avatar asked May 20 '15 19:05

nwalke


People also ask

Where does MySQL store Outfile?

C:\ProgramData\MySQL\MySQL Server 5.6\data\name.csv Show activity on this post. Show activity on this post. If you don't specify an absoulte path but use something like INTO OUTFILE 'output.

How do I grant a file privilege in MySQL?

The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server.

What is process privilege in MySQL?

The PROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions). Thread information available using the SHOW PROCESSLIST statement, the mysqladmin processlist command, the INFORMATION_SCHEMA.


1 Answers

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:

  1. service mysql restart --skip-networking --skip-grant-tables
  2. mysql <hit enter>
  3. UPDATE mysql.user SET File_priv = 'Y' WHERE user='lea' AND host='localhost';
  4. exit
  5. 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
like image 59
Alex Tartan Avatar answered Oct 27 '22 22:10

Alex Tartan