Обсуждение: permission in the db or in the application?

Hi all,

  I'm starting a project in which I will use PostgreSQL in which I need to
  check permissions at different levels (eg.: status of a record, hierarchy
  and so on). The application needs to run with a web interface (sigh!).

  At first I thought i'd like to put as much permission logic as possible in
  the database, and I was willing to evaluate veil for that.

  The reasons where mainly two:

  1. to be sure that those permission where observed independently from the
     way I was accessing the data. No way to create security 'holes'

  2. simplicity in the code

  Some days ago I read an e-mail of somebody that strongly opposed to using
  a db other than for ACID features.

  I'd like to hear from this list some thoughts on this subjects.

  thanks
  sandro
  *:-)



--
Sandro Dentella  *:-)
http://www.tksql.org                    TkSQL Home page - My GPL work

In response to Sandro Dentella <sandro@e-den.it>:

>
> Hi all,
>
>   I'm starting a project in which I will use PostgreSQL in which I need to
>   check permissions at different levels (eg.: status of a record, hierarchy
>   and so on). The application needs to run with a web interface (sigh!).
>
>   At first I thought i'd like to put as much permission logic as possible in
>   the database, and I was willing to evaluate veil for that.
>
>   The reasons where mainly two:
>
>   1. to be sure that those permission where observed independently from the
>      way I was accessing the data. No way to create security 'holes'
>
>   2. simplicity in the code
>
>   Some days ago I read an e-mail of somebody that strongly opposed to using
>   a db other than for ACID features.
>
>   I'd like to hear from this list some thoughts on this subjects.

My big argument is that database applications have a way of outgrowing
their original requirements.

If you write your application well, it's very likely that someone will want
a desktop app version, and others will want direct access to the tables
and so forth and so on.

If you put all the security in the database itself, you can do these other
tools rather quickly, and not duplicate the security code.  In addition,
you can simply say, "Hire a .NET programmer to write that, we don't have
to worry about him implementing the security properly."  If you have
clients that have some knowledge of SQL, you can give them direct access
to the db with impunity, which is a _fantastic_ thing to be able to do.
Even the ones who will want to use MS Access will show you interesting
stuff that you could be doing with the data.  In other words, you application
will become more valuable.  This is the origin of the term "scale".

Otherwise, _every_ time you mess with the application you risk breaking the
security.  And every time you need to implement and interface in a different
form, you have to recreate that security code.  Yuck.

--
Bill Moran
Collaborative Fusion Inc.

Hi Sandro.

I think there is no silver bullet here (as in many other fields, too).

You could end with a mix of different approaches (simple checks done
on the database, while complex one are left in the application logic).
Also, most probably you will end moving that logic in the lifetime of
the application.

If you have time (and money) take a look at Chapter 20 from "Agile
Database Techniques" from Scott Ambler: it examines a number of
possibilities which could be worthy to you.

Regards
Marco
--
Marco Bizzarri
http://iliveinpisa.blogspot.com/

Bill makes several valid points, but in spite of them, the app I'm
writing has almost no logic in the database.
Why? Well, mostly because it would be too much trouble to remove all
of it. :) No, seriously...

Lack of a good language. Postgresql understands a growing number of
languages and that's great, but most business applications (the
dominant type of application when discussing security issues) are
written in an object oriented language like Java or C# (please don't
be offended if I've missed anyone's favourite over-10%-of-the-market
OO language).
pljava is becoming an alternative, but it's still kind of early to
tell if VM-executed languages behave well in a database environment.
It's hard to wri...maintain, maintain anything non-trivial written in
plpgsql: I face that exact problem right now with a couple of fairly
complex import scripts.

Lack of a good IDE. I've yet to hear how I can debug a plpgsql
script/function. If it's just a matter of ignorance, someone please
enlighten me.

Code version control. Yes, you can dump a pgsql database schema
without the actual data and store it in a repository somewhere, but a)
you basically version-control a single large file and b) at least some
other manistream RDBMSes (MSSQL, for one) can't make that kind of dump
(easily), so the potential user base is much reduced and best
(workable?) patterns and practices haven't been established.


On the other hand, writing application logic (which includes security)
in the application isn't at all that bad. Modular design really helps
there: containing the logic in a package/library/module/whatever goes
a long way to allow trivial or very easy reuse in a second or third
app.
And if what you want is a truly heterogeneous application ecosystem
built around the same application logic, you can always go the SOA and
WS way: "hey, you want to code a GUI in RoR? Here you go..." At that
point, you can basically extend your system which ever way you want.


...or at least so I've heard during my knitting class. :)
t.n.a.

On Dec 18, 1:10 pm, san...@e-den.it (Sandro Dentella) wrote:
> Hi all,
>
>   I'm starting a project in which I will use PostgreSQL in which I need to
>   check permissions at different levels (eg.: status of a record, hierarchy
>   and so on). The application needs to run with a web interface (sigh!).
>
>   At first I thought i'd like to put as much permission logic as possible in
>   the database, and I was willing to evaluate veil for that.
>
>   The reasons where mainly two:
>
>   1. to be sure that those permission where observed independently from the
>      way I was accessing the data. No way to create security 'holes'
>
>   2. simplicity in the code
>
>   Some days ago I read an e-mail of somebody that strongly opposed to using
>   a db other than for ACID features.
>
>   I'd like to hear from this list some thoughts on this subjects.

I've recently faced a similar problem for an application with a web
interface as the primary GUI and decided to stick all security in the
PostgresSQL layer. My decision was based on the fact that the
application interface to my data is really not nearly as important as
the consistency and the security of my data.[1]

There's the problem that many web application frameworks always connect
as the same user, but this doesn't have to be a problem thanks to "set
session authorization" and "set role". For Rails, I've written a very
simple plugin[2] which, for every request, does a "set session
authorization" to a less privileged user and, if a user tries to login,
checks a user's password against the pg_catalog and does a "set session
authorization" if the supplied password is correct. That's how easy it
can be to have reliable, deep authentication in a web application.

But, pick the right tool for the right job. Your requirements probably
aren't the same as mine. ;-)

[1] Of course, you could consider web services enough of an interface,
but I think I like to have ODBC access to my DB. Also I now have
well-performing LDAP auto-completion in our email clients. For things
like an LDAP access layer performance has proven quite critical and I
doubt if I could have done this without sticking as much logic in the
DB layer as possible.

[2] http://rubyforge.org/projects/rails-psql-auth/