I have a generic design question that relates to enterprise applications but I think that software developers from other types of projects may have experience with this as well.

The scenario is following - you have a database and you are about to write an application that provides access to its data. The database also contains data about user accounts, their roles and authorization. The problem is that other processes are accessing the database as well and any one of these processes may change any data at any moment (e.g., delete a user).

In other projects I used to have exclusive access to the database and so I usually wrapped the database with a layer that exposed the related domain functionality in a SOA-style. This wrapper had a rich domain model and could utilize caching of the database data because it was aware of any potential change (by intercepting domain events, CQRS) that could modify it.

With a shared database I don't know how to approach the design. Imagine this situation:

  1. HTTP request arrives to you application and you are to authenticate this request.
  2. You query the database for the claimed user account and its login data. If the supplied credential matches then a session is established (e.g., in ASP.NET a secure cookie is sent to the client).
  3. But then a subsequent request from that user arrives. The problem is that instead of doing something useful at this moment, it seems like that one needs to check whether the associated user account still exists in the database. Any information can stop being valid at any point because some other process may alter the database.

This is of course a more generic problem. The data may not only change between requests but even between two separate database calls. The easy answer here is to use locks/transactions. But the truth is that this would require huge changes to the database schema. For example, every user account would require an additional column 'InUse' - but that is just a very simple scenario. I can imagine that completely new tables would need to be created. Also, the transactions would typically span across many HTTP requests and thus render the database useless for long periods of time for other users.

I have read the PoEAA book and I am well aware of various synchronization techniques. What I am looking for here is some generic technique or methodology how to achieve the so called "good-enough-design" that does not require changes to the database schema and can handle the mentioned situations where anything can change between two requests.

Right now I think I will go with the following approach - I will authenticate the user. Then I will not perform any subsequent checks to ensure that he was not deleted in the meanwhile. But when I perform some operation that relies on the existence of the user I will receive an exception and deduce that the user must have been deleted. The response will then be some kind of error message or error page.

I am sure this is a common problem that many of you must have encountered. I will appreciate any solution or even a reference to some book or paper. Thanks.

  • Even if you have one app and one database, in the enterprise (assuming this at least means a lot of users, processes, services etc.) an admin could make a security change while a user is going from one task to another. Esp. in a Windows shop, integrated security is going to be expected out of your app.
    – JeffO
    Mar 26, 2013 at 12:02
  • If the user has an open session and their account is disabled in the middle of it they still will be able to operate within the context of the session in pretty much any application unless they need to re-authenticate. If you attempt to query for a record and it's an exception that it does not exist, the query caller should interpret that as appropriate
    – Kevin
    Mar 27, 2013 at 0:41

1 Answer 1


If this is for an enterprise, I am surprised that you don't have a single sign on facility (or several) to use. That would be my suggestion. In my experience, it is not common to have applications sharing a database, even for authentication. The thing that gets me here is that it is another enterprise system reinventing the wheel with user authentication, another set of passwords to remember, etc.

Authentication is typically provided either by a web service call (SOAP), or using SSO, and sometimes using a message queue.

By having a separate database you are enforcing a separation of concerns, and you can keep your data to yourself. My suggestion would be if at all possible (which it's not always due to enterprise bureaucracy) to expose the user account management as a service.

If thats not possible and you do have to work with the constraint of the existing database, I would suggest your domain model includes the authentication tables.

In terms of dealing with data that has been changed, the easiest solution will probably to be maintain a session variable such as LoggedInDateTime that caches the value of DateTime.Now (assuming .NET and SQL Server, adjust as necessary), and have an update stamp on your user rows that have a trigger set to automatically update the stamp on an update. Then, for each request check whether the UpdateStamp has been updated since you logged in.

In terms of checking if the user is deleted, per request check that the user row still exists (if using physical deletion) otherwise check for a DeleteStamp or IsDeleted value.

  • Thanks, the timestamp solution seems reasonable and should get me going. Regarding the approach with factoring the membership tables to a standalone DB - it would be the same for, e.g., invoice tables (invoice data can also change between any two DB queries) plus I am constrained by bureaucracy so it isn't a way. I think that this problem could be named 'Multiple Apps - Single DB' and developers who have deployed web farm applications must have working solutions and patterns for this.
    – Tomas
    Mar 27, 2013 at 8:55
  • I feel for you working within the bureaucracy. It is never fun.
    – Sam
    Mar 27, 2013 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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