Simple and flexible permission design?

You: What do I know? I: also the following two points. Understand table design ii based on RBAC idea. How are the data used in actual development scenarios You: I think it should be a little bit awfully awfully me: I don’t want you to feel, AWFULLY AWFULLY ͜ feeling

Daniel: Hi, egghead, I recently received a request to add permissions related functions to existing projects. I have been concentrating on the front end for a long time, and haven’t touched watch design for a long time. Do you know anything about this?

Mr. Egg: [] (￣▽￣) * slightly understand slightly understand ~! You can’t “do what you want” when you have a project. Go ahead, some information about the DB of the existing project

Daniel: The database is MySQL, and the database is connected using Sequelize, an ORM node.js library.

Mr. Egg: OK, the suggested process for this combination is to design the table structure with EER graph tool (such as MySQLWorkbench), then export the SQL, and then automatically generate the Model via Sequelize-Auto

Daniel: Yes, dude, automatic SQL generation, automatic Model generation. Long time no see. You are still so lazy. When you say that, you’ve solved my first problem. Let’s move on to permission design

RBAC table design

Daniel: Permission design, is that a complicated part?

Mr. Egg: It can be as complicated as you want. What level of difficulty do you want? < ~ ~ ~ /

Daniel: No, no, no, I want something simple and flexible, which can be easily expanded ʅ (´◔౪◔) ʃ

Mr. Egg: That’s a lot to ask. Now this industry is used more RBAC (role-based access control) ideas, that is, role-based access control. So without further ado, LET me get right to the picture

You only need to assign a role to the user, and the role determines which Resource can be operated on. Operation is usually CRUD

Daniel: Why is there no password in the Users table? Why is code always varchar(45)

Mr. Egg: Hey, don’t worry about the details, okay? ヘ (_ |

Daniel: Ok, ok. The design looks simple, isn’t it?

Mr. Egg: Come on, according to your actual situation, please make a move

Functional authority

Daniel: Suppose you have users A and B; The system has two functions: project management and user management. User A is the administrator and can access both functions. User B is an ordinary user and can only access project management.

Mr. Egg: No problem. ┏ (^ ^) omega = ☞

1. Create data

Create resource data: Project management and user management are resources at the function module level. The data is as follows:

// resources: 
{ code: 'projects', name: 'projects', type: 'module' },
{ code: 'users', name: 'users', type: 'module' },
Copy the code

Create a role and assign related operation rights to it

// roles: 
{ code: 'admin', name: 'admin' },
{ code: 'guess', name: 'guess' },

// role_permissions:
{ roleCode: 'admin', resourceCode: 'projects', operation: 'C' },
{ roleCode: 'admin', resourceCode: 'projects', operation: 'D' },
{ roleCode: 'admin', resourceCode: 'projects', operation: 'R' },
{ roleCode: 'admin', resourceCode: 'projects', operation: 'U' },
{ roleCode: 'admin', resourceCode: 'users', operation: 'C' },
{ roleCode: 'admin', resourceCode: 'users', operation: 'D' },
{ roleCode: 'admin', resourceCode: 'users', operation: 'R' },
{ roleCode: 'admin', resourceCode: 'users', operation: 'U' },
{ roleCode: 'guess', resourceCode: 'projects', operation: 'R' },
Copy the code

Create a user and assign roles to the user

// users:
{ code: 'user_a', name: 'user_a' },
{ code: 'user_b', name: 'user_b' },

// user_role:
{ userCode: 'user_a', roleCode: 'admin' },
{ userCode: 'user_b', roleCode: 'guess' },
Copy the code

2. Consumption data

Now let’s provide data to the front end to determine which functional modules can be seen by user A and whether to display the create, delete and other buttons

SELECT 
    u.code userCode,
    res.code resourceCode,
    GROUP_CONCAT(DISTINCT rp.operation) operations
FROM
    resources res,
    role_permissions rp,
    roles r,
    user_role ur,
    users u
WHERE
    res.code = rp.resource_code
        AND rp.role_code = r.code
        AND r.code = ur.role_code
        AND ur.user_code = u.code
        AND res.type = 'module'
        AND u.code = 'user_a'
GROUP BY u.code , res.code
Copy the code

The resulting user_A permissions are as follows:

userCode	resourceCode	operations
user_a	projects	R,D,U,C
user_a	users	R,D,U,C

In this way, the front end only needs to determine whether projects have operation of R to decide whether to display the project function menu. If C is present, the create button is displayed; If there is D, the delete button is displayed. With U, the edit button is displayed

3. View simplification

Daniel: The problem is solved, but the SQL, isn’t it a bit complicated? (~ ~ ▽ ~) ~

Mr. Egg: Well, exactly. So let’s simplify it.

Use the following SQL to create a view view of user function module permissions

CREATE VIEW `user_module_view` AS
    SELECT 
        ur.user_code,
        rp.resource_code,
        CONCAT('|',
                GROUP_CONCAT(DISTINCT rp.operation
                    SEPARATOR '|'),
                '|') operation
    FROM
        user_role ur,
        role_permissions rp,
        resources rs
    WHERE
        ur.role_code = rp.role_code
            AND rs.code = rp.resource_code
            AND rs.type = 'module'
    GROUP BY rp.resource_code , ur.user_code
Copy the code

Now we can simplify the long SQL from above into the following single-table operations:

SELECT 
    *
FROM
    user_module_view
WHERE
    user_code = 'user_a'
Copy the code

Note: above CONCAT(….) The result of the operation in the format as follows: | | R | C U | D |, so in order to accurately through the like (e.g., like ‘% % | R |’) to check if you have a certain privileges

Data access

Daniel: Then I will continue to work out the questions. Although both user A and user B have read permission for the project management function, user B is an ordinary user. Suppose user B belongs to OrgB, then he can only view the project under OrgB.

Mr. Egg: Then you can extend operation. Now let’s modify the data for role_Permission

{ roleCode: 'guess', resourceCode: 'projects', operation: 'R' } 
=>
{ roleCode: 'guess', resourceCode: 'projects', operation: 'R_ORG' },
Copy the code

This means that the GUESS role has org-wide read permission on projects resources. In this way, when the server interface is fetching item list data, it can determine the filtering conditions of the list data according to the operation flag bit of R_ORG

Data item level permission

Daniel: There seems to be nothing wrong with the regular needs. But NOW I have a permission related requirement, I don’t know if your set can be used

Egg: come on, I’ll play it to the end today (~) ~)

