How can I model (github-like) permissions relationally?

Question

tl; dr: how do I implement a permission model, for example (e.g. github's

Updated to try referring to some @philipxy comments:

I plan to implement a permission model similar to github:

• of users
• users can be groups.
• users can be in the organization
• groups may be in organizations
• the user will be allowed any operation C, R, U and D for an asset, group or organization as:
  • individual user to whom those were allowed (any operations C, R, U, D)
  • member of the group who has been granted these permissions
  • member of the organization who has been granted these permissions
    • or as a member of a group in which this group belongs to an organization that has permissions
• Read is provided to the user because the asset / group / organization is available for viewing (anonymous users) ("public").
• the user must also have a set of permissions to say whether they can allow any of C, R, U, or D in the permissions (the user can create permission [C, R, U, D] for another user, group, or organization)
  • the user can set permissions for any asset, group or organization that they create, or any asset, group or organization for which they have been granted permission to set permissions.

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

Roughly how can I simulate this?

Obviously, I have these models:

• Asset
• User
• Group
• Organization

What's next?

• Resolution?
• PermissionType (for capturing C / R / U / D)?

I use mysql from node (via sequelize), but I can determine the specific syntax and all this myself, I just have not figured out how to do this conceptually.

More for the @philipxy point:

What you offer, I do more, this is what I think I'm asking for help. That is, these information development methods (NIAM, FCO-IM, ORM2, IDEF1X) are what I am looking for. I know a decent amount about implementing relational db (days of normalization training and normal forms and much more), but really the process of defining business requirements and converting them into suitable specifications is a problem.

• ORM2 is hard to find due to name conflicts with the nodejs module. : I downloaded a book related to the NIAM wikipedia page
• Is NIAM currently less common to use?
• FCO-IM: I downloaded the book from my website.
• IDEF1X: also looks interesting

I guess I'm going to pick up a textbook on a database.

Work on predicates:

• U identifies User
• A identifies Asset
• G identifies Group
• a User U may be in 0 or more Groups G
• O identifies Organization
• a User U may be in 0 or more Organizations O
• a Group G may be in 0 or more Organizations O
• asset A can be created by User U
• CRUD on Assets :
  • a Entity E can be allowed (via Permission P ?) to perform Ac actions on Assets
  • those Actions :
    • Create
    • Read
    • Update
    • Delete
  • Entity can be of the type:
    • User
    • Group
    • Organization
    • Anonymous User / "public"
  • Details (shown only for Read , but also relevant for Create , Update and Delete ):
    • a User U0 may allow other User U1 to Read a Asset A
    • a User U0 can allow Users U , which are members of Group G to Read a Asset A
    • a User U0 can allow Users U , which are members of Organization O - Read a Asset A
    • Users U in Group G1 , where G1 is a Group , which is located in Organization O , which is allowed Read Asset A , therefore Read Asset A allowed
• a Permission P , which refers to Asset A , can only be created by specific users:
  • By default, User U , the creator of Entity , can create Permissions for this Entity .
  • but they can only refer to Assets with which they have Permission (in the base case: those Assets create U )
  • a User which, like Grant(?) ed, can also refer to Entity E in Permission
  • Gr identifies a Grant
    • a Grant grants Entity privilege to create, read, update, or delete Permissions by referencing another Entity
    • like Permissions , Grants are transitive in that:
      • If Organization O was a Granted privilege (for example) to change Permissions for Entity E , then
      • not only Users who are members of O modify Permissions , referencing E ,
        • 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 proposal is a statement that is true or false in a business situation. A predicate is an argument parameterized by the parameters of the column that defines the row. A table (base or query result) contains rows that make a true sentence 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 operator 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 parametric predicates. They can use parameter names bound by FOR ALL and FOR SOME ( THERE EXISTS ). Business rules formulated in terms of such propositional predicates and / or table names are database restrictions. Given User(U,N) and Grantor(R) as abbreviations for the first two predicates above as predicates for the User and Grantor tables, the following lines 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); indicates what is indicated above (note its similarity to the middle two) plus that R is UNIQUE NOT NULL in the Grant.

