Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,026 | Comments: 44,842

filter by tags archive

System vs. User task security: Who pays the sports writer?

time to read 3 min | 455 words

Let us assume for a moment that we are building a system for a sports site. We have multiple authors, submitting articles, and we pay each author for those articles.

The data model might look like this:


In this post, I want to talk about the security implications of such a system. Typically, this gets translated to requirements such as:

  • Authors can edit their articles.
  • Authors cannot modify / view any payments.

Which very often gets boiled down to something like this:


What do you think of such a system? My approach, this is a horrible mess altogether. Think what it means for something like this:

public ActionResult SubmitArticle(Article article)
        return View();


    var payment = GetOrCreatePaymentFor(article.Author);


    return RedirectToAction("index");

In order to run, this code would actually have to run under several different security credentials in order to work successfully.

That is before we take into account how using multiple users for different operations would result in total chaos for small things like connection pooling.

In real world systems, the security can’t really operate based on the physical structure of the data in the data store. It is far too complex to manage. Instead, we implement security by separating the notion of the System performing tasks (such as adding a payment for an article) that are system tasks, and the System performing tasks on behalf of  the user.

The security rules are implemented in the system, and the application user have no physical manifestations (such as being DB users) in the system at all.

And to the commentators, I know there are going to be some of you are going to claim that physical security at the database level is super critical, but while you are doing that, please also answer the problems of connection pooling and the complexities of multiple security contexts required for most real world business operations.



Altough I don't create database roles I don't see how that would be a problem with connection pooling. There would just be more then one pool of connections. That's all. I don't think that's expensive.

Ayende Rahien

Karep, You would have a connection pool per user in the app. If you have many users, that means: a) Effectively no connection pooling. b) A lot of hanging connections


Shouldn't the add payment section be re-factored into an add payment method that would be run by another user (admin maybe) after they had validated that the author needed paying?

Dalibor Carapic

Judging from the method signature "ActionResult SubmitArticle(Article article)" it seems that you are running MVC under IIS. Are you building an intranet site which has user integrated security and impersonation so that each request is run under browser-user identity? Wouldn't it be easier to have the website running under one account which have necessary privileges and handle authentication through web forms and some custom authorization?

Staffan Eketorp

I completely agree. It is a mess. And I believe it's the result of a view of the DB is "the place where everything happens".

Claerly you have a point that sometimes you do things "as a user" and sometimes "as the system" and sometimes they're intertweened in one session. But there are other issues too.

Even in an Intranet environment like you suggest Dalibor, I'd argue that it's a fallacy to believe that user rights can be modeled well in the DB. Maybe I only should have access to my articles? Maybe I only should have access to my articles and "public" articles? Maybe I should only be able to update articles that haven't expired? The rights system is often so much more complicated than the very rough one you get from a DB. And - it's easy to forget/bypass it. What happens when I introduce a cache layer? Where are my DB rights then?

To me this blog entry is simply a good example of a single responsibility principle violation.


Ayende there should be one server login (connection string) for one database role. Not different login for every different user! So you have only a few different connection string so everything is still ok (if we are talking about connection pooling).

Gene Hughson

Absolutely. Nothing worse than a system where the database is aware of individual users on the network. Just because I want to give user X the ability to add/change information via my app does not mean I want them to be able to do so outside my app. Not to mention that the security model of most apps I've worked on is entirely different than SQL's.

Staffan got it perfectly...SRP applies at the macro level as well.

Ayende Rahien

Dirk, For the purpose of discussion, I intentionally specified things this way. There are plenty of cases where something similar is required, and this is relatively easy to explain

Ayende Rahien

Dalibor, Yes, exactly my point

Dalibor Carapic

Hmm, Perhaps I am used to somewhat more 'advanced' design topics on your blog but I do not see why would somebody implement authorization/authentication in such a way when (probably) everybody (and their mother) would implement it in the way I said. I will keep waiting for the 'hook' of the blog post.

Daniel Lang

Yes, that's how it is supposed to be. I thought this was so clear that everybody with a few month of programming experienced would have got this right anyway... no?

Gerke Geurts

I would first have a look at what would happen if no computers were involved, for example: 1) author submits article to site owner. 2) Site owner evaluates article and on acceptance issues payment request to accountant. 3) Accountant evaluates payment request and on acceptance updates the books.

The SubmitArticle action attempts to perform all these actions in one go, causing the security headache as described.

From a security perspective it might be better to split the work in this action into 3 separate actions that are performed by 3 separate roles. The work to be performed under the credentials of the site owner and accountant could for example be implemented as service and triggered by one way messages. And each service would be responsible for authentication and authorisation of the user who initiated the message.

The result is then three application pools, one for the web site (representing authors), one for the site owner service and one for the accounting service.

Adam Langley


Absolutely Oren. 100% behind you on this one. This is exactly why I never reply on user-credentials for accessing a database from a server infrastructure.


Relational databases such as Oracle and PostgreSQL have had table, column and ROW level security that works with connection pooling for some time. MS SQL is pretty far behind in this area if that's what you are using as a comparision here.




@Dalibor +1

'advanced' design topics ... LOL ... ayende is a tool

Ayende Rahien

Justin, Feel free to show me how you would write the same code above using what API that supports this with different users without going really crazy with the details.


PostgreSQL using Veil:

select connectperson(authortoken);

insert into Articles ...

select connectperson(systemtoken);

insert into Payments...

Of course such a trivial example is the typical candidate for a function with SECURITY DEFINER. Heck you could just do a stored procedure in MSSQL and just grant execute access to the procedure and no access to the tables like everyone has been doing for decades if we are just talking about the little bushiness logic example.

Something like Veil gets useful when you need to support ad-hoc querying/dynamic SQL while enforcing security.

Ayende Rahien

Justin, Now do this in code, with the proper switching between users, and see what you get.

Sony Mathew

How do you "securely" define a System user (who technically has all permissions)? This is a challenge I'm facing currently. One approach is to define it as any other user in your LDAP and give it all privileges - but this means its password must be kept secure in the deployed codebase.

Ayende Rahien

Sony, I am not sure that I follow. You mean, how do I store the credentials? In app.config in an encrypted section, usually.

Sony Mathew

My users are defined in LDAP. Assuming all services/tasks are protected by permissions, a System user would need to exist with the required permissions for a task if it were to be performed by the System (e.g. a timer based batch job). I also have the challenge these system threads accessing distributed service which are also protected by permissions. Should I define my System user in LDAP along with my other users with all the permissions it requires? or is there a better way?

Sony Mathew

Then the challenge becomes how do store the System user's password securely for the system jobs to use?

Ayende Rahien

Sony, encrypted app.config section

Sony Mathew

Challenge continues - where does the system get the encryption key? from a password protected keystore?

Sony Mathew

Thanks much - that is useful. DPAPI still requires a password which it grabs from the current user which can work. Now i need to find something similar from Java :-)

Comment preview

Comments have been closed on this topic.


No future posts left, oh my!


  1. Technical observations from my wife (3):
    13 Nov 2015 - Production issues
  2. Production postmortem (13):
    13 Nov 2015 - The case of the “it is slow on that machine (only)”
  3. Speaking (5):
    09 Nov 2015 - Community talk in Kiev, Ukraine–What does it take to be a good developer
  4. Find the bug (5):
    11 Sep 2015 - The concurrent memory buster
  5. Buffer allocation strategies (3):
    09 Sep 2015 - Bad usage patterns
View all series


Main feed Feed Stats
Comments feed   Comments Feed Stats