Relational database & user permissions (select, add, update, delete)
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. ... but most Database Adapters use a single, static user login for database connections. The single user login provides for easy db connection configuration but doesn't help when you want to use a databases built-in security. I don't really want to program this logic in python when Postgresql already has said features. Does anyone have experience implementing user-level database security in a Zope app? If so, could you reply with some suggestions or examples please. Thanks, Jon Erickson
Jon Erickson wrote:
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... .... ... but most Database Adapters use a single, static user login for database connections.
To solve this you can develop a DA that creates a new db connection. But this is not a working solution. Firlst of all: A Zope DA creates a backend process when connecting to PostGreSQL. The number of backends at the same time is limited (default value is 32) Second: Connecting to the db is a very expensive operation.
I don't really want to program this logic in python when Postgresql already has said features.
Does anyone have experience implementing user-level database security in a Zope app? If so, could you reply with some suggestions or examples please.
Have a look at ZDatabaseTool Demo at: http://www.easyleading.org/demo/ It gives you a RowLevelSecurity tab in the management screen. At this time a new ZopeDA for Postgres called ZEasyPGDA is also in work. Using this DA with ZDatabaseTool you can upload any file an store it on PostgreSQL as BLOB ! Unfortunately the tool and the DA are not officially released. But I'll send a beta package to all of you who ask for it. Please let me know what you think about it. -- _______________________________________________________________________ Andreas Heckel andreas@easyleading.org LINUX is like a wigwam...no gates...no windows and an apache inside ;-)
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
participants (3)
-
Andreas Heckel -
Dieter Maurer -
Jon Erickson