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:
In this case, Alice has read access to
The interesting case is when we look at the
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:
This allows you to easily query for permissions in SQL using a
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?).