MSSQL library design table: Users User table fields: userid, username, userpermissionRoles role table fields: roleid, rolename, rolepermissionUserInRole user roles correspondence table field: userid, roleidPermissionList permissions list field: permissionid, permissionDescription, permissionGroup
Permission design: license, prohibiting and not set three states, allow, deny, not set
Target: Implement user permissions. First define role permissions, multiple-to-many relationships between users and roles. User permissions inherit from role permissions. Situation 1: When the user belongs, the minimum permissions are minimized, that is, the permission role A license, the role B is disabled, the permission is disabled. Situation 2: When the role to which the user belongs, the NOTSET state is not set, the permissions are in the case. Three: When the role to which the user belongs to a permission, it can also set this permission to be disabled.
Function: Setting User Permissions: By default, user permissions inherit the permissions of the role can be set separately to set a user's permission extension permission definition to increase at any time, and can group. When the permissions are added, the default role permissions are not set.
Question: 1. How do you set the field type of the USERPERMISSION and ROLEPERMISSION field in the MSSQL database? 2, how to access user permissions? Especially when the user belongs to multiple characters, how to efficiently set the problem of user permissions
Netizen lidong6 (Lon Winter) Reply:
Already very good, now a lot of permissions are like this. However, you can consider using multiple levels for permission tables. Not just a group.
Netizen MYLF (not sleeping fish) Reply:
For the case of permission to set a user separately, you can complete this user as a private role (ie, there is a role of the username in the Roles table, which only belongs to this user), this, You don't have to distinguish whether the user is still a role, and unity is handled in roles.
The design of the data sheet should only be defined in what kind of permissions, so there should be another table, store the table of Permission and Roles, such as PermissionSetting, where the field is Permissionid, RoleId. So, UserPermission, These fields are unwanted by Rolepermission.
Get the user rights, depending on the association of the user and role, then lookup in Permissionstting, it is not difficult. My own reply
My idea is this, using a user management system as an example permissionList as follows: PermissionID, PermissionDescription, PermissionGroup1, adduser, User Management Group 2, DELUSER, User Management Group 3, Edituser, User Management Group 4, AddRole, Role Management 5, DELROLE, Role Management 6, Editrole, Role Management 7, Addmember, Role Management 8, RemoveMember, Role Management
Define an account administrator in the role. This role is specifically used to add / delete the user's permission is 1, 2, 3 license, the other does not set another role administrator, the permissions are 4, 5, 6, 7, 8 is permission, other not set. If the user A belongs to two roles, all permissions I originally thought is to set the USERPERMISSION to the binary field, the existence of long binary numbers. Permissions can be taken according to the bit. Such as account management The role weight limit of the member is 11100000, the role administrator's role value is 0001111, the permission of the user A is 11111111, but the value of the two roles is 11111111. However, the programming implementation seems to be very inconvenient. And If the binary field, you should save the SQL database as a binary number as a binary, it seems that it is not so easy. Because the string of 0101 is turned into an int type, the big point is the Bigint type, subject to its size Restrictions, the length of the 01 string cannot exceed 30 digits. This is the permissions definitions not more than 30. So I don't know how to do it. Retreat, when the role you belong to the user, or the amount of operation may be relatively large, possibly It will affect the program performance. Of course, when the user permission is inherited from the role permission, only one or operates, then stores its weight limit into the userpermission, which may enhance a little performance. Reference:
Resource definition of RBAC permission system
Http://forum.javaeye.com/viewtopic.php?t=15953&sid=7cc2af0d4ba7bc79461d5bf4d4a96e7f
