Under the privileges that can be assigned to a user, I can't find whether to allow or disallow a user from being able to dump a database.

I want to disallow a user from taking backup of a database.


If a user has read access to the database he will be able to dump the database one way or the other - if he can read it, he can choose to save it somewhere as well.

  • from security view, that is not a very good solution, is it? Isn't there any solution at all? – Johan Larsson Jun 22 '12 at 10:58
  • 3
    @Johan Why would a user be allowed to view every single record but not dump the db? They amount to the same thing, i.e. permission to read the contents of the db. – Kenny Rasschaert Jun 22 '12 at 11:01
  • I have a very specific situation where I want to make it more difficult for copying data from the database, True, viewing is still a similar risk. It's more time consuming to browse within a 1GB database and view the records one by one than dumping it within seconds. – Johan Larsson Jun 22 '12 at 11:14
  • Database dump is a whole database in a file. To create such dump you need to 1. read the contents of the database, 2. save it to a file. If user has access to the database he can read it (step 1, check) and can do whatever he wants with the data he just read, outside MySQL server, like for example save it - even copy&paste into notepad or use one of many db backup tools (step 2, check). To sum up: if you have access to database you can't be stopped from saving the data. It's just impossible. – c2h5oh Jun 22 '12 at 11:16
  • Ok, Can't we set a limit on the dump file size? My DB is almost 1GB in size, if we limit it to lets say 10mb, than it will be useless to use the dump command. – Johan Larsson Jun 22 '12 at 11:18

According the MySQL Documentation on mysqldump:

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

You could remove SELECT, LOCK TABLES and SHOW VIEW privileges, but that's about it. Of course, taking away SELECT kind of defeats the purpose of wanting to connect to a database.

You may want to consider tweeking the OS privileges of users in terms of where they can/cannot write their data.

  • Even if you tweak the users permissions to deny writing the data on the server he can still redirect the output outside of the box, so basically I think if he/she has the SELECT permissions you can't do much :) – golja Jul 2 '12 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.