SELECT INTO OUTFILE权限被拒绝，但用户可以写入目录 [英] SELECT INTO OUTFILE permission denied but the user can write to the directory

问题描述

我在尝试使用SELECT INTO OUTFILE和使用/tmp以外的目录时遇到问题.

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

我的Linux用户名为datam，我的MySQL用户是lea，而MySQL以mysql的身份运行.

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

当datam运行mysql -u lea database并尝试使用路径/home/datam/xfers/online/file.csv执行SELECT INTO OUTFILE时，我得到错误代码13，权限被拒绝.使用/tmp/file.csv可以正常工作，因此我相当有信心，这与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.

我已将mysql添加到datam组中，并通过以下方式进行了验证:

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)

我递归将/home/datam/设置为775.

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

如果我执行sudo -u mysql /bin/bash并转到/home/datam/xfers/online/并执行touch file，它将写入文件.

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

我该怎么做才能允许mysql从SELECT INTO OUTFILE写入文件?

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

我认为这不是围绕该主题的其他问题的重复，因为我已经查看了它们并遵循了它们的所有说明(在通往我想要的目录的所有目录上设置execute，将GRANT FILE ON设置为ON，等等) ).

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用户lea授予:

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'                                    |
+-----------------------------------------------------------------------------------------------------------+

推荐答案

这可能是由mysql用户权限引起的.

This may be caused by mysql user permissions.

如此处所述 https://dba.stackexchange.com/questions/17029/无法将mysql数据输出到文件

要赋予自己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

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

Linux用户可以编写文件.但是mysql服务可能会被apparmor阻止.

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

检查此文件:/etc/apparmor.d/usr.sbin.mysqld.

在此处添加您的项目文件夹:

Add your project folder there:

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

最后，做一个

sudo /etc/init.d/apparmor reload

这篇关于SELECT INTO OUTFILE权限被拒绝，但用户可以写入目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！