whether in the Web site development or MIS system development, involving a multi-user software system will encounter this problem, how to solve this problem more elegant has always been a hot topic of discussion, this article try to talk about their own views, hope and everyone together. Method one: user table: t_userinfo id name Object Table: t_object& nbsp id name Permissions table t_access accessid UserID (foreign key, from user table) objectid (foreign key, from Object table) access (code to record user's rights combination: 1000 browsing 1100 browsing, adding 1110 Browse, add, edit 1111 Browse, add, edit, Delete etc) method two: user table: t_userinfo id name Object Table: t_object id name &n bsp; Access1 (for browse, save user ID number, comma delimited) Access2 (on behalf of Browse, add) ACCESS3 (for browse, add, edit) ACCESS4 (on behalf of browse, add, edit, delete) which is better? --------------------------------------------------------------- We're using the first Windows system and the first --------------------------------------------------------------- Method 2 is not advisable, the user increases the time is very troublesome, And the length of the ACCESS1--ACCESS4 is difficult to determine. the following I would like to say is MIS system Rights Management database design and implementation, of course, these ideas can also be extended to applications, such as in the BBS to manage different levels of user rights. The rights design usually includes the database design, the application Interface (API) design, the implementation of the program three parts. these three parts are interdependent, inseparable, to achieve a sound authority management system, we must take into account the feasibility and complexity of each link or even the efficiency of execution. We classify the permissions, first of all the permissions for data access, there are usually input, browse, modify, delete four, followed by the function, it can include such as statistics and other non-direct data access operations, in addition, we may also some key data table some field access restrictions. In addition to this, I can't think of a different kind of permission category. Perfect authority design should have sufficient extensibility, that is to say, the system adds the new other function should not bring the big change to the whole authority management system, to achieve this goal, first is the database design is reasonable, next is the application interface specification. Let's talk about database design first. Typically we use relational databases, which do not discuss rights management based on Lotus products. The permissions table and related content can be described in roughly six tables, as follows: 1 roles (i.e. user groups) Table: Includes three fields, ID, role name, description of the role, 2 user table: includes three or more fields, ID, user name, description of the user, other (such as address, telephone, etc.); 3 Role-User table: This table records the correspondence between users and roles, one user can belong to multiple roles, and one role group can have multiple users. Includes three fields, ID, role ID, user ID, 4 restricted Content list: This table records all of the data tables, functions, and fields that need to be restricted by permissions and their descriptions, including three fields, IDs, names, descriptions, 5 permission lists: The table records all permissions to be controlled, such as input, modify, delete, execution, etc., also includes three fields, ID, name, description; 6 permissions-roles-user table: In general, we have the role/user permissions to do the following, the role has authorized permission, the other is prohibited, the user inherits all the permissions of the role, In this scope of permission except expressly prohibited outside all permitted, outside the scope of permission except expressly permitted to prohibit all. The design of the table is the focus of authority management, the design of a lot of ideas, can be said to be different, not mechanically say some way good. In this respect, my opinion is to find out what I think is appropriate to solve the problem in my personal situation. The first and easiest way to understand this is to design five fields: ID, limit content ID, permission ID, role/user type (Boolean field that describes whether a record is a role or user right), role/user ID, Permission type (boolean field, Used to describe whether a record represents a permit or a bar)Well, with these six tables, according to table six, we can tell if a role/user has a certain permission. or, this design is enough, we have fully realized the required function: the role and users can be customized permissions, but also a considerable scalability, such as adding new features, we only need to add one or a few records can be, while the application interface does not need to change, is quite feasible. However, in the process of implementation of the program, we found that using this method is not very scientific, such as browsing the permissions of a user, you need to make multiple (or even recursive) queries to the database, very inconvenient. So we need to think of other ways. People who have used Unix systems know that UNIX file systems will be divided into three types of file operations: Read, write, and Execute, with 1, 2, and 43 code identifiers, and files with read and write permissions for the user are recorded as 3, or 1+2. We can also solve this problem in a similar way. The initial idea is to modify the permissions list, add a field: Identification code, for example, we can identify the entry permission as 1, the Browse permission is identified as 2, the modification permission is identified as 4, the Delete permission is identified as 8, the execution permission is identified as 16, so, We can easily be divided into a few records described by the method of permission to put together, for example, assume that a user ID is 1, the inventory table corresponding to the limit of the Content ID 2, while the role type 0, the user type of 1, we can have the user input, browse, modify, The permission description for the Delete inventory table is: 2,15,1,1. It's really simple, isn't it? There are even more drastic ways to add a column of restricted content lists and define identifiers so that we can even use a simple record to describe all the permissions a user has on all of the content. Of course, the premise of this is to limit the amount of content is smaller, otherwise, hehe, 2 of the N-time increment up but the number is amazing, not easy to parse. on the surface, the above method is sufficient to achieve the function, simplify the database design and implementation of the complexity of this purpose, but there is a disadvantage, we are involved in the list of permissions are not independent of each other, but interdependent, such as the modification of permissions, in fact, including the permission to browse, for example, We may simply set the user's access to the inventory table to a value of entry + Modify + DELETE (1+4+8=13), but in fact the user has (1+2+4+8=15) permission, that is, in this scenario, 13=15. So when we call the API to ask if a user has permission to browse, it must be judged whether the user has permission to modify the data table, so if you cannot cure the inclusion relationship between the permissions, you can not take advantage of the application interface simple to make judgments. But this contradicts the "full scalability" of our purpose. How to solve this problem? I think of another way to set the identifier, which is to use prime numbers. We may wish to enter, browse, modify, delete, execute the basic symbol code as 2,3,5,7,11, when we encounter the rights to each other, we set its identification code to two (or more) the product of the basic code, for example, you can change the "modify" function of the flag code as 3*5= 15, and then multiply all the permissions, you get the final permission ID value we need. In this way, we ask the user whether or not to have a certain permission, we only need to decompose the final value into a quality factor, for example, we can define a user has input + Modify + Delete inventory table permissions for 2*15*7=2*3*5*7, that is, the user has the Inventory table entry + Browse + Modify + DELETE permissions. Of course, for the permission list we use the above method, the premise is that the permission list record bar number is not too much and the relationship is not very complex, otherwise, just parse the permission code will be the machine to fool half-house:)I hope the above analysis is correct and effective (in fact, I also use these methods in more than one system to implement), but anyway, I think this implementation of rights management, just consider the database design and application interface two parts, for the implementation, or it seems very laborious. Therefore, I implore the comrades who have experienced similar designs and experiences to make constructive comments and revise suggestions. http://zhoufoxcn.blog.51cto.com/792419/166431/
Database Design for permissions
