Per-User Database Authentication in Django

A colleague was recently talking about the need for a data entry interface to a database, and I glibly said "let me see how easy it is to put a Django-admin front-end on it!".  It turned out to be some-what less easy than expected; this post documents my solution to one of the less-easy aspects of it (there were others, but those were mostly to do with concerns of scale and better-documented issues surrounding legacy databases).  It is still quite an ugly solution, and please see the end for some fairly important caveats.  I'm documenting it here though because I did find other people with the same problem, but have yet to come across a solution. The problem is this: Django assumes that in general it will control the database, including creating the schema and implementing any logic.  This particular database made the opposite assumption; front-ends were merely conduits, and any verification etc would be handled internally.  In this case that wasn't much more complicated than updating fields such as "added by" via triggers, but the principles apply across a range of scenarios.  Here's a random example of the Django way. So, to play nicely with the database we'd ideally like each connection to be made using the credentials of the user using the application, and Django really isn't built for this.  One reason is that django.db.connection is a module-constant initialised with the database settings from settings.py, but if you are willing to swallow your pride we can also exploit this to our advantage. The first step is write an authentication back-end so the user will be authenticated using their database credentials: (I also changed the base admin template so it doesn't offer the "change password" option).  This is fairly standard; I'm using Oracle, but obviously any database can be made to work similarly.  Note that we still need the default database account in settings.py, and this will be used to create the new user in the django.contrib.auth tables for example. Step two is, err, to save the password in the session (I told you I felt dirty), so we can create a database connection using it each request.  This needs to happen in the login method, where you have access to both the session and the username/password. Step three is to convert django.db.connection to use these credentials instead of the defaults from settings.py; I did this using a custom middleware: At this point we're almost done, but a problem still remains: I'm using Oracle where each user has their own private schema, and the application is expecting to find a bunch of tables which don't exist in the user's schema.  We could get around this using synonyms for example, but instead I used a signal that is fired each time a connection is created (there seems to be precious little information or examples about this signal either) in order to change the schema the connection is using: This needs to be executed each time, so I put it in models.py. So, all in all, a fairly minimal solution, but…Here be Dragons!  As I mentioned, there are some pretty important caveats to be aware of if you ever use this (I accept no responsibility for any breakage, break-ins, yadda yadda yadda).  Most importantly, you are storing the user's password in plain-text for the duration of the session.  For our purposes this is fine; it's an internal application where security isn't exactly critical.  Your mileage may vary, significantly. Secondly and perhaps more importantly, the various other Django applications still need access to their tables, but now you are connecting as a different user, so each user using the application will also need permission on these tables.  This is the bit that turns my stomach the most! Lastly, there may well be consequences, race conditions, etc in manipulating django.db.connection that I have not come across yet.Please let me know if there is an easier/cleaner way!  (I still feel dirty)