How can I have MySQL write outfiles as a different user?

Question

I'm working with a MySQL query that writes into an outfile. I run this query once every day or two and so I want to be able to remove the outfile without having to resort to su or sudo. The only way I can think of making that happen is to have the outfile written as owned by someone other than the mysql user. Is this possible?

Edit: I am not redirecting output to a file, I am using the INTO OUTFILE part of a select query to output to a file.

If it helps:

mysql --version
mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (x86_64) using readline 5.2

Flyspeck

Answer 1

The output file is created by the mysqld process, not by your client process. Therefore the output file must be owned by the uid and gid of the mysqld process.

You can avoid having to sudo to access the file if you access it from a process under a uid or gid that can access the file. In other words, if mysqld creates files owned by uid and gid "mysql"/"mysql", then add your own account to group "mysql". Then you should be able to access the file, provided the file's permission mode includes group access.

Edit:

You are deleting a file in /tmp, with a directory permission mode of rwxrwxrwt. The sticky bit ('t') means you can remove files only if your uid is the same as the owner of the file, regardless of permissions on the file or the directory.

If you save your output file in another directory that doesn't have the sticky bit set, you should be able to remove the file normally.

Read this excerpt from the man page for sticky(8):

STICKY DIRECTORIES

A directory whose `sticky bit' is set becomes an append-only directory, or, more accurately, a directory in which the deletion of files is restricted. A file in a sticky directory may only be removed or renamed by a user if the user has write permission for the directory and the user is the owner of the file, the owner of the directory, or the super-user. This feature is usefully applied to directories such as /tmp which must be publicly writable but should deny users the license to arbitrarily delete or rename each others' files.

Tinsmith

The file is owned at "mysql"/"mysql" and modded 666. I've added myself to the mysql group and I still can't delete it. – Flyspeck

What are the ownerships and permissions on the directory in which the file resides? – Tinsmith

I think what Bill is getting at is that you have to have write permission on the containing dir to be able to delete a file in UNIX (b/c it updates the dir listing). So "chgrp mysql" the dir and "chmod ug+rwX" it to give your mysql-group user write perms on the dir. – Macaronic

Yep, that was what I was getting at. – Tinsmith

I'm writing the file to /tmp. "drwxrwxrwt" root/root – Flyspeck

Sticky bit changes delete behavior in a directory. See my edited comment, or "man sticky" for details. – Tinsmith

Answer 2

Not using the "SELECT...INTO OUTFILE" syntax, no.

You need to run the query (ie client) as another user, and redirect the output. For example, edit your crontab to run the following command whenever you want:

mysql db_schema -e 'SELECT col,... FROM table' > /tmp/outfile.txt

That will create /tmp/outfile.txt as the user who's crontab you've added the command to.

Dorking

Note that piping will yield different results when compared to into outfile. For instance piping includes column names as first row, NULL instead of \N etc. (in Ubuntu. Details may vary in other OSes) – Alva

Answer 3

I just do

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

and add

 /var/www/codeigniter/assets/download/* w,

and

sudo service mysql restart

And that's it, I can do easily SELECT INTO OUTFILE any filename

Acquit

Answer 4

If you have another user run the query from cron, it will create the file as that user.

Shortlived