MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Databases > MySQL Help
Reload this Page

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


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old September 3rd, 2012, 04:25 AM
Northie's Avatar
Northie Northie is offline
Square Peg in a Round Hole
Click here for more information.
  
Join Date: Oct 2007
Location: North Yorkshire, UK
Posts: 3,420 Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 10 h 50 m 32 sec
Reputation Power: 3896
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]
__________________
PHP OOPS! <?php DB::Execute(SQL::makeFrom($_GET))->fetchArray()->FormatWith(Template::getInstance('default'))->printHtml(); ?>

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 ]
Reply With Quote
  #2  
Old September 3rd, 2012, 05:02 AM
cafelatte cafelatte is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
  
Join Date: Mar 2008
Posts: 1,923 cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377
Sometimes wheels need reinventing, but are you sure that this is such an occasion?
Reply With Quote
  #3  
Old September 3rd, 2012, 08:10 AM
Northie's Avatar
Northie Northie is offline
Square Peg in a Round Hole
Click here for more information.
  
Join Date: Oct 2007
Location: North Yorkshire, UK
Posts: 3,420 Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 10 h 50 m 32 sec
Reputation Power: 3896
Quote:
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
Reply With Quote
  #4  
Old September 3rd, 2012, 08:49 AM
cafelatte cafelatte is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
  
Join Date: Mar 2008
Posts: 1,923 cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377
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!
Reply With Quote
  #5  
Old September 3rd, 2012, 10:18 AM
Northie's Avatar
Northie Northie is offline
Square Peg in a Round Hole
Click here for more information.
  
Join Date: Oct 2007
Location: North Yorkshire, UK
Posts: 3,420 Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level)Northie User rank is General 44th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Weeks 5 Days 10 h 50 m 32 sec
Reputation Power: 3896
Quote:
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)
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MySQL Help > Ordering a dataset by the values in the first row of that dataset

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap