How to configure database permissions for a Django app?

Question

I'm looking for links, or an answer here, on to how to properly configure the database permissions to secure a Django app? To be clear, I'm looking specifically for material dealing with grants on the database, not permissions within the Django framework itself.

Solution 1

From the django docs:

https://docs.djangoproject.com/en/dev/topics/install/

If you plan to use Django’s manage.py syncdb command to automatically create database tables for your models (after first installing Django and creating a project), you’ll need to ensure that Django has permission to create and alter tables in the database you’re using; if you plan to manually create the tables, you can simply grant Django SELECT, INSERT, UPDATE and DELETE permissions. On some databases, Django will need ALTER TABLE privileges during syncdb but won’t issue ALTER TABLE statements on a table once syncdb has created it. After creating a database user with these permissions, you’ll specify the details in your project’s settings file, see DATABASES for details.

Solution 2

I've just tested initial setup with MySQL. For python manage.py migrate at least you need following grants for simple operation (if yo use db-preparation):

1. CREATE, ALTER, INDEX
2. SELECT, UPDATE, INSERT, DELETE

And, by the way - security matters. You can reduce attack impact by limiting your system exposure. In this case - you can restrict 'DROP' - which is fairly huge plus. If you leave some tricky hole with ability to SQL-inject - you probably reduce the damage. I will research in the future if it will not do any harm to remove DELETE keyword - that would limit potential threats as well. Just because we all leave bugs from time to time :)

Solution 3

I usually:

grant all privileges on my_db.* to my_user@localhost identified by 'my_user_pass'
grant all privileges on test_my_db.* to my_user@localhost identified by 'my_user_pass'

I suppose if there were a bug in django, you might be opening your database up to terrible things, but you'd have other problems if there were that big of a security hole in django.

django minimally needs select, insert, update, and delete, to operate. If you're using test or syncdb at all, you'll also need to be able to create tables, and indexes (and maybe the file permission for loading sql fixtures).

So, for a mysql db, I'd guess the optimal set of permissions might be select, insert, update, delete, create, index, and file. If you wanted to get real nitty-gritty, you could selectively grant these permissions as appropriate on the table level (rather than the db level).

Personally, I find grant all ... easier to type.