Discussion on permission design
Original: http: //expert.9cbs.net/Expert/topic/1653/1653829.xml temp = .2015345 Author:? Zealberg (iceberg)
However, network or single-machine programs involving multi-user different privileges will have permission management issues, and it is more prominent that MIS systems.
Below I want to say, the database design and implementation of MIS system rights management, of course, these ideas can also be promoted, such as the user permission to manage different levels in BBS.
Permission design usually includes a database design, an application interface (API) design, and a program implementation three parts.
These three parts are interdependent, unparalleled, to achieve a complete permission management system, must take into account each link feasibility and complexity or even perform efficiency.
We will classify the permissions, first of all, usually have four types of access, browsing, modification, and delete, followed by the function, which may include, for example, all non-direct data access operations such as statistics, and we may also Some key data tables are restricted by the access of certain fields. In addition, I don't think there is another kind of authority category.
Perfect permission design should have sufficient scalability, that is, the system adds new other features that should not change the entire permissions management system. To achieve this, the first is reasonable database design, followed by Application interface specification.
Let's discuss the database design first. Usually we use a relational database that does not discuss the permission management based on Lotus products.
Permissions and related content can be described in six tables, as follows: 1 Role (ie, user group) table: including three fields, ID, role names, descriptions for this role; 2 user table: including three or more Field, ID, user name, description of the user, other (such as address, telephone, etc.); 3 Role-User Correspondence: This table records the correspondence between users and roles, one user can belong to multiple roles A role group can also have multiple users. Includes three fields, ID, role ID, user ID; 4 Limit content list: This table records all data sheets, functions, and fields that need to be permissions limitations and descriptions, including three fields, ID, name, description 5 Permissions list: This table records all permissions to be controlled, such as entry, modification, deletion, execution, etc., including three fields, ID, name, description; 6 permissions - role - user correspondent table: Under normal circumstances, We have the following privileges that have the permissions owned by the role / users, and the role has the permissions allowed by the characters. Other prohibits all permissions to which the user is inherited, and the permissions within this range are prohibited to allow all permissions, and the range external weight limit Allow all prohibited to be prohibited. The design is the focus of authority management, and there are many ideas for design. It can be said that there are thousands of autumn, and you can't give hard to say some way. In this regard, my opinion is to find someone to solve the problem.
First, the first kind is also the most easily understood method, design five fields: ID, limit content ID, permission ID, role / user type (Boolean field, used to describe a record record is role permission or user rights), Role / User ID, Permissions Type (Boolean field, is used to describe a record indication to be allowed or disabled)
Ok, there is such a table, according to Table 6, we can know that a role / user has / prohibits some permissions. Or, this is sufficient, we fully realize the features required: the role and the user must perform the right to qualify and have considerable scalability, such as adding new features, we only need to add one or a few The record can be recorded, and the application interface does not require changes, it has considerable feasibility. However, in the process of program implementation, we found that it is not very scientific, for example, when browsing the permissions owned by a user, you need to perform multiple times (even recursive) queries for the database, which is extremely inconvenient. So we need to think other ways. People who have used UNIX systems know that UNIX file systems divide three types of operations for files: read, write, and execution, with 1, 2, 4 three code identifiers, and files with read and write access to users The recorded 3, 1 2. We can also solve this problem with a similar approach. The initial idea is to modify the right list, add a field: identification code, for example, we can identify the entry permission to 1, browse the permission identifier is 2, the modification permission ID is 4, the delete permission ID is 8, the execution authority identifier is 16, In this way, we can easily put the permissions that the permission to be divided into several records will be placed together. For example, if a user ID is 1, the inventory table corresponds to 2, and the role is specified Type is 0, the user type is 1, we can have the user with an entry, browse, modify, and delete the inventory table. Description is: 2, 15, 1, 1.
It's really simple, isn't it? There is even more excited approach, and the list of restrictions is also plus a column, which defines the identification code, so that we can even describe all the privileges with all the contents of the entire content. Of course, the premise of this is that the limit is small, otherwise, huh, 2 N times have increasing, but quantity is amazing, not easy to analyze.
From the surface, the above method is sufficient to achieve functionality, simplifying the complexity of database design and implementation, but there is a drawback, the list of permissions we involve is not independent of each other but interdependence, such as modifying permissions, In fact, it includes browsing permissions, for example, we may just set the user's access value to the stock access to the entry modification delete (1 4 8 = 13), but in fact, the user has (1 2 4 8 = 15) permission, that is, in this scheme, 13 = 15. Then when we call the API asking if a user has a browsing permissions, it must be determined whether the user has a modification permission to the data table, so if the relationship is not included in the program, the application interface cannot be utilized. Simple judgment. But this is contradictory with our purpose "full scalability".
How to solve this problem? I thought of another way to set identification codes, that is, using the number of prime. We may wish to enter, browse, modify, delete, execute the basic flag code of 2, 3, 5, 7, 11, when we encounter permissions, we set its identification code to two (or Multiple) The product of the basic flag code, for example, the flag code of the "Modify" function can be set to 3 * 5 = 15, and then all permissions are multiplied, and the final permission identifier value we need is obtained. In this way, we only need to decompose the final value to the mass factor, for example, we can define a user with entry modification delete the stock of 2 * 15 * 7 = 2 * 3 * 5 * 7, that is, the user has an entry entry browsing modification delete permission. Of course, the premise of using the above method is the premise of the above method is that the number of permissions will not be too much and the relationship is not very complicated. Otherwise, the light is parsing the permission code to be machine flicker half-hour :)
I hope that the above analysis is correct and effective (in fact, I also use these methods in more than one system implementation), but in any case, I think this implementation rights management, just considering database design and application interface two Part of the content is still very difficult for implementation. Therefore, I invited comrades who have similar designs and experiences to make constructive opinions and revision recommendations.
In addition, the idea of database design is also using two-dimensional tables, which will be discussed in the later time, regarding the design and implementation of the application interface, I will also use the different levels and everyone to discuss, the code will use class C syntax Implement (I don't like Pascal, sorry)
Welcome friends and I contact me, Mailto: Berg@91search.com, also welcome to visit me with another friend: http://www.91search.com, there will be a tool software with a music search to download .
=========================================
About the permissions inclusive relationship is achieved by a role and permission mask.
///
///
If the user "Popeye" is read or written (read modification delete new) system object: (permission table is defined as the following tpermission) FormID uid permission ======= ===========================================================================2 Permissions such as "Calculation Differential Table" in the customs declaration system, are defined by the system), which is not too worrying, because all permissions are not possible in an Form, so system Custom permission masks can be reused in different forms. *****
It is recommended not to separate the roles and users to store (refer to the SYS_USERS table in MS-SQL Server), because the UID of this table is needed in the later permissions definition table (which can be user or role, SQL It is the use of the UID to distinguish the user and the role, the recommendation is also the same.) Because the role is just a user group, it should enjoy the user's permissions definition scope, and the role member of its subordinates (note that the role member is different from the user or role, it can be used for the role can also inherit its default Permissions unless the role member rejects the right to define its superiors. Let's give my related table definition:
Tuser (user or role table) ======================================= (100) NullMasterno varchar (255) NULL (Note: This field corresponds to employee numbers in the employee table. You can associate employees to which the user or role belongs through this field. Useful!)
Tmember (user and role table) ================================== (* pk) RoleId int not null (* pk) UserId int not null
Tpermission ================================= (* pk) Formid Int Not NULL (indicating the unique number of each module or form in the system) ( * PK) UserID int not null Protect Bit Not Null (1: Represents a grant permission; 0: Represents Dressing Permissions) Permission Int Not Null (permission mask and) **** If is it? Brothers intentionally study the permissions and process customizations, I believe that there is no mistake! ! ! Ha ha ~~~ Boss, give a minute ~~~~~ ×××××
=================================================================================================================================================================================
The above method is basically consistent with the method of the project I do, and a part of the table structure is summarized for your reference to create table t_workelement / * work element table * / (Code varchar (20) not null, / * element code, unique * / Name varchar (50) NOT NULL UNIQUE, / * Element Name, unique * / type int not null, / * Type 0- Document Operation 1 - Report Operation 2 - Function * / Bcode Varchar (20) NULL, / * Calibration document / report / function code * / style int NULL, / * Document: Type 0 - View 1 - Add 2- Modify 3 - Delete * / / * Report: No * / / * Function: None * / Term NText Null, / * Document: The conditions to be eliminated when viewing / modify / delete, such as "{$ contract. Number} = 12 / n {$ contract. Name} <> 'AFD'" * / primary Key (code)) godrop table t_rolegocreate table t_role / * role table * / (name varchar (30) not null, category varchar (50) null, remark varchar (100) null, primary key (name)) godrop table t_roleelementgocreate table t_roleelement / * Role elements Operation table * / (RNAME VARCHAR (30) Not null, / * Role Name * / ecode varchar (20) Not null, / * Element code * / primary key (RNAME, ECODE)) godrop table t_usersgocreate Table T_USERS / * User table * / (Name Varchar (20) Not null, / * User Name * / DCode Varchar (20) NOT NULL, / * The department belonging * / category varchar (50) NULL, / * Category * / Pswd varchar (15) null, / * Password * / primary key (name)) GO / * Insert System Administrator * / Insert Into T_Users (Name, DCOD E, Category, PSWD) VALUES ('Admini', 'System', 'Super User', '') Godrop Table T_UserrolegOcreate Table T_UserRole / * User Role Table * / (Uname Varchar (20) Not Null, / * User Name * / RNAME VARCHAR (30) NOT NULL, / * Role Name * / Primary Key (Uname, RNAME)) GoInsert INTO T_USERROLE (Uname, RNAME) VALUES ('Admini', 'System Administrator') Godrop Table T_Deptgocreate Table T_DEPT / * Department table * / (Code varchar (20) Not null, / * department code * / name varchar (50) Not null unique, / * department name * / Type varchar (10) NULL, / * Department Category Administration Warehouse shop * / subtype varchar (16) NULL, / * Subcategory finished warehouse raw material warehouse custom * / primary key (code)) GO / * Insert System Management * / Insert Into T_DEPT (Code, Name, Type) VALUES 'System', 'System Management', '
