Saving application permissions in the database

Question

I am developing an application for our company, which ultimately will have many ways to limit users to specific sections / modules. Although the application is still small, I would like to move on to a new method for storing permissions that, as the application grows, will remain simple to maintain and query.

Currently, our MySQL database has a table called "user" that stores the user ID, username and password. In a separate table called "user_acl" the following is indicated:

user_acl_id acl_root acl_news_read acl_news_write acl_news_modify acl_reports_read acl_reports_write acl_reports_modify acl_users_read acl_users_write acl_users_modify 

We only have 3 modules per minute, but over time, more will be created, and you will need to add permissions for each.

Instead of creating a column for each permission, is there another way or to store this information?

Answer 1

I would do it like this.

 table name: permission columns: id, permission_name 

and then I can assign multiple permissions to the user using the many-many relationship table

 table name: user_permission columns: permission_id, user_id 

This design will allow me to add as many permissions as I want and assign them to as many users as possible.

While the above design comes with your requirement, I have my own ACL implementation method in my application. I post it here.

My ACL implementation method is as follows:

• The user will be assigned a role (Admin, guest, staff, public)
• The role will be assigned one or more permissions (user_write, user_modify, report_read), etc.
• Permission for the User will be inherited from the role that he / she is
• A user can be assigned by permission manually, except for the permission inherited from the role.

For this, I came up with the following database design.

 role I store the role name here +----------+ | Field | +----------+ | id | | roleName | +----------+ permission: I store the permission name and key here Permission name is for displaying to user. Permission key is for determining the permission. +----------------+ | Field | +----------------+ | id | | permissionName | | permissionKey | +----------------+ role_permission I assign permission to role here +---------------+ | Field | +---------------+ | id | | role_id | | permission_id | +---------------+ user_role I assign role to the user here +---------------+ | Field | +---------------+ | id | | user_id | | role_id | +---------------+ user_permission I store the manual permission I may allow for the user here +---------------+ | Field | +---------------+ | id | | user_id | | permission_id | +---------------+ 

This gives me more control over the ACL. I can allow super administrators to assign permissions, etc. As I said, this is just to give you this idea.

Answer 2

I believe that you should do these permissions in jSON format.

you can create another table with permissions in which you can add, modify, delete rows at any time.

or

every time a user authenticates, you can store these permissions in a session and pass it into your scripts.

My personal suggestion is the first option.

Answer 3

As Ibrahim says, create a new table specifically for your rights. Assign a numerical value to the user who represents their permission level, say: 1 = read, 2 = write / read, 3 = change / write / read. Then, in your code, verify that the permission level is correct before allowing the user to perform a specific task. If they do not have the required value (3 to change or> = 2 to write), you block this ability.

Answer 4

I think you should have favela tables:

 user user_x_profile profile profile_x_function function 

You set up various “general” profiles: “viewer”, “employee”, “manager”, etc.

You create a function entry for each object that you want to control.

Then associate the functions with the profiles in the profile_x_function function.

Then assign one or more profiles to each user.

This reduces administrative effort. Suppose you want to add another function that only "managers" can use - you simply add a new entry to the function table, and then add an entry to the "profile_x_function" table, which refers to the manager profile for manager profile permission and availability for all managers.

To request access, you need a join with five tables, but you select only one permission attribute.