I am trying to import a table detail into CSV using the 'OUTFILE' command (Linux box). My actual command is as below:
SELECT 'id','progname','contact' UNION ALL SELECT id,progname,contact INTO OUTFILE '/tmp/contact_1.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM dbo.contactdetails
When doing so, I got the below error:
ERROR 1227 (42000): Access denied; you need (at least one of) the FILE privilege(s) for this operation
The 'tmp' folder is wide open with 777. My current username is "prod". I logged into mysql shell using this user and tried to create another super user and still for 'Access denied' permission.
When tried as root user, I am unable to login to the mysql shell at all. I see
access denied for user 'root'@'10.0.65.152' error
Honestly, only the user "prod" has some rights and I am unable to provide file permissions to this user as well. Any help on how this can be fixed?
UPDATE: I forgot to mention that fact that my backend is a remote RDS instance. The user Prod has access to that RDS instance and root user does not have access to that instance.