Keep Your Database Permissions Small

Update: March 15, 2016
There were several interesting bits of legitimate criticism on this post on Reddit.  The idea of the Principle of Least Privilege that this post communicates is right.  Some of the implementation details in the examples fell a little short though.  A thousand thanks to all who commented.  I learned a lot from this exchange.  The original post below........
 

A typical web application needs an account to access a database in order to do it's job.  These applications often face the outside world.  As such, direct access to your database is only a few degrees of separation from your users.  How does one secure this?  Start with minimalism.

Make A Minimal Database Account

Start by setting up a database account.  When doing this, err to too few permissions rather than too many.  It is inconvenient in the short term.  However, over the life of your project, you'll be glad you did.  Things will be more secure and easier to maintain.

An example.  There is a feature in one of my projects where the user can delete learning resources.  Here is a piece of Ansible configuration that sets up the MySQL database account.



Notice that the account does not offer any delete permissions at all.  So how is deletion possible?

Coding Within Constraints

The trick is this.  When the user "deletes" a learning resource, they don't directly delete anything at all.  Instead, they put in a request to delete it.  This much only requires insert privileges.  Here is an example using Python and SQLAlchemy.



As far as the user is concerned, the resource is "deleted" right away.  This is thanks to a subquery used to help with the lookup.

Doing Real Deletions Safely

To be sure, that can take up a good bit of space over time.  A real deletion of data is ultimately called for.  Here's a snippet from a script that does that job.



Not that this script does use a database account with delete permissions.  It is also a task run through a cron scheduler on an Ubuntu Linux server.  This is important because it is not invoked by the main web application at all.  The system is more secure because actual deletion is treated as an internal detail that exists outside of public access.  Below is one way to set up a cron scheduled job to run every other hour.



The takeaway is this.  A good security mindset can lead to solutions that provide features to user while also keeping data safer.  So the next time there comes a situation where you think you need more permissions, stop and reconsider.  You might be able to get by with less than you think.