A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
Whether in website development or MIS system development, software systems involving multiple users will encounter this problem. how to solve this problem elegantly has always been a hot topic that people often discuss, this article will talk about your ideas and hope to share them with you. Method 1: User table: T_UserInfo id name Object table: T_Object id
Whether in website development or MIS system development, software systems involving multiple users will encounter this problem. how to solve this problem elegantly has always been a hot topic that people often discuss, this article will talk about your ideas and hope to share them with you.
Userid (foreign key, from user table)
Objectid (foreign key, from Object table)
Access (use code to record a user's permission combination:
1100 browse and add
1110 browsing, adding, and editing
1111 browsing, adding, editing, and deleting
Access1 (representing browsing, saving the user ID, separated by commas)
Access2 (indicating browsing and adding)
Access3 (for browsing, adding, and editing)
Access4 (for browsing, adding, editing, and deleting)
Superior or inferior?
We use the first
WINDOWS systems are also the first
Method 2 is not desirable, and it is very troublesome for users to increase, and the length of access1 -- access4 is difficult to determine.
I will talk about the database design and implementation of MIS permission management. of course, these ideas can also be applied, for example, to manage different levels of user permissions in BBS.
Permission design consists of three parts: database design, application interface (API) design, and program implementation.
These three parts are mutually dependent and inseparable. to achieve a sound permission management system, the feasibility, complexity, and even execution efficiency of each link must be taken into account.
We classify permissions into four types: data access permissions, which are input, browse, modify, and delete, followed by functions, it can include all non-direct data access operations such as statistics. In addition, we may restrict the access to some fields in some key data tables. In addition, I cannot think of another permission category.
The complete permission design should be fully scalable. that is to say, the system should not bring major changes to the entire permission management system when new functions are added, the first is the rational database design, and the second is the application interface specification.
We will first discuss database design. We usually use relational databases. here we do not discuss permission management based on Lotus products.
The permission table and related content can be described in six tables as follows:
1. Role (user group) table: contains three fields: ID, role name, and description of the role;
2. user table: contains three or more fields, ID, user name, description of the user, and other information (such as address and phone number );
3 Role-user table: This table records the relationship between users and roles. a user can belong to multiple roles and a role Group can also have multiple users. It includes three fields: ID, role ID, and user ID;
4. restricted content list: This table records all data tables, functions, fields, and descriptions that require permission differentiation, including three fields, ID, name, and description;
5. Permission list: This table records all permissions to be controlled, such as input, modification, deletion, and execution. It also contains three fields: ID, name, and description;
6. permission-role-user table: Generally, the permissions of the role/user are as follows, the user inherits all permissions of the role. all permissions in this range are allowed except explicitly, and all permissions outside the scope are prohibited. The design of this table is the focus of permission management, and there are also a lot of design ideas. it can be said that each table has its own merits. In this regard, my opinion is that, in my personal circumstances, I find myself appropriate to solve the problem.
The first and easiest way to understand is to design five fields: ID, Content ID, permission ID, role/user type (Boolean field, used to describe whether a record is a role permission or a user permission), role/user ID, and permission type (Boolean field, used to describe whether a record is allowed or not)
All right, there are these six tables. according to Table 6, we can know whether a role/user has or disallows some permissions.
In other words, this design is enough, and we fully implement the required functions: you can customize permissions for roles and users separately, and also have considerable scalability. for example, new features are added, we only need to add one or more records, and the application interface does not need to be changed, which is quite feasible. However, in the process of program implementation, we found that using this method is not very scientific, such as browsing the permissions owned by a user, it is inconvenient to perform multiple (or even recursive) queries on the database. So we need to find other methods. People who have used Unix systems know that Unix file systems divide file operation permissions into three types: read, write, and execute, which are identified by code 1, 2, and 4, respectively, 3 files with the same read and write permissions are recorded, that is, 1 + 2. We can also solve this problem in a similar way. The initial idea is to modify the permission list and add a field: ID code. for example, we can mark the input permission as 1, the Browse permission as 2, and the access permission as 4, the delete permission ID is 8 and the execution permission ID is 16. in this way, we can easily combine the permissions originally divided into several records and descriptions by accumulating the permissions. for example, assume that a user ID is 1, the restricted content ID of the database table is 2, and the role type is 0 and the user type is 1, we can describe the permissions that the user has to input, browse, modify, and delete the database and table.
It's really easy, isn't it? There are even more radical ways to add a column to the restricted content list and define the ID code, we can even use a simple record to describe all permissions a user has on all the content. Of course, the premise of doing so is to limit the small amount of content. Otherwise, the npower of 2 is increasing, but the number is amazing and it is not easy to parse.
On the surface, the above method is sufficient to achieve the purpose of implementing functions, simplifying database design, and implementing complexity. However, this method has some disadvantages, the list of permissions involved is not independent of each other, but dependent on each other. for example, modifying permissions actually includes browsing permissions. for example, we may simply set the user's access permission to the database and table to input, modify, and delete (1 + 4 + 8 = 13), but in fact, this user has (1 + 2 + 4 + 8 = 15) permissions, that is, in this solution, 13 = 15. Therefore, when we call an API to check whether a user has browsing permission, we must determine whether the user has the permission to modify the data table. therefore, if the inclusion relationship between permissions cannot be fixed in the program, the application interface cannot be used for simple judgment. However, this is in conflict with our goal of "full scalability.
How can this problem be solved? I think of another way to set the ID code, that is, using prime numbers. We may set the basic flag codes for entry, browsing, modification, deletion, and execution to 2, 3, 5, 7, and 11. when the permissions are mutually included, we set its identifier code to the product of two or more basic tokens. for example, we can set the identifier code of the "modify" function to 3*5 = 15, then, all permissions are multiplied to obtain the final permission ID value we need. In this way, we only need to break the final value into a quality factor when asking whether the user has a certain permission. for example, we can define that a user has the permission to enter, modify, and delete a database and table as 2*15*7 = 2*3*5*7, that is, this user has the permission to enter, browse, modify, and delete the database and table.
Of course, the premise for using the above method for the permission list is that the number of records in the permission list is not too large and the relationship is not very complex. Otherwise, simply parsing the permission code requires the machine to be fooled for half a night :)
I hope the above analysis is correct and effective (in fact, I also use these methods in more than one system), but in any case, I think permission management is implemented in this way, it is difficult to implement the database design and application interface. Therefore, I urge the comrades who have experience in similar design and implementation to put forward constructive comments and suggestions for modification.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service