Review Integration of Role Management Into Permission System

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;

Here is the next one I began working on. This one determines whether a user has permission to edit, delete or read a piece of content. programmatically a piece of content is referred to as a node and stored in the table MCP_NODES. The tricky part here is that two levels of permissions exist. The permission that may be assigned directly to the node and one that is assigned to the node type (content classification) that controls permissions of nodes (content) derived of that type. I also attached a screen of the explain.

  • the back-ticks are there for application variable replacement purposes

#stores content#
CREATE TABLE `MCP_NODES` (
  `nodes_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sites_id` bigint(20) unsigned NOT NULL,
  `authors_id` bigint(20) unsigned NOT NULL,
  `node_types_id` bigint(20) unsigned NOT NULL,
  `content_type` enum('html','php','text') NOT NULL DEFAULT 'html',
  `intro_type` enum('html','php','text') NOT NULL DEFAULT 'html',
  `node_published` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `node_url` varchar(128) NOT NULL,
  `node_title` varchar(128) NOT NULL,
  `node_subtitle` varchar(128) DEFAULT NULL,
  `node_content` longtext NOT NULL,
  `intro_content` 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 (`nodes_id`),
  UNIQUE KEY `node_url` (`node_url`,`sites_id`,`node_types_id`,`deleted`),
  KEY `sites_id` (`sites_id`),
  KEY `node_published` (`node_published`),
  KEY `authors_id` (`authors_id`),
  KEY `deleted` (`deleted`),
  KEY `node_types_id` (`node_types_id`)
) ENGINE=MyISAM AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;


SELECT
 	 b.nodes_id item_id #base item unique id#
 	 
 	 #can user delete node#
	 ,CASE 
			
		#user permission resolution (priority)#
		WHEN upe.`delete` IS NOT NULL
		THEN upe.`delete`
		
		WHEN b.`authors_id` = upe.users_id AND upe.`delete_own` IS NOT NULL
		THEN upe.`delete_own`
			      	
		WHEN b.`authors_id` = upp.users_id AND upp.`delete_own_child` IS NOT NULL
		THEN upp.`delete_own_child`
			      	
		WHEN upp.`delete_child` IS NOT NULL
		THEN upp.`delete_child`
		
		#role permission resolution#
		WHEN MAX(rpe.`delete`) IS NOT NULL
		THEN MAX(rpe.`delete`)
		
		WHEN MAX(rpe.`delete_own`) IS NOT NULL
		THEN MAX(rpe.`delete_own`)
			      	
		WHEN MAX(rpp.`delete_own_child`) IS NOT NULL
		THEN MAX(rpp.`delete_own_child`)
			      	
		WHEN MAX(rpp.`delete_child`) IS NOT NULL
		THEN MAX(rpp.`delete_child`)	
		
		#by default the creator of the node is allowed to delete it#
		WHEN b.`authors_id` = 10
		THEN 1
		
		#by default if user has no permissions to delete deny#
		ELSE
		0
			      
	END allow_delete
	
	#can the user edit node#		      
	,CASE 
			
		#user permission resolution (priority)#
		WHEN upe.`edit` IS NOT NULL
		THEN upe.`edit`
		
		WHEN b.`authors_id` = upe.users_id AND upe.`edit_own` IS NOT NULL
		THEN upe.`edit_own`
			      	
		WHEN b.`authors_id` = upp.users_id AND upp.`edit_own_child` IS NOT NULL
		THEN upp.`edit_own_child`
			      	
		WHEN upp.`edit_child` IS NOT NULL
		THEN upp.`edit_child`
		
		#role permission resolution#
		WHEN MAX(rpe.`edit`) IS NOT NULL
		THEN MAX(rpe.`edit`)
		
		WHEN MAX(rpe.`edit_own`) IS NOT NULL
		THEN MAX(rpe.`edit_own`)
			      	
		WHEN MAX(rpp.`edit_own_child`) IS NOT NULL
		THEN MAX(rpp.`edit_own_child`)
			      	
		WHEN MAX(rpp.`edit_child`) IS NOT NULL
		THEN MAX(rpp.`edit_child`)
			      
		#by default creator of node is allowed to edit it#
		WHEN b.`authors_id` = 10
		THEN 1
			 
		#deny edit for everyone else#
		ELSE
		0
			      
	END allow_edit	
	
	#can the user read node#		      
	,CASE 
			
		#user permission resolution (priority)#
		WHEN upe.`read` IS NOT NULL
		THEN upe.`read`
		
		WHEN b.`authors_id` = upe.users_id AND upe.`read_own` IS NOT NULL
		THEN upe.`read_own`
			      	
		WHEN b.`authors_id` = upp.users_id AND upp.`read_own_child` IS NOT NULL
		THEN upp.`read_own_child`
			      	
		WHEN upp.`read_child` IS NOT NULL
		THEN upp.`read_child`
		
		#role permission resolution#
		WHEN MAX(rpe.`read`) IS NOT NULL
		THEN MAX(rpe.`read`)
		
		WHEN MAX(rpe.`read_own`) IS NOT NULL
		THEN MAX(rpe.`read_own`)
			      	
		WHEN MAX(rpp.`read_own_child`) IS NOT NULL
		THEN MAX(rpp.`read_own_child`)
			      	
		WHEN MAX(rpp.`read_child`) IS NOT NULL
		THEN MAX(rpp.`read_child`)
		
		#by default author may read node#
		WHEN b.`authors_id` = 10
		THEN 1
			
		#by default everyone may read the node#
		ELSE
		1
			      
	END allow_read
			      
