13

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?

Joe
  • 7,749
  • 19
  • 60
  • 110
  • Are you sure, that the password is correct? For SELECT INTO OUTFILE there is just one privelege necessary: SELECT_PRIV – Martin Rothenberger Mar 08 '12 at 12:09
  • The password is correct because I tried with exactly the same code but after give it DBA role and it worked fine. – Joe Mar 08 '12 at 17:20
  • I found out that Workbench doesn't allow to backup permissions to GRANT FILE. Which is the command to do it for an user? http://dev.mysql.com/doc/refman/5.0/es/grant.html – Joe Mar 09 '12 at 13:34

1 Answers1

20

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'@'%';
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • 1
    And the reason for needing these privileges is that the files are created on the **server** if you are running the command against a remote DB server. Beware of killing hosts by filling up disk space. – Alex Ciminian Oct 30 '13 at 18:28
  • This did not work for me, nor did changing AppArmor nor did running as root. Only creating /tmp/mysql-dump/ then chown -R 777 /tmp/mysql-dump/ and running mysqldump into that folder. – eb80 Sep 04 '18 at 18:19