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 )