Permission Database Design

From:Http://hi.baidu.com/circledong/blog/item/ec56e1101549cf79cb80c463.html

 

When developing a system, we often encounter a system that requires permission control. Different Levels of permission control have different design schemes.

1.Role-based permission Design
This solution is the most common and relatively simple solution, but this design is usually enough, so Microsoft has designed a general practice for this solution. This solution does not control every operation, just inProgramTo control the operation permissions based on the role.

2.Operation-based permission Design
In this mode, each operation is recorded in the database, and the user's permissions for this operation are also recorded in the database. The structure is as follows:

However, if the above design is used directly, the useraction in the database will beThis table has a large amount of data, so we need to further design and improve efficiency. See solution 3.

3.Role-and operation-based permission Design

As shown in, we have added the role, And roleactionTable to reduce useractionAnd make the design more flexible.
However, this solution may not be flexible enough to meet the user's needs. For example, when a user requests temporary operation permissions for an ordinary employee, we need to add a new user role, but this user role is not necessary because it is only a temporary role, if a role needs to be deleted when the general employee's permissions are revoked, we need to design a more appropriate structure to meet the user's requirements for permission settings.

4.2, 3The permission combination is designed with the following structure:

We can see that useraction is added inTable, which is used to add special user permissions. The table has a field haspermission.You can determine whether a user has certain operation permissions. The permission record in the change table has a higher priority than userrole.User Permissions recorded in. In this way, we need to use userrole in the ApplicationAnd useractionThe record judgment permission in the two tables.
This is not an end. It is possible that the user will give the following requirement: For an actionThe operation object has permissions for some records, but does not have permissions for other records. For example, a content management system has the permission to modify a user of a channel, if you do not have the permission to modify other channels, we need to design more complex permission mechanisms.

5.Users of the same entity (Resource) can have permissions on some records, but have no permissions on other records:

For such a requirement, we need to create a permission table for each different resource.And ChannelThe two types of resources respectively create the useractioncontentAnd useractionchannelTable is used to define whether a user has permissions on a record. This design can meet user needs but is not very economical. useractionchannelAnd useractioncontentThere will be a lot of records, but in actual applications, it is not necessary to record the permission information of all records. Sometimes it may be just a rule, for example, for the root channelAt what level do people have permissions? At this time, we can define rules to determine user permissions. below is the design.

6.Permission design involving resources, permissions, and rules

In this design, the role concept has been