[Zope] SQL security at row level

[Jim Sanford]

We provide access tour database by outside distributors who are only allowed
to see "their" records while our company users can see all records. Every
data record has an org_id column.

Every call to data is wrapped in a sql call to a user database that uses the
authenticated_user's id to retreive their org_id and other security
privileges (access to specific fields, as well as, type of html controls
that show up - some data thay can view but not change while our company
users can change them.)

The update and insert queries have wrappers to so even if the no privileged
users monkey with the form before submitting there chages won't get saved.

Jim sanord

----- Original Message -----
From: "Hung Jung Lu" <hungjunglu@hotmail.com>
To: <zope@zope.org>
Sent: Monday, June 19, 2000 7:28 PM
Subject: [Zope] SQL security at row level


Hi,

This is a general question.

One can protect the ZSQL methods by employing user folders. This offers
table-level protection.

But what about protecting database information at row level? (This can come
from hackers tweaking form variables.)

Currently I store some security information (like the owner of row, among
other things) on all rows on all tables. When retrieving records from tables
(in particular, for "select" and "update" methods), I'd like to verify that
the current user indeed has the privilege for accessing a particular row. I
know at least two ways for checking this information. (Notice that we may
have complicated situations where not only the owner can have access... we
may have group privileges, superuser user privileges, etc. Also, I have a
few hundreds ZSQL statements, so it's not a small question.)

(1) Pre-generate the allowed owner ids, so "select" statements can only
select rows with given owner ids... I could put something like: (keeping in
mind superusers, team-members, supervisers, etc.)

  where <dtml-sqltest owner_id column=owner_id type=int>
  or <dtml-sqltest additional_id1 column=owner_id type=int>
  or <dtml-sqltest additional_id2 column=owner_id type=int>
  .....

(2) Use a wrapper for ZSQL methods, and raise an exception when unauthorized
rows are retrieved.

I guess row-level protection is a very common issue, so I would like to know
what other strategies are used. I would appreciate description of actual
implementations.

regards,

Hung Jung

________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com


_______________________________________________
Zope maillist  -  Zope@zope.org
http://lists.zope.org/mailman/listinfo/zope
**   No cross posts or HTML encoding!  **
(Related lists -
 http://lists.zope.org/mailman/listinfo/zope-announce
 http://lists.zope.org/mailman/listinfo/zope-dev )