FROM
	`MCP_NODES` b #base entity table#
	
# user entity permission#
LEFT OUTER
JOIN
	MCP_PERMISSIONS_USERS upe #explicit user node permissions(highest precedence) - user(u) permission(p) entity(e)#
  ON
	b.nodes_id = upe.item_id
 AND
    upe.users_id = 10
 AND
    upe.item_type = 'MCP_NODES'
    
 #user entity parent permission#
 LEFT OUTER
 JOIN
     MCP_PERMISSIONS_USERS upp #explicit user node type permissions (parent permission) - user(u) permission(p) parent(p)#
   ON
     b.node_types_id = upp.item_id
  AND
     upp.users_id = 10
  AND
	 upp.item_type = 'MCP_NODE_TYPES'

  # entity role permission#	 
  LEFT OUTER
  JOIN
     MCP_USERS_ROLES u2r #roles user has been assigned to - for entity role permission resolution#
    ON
     u2r.users_id = 10
  LEFT OUTER
  JOIN
     MCP_ROLES r #roles - resolving entity role permission#
    ON
      u2r.roles_id = r.roles_id
   AND
      r.deleted = 0
  LEFT OUTER
  JOIN
	  MCP_PERMISSIONS_ROLES rpe #role(r) permission(p) entity(e)#
	ON
      rpe.item_type = 'MCP_NODES'
    AND
	  b.nodes_id = rpe.item_id
    AND
      r.roles_id = rpe.roles_id
      
   # parent role permission#
   LEFT OUTER
   JOIN
      MCP_USERS_ROLES u2r2 #roles users has been assigned to - for parent role permission resolution#
     ON
      u2r2.users_id = 10
   LEFT OUTER
   JOIN
      MCP_ROLES r2 #roles - resolving parent entity role permission#
     ON
      u2r2.roles_id = r2.roles_id
    AND
      r2.deleted = 0
   LEFT OUTER
   JOIN
      MCP_PERMISSIONS_ROLES rpp #role(r) permission(p) parent(p)#
     ON
      rpp.item_type = 'MCP_NODE_TYPES'
    AND
      b.node_types_id = rpp.item_id
    AND
      r2.roles_id = rpp.roles_id
	 
 WHERE
	  b.nodes_id IN (1,2,3,4)
 GROUP
    BY
      b.nodes_id