http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Thanks 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 :-)http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment26http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment26Fri, 20 Jul 2012 19:56:05 GMTSony, DPAPIhttp://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment25http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment25Fri, 20 Jul 2012 19:42:02 GMTChallenge continues - where does the system get the encryption key? from a password protected keystore?http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment24http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment24Fri, 20 Jul 2012 19:34:34 GMTSony, encrypted app.config sectionhttp://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment23http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment23Fri, 20 Jul 2012 19:31:48 GMTThen the challenge becomes how do store the System user's password securely for the system jobs to use?http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment22http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment22Fri, 20 Jul 2012 19:18:13 GMTMy 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?http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment21http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment21Fri, 20 Jul 2012 19:02:28 GMTSony, I am not sure that I follow. You mean, how do I store the credentials? In app.config in an encrypted section, usually.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment20http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment20Fri, 20 Jul 2012 18:55:10 GMTHow 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.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment19http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment19Fri, 20 Jul 2012 18:27:48 GMTJustin, Now do this in code, with the proper switching between users, and see what you get.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment18http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment18Fri, 20 Jul 2012 05:21:57 GMTPostgreSQL using Veil: select connect_person(author_token); insert into Articles ... select connect_person(system_token); 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.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment17http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment17Fri, 20 Jul 2012 01:16:41 GMTJustin, 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.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment16http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment16Thu, 19 Jul 2012 22:16:42 GMT@Dalibor +1 'advanced' design topics ... LOL ... ayende is a toolhttp://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment15http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment15Thu, 19 Jul 2012 19:40:16 GMTRelational 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. http://veil.projects.postgresql.org/curdocs/index.html http://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvpoli.htmhttp://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment14http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment14Thu, 19 Jul 2012 18:08:43 GMTCONNECTION POOLING! 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.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment13http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment13Wed, 18 Jul 2012 21:52:38 GMTI 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.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment12http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment12Wed, 18 Jul 2012 21:10:28 GMTYes, 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?http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment11http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment11Wed, 18 Jul 2012 14:17:29 GMTHmm, 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.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment10http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment10Wed, 18 Jul 2012 13:37:33 GMTDalibor, Yes, exactly my pointhttp://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment9http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment9Wed, 18 Jul 2012 13:29:05 GMTDirk, 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 explainhttp://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment8http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment8Wed, 18 Jul 2012 13:28:46 GMTAbsolutely. 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.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment7http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment7Wed, 18 Jul 2012 12:49:31 GMTAyende 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).http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment6http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment6Wed, 18 Jul 2012 11:29:25 GMTI 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.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment5http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment5Wed, 18 Jul 2012 10:25:50 GMTJudging 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?http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment4http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment4Wed, 18 Jul 2012 09:41:20 GMTShouldn'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?http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment3http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment3Wed, 18 Jul 2012 09:37:18 GMTKarep, 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 connectionshttp://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment2http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment2Wed, 18 Jul 2012 09:33:03 GMTAltough 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.http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment1http://ayende.com/157089/system-vs-user-task-security-who-pays-the-sports-writer#comment1Wed, 18 Jul 2012 09:29:41 GMT