#1
  1. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4124

    Ordering a dataset by the values in the first row of that dataset


    I am designing an access control list feature for my php+mysql web app and trying to make it as automatic as possible.

    I think I've got a proof of concept of how the ACL should work, but I'm struggling to optimise my queries

    The concept of the automation is to allow for conflicting rules, and then order the rules depending on how important the rules are, then reorder the rules based on information held in the most important rule.

    [In this case, a rule weight of 0 is more important that weights with higher numbers...think "inverse weight"]

    So what I have so far may return something like this:

    This represents a default rule of allow delete, then a user (weight 1) protecting his/her resource group and then an admin (weight 0) allowing a specific resource to be deleted by anyone

    Code:
    +---------------+-----------------------------------------------+-------+----------------+------+---------------+---------------+-------------------------------------+
    |role 		|policy						|weight |limit_type 	 |action|allow_deny 	|group_name 	|resource			      |
    +---------------+-----------------------------------------------+-------+----------------+------+---------------+---------------+-------------------------------------+
    |General 	|Global>Resource>User>Resource Group>User Group |0 	|Resource 	 |DELETE|ALLOW 		|NULL		|7d81aa2e-f385-11e1-8ca6-80c16eeeb2ad |
    |Prevent Delete |Global>Resource Group>User Group>Resource>User |1 	|Resource Group  |DELETE|DENY 		|protect mine 	|7d81aa2e-f385-11e1-8ca6-80c16eeeb2ad |
    |General 	|User>Resource>User Group>Resource Group>Global |9999 	|Resource Group  |DELETE|ALLOW 		|company 	|7d81aa2e-f385-11e1-8ca6-80c16eeeb2ad |
    +---------------+-----------------------------------------------+-------+----------------+------+---------------+---------------+-------------------------------------+
    (ordered by weight ASC)

    I then want to order the same result set but on the limit_type column and the order should follow the (reverse) order of the policy template. At present I have copied the original query but changed the order by to this

    Code:
    ORDER BY
    	CASE
    		acl.limit_type 
    			WHEN 'User Group' THEN 1
    			WHEN 'Resource Group' THEN 2
    			WHEN 'User' THEN 3
    			WHEN 'Resource' THEN 4
    			WHEN 'Global' THEN 5
    	END
    This then gives a result set looking like this:

    Code:
    +---------------+-----------------------------------------------+-------+----------------+------+---------------+---------------+-------------------------------------+
    |role 		|policy						|weight |limit_type 	 |action|allow_deny 	|group_name 	|resource			      |
    +---------------+-----------------------------------------------+-------+----------------+------+---------------+---------------+-------------------------------------+
    |General 	|User>Resource>User Group>Resource Group>Global |9999 	|Resource Group  |DELETE|ALLOW 		|company 	|7d81aa2e-f385-11e1-8ca6-80c16eeeb2ad |
    |Prevent Delete |Global>Resource Group>User Group>Resource>User |1 	|Resource Group  |DELETE|DENY 		|protect mine 	|7d81aa2e-f385-11e1-8ca6-80c16eeeb2ad |
    |General 	|Global>Resource>User>Resource Group>User Group |0 	|Resource 	 |DELETE|ALLOW 		|NULL		|7d81aa2e-f385-11e1-8ca6-80c16eeeb2ad |
    +---------------+-----------------------------------------------+-------+----------------+------+---------------+---------------+-------------------------------------+
    The system then loops over this set setting the action of "delete" to ALLOW or DENY based on the allow_deny column

    [the reason this loops and flip flops between states is that the last row may not always contain an explicit action]

    So, my question is this: Can I sensibly combine the two queries into one?

    [The policy field is just a varchar string and does not have to be separated by ">", so i could reformat this column if necessary]
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Sometimes wheels need reinventing, but are you sure that this is such an occasion?
  4. #3
  5. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4124
    Originally Posted by cafelatte
    Sometimes wheels need reinventing, but are you sure that this is such an occasion?
    Yes.

    My experience with administering ACLs in other web apps as an administrator almost requires a degree in computer science. I'm building software to sell to people who are not necessarily computer literate.

    Permissions need to work, sensibly and quickly
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Cool!

    I'm far from expert - and I'm not trying to do you out of a living - but the free example provided here looks pretty easy to use to me!
  8. #5
  9. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4124
    Originally Posted by cafelatte
    Cool!

    I'm far from expert - and I'm not trying to do you out of a living - but the free example provided here looks pretty easy to use to me!
    Not bad for your personal blog but lets take

    [the extra R in CRRUD below is for two types of read: read in list view (overview) and read full details]

    26 possible actions (CRRUD for parent types, CRRUD for child types, grant and request CRRUD for parent and child types, revoke CRRUD for parent and child types)
    30 parent resource types
    21 child resource types
    500 users

    and the requirement that no one should really see a permissions matrix.

    yes, there will be matrix screens of some sort, but I'm aiming to keep these to a minimum with the use of modules (groups of resource types), resource groups (manual and automatic) and user groups (manual and automatic)
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]

IMN logo majestic logo threadwatch logo seochat tools logo