How do I model (GitHub-like) permissions relationally?

Question

tl;dr: how do i implement a permissions model like (e.g.) github's

Updated to try to address some of @philipxy's comments:

I am planning to implement a permissions model similar to github's:

1. users
2. users can be in groups
3. users can be in organizations
4. groups can be in organizations
5. a user will be permitted any of C, R, U, and D operations on an asset, group, or organization as:
   1. an individual user who has been permitted those (any of C, R, U, D) operations
   2. a member of a group which has been granted those permissions
   3. a member of an organization that has been granted those permissions
      1. or as a member of a group where that group belongs to an org that has permissions
6. a user is granted Read because the asset/group/org is viewable (readable) to anonymous users ("public")
7. a user should also have a set of permissions to say whether they can do any of C, R, U, or D on the permissions (a user can create a permission[C,R,U,D] for another user, a group, or an org)
   1. a user can set the permissions for any asset, group, or org they create, or any asset, group, or org for which they have been given permission to set permissions.

These permissions will control who can perform Create, Read, Update, and Delete (CRUD) actions on assets, groups, and organizations in the site.

Approximately how do I model this?

Obviously i have these models:

1. Asset
2. User
3. Group
4. Organization

What next?

1. Permission?
2. PermissionType (to capture C/R/U/D)?

I am using mysql from node (via sequelize), but I can figure out specific syntax an all that myself, I just haven't yet figured out how to do this conceptually.

More to @philipxy's point:

The very thing you are proposing I do more of is indeed the thing I think I'm asking for help with. That is, those info design methods (NIAM, FCO-IM, ORM2, IDEF1X) are what i'm looking for. I know a decent amount about relational db implementation (days of learning normalizing and normal forms and whatnot), but indeed the process of specifying business requirements and converting them into actionable specs is the challenge.

• ORM2 is difficult to find because of name collisions with the nodejs module. : I have downloaded the book linked from the NIAM wikipedia page
• NIAM seems to be less common in usage nowadays?
• FCO-IM: I have downloaded the book from their website
• IDEF1X: also looks interesting

I guess I'm going to pick up a database text book.

More work toward predicates:

1. U identifies a User
2. A identifies an Asset
3. G identifies a Group
4. a User U can be in 0 or more Groups G
5. O identifies an Organization
6. a User U can be in 0 or more Organizations O
7. a Group G can be in 0 or more Organizations O
8. an asset A can be created by a User U
9. CRUD on Assets:
   1. an Entity E can be permitted (through Permission P ?) to perform actions Ac on Assets
   2. those Actions are:
      1. Create
      2. Read
      3. Update
      4. Delete
   3. the Entity may be of types:
      1. User
      2. Group
      3. Organization
      4. Anonymous User/"the public"
   4. details (shown only for Read, but also relevant for Create, Update, and Delete):
      1. a User U0 can permit another User U1 to Read an Asset A
      2. a User U0 can permit Users U who are members of Group G to Read an Asset A
      3. a User U0 can permit Users U who are members of Organization O to Read an Asset A
      4. Users U in Group G1, where G1 is a Group that is in an Organization O that has been permitted to Read Asset A, are therefore permitted Read Asset A
10. a Permission P that references an Asset A may only be created by certain users:
    1. By default, the User U who is the creator of an Entity can create Permissions for that Entity,
    2. but they may only reference Assets to which they have Permission (in the base case: those Assets create by U)
    3. a User who as been Grant(?)ed the privilege can also reference Entity E in a Permission
    4. Gr identifies a Grant
       1. a Grant gives an Entity the privilege to create, read, update, or delete Permissions that reference another Entity
       2. like Permissions, Grants have a transitive nature in that:
          1. if Organization O has been Granted the privilege to (e.g.) modify Permissions for Entity E, then
          2. not only may Users who are members of O modify Permissions referencing E,
             1. but also Users who are members of any Group G where G is in O have the privilege to modify Permissions referencing E

Answer 1

Predicates and tables

A proposition is a statement that is true or false of a business situation. A predicate is a column-parameterized statement that given a row gives a proposition. A table (base or query result) holds the rows that make a true proposition from its predicate.

user (with id) U has name N
R is a grantor (may grant permissions)

user U has permission to update asset A
grantor R gave permission to grantor E to use an operator of type 'CRUD'
grantor E is of type 'user' AND grantor E has permission to update assets

Business rules

A business rule is an always-true statement that defines a term or describes a policy or process.

A user is uniquely identified by an id assigned when their cheque clears.  
A crudable is an asset, group or organization.  
A grantor is a user, group, organization.
"Grantee" refers to a grantor receiving or holding a permission.    
Users can be in organizations.  

