SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,187
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)

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

    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

    Code SQL:
    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;

    Code SQL:
    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;
    Attached Images Attached Images
    The only code I hate more than my own is everyone else's.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,187
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937
    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
    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.

    Quote Originally Posted by r937
    also, what drives this query? i see ur.users_id = 10 as well as pr.item_type LIKE 'cfg:%'
    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.

    Quote Originally Posted by r937
    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
    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.
    The only code I hate more than my own is everyone else's.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,187
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    no problem

    Permissions can be tricky subject.

    Here is some more background if someone is so inclined.
    The only code I hate more than my own is everyone else's.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •