On May 19, 2011, at 1:16 PM, Matthias wrote: > Hello, > > I want to secure my entities on an object-by-object basis. I.e. something > like row level permissions. > > So I found http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery > which seems to proof that something like this is possible with sqlalchemy. > > On the other hand it seems there's row level permissions available directly > in some databases. E.g. Oracle already has it and PostgresQL seems to gain it > in the upcoming 9.2. > > My ultimate goal is to run arbitrary user-supplied adhoc queries. Of course > these queries should only return data which the user actually has access to. > > Which approach do you think does make more sense? Using the features directly > available in some databases or using a prefiltered query via sqlalchemy?
Couldn't find anything about how "row level security" might work in PG - other than the obvious, which is to use triggers, that is a write only type of thing. Oracle appeared to have something that limits read access as well - and of course it seems very complex (http://www.dba-oracle.com/concepts/restricting_access.htm). Its not clear here if you are interested in silent omission of rows from read operations, or errors issued upon write operations. The typical way to lock down a table and only allow a subset of rows to be read is to create a view, and remove all permissions from the tables themselves. I'd go with that approach if it works since its the most simple. Using a built in PG feature 9.2 if it exists would be a good way to go as well. It depends on what "secure my entities" really means. If i say query.get(5), and i don't have access to "5", the "silent omission" idea means I'd get None back. Seems like "secure" would be more like, raise an error. If I really wanted just individual entities to say, "you're not allowed to read or change me!" upon access, but I don't need "real" security, I'd probably implement that as a __getattribute__()/__setattr__() type of thing. Or maybe attempt to use Zope security proxies or something like that. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
