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;