current position:Home>Simple and flexible permission design?

Simple and flexible permission design?

2022-06-24 12:32:56Mr egg

you : What can I know after watching ? I : Let's talk about the following two points One . Based on understanding RBAC Table design of ideas Two . How table data is used in actual development scenarios you : I think it's still a little dry I : I don't want you to think , I want me to feel (͡ ͡° ͜ つ ͡͡°)

Daniel :Hi, Brother egg , Recently received a demand , You need to add permission related functions to existing projects , Think about it. I've been focusing on the front end for a long time ,N It's been a long time since I touched the watch , Do you know anything about this ?

Mr. egg :[]( ̄▽ ̄)* A little understanding a little understanding ~! Existing projects , That's not the way “ Follow one's inclinations ” Slightly . Say it. , About existing projects DB Information about

Daniel : Database is used MySQL, The connection to the database is Sequelize, One ORM Of Node.js library .

Mr. egg :OK, The process of this combination suggestion is : First use EER Figure tools ( Such as MySQLWorkbench) Design table structure , Then export SQL, Finally through Sequelize-Auto Automatic generation Model

Daniel : Yes, brother egg , Automatic generation SQL, Automatic generation Model. Long time no see , You are still so lazy (^▽^ ). You can say so casually , I've solved my first problem . Then let's talk about authority design


RBAC Table design

Daniel : Permission to design , Is this piece complicated ?

Mr. egg : You can be as complex as you want to be , What kind of difficulty coefficient do you want ?<( ̄ˇ ̄)/

Daniel : No no no , I want to be simple and flexible , It's easy to expand that kind of ʅ(´◔౪◔)ʃ

Mr. egg : It's very demanding . Now the industry is using more RBAC(Role-based access control) The idea of , Role based access control . Don't talk much , I'll go straight to the picture

It's very simple , You just need to give the user a role , And roles determine what resources can be used (Resource) What kind of operation (Operation),Operation It is commonly CRUD

Daniel :users Why doesn't the watch password ah , Why? code What is varchar(45) ah

Mr. egg : Hello, hello. , Don't worry about the details, OK ?ヘ(・_|

Daniel : Good good . The design of this watch looks very simple , Can't you ?

Mr. egg : Come on , According to your actual scene , Come on


Function permissions

Daniel : Suppose there are users A And the user B; There is project management in the system , User management has two functions ; user A It's the administrator , Both functions are accessible . And users B It's the average user , Only access project management , How to get ?

Mr. egg : small token of kindly feelings .┏ (^ω^)=*

1. Create data

  • Create resource data : project management , User management is a resource at the functional module level , The data are as follows :
// resources: 
{ code: 'projects', name: 'projects', type: 'module' },
{ code: 'users', name: 'users', type: 'module' },
 Copy code 
  • Create roles and give relevant operation permissions
// 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 code 
  • Create users and give them roles
// 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 code 

2. Consumption data

Now let's provide the front-end children with data to determine the user A Which functional modules can be seen , And whether to display the creation , Delete etc

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 code 

Got user_a The authority of is as follows :

userCode

resourceCode

operations

user_a

projects

R,D,U,C

user_a

users

R,D,U,C

such , The front end just needs to judge projects Owned or not R Of operation, You can decide whether to display the item function menu . If there is C, The Create button is displayed ; Yes D, The delete button is displayed ; Yes U, The Edit button is displayed

3. View simplification

Daniel : The problem is that it's solved , But that SQL, Is it a bit complicated (~ ̄▽ ̄)~

Mr. egg : forehead , exactly . Let's simplify it .

By SQL Create a view of user function module permissions view

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 code 

Now we can just put the lengthy SQL Simplified to the following single table operation :

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

remarks : above CONCAT(....) Middle make operation The result format is :|C|R|U|D|, This is to get through like( Such as like '%|R|%') To query whether you have a certain permission


Data access

Daniel : Then I'll go on with the topic . user A And the user B Although all of them have the function of project management read jurisdiction , But users B It's the average user , Suppose the user B Belong to OrgB organization , Then he can only check OrgB The next project should be done yesterday ?

Mr. egg : That would be good for operation Expanded . Now let's revise role_permission The data of

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

This means guess The role is right projects Resource ownership org Scope read jurisdiction . In this way, when the server interface fetches the item list data , According to R_ORG To determine the filtering condition of the list data


Data item level permissions

Daniel : There seems to be no problem with regular requirements . But now I have a permission related requirement , I don't know if you can use this

Mr. egg : Come on , I'll be with you today ( ̄︶ ̄)

Daniel : Then I will not be polite . In my project management function , Every project is created with... By default view / edit / admin role . The above example can only be used for the specified range ( such as org) Do the same for the project , But different projects specify different actions , It doesn't seem to work

Mr. egg :[]( ̄▽ ̄)* Let's change the angle , How about treating every project as a resource .

Daniel : Can you be more specific ? It's better to say what to do when creating a project

Mr. egg : Cough cough cough ~, That's all right. , Come on

As you ask , When creating a project , You need to initialize the corresponding built-in roles , In this way, users can be assigned roles . Let's talk about creating a project project_a, Which tables need to be added which data

// 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 code 

So you just need to give the user B increase pro_a_view role , user B That is to have the right to project_a Read permission of

Note that there operation did not C, Because a resource is a single project , So where does a single project come from create Well ? Is that so? (^▽^ )

Daniel : Okay , It looks the same as when the whole project function is used as a resource . But I found a problem , If you use each project as a resource , Then I want to query the users B What can I see , It seems very troublesome . You can't find one by one , And then put it together

Mr. egg : Of course , Remember that we used it View view Do you ? Now we also give project Create a resource of type view Well

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 code 

In this way, users can also be queried by a single table B You can view the list of items and the operation permission of each item

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

Special permission requirements

Daniel : Oh, yes . I have one last need , It's the image resources in the project , If the user B Yes project_a Have edit role , You can only delete the image resources you added , You can't delete a picture resource that someone else has added , Can this be realized . I don't want to be a resource record like a project

Mr. egg :(lll¬ω¬) This... ...

Daniel : Looks like you're stuck , ha-ha

Mr. egg : No more than that. . Powerful operation It's not a vegetarian . All I have to do is edit Character's update The operation authority is increased limited You can use the modifier of . Such as U_LIMITED

Daniel : All right , That makes sense . because operation You can extend the , So as long as we define its behavior , It's like everything can be done

Mr. egg :All right. Scalability is a must , and operation That's the key to expansion .operation Defines the operation identifier , According to the contract of operation identifier, the developer , Just implement the specified logic

Daniel : got it , Thank you , Brother egg , Goodbye

Mr. egg : You're welcome , Don't give away when you're good !


It's over here , Thank you for watching. !(๑¯∀¯๑)

copyright notice
author[Mr egg],Please bring the original link to reprint, thank you.
https://en.chowdera.com/2022/175/20210531224930485r.html

Random recommended