SitePoint Sponsor

User Tag List

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

    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)
    Code SQL:
    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
    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`)
    ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

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

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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

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

    Code SQL:
    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
    Attached Images Attached Images
    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
  •