Do not confuse rules with predicates. They have different uses and usually different forms. (A template without parameters can be used as.) A rule is a true statement; A predicate is a parameterized operator. See how my answer separates them. Base tables and query result tables have predicates. But a rule may assume that you need a basic predicate / table to write something. We have basic predicates / tables when we see from the rule that we should write down some statements about the current situation. Please note that some rules do not contain basic predicates.

You probably want to confirm the 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 finds necessary and sufficient rules and basic predicates

Here are the relevant predicates for recording situations that may arise in your exposure.

• of users

 U identifies a user 

2. users can be groups.

 G identifies a group user U is in group G 

3. users can be in the organization

 O identifies an organization user U is in organization O 

4. groups may be in organizations

 group G is in organization O 

5. the user will be allowed CRUD operations for the 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 in which this group belongs to an organization that has permissions),

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

or because the object / group / org is viewable (readable) by anonymous users ("public")

 crudable C is public 

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

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

What are the “above permissions”? Maybe you mean CRUD user permission and organization CRUD permission? Maybe you mean that there are separate permissions for Create, Read, etc.? You need to be more clear.

What are the permissions in the "permission set"? By "permission" here, you instead mean "specific permission for a specific recipient"? You must be more clear.

A way to be clearer is to give rules and predicates that are as simple as possible, but also not so simple that they do not mention the corresponding objects / values. After that, you will want to generalize several rules and predicates into separate ones. For example, instead of dealing with users, groups, organizations, and assets, there are grantors and rattles: Grantors may grant permissions. and grantor R gives permission P to grantee E If some of these permissions are also associated with specific grantees, you may also need predicates, such as grantor R gives permission P to grantee E re permission Q and grantee F

6.1. the user can set permissions for any asset, group or organization that they create,

 user U created crudable C 

or any asset, group or organization for which they were allowed to establish permits.

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

You need to write things like user U has name N and ...

Learn about database design

Look for database search results / SQL subtyping / inheritance / polymorphism. For example, the user, group, and organization are owners and owners of permissions; I made them subtypes of the type of grantor. Maybe you need some type of target permission type, which is the union of the indifferent and the grantor. Perhaps you need permission types. Some permissions may have associated recipients. Perhaps "C", "R", "U" and "D" are permissions, and "CRUD" is a permission type. You will probably want to record which grantor granted permission to the recipient.

Later we can replace the tables with our join if the connection is on a common PK / UNIQUE with the same set of values ​​in both. When we can join PK / UNIQUE and FK, we can replace the tables with one, like joining them, but with a zero value of FK. There are other cases where we can easily replace multiple tables with one. But first, identify the main predicates.

Learn about the relational database. Follow some method of developing information. Best are members of the NIAM / FCO-IM / ORM2 family. Take a look at IDEF1X. Do not rely on products.

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

Answer 2

It seems to me that you need to create a concept for an object that can receive permissions for a repository or organization.

In this model, the problem becomes relatively simple, since permissions will revolve around the RepositoryPermissions table of the form:

 (EntityId, RepositoryId, canCreate, canRead, canUpdate, canDelete) 

and a OrganizationPermissions table view:

 (EntityId, OrganizationId, canCreate, canRead, canUpdate, canDelete) 

There are two types of Groups and Users objects, and the permissions of any user must be checked in four ways:

• Direct permissions User at the Repository level
• Group membership user with permissions at the Repository level
• Direct permissions User at Organization level
• A user in a group with permissions at the Organization level.

