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.
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.
There are many ways as there are data structures:
Variables
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
users
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.
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
