Optimization For Permissions That Belong to Users and Roles For Ind. Entity Fields

Databases
1

The query provided below is responsible for resolving permissions to edit and read individual configuration fields. However, in its current state both file sort and temporary tables are being used. The tricky part of this is that user permissions trump role permissions. Therefore, when a user permission exists it needs to override any role permissions that can exist. So I was wondering if there is an alternative way to produce the same result set but avoiding temporary tables and/or file sort (ideally both) or I’m sh*t out of luck.

Thanks

  • Table definitions follow query and explain is provided as an image attachment

Thanks


SELECT
       p.item_type item_id
      ,COALESCE(MAX(p.edit),0) allow_edit
      ,COALESCE(MAX(p.`read`),0) allow_read
   FROM
      (SELECT
            'user_perm' `type`
            ,pu.item_type
            ,NULL has_user_perm
            ,pu.edit
            ,pu.`read`
         FROM
            MCP_PERMISSIONS_USERS pu
        WHERE
            pu.users_id = 10
          AND
            pu.item_type LIKE 'cfg:%'
          AND
            pu.item_id = 0
    UNION ALL
       SELECT
            'role_perm'
            ,pr.item_type
            ,CASE
                WHEN pu.permissions_id IS NULL
                THEN 1
          
                ELSE
                0
             END
            ,CASE
                WHEN pu.edit IS NOT NULL
                THEN pu.edit
          
                ELSE
                MAX(pr.edit)
             END
            ,CASE
                WHEN pu.`read` IS NOT NULL
                THEN pu.`read`
          
                ELSE
                MAX(pr.`read`)
             END
         FROM
            MCP_PERMISSIONS_ROLES pr
        INNER
         JOIN
            MCP_ROLES r
           ON
            pr.roles_id = r.roles_id
          AND
            r.deleted = 0
        INNER
         JOIN
            MCP_USERS_ROLES ur
           ON
            r.roles_id = ur.roles_id
          AND
            ur.users_id = 10
       INNER
        JOIN
           MCP_PERMISSIONS_USERS pu
          ON
           ur.users_id = pu.users_id
         AND
           pr.item_id = pu.item_id
         AND
           pr.item_type = pu.item_type
       WHERE
           pr.item_type LIKE 'cfg:%'
         AND
           pr.item_id = 0
       GROUP
          BY
           pr.item_type) p
       WHERE
           p.`type` = 'user_perm'
          OR
          (p.`type` = 'role_perm' AND p.has_user_perm = 0)
 GROUP
    BY
     p.item_type;



