Users and roles: how does a common Rights management system database table structure be designed?

First, the application of the Authority management system should be used in three different natures,
A, permission to use
B, assigning permissions
C, Authorization permissions
This article only from the "Use Rights" and "assign permission" Two application level analysis, temporarily do not consider the "authorized permission" this.
Second, preliminary analysis of users and roles speaking of Rights management, the first thing to think about, of course, to design a user table, a permission table. This determines what kind of authority a person has.
Do the work will find that this design is too cumbersome, if all employees in the company have such permissions, everyone to configure? That's a very painful thing to do. So add a role table, put some people in a category, and then assign the permissions to the role. Users under the role also have permissions.
The relationship between users and roles is that one user can correspond to multiple roles, and one role can correspond to multiple users. Many-to-many relationships.
So need an intermediate table, I believe we are very familiar with, naturally will not have doubts.
After the application scenario has users and roles, it is necessary to design the application scenarios, such as how many modules (System modules, project management modules, sales modules) an application has.
A module like this is an application scenario, with menus, operations, and so on.
Let's say we've designed it now, and the application scenario includes modules, menus, and actions, so there are six different relationships

• One user can correspond to multiple modules, and one module can correspond to multiple users. Many-to-many relationships.
• A user can correspond to multiple menus, and a menu can correspond to multiple users. Many-to-many relationships.
• One user can correspond to multiple actions, and one action can correspond to multiple users. Many-to-many relationships.
• A role can correspond to multiple modules, and a module can correspond to multiple roles. Many-to-many relationships.
• A role can correspond to multiple menus, and a menu can correspond to multiple roles. Many-to-many relationships.
• A role can correspond to multiple actions, and one action can correspond to multiple roles. Many-to-many relationships.

Six tables were created to maintain the six relationships.
The design looks fine. Yes, if you do not add a new relationship, you can already meet most of the requirements. But if it is, the new relationship (demand) tends to come into the system. At this point, we need to build a new table. The complexity of the system increases as well.
As you can see, there are several problems with this design:

• Data table design too complex
• Coping with system scenarios is too fixed

Three, to simplify the problem

Different applications, you may come up with different needs, after a new demand, you may find that the original design is not implemented, and then add a new table. This is also the problem mentioned above.
In fact, you don't have to be so complicated, permissions can be simply described as:
XXX subject has a certain authority in XXX field

1, the subject can be a user, can be a role, or it can be a department

2, the domain can be a module, can be a page, or it can be a button on the page

3, the permission can be "visible", can be "read-only" or "available" (such as a button can be clicked)

is actually the WHO, what, how problem

So the six tables mentioned above can actually design a table:




IV, example description
Here is an example of a design description. "User, role on page is permission to use"
Detailed design:
1, put the menu configuration on the database, each menu for a unique encoding Menuno, each "leaf node" menu item for a page (URL).
2, put the configuration of the button on the database, and belong to a menu item (in fact, is hanging on a certain page). A page might have several button groups, such as two lists, both of which need to have "add, modify, delete." So you need to add a button to group the fields to differentiate.
3, assign menu permissions to user/role, privilegemaster "user" or "role", privilegemastervalue as UserID or roleid,privilegeaccess as "menu", Privilegeaccessvalue to Menuno,privilegeoperation as "enabled"
4, Assign button permissions to user/role, privilegemaster "user" or "role", privilegemastervalue as UserID or roleid,privilegeaccess as "button", Privilegeaccessvalue to Btnid,privilegeoperation as "enabled"
5, Privilegeoperation is set to "disabled" if you need to prohibit the permissions of individual users.
If not clear can see:

Database design:



Four, the conclusion
Speaking so much, in fact I recommend only privilege table design. This table is the design of the WHO, what, how problem prototypes. Not only is extensibility, flexibility is good, but also the complex rights management system condensed into one sentence.

and privilegeoperation not only use and prohibit two, including the allocation of permissions, authorization permissions, can be defined with this field. It's just that it makes the design of the application more difficult, but it can be done without any modification to the table design, which shows its flexibility.

Http://club.sm160.com/showtopic-934193.aspx

Users and roles: how does a common Rights management system database table structure be designed?