How should permissions be stored?

Question

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)

Answer 1

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 /bar/foo.

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 )

The 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 JOIN with WHERE EXISTS, resulting in a single value from the database, containing a 1 or 0 to signify whether the required permission(s) are set.

Answer 2

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