[Zope] Relational database & user permissions (select, add, update, delete)

[Dieter Maurer]

Jon Erickson writes:
 > I've run into a problem while developing a multi-user database
 > application using Postgresql and Zope with ZSQL methods.  I need
 > user\group based security for the database similar to...
 > 
 > User A can select from table FOO but cannot update, delete, or add
 > records to FOO.
 > 
 > User B can select, update and delete from FOO but cannot add records to
 > FOO.
I see two choices:

  *  you do it in the database.

     Then you need a database that supports multiple users on a single
     connection. Not too many support that...

     I read, Oracle > 8.1 does and SQLRelay (<http://www.firstworks.com>)
     can use it. Nevertheless, you need to extend the SQLRelay DA
     (i.e. the Zope integration to put the user info into the
     SQLRelay "connect").

  *  you do it in Zope

     Calling a Z SQL Method is controlled by the "Call database methods"
     permission. You can map this permission differently for any
     individual SQL Method you like. I.e. map it for method 1
     in such a way that user A can call it while user B cannot,
     and the other way round for method 2.

     Grouping Z SQL Methods in an appropriate folder structure
     may help you to manage the permissions mapping more effectively:

     E.g. one folder only contains "deletes". The folders
     "Call database methods" mapping is inherited to it
     content (unless overridden). Thus, you can control
     the permission for all deleting SQL methods.



Dieter