CRUD Permissions for Users in a tree structure

Question

I currently have a tree structure containing data elements; on each of these, it is possible to perform basic CRUD operations. From here, I need to implement per-user permissions for each of these four actions. So a given user could be given Create and Read, but no Update or Delete permission. These permissions would then cascade down the tree to any children of the permitted object; therefore, that given user would have Create and Read for any child objects of the root object.

What's the best way of storing these permissions using an SQL (MySQL with PHP specifically) database? Currently I'm thinking the ideal solution may be to create another database table which tracks a User ID, an object ID, and then a list of booleans tracking each possible permission, and then checking the user ID and object ID against the permission table and traveling up the tree until a permission object is found (or not found, as the case may be).

My main issue with this is twofold. Firstly, it makes it impossible to give permission to one object, but not its children. Secondly, it seems like it might cause a performance hit on particularly deep objects. So, what seems like a good way of going about this?

Answer 1

Recursive data structures are often hard to "map" to SQL queries. Some databases have special support for it (Oracle, for example) but MySQL has no built-in support (= you can work around that but it's clumsy).

We needed something similar in our application. Our solution was to store normalized data (i.e. "user X has permission Y on node Z" -> three columns with FK relations) in a simple table.

A DAO/manager object reads this table and builds a cache where it can quickly look up permissions as we need them.

To summarize: Keep the database simple and write special helper code in your application to transform the database into the structure which you need.