0

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.

2 Answers 2

4

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.

8
  • from security view, that is not a very good solution, is it? Isn't there any solution at all? Jun 22, 2012 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. Jun 22, 2012 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. Jun 22, 2012 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, 2012 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. Jun 22, 2012 at 11:18
2

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.

1
  • 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, 2012 at 10:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .