Which are the proper privileges to mysqldump for the Error Access denied when executing 'SELECT INTO OUTFILE'.?

Question

backup user has BackupAdmin role and ALL privileges (object rights, DDL, GRANT).

cmd> mysqldump --routines=TRUE --tab=C:\tmp -h localhost -u backup -pbackup schemalocal
cmd> mysqldump: Got error: 1045: Access denied for user 'backup'@'%' (using password: YES) when executing 'SELECT INTO OUTFILE'

The only way to make it work is to give DBA role to backup account, but that is a security risk so I want to give it only the neccesary rights. Which are them?

Answer 1

You need the FILE privilege in order to be allowed to use SELECT...INTO OUTFILE, which seems to be what mysqldump --tab uses to generate the tab-separated dump.

This privilege is global, which means it may only be granted "ON *.*" :

GRANT FILE ON *.* TO 'backup'@'%';