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

Source: Internet
Author: User
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.
Doing it will find that the design is too cumbersome, if all employees in the company have such permissions, everyone should be configured. 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

The

3,  permission can be "visible", either "read-only" or "available" (such as a button can be clicked) is actually a question of who, what, how

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




Four, instance description
Use an example to do the design description below. "Users, roles on page is use rights"
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 as "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 as "user" or "roles", Privilegemastervalue for userid or roleid,privilegeaccess as "button" and Privilegeaccessvalue for Btnid,privilegeoperation " Enabled "
5, Privilegeoperation set to" disabled "if you need to suppress permissions for individual users.
If you are not clear, you can look at the following image:
 
Database design:
 


Four, conclusion
said 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.