[Zope-DB] Zope - SQL authorisation model

[Terry Kerr]

Hi,

I am building a site in zope that interfaces an SQL database 
(postgresql) for all its data, including users, which are stored in a 
"Persons" table.

I am not to sure on the best development model to use for 
authorisation.  Authentication is relatively easy and can be implemented 
using an out of the box product such as exUserFolder, or something I 
hack up myself.

However, I don't think Zopes internal security mechanism is really 
suitable for defining permissions on the sites interaction with the 
database...unless somebody can prove me wrong!

For example, the person who is authenticated to the site (will be 
authenticated via my user folder looking at the credentials in the 
person record in the person table), is only allowed to update records in 
a specific table that they own, as determined by a foreign key link to 
the person record.  The only way I can see to implement security is to 
explicity code in my python form validation script, a check that makes 
sure the person is infact allowed to edit the record...this in itself 
would require a database query to check the foreign key link against the 
authenticated user id. 

My authorization gets more complicated than that though.  Some records 
can only be updated if the person has a specific level of access, that 
is defined by another record with a foreign key link in an 
"access_level" table.  Given that my site will have hundreds of such 
actions, its a cumbersome task to implement security in this way. I 
would also need to implement the security for the form itself, rather 
than the user only getting an 'unauthorised' error after submitting the 
form...this would involve similar coding in each form page template!

Another approach maybe to implement the authorization at the database 
level by using GRANT, REVOKE, rules on tables, functions, views, etc.  
If the Zope database connector could connect as the authenticated user, 
then the rules would apply.  Then all I would to do is handle the 
postgres unauthorized error gracefully in my application.  However, as 
far as I can tell, all Zope database adapters, well the Postgres ones 
anyway, maintain a pool of connections to the database, and each is 
connected with the same database user.  So this approach doesn't seem to 
be possible.  Not to mension the performance hit I assume exists of 
having to open a new database connection with each page request.  I 
notice a previous discussion about this issue in this list at 
http://gossamer-threads.com/lists/zope/db/48032?search_string=authorisation;#48032

Do people have any related experiences they can share?


terry