Daniel: Then I don’t mind. In my project management feature, each project is created with a view/Edit/admin role by default. The above example can only do the same for projects with a specified scope (such as org), but it seems impossible to specify different operations for different projects

Mr. Egg: [] (~ ▽ ~) * Then change the Angle, how about treating every project as a resource?

Daniel: Can you be more specific? Would it be nice to talk about what you should do in the permissions section when you create a project

Mr. Egg: Ahem, ahem, no problem, come on

When you create your project, you need to initialize the corresponding built-in roles so that users can be assigned roles. Here’s what data needs to be added to which tables, assuming you create project Project_A

// 1. add resource:  
{ code: 'project_a'.name: 'project_a'.type: 'project' }


// 2. add roles: 
{ code: 'pro_a_view'.name: 'pro_a_view' },
{ code: 'pro_a_edit'.name: 'pro_a_edit' },
{ code: 'pro_a_admin'.name: 'pro_a_admin' },

// 3. add role_permission:
{ roleCode: 'pro_a_view'.resourceCode: 'project_a'.operation: 'R' },
{ roleCode: 'pro_a_edit'.resourceCode: 'project_a'.operation: 'R' },
{ roleCode: 'pro_a_edit'.resourceCode: 'project_a'.operation: 'U' },
{ roleCode: 'pro_a_admin'.resourceCode: 'project_a'.operation: 'R' },
{ roleCode: 'pro_a_admin'.resourceCode: 'project_a'.operation: 'U' },
{ roleCode: 'pro_a_admin'.resourceCode: 'project_a'.operation: 'D' },
Copy the code

In this way, you only need to add the pro_a_view role to user B, and user B has read permission to project_A

Note that operation does not have a C here, because resources refer to individual projects, so where does a single project get create? Is it (^ del ^)

Daniel: Well, it looks exactly the same as the whole project function as a resource. But I found a problem, if each project as a resource, then I have to query user B can see which projects, seems to be very cumbersome. You can’t just find them one by one and put them together

Mr. Egg: Of course, remember the view we used above? Now let’s also create a view for a resource of type Project

CREATE VIEW 'user_project_view' AS 
    SELECT 
        ur.user_code,
        rp.resource_code,
        CONCAT('|',
                GROUP_CONCAT(DISTINCT rp.operation
                    SEPARATOR '|'),
                '|') operation
    FROM
        user_role ur,
        role_permissions rp,
        resources rs
    WHERE
        ur.role_code = rp.role_code
            AND rs.code = rp.resource_code
            AND rs.type = 'project'
    GROUP BY rp.resource_code , ur.user_code
Copy the code

This also requires a single table to query the list of items that user B can view and the permissions for each item

SELECT 
    *
FROM
    user_project_view
WHERE
    user_code = 'user_b'
        AND operation LIKE '%|R|%'
Copy the code

Special permission requirements

Daniel: Ouch! If user B has the edit role for project_a, he can only delete his own image resources, not others. I don’t want to record images as projects anymore

Mr. Egg :(LLL ￢ω￢) well…

Daniel: I guess you got it, haha

Mr. Egg: No, no, no. Powerful Operation is not a vegetarian. I simply add the limited modifier to the Update permission of the Edit role. Such as U_LIMITED

Daniel: That’s all right. It makes sense. Since Operation can be extended, as long as we specify its behavior, it’s as if everything is ok, right

Mr. Egg: All right. Scalability is a must, and operation is the key to it. Operation defines the operation identifier. The developer can implement the specified logic according to the convention of the operation identifier

Daniel: I see. Thanks, egghead. See you later

Mr. Egg: You’re welcome. Can’t you see me off?

This is the end, thanks for watching! (๑ ¯ ∀ ¯ ๑)

Simple and flexible permission design?

RBAC table design

Functional authority

1. Create data

2. Consumption data

3. View simplification

Data access

Data item level permission

Special permission requirements

Related Posts

Understand the React Virtual DOM in depth

Teach you hand a atlas packaging tools | nodeJs

Micro channel small program development a little experience