树结构中用户的CRUD权限 [英] CRUD Permissions for Users in a tree structure

问题描述

我目前有一个包含数据元素的树结构；在每一个上，都可以执行基本的CRUD操作.从这里开始，我需要为这四个操作中的每一个实现每个用户的权限.因此，可以为给定的用户提供创建"和读取"权限，但是没有更新"或删除"权限.然后，这些权限将把树向下层叠到允许对象的任何子级.因此，该给定的用户将具有针对根对象的所有子对象的Create和Read.

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.

使用SQL(专用于MySQL和PHP)数据库存储这些权限的最佳方法是什么?当前，我在想理想的解决方案可能是创建另一个数据库表，该数据库表跟踪一个用户ID，一个对象ID，然后是一个跟踪每个可能的权限的布尔值列表，然后对照该权限表检查该用户ID和对象ID，并在树上向上行驶，直到找到(或未找到)许可对象为止.

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?

推荐答案

递归数据结构通常很难映射"到SQL查询.某些数据库对此有特殊支持(例如，Oracle)，但

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).

我们的应用程序中需要类似的内容.我们的解决方案是将规范化数据(即用户X在节点Z上具有权限Y"->具有FK关系的三列)存储在一个简单表中.

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.

DAO/管理器对象读取此表并建立缓存，在缓存中可以根据需要快速查找权限.

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.

这篇关于树结构中用户的CRUD权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！