In a system where users have multiple permissions that may overlap (i.e. "Write" might not include "Read" etc.) what is the best way to keep them in the database? Both in terms of security and "readability" (when I want to know if someone has a certain permission)
The standard method is a Discretionary Access Control List (DACL). Such a structure maps entities (e.g. users, user groups, etc.) to resources (e.g. files, mutexes, sockets, etc.) with a defined set of permission attributes placed on each link.
For example, the following represents a simple read/write DACL:
Resource | Entity | R | W | ----------------+----------+---+---+ /foo/bar | Alice | 1 | 0 | /foo/bar | Bob | 1 | 1 | /bar/foo | Eve | 1 | 1 | /log | Everyone | 0 | 1 | /log | Alice | 1 | 1 |
In this case, Alice has read access to
/foo/bar and Bob has read/write access to
/foo/bar, whereas Eve has no access to
/foo/bar because she has no entry in the DACL table. Only Eve has access to
The interesting case is when we look at the
/log resource, which the Everyone group has write-only access to. Since Alice is in the Everyone group, we would imagine that she shouldn't need an entry, but her entry actually overrides the group setting. At this point, we've build a hierarchical DACL, which allows us to inherit permissions and provide specific overriding behaviours lower in the hierarchy.
Such a model provides extremely flexible permissions and can be implemented in a variety of different data storage systems, e.g. filesystem, RDBMS, key-value storage engine, etc.
Of course, the example above doesn't directly translate to a relational database table, because of the ambiguity between user groups and users. This can be resolved by using a security identifier (SID), which uniquely identifies any entity. I'd probably implement it something like this:
TABLE resource_dacl resource_id INTEGER NOT_NULL security_id INTEGER NULL perm_read TINYINT(1) DEFAULT 0 // permission to read the resource perm_write TINYINT(1) DEFAULT 0 // permission to write to the resource perm_delete TINYINT(1) DEFAULT 0 // permission to delete the resource perm_acl TINYINT(1) DEFAULT 0 // permission to change the resource's ACL ... PRIMARY_KEY ( resource_id, security_id )
security_id should be unique for each entity, and would be stored within the user and user group tables.
This allows you to easily query for permissions in SQL using a
WHERE EXISTS, resulting in a single value from the database, containing a 1 or 0 to signify whether the required permission(s) are set.
One simple way would be to have a table where each row has the subject (e.g., Alice), the resource (e.g., some file), and the list of permissions that this subject has to this resource (e.g., read, write, execute).
The representation of this data is not terribly important to security. More important are issues like: the granularity of permissions; the granularity of subjects (are they users? apps?); the granularity of resources; how the policy can be changed (can subjects delegate access they have to other subjects, and if so, are there any constraints or restrictions?).