MYSQL into outfile access denied - but my user has ALL access and the folder is CHMOD 777

Question

Any ideas    SELECT   INTO OUTFILE   home myacnt docs mysqlCSVtest csv  FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY     LINES TERMINATED BY   n  FROM tbl property  WHERE managerGroupID     managerGroupID    Error   Access denied for user  asdfsdf   localhost   using password  YES

User · Answer

Since cP WHM took away the ability to modify User privileges as root in PHPMyAdmin  you have to use the command line to   mysql gt   GRANT FILE ON     TO  user   localhost     Step 2 is to allow that user to dump a file in a specific folder  There are a few ways to do this but I ended up putting a folder in     home user tmp db   and   chown mysql mysql  home user tmp db   That allows the mysql user to write the file  As previous posters have said  you can use the MySQL temp folder too  I don t suppose it really matters but you definitely don t want to make it 0777 permission  world-writeable  unless you want the world to see your data  There is a potential problem if you want to rinse-repeat the process as INTO OUTFILE won t work if the file exists  If your files are owned by a different user then just trying to unlink  file  won t work  If you re like me  paranoid about 0777  then you can set your target directory using   chmod  dir 0777    just prior to doing the SQL command  then  chmod  dir 0755    immediately after  followed by unlink file  to delete the file  This keeps it all running under your web user and no need to invoke the mysql user

User · Answer

For future readers  one easy way is as follows if they wish to export in bulk using bash    akshay ideapad  tmp  mysql -u someuser -p test -e  select   from offices  Enter password    ------------ --------------- ------------------ -------------------------- -------------- ------------ ----------- ------------ -----------    officeCode   city            phone              addressLine1               addressLine2   state        country     postalCode   territory    ------------ --------------- ------------------ -------------------------- -------------- ------------ ----------- ------------ -----------    1            San Francisco    1 650 219 4782    100 Market Street          Suite 300      CA           USA         94080        NA            2            Boston           1 215 837 0825    1550 Court Place           Suite 102      MA           USA         02107        NA            3            NYC              1 212 555 3000    523 East 53rd Street       apt  5A        NY           USA         10022        NA            4            Paris            33 14 723 4404    43 Rue Jouffroy D abbans   NULL           NULL         France      75017        EMEA          5            Tokyo            81 33 224 5000    4-1 Kioicho                NULL           Chiyoda-Ku   Japan       102-8578     Japan         6            Sydney           61 2 9264 2451    5-11 Wentworth Avenue      Floor  2       NULL         Australia   NSW 2010     APAC          7            London           44 20 7877 2041   25 Old Broad Street        Level 7        NULL         UK          EC2N 1HN     EMEA         ------------ --------------- ------------------ -------------------------- -------------- ------------ ----------- ------------ -----------     If you re exporting by non-root user then set permission like below  root ideapad  tmp  mysql -u root -p MariaDB  none   gt  UPDATE mysql user SET File priv    Y  WHERE user  someuser  AND host  localhost     Restart or Reload mysqld  akshay ideapad  tmp  sudo su root ideapad  tmp   systemctl restart mariadb   Sample code snippet  akshay ideapad  tmp  cat test sh     usr bin env bash  user  someuser  password  password  database  test   mysql -u  user  -p  password    database   lt  lt EOF SELECT    INTO OUTFILE   tmp csvs offices csv   FIELDS TERMINATED BY      ENCLOSED BY      LINES TERMINATED BY   n  FROM offices  EOF    Execute  akshay ideapad  tmp  mkdir -p  tmp csvs akshay ideapad  tmp  chmod  x test sh akshay ideapad  tmp    test sh  akshay ideapad  tmp  cat  tmp csvs offices csv   1   San Francisco    1 650 219 4782   100 Market Street   Suite 300   CA   USA   94080   NA   2   Boston    1 215 837 0825   1550 Court Place   Suite 102   MA   USA   02107   NA   3   NYC    1 212 555 3000   523 East 53rd Street   apt  5A   NY   USA   10022   NA   4   Paris    33 14 723 4404   43 Rue Jouffroy D abbans   N  N  France   75017   EMEA   5   Tokyo    81 33 224 5000   4-1 Kioicho   N  Chiyoda-Ku   Japan   102-8578   Japan   6   Sydney    61 2 9264 2451   5-11 Wentworth Avenue   Floor  2   N  Australia   NSW 2010   APAC   7   London    44 20 7877 2041   25 Old Broad Street   Level 7   N  UK   EC2N 1HN   EMEA

User · Answer

Try executing this SQL command    gt  grant all privileges    on YOUR DATABASE      to  asdfsdf   localhost     identified by  your password    gt  flush privileges     It seems that you are having issues with connecting to the database and not writing to the folder you   re mentioning   Also  make sure you have granted FILE to user  asdfsdf   localhost     gt  GRANT FILE ON     TO  asdfsdf   localhost

User · Answer

As  fijaaron says    GRANT ALL does not imply GRANT FILE GRANT FILE only works with       So do  GRANT FILE ON     TO user

User · Answer

Honestly I didnt bother to deal with the grants and this worked even without the privileges   echo  select   from employee    mysql --host HOST --port PORT --user UserName --password Password DATABASE SCHEMA  gt  output txt

User · Answer

I tried all the solutions but it still wasn t sufficient  After some more digging I eventually found I had also to set the  file priv  flag  and restart mysql   To resume    Grant the privileges     gt  GRANT ALL PRIVILEGES   ON my database      to  my user   localhost     gt  GRANT FILE ON     TO my user    gt  FLUSH PRIVILEGES     Set the flag     gt  UPDATE mysql user SET File priv    Y  WHERE user  my user  AND host  localhost     Finally restart the mysql server     sudo service mysql restart   After that  I could write into the secure file priv directory  For me it was  var lib mysql-files   but you can check it with the following command     gt  SHOW VARIABLES LIKE  secure file priv

[mysql] MYSQL into outfile "access denied" - but my user has "ALL" access.. and the folder is CHMOD 777

Examples related to mysql

Examples related to mysql-error-1045

Examples related to into-outfile