On Fri, Feb 20, 2009 at 8:00 AM, Jim Starkey <[email protected]> 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
