[Zope-DB] Zope - SQL authorisation model
terryk at adroit.net
Tue May 31 05:02:02 EDT 2005
I am building a site in zope that interfaces an SQL database
(postgresql) for all its data, including users, which are stored in a
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
Do people have any related experiences they can share?
More information about the Zope-DB