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)

  • 3
    I assume you're not just asking about data structures, right? (Because if so, it would probably be better on Stack Overflow or even Database Administrators). This could be a great question, but it should include managability aspects, access control model, granularity requirements, etc.
    – AviD
    Nov 6, 2012 at 7:34
  • What type of database are you using? Also, this is more of an SO question.
    – rook
    Nov 6, 2012 at 8:18
  • @AviD, you're right. Data structures are also part of it, but the question includes, for instance, if in terms of security a dictionary is better than a row of bits. I'm assuming different methods do exist.
    – JNF
    Nov 6, 2012 at 9:54
  • Dictionary / row of bits is the data structure, and this is largely orthogonal to the security model - as long as the data retains its integrity (and is stored securely, but that's irrelevant to here...)
    – AviD
    Nov 6, 2012 at 10:42

2 Answers 2


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.

  • why did you assume this was a sql database?
    – rook
    Nov 7, 2012 at 5:38
  • @Rook Intuition. Regardless, the structure can be trivially translated into flat-files or NoSQL.
    – Polynomial
    Nov 7, 2012 at 6:53

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

  • 1
    You certainly don't want 3 columns in a relational table if you want to easily query privileges. You want 2+n, where n is your number of defined privileges.
    – Polynomial
    Nov 6, 2012 at 8:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .