On Fri, Feb 20, 2009 at 8:00 AM, Jim Starkey <jstar...@nimbusdb.com> wrote: > MARK CALLAGHAN wrote: >> >> Do web apps need access control? Stewart has a blog post with the >> statement that they don't and that access control is to be done at the >> app server. I don't support a web app. I have to deal with audits and >> all of the other fun stuff. But I suspect that at some point many web >> apps must also deal with audits if they store personal data which is >> subject to many rules in many countries. >> >> Has anyone here undergone an audit for 'web app' personal data? >> In such an audit has access control at the app server been sufficient? >> Are managers at such a company comfortable that anyone (such as a >> disgruntled employee) with access to the db can read all customer >> data? >> >> Note that once a web app gets big and needs to make some money there >> will be just as much internal access to the database as there is >> user-facing access and the internal access won't go through the web >> app. The internal access will be large queries used for monetization. >> And those queries are the ones that might not be allowed access to the >> user's personal data. >> >> > > Web applications absolutely need and benefit from server based access > control. > The SQL security model dates back to the time sharing era (oh, how little it > is missed) where tables were owned by individual users with rights granted > to other users. This was augmented with a restricted role model where > rights could be granted to roles (dba, sysadmin, peon, etc.) and where a > "user" logged onto to a database with a specific role. > > Little of this makes sense anymore. Almost all database access takes place > through application server which serve many classes of user, rendering the > concept of database "user" meaningless. Application server know after a > query or two either who their client is or at least was class the client > falls in. At the point, of course, the application server is already logged > on the database (or, more likely, picked an existing connection from a > pool), so changing the login role isn't an option.
App servers are older than SQL. Leaving all of this up to the many app servers that run against a big deployment scares me. And if many different apps run with the same credentials in the database because they reuse the same connections in the app server, then I lose any chance to monitor and rate limit by role/account. I doubt that almost all database access takes place through an app server and that isn't the case for my deployment. Perhaps others with MySQL deployments can chime in. The database will also be used for batch queries. I suppose those could be routed through an app server but that would be inefficient and add little. And as soon as the database contains personally identifiable information, then eventually there will be requirements to prevent anyone on the inside from viewing some of that data. > > What Stewart is really saying is that database systems can't manage security > or access in a three-tiered world, so why even try? > > Stewart is right in that the existing SQL security model or the even more > antiquated MySQL security model are next to useless for web applications. > But unless your brain is totally ossified, that isn't the right question. > The right question is, "What is an appropriate security model for web > applications." I am confused. Above you state that the SQL model and more limited MySQL implementation are next to useless, but then below you think that roles and optionally row-level security are sufficient to fix it. Roles are in the standard so the SQL model isn't next to useless. > The model I have used in the past and again in Nimbus is the "flexible role > model". The key elements are: > > * Rights are generally granted to roles, not accounts (users) > * Each account is assigned a set of roles. Some are, by default, > active and other are latent > * A client can use the API to activate or deactivate roles > * Access rights are determined by the active roles of a connection. This isn't that far from what MySQL provides. It doesn't do roles and thus it also doesn't allow the roles on a connection to be changed. The C API allows the credentials for a connection to be changed. The next Google patch has limited support for that (users can map to 1 role, user does authentication, role does access control). A real version of this would do more -- http://forge.mysql.com/worklog/task.php?id=988 > > Applications servers generally log on with a single account, perform some > query to authenticate the client, the adjust roles accordingly. Note that > his model works very well for connection pools as well. > > A database can go far beyond simple access control, however. Filtering > records by role is a particularly powerful feature that makes writing > robust, secure applications much easier. > > Where Brian, Stewart, and I will disagree is whether this should be a > database responsibility. I will argue that it should be for the following > reasons: > > 1. It is a necessary function common to all applications. > 2. It can be declaratory in the database rather than coded in the > application server. > 3. It reduces the complexity, therefor cost, of applications > 4. A single central implementation is much more robust than > distributing security code in a hundred or thousand places in an > application > 5. It is very hard for application programmer to screw it up. > > In the unlikely event anyone would like to hear more about filterset (used > for role based table filtering), just let me know. It's a wonderful > feature. > > -- > Jim Starkey > President, NimbusDB, Inc. > 978 526-1376 > > -- Mark Callaghan mdcal...@gmail.com _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : drizzle-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
