What is the accepted way of keeping track of a user's permissions in a database? Say I have a web app where users can pay for privileges. To use a straightforward analogy, imagine Github's users (paying members can have private repos, etc.) One way I see of doing it is keeping track of user type in the database:

account_type: String

The other way is to keep an array of permissions:

permissions: [String]

Which is preferred?


If money is involved, and who knows what the marketing team will dream up in the future, go with permissions and roles.

A permission is a single action in the system, like creating a new private repo. A role is a collection of permissions. Users can be assigned any number of roles. Put a start date and end date so you can grant users access to roles for a certain amount of time.

| improve this answer | |
  • ACL – Laiv Nov 20 '16 at 22:18

I've seen it done in a variety of ways. I believe that if it's a simple toggle between "free account" and "paid account" a single 0/1 integer field or enum will do, whereas if there are a number of different permissions, you'd probably want to use a bitstring where each individual bit represents a particular permission or account setting.

| improve this answer | |
  • What you're describing is a primitive 'role based' setup - the role is "paid" or "free", and each role provides various permissions (eg. "can create private repo", "can view billing information" etc). Rather than limiting to just two roles and two sets of possible permissions, I'd personally have a table of permissions, a table of role-to-permissions which give a role a list of permissions and then finally, a user table which says which role the user is in. That may be more complex than a simple two state app needs though. – Ralph Bolton Nov 3 '17 at 12:46

There are many ways as there are data structures:


can_edit = true
is_admin = true

Key-value pairs in a key-value pair table or key-value store like mongo

can_edit : true
is_admin : true

A hash of key value-pairs, e.g. ruby

can_edit => true
is_admin => true

A database table for 'users' with various true/false fields

can_edit BOOLEAN
is_admin BOOLEAN

Database tables for users and roles & then another database table to act as the join table for permissions meaning that it will have columns for user_id and role_id in it

mysql> describe users;
| Field    | Type      | Null | Key | Default | Extra          |
| id       | int(11)   | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11)   | NO   |     | NULL    |                |
| role_id  | int(11)   | NO   |     | NULL    |                |

In the above example can_edit and is_admin would be roles stored in the roles table, one row for each and each role row having a unique id that is then referenced in the above permissions table along with the user_id for the user who has that role.

| improve this answer | |

The array logic you mention is an imperative language solution, not quite appropriate for storing on a database.

Relational databases use set logic, which is somewhat similar to array concepts but not the same. You think on sets / tables which are like extensible arrays.

The most usual database design for your problem (with many different variants) would include 3 tables, product of normalization of the data requirements:

a) Users, basic fields like id_user and login_user plus any fields you require (e.g. first_name, last_name)

Example data: id_user login_user 1 admin 2 db2791 3 bruno

b) Permissions, with basic fields like id_permission and name_permission Example data:

id_permission name_permission 1 Create Repo 2 Commit in Repo

c) Users - Permissions relationship table, with basic fields the foreign keys id_user and id_permission; then flags if you have different levels for the permissions, e.g. can_read, can_write, which may instead be independent permissions

Example data: id_user id_permission 1 1 1 2 2 2

The lack of data in the relationship imply no permission. You may want to add a grant/deny flag to allow explicit permission removal.

Another common pattern that extends this one, is to add a Resources table (or Securables), changing relationship to user - permission - resource

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.