You can make true statements that are parameterless predicates. These can use parameter names that are bound by FOR ALL & FOR SOME (THERE EXISTS). Business rules phrased in terms of such propositional predicates and/or table names are database constraints. Given User(U,N) & Grantor(R) as shorthands for the first two predicates above as predicates for tables User & Grantor, the following lines all say the same thing:

A user is a grantor.
FOR ALL U, if U is a user then U is a grantor.

FOR ALL U, (FOR SOME N, User(U, N)) IMPLIES Grantor(U).
(SELECT U FROM User) ⊆ (SELECT R AS U FROM Grantor).

FOR ALL U & N, User(U, N) IMPLIES Grantor(U).
FOR ALL U & N, (U, N) IN User IMPLIES (U) IN Grantor.

FOREIGN KEY User (U) REFERENCES Grantor (R); states what the above do (note its similarity to the middle two) plus that R is UNIQUE NOT NULL in Grantor.

Don't confuse rules with predicates. They have different uses & usually different forms. (A parameterless sentence template can be used as either.) A rule is a true statement; a predicate is a parameterized statement. Look at how my answer separates them. Base tables and query result tables have predicates. But a rule may suggest that you need a base predicate/table to record something. We have base predicates/tables when we see from a rule that we have to record some statements about the current situation. Note some rules inspire no base predicates.

You probably want to reify types and permissions.

A user is a grantor of type 'user'.
Permission named 'C' is permission for a grantee to create a crudable.

Grantor E is of type 'user'.
Permission P is of type 'CRUD'.
Grantor R gave permission P of type 'CRUD' on crudable C to grantee E.

Design is finding necessary & sufficient rules & base predicates

Here are relevant predicates to record situations that your exposition suggests arise.

1. users

U identifies a user

2. users can be in groups

G identifies a group
user U is in group G

3. users can be in organizations

O identifies an organization
user U is in organization O

4. groups can be in organizations

group G is in organization O

5. a user will be permitted CRUD operations on an asset, group, or organization

A identifies a crudable of type 'asset'
user U is permitted CRUD operations on crudable C

5.1 as an individual user, or as a member of a group, or as a member of an organization (or as a member of a group where that group belongs to an org that has permissions),

P identifies a permission
organization O is permitted CRUD operations on crudable C

or because the asset/group/org is viewable (readable) to anonymous users ("public")

crudable C is public

6. a user should also have a set of permissions to say whether they can set the above permissions

grantor R has permission to set CRUD permission for users on crudable C --?  

What are "the above permissions"? Maybe you mean user CRUD permission and organization CRUD permission? Maybe you mean there are individual permissions for operations Create, Read, etc? You need to be clearer.

What are the permissions in "a set of permissions"? By "permission" here do you instead really mean "particular permission to a particular grantee"? You need to be more clearer.

The way to be clearer is to give rules & predicates that are as simple as possible but also not so simple that they don't mention relevant entities/values. You may afterwards want to generalize multiple rules & predicates into single ones. Eg instead of dealing with users, groups, organizations and assets, have grantors and crudables: Grantors may grant permissions. & grantor R gives permission P to grantee E. If some such permissions are also associated with specific grantees you might also need predicates like grantor R gives permission P to grantee E re permission Q and grantee F.

6.1. a user can set the permissions for any asset, group, or org they create,

user U created crudable C

or any asset, group, or org for which they have been given permission to set permissions.

user U has permission to set permission P for crudable C --?  

You will want to record things like that user U has name N and ....

Learn about database design

Search re database/SQL subtyping/inheritance/polymorphism idioms. Eg user, group and organization are types of permission possessors & holders; I made them subtypes of a type grantor. Maybe you want some kind of permission target type that is the union of crudable & grantor. Maybe you want types of permissions. Maybe some permission permissions have associated grantees. Maybe 'C', 'R', 'U' & 'D' are permissions, and 'CRUD' is a type of permission. You probably want to record what grantor gave what permission to a grantee.

Later we can replace tables by their join if the join is on a shared PK/UNIQUE with the same set of values in both. When we can join on a PK/UNIQUE & FK we can replace tables by one like their join but with the FK nullable. There are yet other times we can replace multiple tables by one without problems. But first identify basic predicates.

Learn about relational database design. Follow some information design method. Best are members of the the NIAM/FCO-IM/ORM2 family. Peek at IDEF1X. Don't rely on products.

Learn about constraints. They follow from predicates and business rules. They are truths about possible business situations in terms of the predicates. Equivalently, they are truths about possible database states in terms of the tables. Also learn about constraints in SQL, both declarative (PK, UNIQUE, FK) & triggered.