The following query should retrieve all permission entries related to the user someUserId in the repository someRepoId

  SELECT rp.canCreate, rp.canRead, rp.canWrite, rp.canDelete FROM RepositoryPermissions AS rp Left JOIN Users AS u ON u.EntityId = rp.EntityId Left JOIN Groups AS g ON g.EntityId = rp.EntityId Left JOIN GroupUsers AS gu ON gu.GroupId = g.GroupId WHERE rp.repositoryId IS "someRepoId" AND ( u.UserId IS "someUserId" OR gu.UserId IS "someUserId" ) UNION SELECT op.canCreate, op.canRead, op.canWrite, op.canDelete FROM Repositories AS r JOIN OrganizationPermissions AS op ON r.repositoryId = op.repositoryId Left JOIN Users AS u ON u.EntityId = op.EntityId Left JOIN Groups AS g ON g.EntityId = op.EntityId Left JOIN GroupUsers AS gu ON gu.GroupId = g.GroupId WHERE r.repositoryId IS "someRepoId" AND ( u.UserId IS "someUserId" OR gu.UserID IS "someUserID" ) 

Answer 3

Well, this is problematic when you are trying to make the database behave just like something like Github. Databases have a completely different paradigm. That is why it is just as bad to create databases based on how C # works. Not the same features or rules. However, this is an interesting question, because too few people pay attention to the permission model as part of their database design and simply give everyone the rights to everything, because it is easier.

First of all, users should not grant rights to others, only administrators should do it. Users should NEVER create objects if you have a good database design. Database design does not work with amateurs.

Users can be individuals, or you can use application users, where all database queries coming from a specific application have the same rights. Applications can even have several universal users, such as XYZAdmin, XYZUser, XYZReadonly (often used for senior managers who should be able to see data, but are not going to adjust it). In my experience, the biggest problems with universal users are that it becomes difficult to check who changed what is in the database (very important in a regulated environment), and some users may have more permissions than they really need.

Databases have several basic types of permissions. You have permissions that apply to all permissions at the database level and object level. You also have specific permissions on the server, such as permissions to use Bulk insert or execute jobs. Permissions must also be granted to groups regarding which databases they can see on the server, since most database servers have multiple databases. Thus, you can grant users write permissions to any table or grant them table rights, but only for certain stored procedures or views. In general, administrative staff receives general rights to everything (or rights at the server level, such as paste rights). This includes database administrators with full rights and other specialists, such as data analysts / senior developers / members of the assembly team, who may only have rights to work, enter data, or create new objects, but not rights to perform tasks such as setting permissions. All other users must be bound to object level permissions.

Now no one should be allowed as an individual (or at least it should be rare). Each person must be in different groups with rights. Individuals can be in the same group, and how these rights interact between the groups can be explained in the documentation related to the specific database product that you use (yes, this is specific to the product, there is no uniform size for all permission models Database). The beauty is that when someone is no longer a user, you only need to remove them from the group (s), and the permissions to leave, and not look for many individual permissions.

Now that you are processing permissions by group, you need to define groups. You may also need to define some additional views or stored procedures if you want the group to have only permissions for a subset of the records or columns instead of the entire table. If you have several clients in one database and you need to make sure that the permissions are intended for only one specific client, you need to use stored procedures / views and grant permissions only at the level of the stored procedure / view, and not at the table level. Using permissions views can be tricky because each database product has specific rules about what makes browsing updatable. You need to understand this at a deep level to determine how you are going to manage permissions.

All created database objects will need the script to specific groups that have been granted permission to this object as part of the creation of the script. (You will never create database objects through any graphical interface, always using scripts stored in source control.)

You can also perform some permissions for working with a client by setting up some database tables with metadata about specific pages that a user can access or specific clients that he is allowed to access. Then, when the application loads, user data from these tables is downloaded, and the application will decide what he or she can do. This is frankly easier, but can be risky.

This means that users must have permission at the table level, and users really should not have them. Because database-level permissions are wider, it is easier for an attacker to log in outside the application and do something that he or she cannot do. This is especially important if you have internal users who can easily log in through something other than a user application, such as SSMS for SQL Server.

If your database application is located in a legislative area such as finance or healthcare, you need to be very strict about permissions, and using ORM instead of stored procedures is contraindicated because you should never set permissions at the table level.