Martin Radosta - Record level security based on SQL
Written on Thu, Dec 4th 2008, 13:26 by Mark Story
Martin's presentation was based around a behavior that he wrote to provide access control using SQL. In designing a solution, martin came up with a few criteria. The solution must be generic, it only requires 4 fields on any table that will be using the behavior. It should perform quickly and not create a lot of extra queries. The solution he searched for also needed to provide a few features. It should provide permissions for read write and delete. Permissions are assigned by role, with users having many roles. This system is similar to ACL but different, in that it stores the permissions for each record.
Martin's behavior uses a permission system similar to the unix file system, with a owner, group, world access. This was implemented as series of bit masks. Unlike the unix filesystems, these permissions are summed and stored as one field. The 4 fields mentioned earlier are user_id, role_id, group_id, and permissions. These four fields allow the behavior to work, both the roles and groups also use binary values to reduce the number of columns. The binary values for roles and groups are compared to those in the role and group id for records . In addition the requested permission is combined with group and role values and checked against the permissions field. Since binary values are used, roles can be combined and will always be unique.
Permissions in a system like this permissions are done via a bitmask system. User values for group, and role are compared to record permissions. If the value of the bitmask meets the expectation, the record is returned.
In addition to a controller, an element, action in Appcontroller and a model are used. Martin gave a quick demo of an application using his behavior. The permissions checks are all done in the SQL of the behavior. Another interesting part of the behavior is that in the behavior's
afterFind() extra values are added to indicate whether or not a user can write or delete. This allows for your interface to display the correct icons. Which is a nice added bonus.
I personally was really impressed with how his system was designed and how it worked. He demonstrated how his fine grained access control group. He even had a root user that was not bound by the permissions system. The permissionYou can find this project at Sourceforge the project is licensed under the MIT license.