Re: [sqlalchemy] Implementing fine-grained access control

[Michael Bayer]

On Nov 22, 2010, at 9:30 AM, Dan Ellis wrote:

 I'd like to find some way to implement access controls on mapped
 objects, with the following features:
 
 * Example: given a BlogPost object, only the owner, or a superuser,
 would be allowed to set fields such as title and body.
 * Example: reading the body field would check the privacy field as
 well as the current user, and only let the owner read a private field.
 * The owner should be determined based on a configurable column name,
 or as the result of a method call.
 * The current user should be explicitly specified rather than coming
 from some global state.
 
 The intention is not to make unwanted operations impossible, but to
 offer the programmer a degree of confidence that, so long as he uses
 the object in a particular way, the security constraints he specifies
 won't be violated, regardless of logic errors elsewhere (in a web
 layer, typically).
 
 It seems that one possible way to do this would be to use proxy
 objects to access the real instances. Returning proxies doesn't seem
 difficult (a mapper extension could do this if the mapped class
 specifies it desires it). Interaction with the session might be
 problematic, though, if all you have is proxy objects.
 
 Does this seem to be the correct path to follow, or is there a better
 approach?

I'm assuming the reason for proxy objects is so that usage would continue to 
look like:

blogpost.body = new body

instead of

blogpost.set_body(user, new body)

?

So for that kind of thing, if you want certain operations to proceed under the 
umbrella of some context, like who the current user is, Python context 
managers are very neat for this.

with security_manager.user(some_user):
blogpost.body = new body

You'd normally use @property on your BlogPost object to intercept read/set 
events, or the @validates decorator which catches only set events, to achieve 
this.   BlogPost could find the local context manager usually via thread local.

You could also use SessionExtension to disallowed changes to objects during 
flush() or commit(), again using some thread local context.

That would be my version.  If you want something a lot more elaborate and 
formalized, there's Zope security proxies: 
http://pypi.python.org/pypi/zope.security .SQLAlchemy mapped objects should 
be able to be used directly with security proxies, however it requires 
integration with the ORM, which we added a lot of hooks in order to help 
someone achieve this about two years ago.  I'm not sure if Zope has published 
an SQLA integration layer with ZCP, however.





 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Implementing fine-grained access control

[A.M.]

On Nov 22, 2010, at 9:30 AM, Dan Ellis wrote:

 I'd like to find some way to implement access controls on mapped
 objects, with the following features:
 
 * Example: given a BlogPost object, only the owner, or a superuser,
 would be allowed to set fields such as title and body.
 * Example: reading the body field would check the privacy field as
 well as the current user, and only let the owner read a private field.
 * The owner should be determined based on a configurable column name,
 or as the result of a method call.
 * The current user should be explicitly specified rather than coming
 from some global state.
 
 The intention is not to make unwanted operations impossible, but to
 offer the programmer a degree of confidence that, so long as he uses
 the object in a particular way, the security constraints he specifies
 won't be violated, regardless of logic errors elsewhere (in a web
 layer, typically).
 
 It seems that one possible way to do this would be to use proxy
 objects to access the real instances. Returning proxies doesn't seem
 difficult (a mapper extension could do this if the mapped class
 specifies it desires it). Interaction with the session might be
 problematic, though, if all you have is proxy objects.
 
 Does this seem to be the correct path to follow, or is there a better
 approach?

The approach I use is rather PostgreSQL-specific but works well for this 
scenario because security is controlled by the database instead of 
potentially-hairy python logic.

When a user first makes a request, I generate the SQLAlchemy models by 
reflecting all views and tables which the user (also mapped as a PostgreSQL 
user) can see (based on the search_path). Optionally-available base model 
classes include details for unreflectable attributes such as foreign keys for 
views. The generated model classes are then cached as modelname_role name.

By manipulating database ACLs and the search_path, it becomes trivial to push 
security and role changes.

This setup allows me to control all models via view manipulation- column- and 
row-level security as well as modifying data for specific roles while using 
bog-standard SQLAlchemy models.

Cheers,
M

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.