CREATE TABLE `MCP_PERMISSIONS_USERS` (
  `permissions_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `users_id` bigint(20) unsigned NOT NULL,
  `item_type` varchar(56) NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `add` tinyint(3) unsigned DEFAULT NULL,
  `edit` tinyint(3) unsigned DEFAULT NULL,
  `delete` tinyint(3) unsigned DEFAULT NULL,
  `read` tinyint(3) unsigned DEFAULT NULL,
  `add_own` tinyint(3) unsigned DEFAULT NULL,
  `edit_own` tinyint(3) unsigned DEFAULT NULL,
  `delete_own` tinyint(3) unsigned DEFAULT NULL,
  `read_own` tinyint(3) unsigned DEFAULT NULL,
  `add_child` tinyint(3) unsigned DEFAULT NULL,
  `edit_child` tinyint(3) unsigned DEFAULT NULL,
  `delete_child` tinyint(3) unsigned DEFAULT NULL,
  `read_child` tinyint(3) unsigned DEFAULT NULL,
  `add_own_child` tinyint(3) unsigned DEFAULT NULL,
  `edit_own_child` tinyint(3) unsigned DEFAULT NULL,
  `delete_own_child` tinyint(3) unsigned DEFAULT NULL,
  `read_own_child` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`permissions_id`),
  UNIQUE KEY `item_type` (`item_type`,`item_id`,`users_id`),
  KEY `users_id` (`users_id`),
  CONSTRAINT `mcp_permissions_users_ibfk_1` FOREIGN KEY (`users_id`) REFERENCES `mcp_users` (`users_id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

CREATE TABLE `MCP_PERMISSIONS_ROLES` (
  `permissions_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `roles_id` bigint(20) unsigned NOT NULL,
  `item_type` varchar(56) NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `add` tinyint(3) unsigned DEFAULT NULL,
  `edit` tinyint(3) unsigned DEFAULT NULL,
  `delete` tinyint(3) unsigned DEFAULT NULL,
  `read` tinyint(3) unsigned DEFAULT NULL,
  `add_own` tinyint(3) unsigned DEFAULT NULL,
  `edit_own` tinyint(3) unsigned DEFAULT NULL,
  `delete_own` tinyint(3) unsigned DEFAULT NULL,
  `read_own` tinyint(3) unsigned DEFAULT NULL,
  `add_child` tinyint(3) unsigned DEFAULT NULL,
  `edit_child` tinyint(3) unsigned DEFAULT NULL,
  `delete_child` tinyint(3) unsigned DEFAULT NULL,
  `read_child` tinyint(3) unsigned DEFAULT NULL,
  `add_own_child` tinyint(3) unsigned DEFAULT NULL,
  `edit_own_child` tinyint(3) unsigned DEFAULT NULL,
  `delete_own_child` tinyint(3) unsigned DEFAULT NULL,
  `read_own_child` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`permissions_id`),
  UNIQUE KEY `item_type` (`item_type`,`item_id`,`roles_id`),
  KEY `roles_id` (`roles_id`),
  KEY `roles_id_2` (`roles_id`,`item_id`),
  CONSTRAINT `mcp_permissions_roles_ibfk_1` FOREIGN KEY (`roles_id`) REFERENCES `mcp_roles` (`roles_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `MCP_USERS_ROLES` (
  `users_roles_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `users_id` bigint(20) unsigned NOT NULL COMMENT 'mcp_user foreign key',
  `roles_id` bigint(20) unsigned NOT NULL COMMENT 'mcp_role foreign key',
  PRIMARY KEY (`users_roles_id`),
  UNIQUE KEY `users_id` (`users_id`,`roles_id`),
  KEY `roles_id` (`roles_id`),
  KEY `users_id_2` (`users_id`),
  CONSTRAINT `mcp_users_roles_ibfk_1` FOREIGN KEY (`roles_id`) REFERENCES `mcp_roles` (`roles_id`),
  CONSTRAINT `mcp_users_roles_ibfk_2` FOREIGN KEY (`users_id`) REFERENCES `mcp_users` (`users_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `MCP_ROLES` (
  `roles_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sites_id` bigint(20) unsigned NOT NULL COMMENT 'site that the role belongs to. System created roles will be null.',
  `creators_id` bigint(20) unsigned DEFAULT NULL COMMENT 'user that created the role',
  `pkg` varchar(128) NOT NULL DEFAULT '' COMMENT 'package that the role belongs to',
  `system_name` varchar(128) NOT NULL COMMENT 'Unique name of role within site',
  `human_name` varchar(128) NOT NULL COMMENT 'Unique label/title of role within site',
  `description` longtext COMMENT 'descrption of role',
  `updated_on_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_on_timestamp` timestamp NULL DEFAULT NULL,
  `deleted_on_timestamp` timestamp NULL DEFAULT NULL,
  `deleted` tinyint(3) unsigned DEFAULT '0' COMMENT '0 means role has not been deleted and NULL means that is has been deleted',
  PRIMARY KEY (`roles_id`),
  UNIQUE KEY `sites_id` (`sites_id`,`pkg`,`system_name`,`deleted`),
  UNIQUE KEY `sites_id_2` (`sites_id`,`pkg`,`human_name`,`deleted`),
  KEY `sites_id_3` (`sites_id`),
  KEY `roles_id` (`roles_id`,`deleted`),
  KEY `creators_id` (`creators_id`),
  CONSTRAINT `mcp_roles_ibfk_1` FOREIGN KEY (`sites_id`) REFERENCES `mcp_sites` (`sites_id`),
  CONSTRAINT `mcp_roles_ibfk_2` FOREIGN KEY (`creators_id`) REFERENCES `mcp_users` (`users_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
2

could you take a moment please and describe in words the purpose of, and relationships between the 4 tables in the second SELECT in the UNION

also, what drives this query? i see ur.users_id = 10 as well as pr.item_type LIKE ‘cfg:%’

other things i don’t understand are pr.item_id = 0 and why there would be NULLs in the pu columns in the CASE expressions

3

The table your not seeing here is the table MCP_USERS. That table is a generic user storage table containing credentials for registered users, username, etc. Every user can than belong to a role. The table that contains that m:n relationship is MCP_USERS_ROLES. This is the table that contains the roles which a user belongs to. So the other side of that is the roles tables MCP_ROLES. Every role in the system can have permissions assigned. So what a user is allowed to do is based upon the permissions that have been assigned to roles which they belong to. The table that contains a roles permissions is MCP_PERMISSIONS_ROLES.

Permissions themselves are a little tricky but I will try to explain my test. Both the MCP_PERMISSIONS_USERS and MCP_PERMISSIONS_ROLES have the exact same structure. Except one has a foreign key reference to a user and the other a role. The most important thing to note is that the columns item_type and item_id is polymorphic in nature. However, in the case of individual fields which have a name instead of an ID (primary key) it is merely just set 0. Considering the pattern for configuration fields in this case is item_type starts with cfg:. So an example of an actual value for item_type could be: cfg:site_sidebar_color. In which case the permission would determine whether a user is a allowed to read or change the “site sidebar color” field for the configuration of a site.

So going to more into it users themselves can either be assigned to roles (MCP_PERMISSIONS_ROLES) or directly to permissions (MCP_PERMISSIONS_USERS). Any given user can be assigned to multiple roles. In this case of overlapping permissions for any given item due to multiple role assignments the maximum value is taken of all roles which the user belongs to. However, the edge case here is that users can also be assigned directly to permissions rather than just indirectly through roles. Permissions assigned directly to a user via MCP_PERMISSIONS_USERS will always have priority over any equivalent roles permissions that indirectly are assigned to the user through MCP_PERMISSIONS_ROLES.

So the union grouped by item provides a fallback for permissions in both directions. In the case that user permissions do not exist for a given item the fallback is a role permission than the default in this case 0 (false). When a user permission does exist it will be used. There is a little bit of an edge case here though. In this example there are two permissions: read and edit. Each of which can be spread out out between user and role permissions. So in the case that a user is assigned to a role that has read set to 0 but is also assigned directly to edit set to 1 the final result would need to be read = 0 and edit = 1. That is where the MAX comes into play.

10 would be swapped out with the id of the user which permissions are being requested. The like portion is to only select permissions for the config (cfg) entity. For example, since we re talking field level permissions valid examples of that actual value could be: cfg:site_logo, cfg:site_title, cfg:recaptch_on, etc. The whole purpose is to determine whether a user has permission to change individual configuration fields. Think of it like this. I may want to grant a normal user permissions to change the logo but not anything that would potentially break the application such as; path to CSS or JS files ( which is a configuration option in my system). The whole idea to have complete control over every configuration field. A purpose might be so that I can create a “Dev” role and assign permissions to it that allow changing things like configuration paths. Whereas, I could also create a role form normal users and assign permissions to merely modify the logo and site title but prevent them from fiddling with other options that they shouldn’t be allowed to touch.

item_id = 0 is merely a placeholder. I’m sure you can tell by now that this is pretty generic and that is done for a reason. For the majority of my application item_type represents a table and item_id referenced a row by primary key. The columns item_type and item_id make up a virtual foreign key through polymorphism. Which I know is an anti-pattern but that is something I got over when I initially developed this concept due to the advantage of isolating permissions to two tables. In addition to being able to adapt the model to do just about anything such as; field level permissions as seen here.

For most the entities within my system this structure makes it possible to easily manage permissions for a three tier structure regardless of context. That was the initial focus. If you want more back ground on that I can provide it but will leave it out considering this problem is a whole separate beast.

It is probably a long catch to have anyone understand this but thought I would at least try.

4

i’ve given this a serious attempt, but it’s pretty much beyond my comprehension, sorry

i think it was the polymorphism which did it

5

no problem

Permissions can be tricky subject.

Here is some more background if someone is so inclined.