[linux-help] Re: Mysql permissions

[Tom Hull <thull2@xxxxxxx>]

bruce bales wrote:
> Jeff Vian wrote:
> 
> 
>>From the documentation for MySQL I found the following
>>
>>   "The |FILE| privilege gives you permission to read and write files
>>   on the server using the |LOAD DATA INFILE| and |SELECT ... INTO
>>   OUTFILE| statements. Any user to whom this privilege is granted can
>>   read or write any file that the MySQL server can read or write."
>>
>>If I am reading this correectly, the MySQL server must have permission 
>>to read/write the file you are trying to write to.  Thus the outfile 
>>would need to be in a location where the server has permissions to 
>>write.  It would appear the file written is not dependent upon the users 
>>permissions (other than the granted privleges) but rather on the servers 
>>permissions.
>>
>> 
>>
> 
> If I start mysql with  "mysql -u root -p"  I can select to the outfile 
> email.dat.
> If I start mysql with  "mysql -u bruce -p"  I cannot.  
> Doesn't mysql always use the same server?

It uses the same server, which is normally a process mysqld running
as user mysql, group mysql. If you do a select into a file, the file
will be created with owner/group mysql, and this will fail if the
pathname is to a directory that does not allow user/group mysql to
create new files. If the server doesn't have permission to do this
you'll get an error like:

   ERROR 1: Can't create/write to file '/...' (Errcode: 13)

This error has nothing to do with an internal permissions error which
might occur when you don't have GRANT FILE permission for the user@hostname
specified when running mysql. The way I read the doc GRANT ALL includes
GRANT FILE, but I've seen some evidence that it doesn't work that way,
and given that the files being created will have mysql/mysql ownership
and be world r/w (0666) there are some obvious security issues with
such grants.

Another way to do this would be to redirect the output of mysql to whatever
file you want to capture the data. You might, for instance:

   $ echo 'select * from foo;' | mysql >foo.select

Add whatever mysql options you need. I don't know whether it's possible
to change the default formatting options (fields separated by tab, rows
by newline), but you can pipe the output to awk and easily hack it from
there.

Another way to dump a table or a whole database would be to use the
mysqldump program. This produces a file which can be redirected into
mysql to restore the table/database.

> While googling on this, I found the following several places:
> 
> SELECT ... INTO OUTFILE is mainly intended to let you very quickly dump 
> a table on
> the server machine.
> 
> which makes me think there must be another (preferred) way to select 
> data to a file.  I'm still looking for this.  Data on the screen is not 
> as useful as data in a file.  I'm very new to databases.
> bruce

-- 
/*
  *  Tom Hull * thull2(cox.net) * http://www.tomhull.com/
  */

-- This is the linux-help@xxxxxxxxx list.  To unsubscribe,
visit http://www.complete.org/cgi-bin/listargate-aclug.cgi