[Zope] SQL security at row level

[Hung Jung Lu]

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