I am in the process of updating a bunch of similar queries with support for roles in a permission management system. Prior to today my permission system has lacked support for role based permission management. Instead permissions were based strictly on direct user assignment. That was a fine for the time being knowing I was going to eventually add role support. Having said that the create statements for the tables of concern are defined below.
A brief overview of how this all works is probably necessary to understand the entire business and domain aspect of the resolving the problem. In short the table MCP_NODE_TYPES represents a collection of content classifications. The end query needs to determine whether the current logged-in user is allowed to create a piece of content of the specified type(s) determined by the ending where clause ie. b.node_types_id IN (x,y,z).
Having said that there are two major parts of permission system. One of those parts is permissions that have been directly assigned to a user which is represented by the table MCP_PERMISSIONS_USERS. The other part is m:n relationship made up of the tables MCP_ROLES, MCP_USERS_ROLES AND MCP_PERMISSIONS_ROLES. The first of which contains all roles, second is the look-op table and third contains the permission settings for a given role.
Both the MCP_PERMISSIONS_* tables have the exact same structure except for a foreign key that references either a user or a role. With that said, each table contains two columns that determine the entity a permission is assigned. Those two columns are: item_type and item_id. The item_type column essentially references a table name and item_id the primary key of a row within that table. The design polymorphic so that all permissions can be stored in the either the user or role permission tables.
Having said that the below query is what I have at this point to determine whether the current user is allowed to create content of a specified type. I just want to know if I’m on the right track here because I have several other similar yet contrasting queries that need to have the role system introduced. So before I go and modify those I would like some input in regards to a more optimized or simple? method to achieve my goal.
Thanks
Node Type (content classifications)
CREATE TABLE `MCP_NODE_TYPES` (
`node_types_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sites_id` bigint(20) unsigned NOT NULL,
`creators_id` bigint(20) unsigned DEFAULT NULL COMMENT 'May be created by system',
`pkg` varchar(128) NOT NULL DEFAULT '',
`system_name` varchar(128) NOT NULL,
`human_name` varchar(128) NOT NULL,
`theme_tpl` varchar(255) DEFAULT NULL,
`description` longtext,
`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',
PRIMARY KEY (`node_types_id`),
UNIQUE KEY `sites_id` (`sites_id`,`pkg`,`system_name`,`deleted`),
UNIQUE KEY `sites_id_2` (`sites_id`,`pkg`,`human_name`,`deleted`),
KEY `creators_id` (`creators_id`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
Users Permissions
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`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
Roles Management Table Collection
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`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM 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`)
) ENGINE=MyISAM 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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query
SELECT
b.node_types_id item_id #the generic entity id#
,CASE
#user permissions have precedence over roles. Determoine whether user has explicit setting to create a node of the type#
WHEN pu.add_child IS NOT NULL
THEN pu.add_child
#user permissions have precedence over roles. Determine whether user has explicit setting to create node of a type they created#
WHEN b.creators_id = pu.users_id AND pu.add_own_child IS NOT NULL
THEN pu.add_own_child
#determines whether user is assigned to role that has settings for creating node of type#
WHEN pr.add_child IS NOT NULL
THEN pr.add_child
#Determines whether user is assigned to role that has settings for creating node of a type that they created#
WHEN pr.add_own_child IS NOT NULL
THEN pr.add_own_child
#by default creator of node type can create nodes of that type#
WHEN b.creators_id = 10
THEN 1
#When nothing has been matched deny creation of node of specified type#
ELSE
0
END allow_add
FROM
MCP_NODE_TYPES b #base table entity#
LEFT OUTER
JOIN
MCP_PERMISSIONS_USERS pu #current logged-in users explicit permission settings#
ON
pu.item_type = 'MCP_NODE_TYPES' #base entity type#
AND
b.node_types_id = pu.item_id #base entity primary key#
AND
pu.users_id = 10 #current user primary key#
LEFT OUTER
JOIN
MCP_USERS_ROLES u2r #roles that the current user is assigned to. This is the look-up table that assigns a role to a user#
ON
u2r.users_id = 10
LEFT OUTER
JOIN
MCP_ROLES r
ON
u2r.roles_id = r.roles_id
AND
r.deleted = 0 #ignore roles that have been deleted ie. when deleted is null the role has beeen deleted#
LEFT OUTER
JOIN
MCP_PERMISSIONS_ROLES pr #permission settings for the roles that the current user has been assigned to#
ON
pr.item_type = 'MCP_NODE_TYPES' #base entity type#
AND
b.node_types_id = pr.item_id #base entity primary key#
AND
r.roles_id = pr.roles_id #role#
WHERE
b.node_types_id IN (1,2,3,4)
GROUP
BY
b.node_types_id;