Hi folks.
Im using MySQL 4.1 and Im having trouble figuring out a (recursive) select query.
I want to select all pictures, for which the given user has the required accesslevel.
Ive got tables (can attach an sql or image to describe structure better if it helps) :
aro -> contains users and groups and their unique id (access request object)
contain cols: id + name + type
arogrpmap -> binds a user to a group or group to a group (tree like)
contain cols: gid (aro.id) + uid (aro.id) (a bit misplaced, i know)
aco -> all "material" has an aco, an aco has a owner (aro.id) (access control object)
contain cols: id + owner (aro.id) + approved
acomap -> for material rights for other users than owner
contain cols: aco_id (aco.id) + aro_id (aro.id) + permissions
pictures -> for pictures (material)
contain cols: id + name + aco_id (aco.id)
so, in text I want this:
to select cols from pictures where either aco.owner equals uid or (pictures.aco_id equals acomap.id and acomap.permissions equals defined or higher in one of the aro.ids, but not if if any of those permissions equals 0).
Piece of cake..almost! this works and finds pictures where the aro (or one of its groups) has permission 2 (write):
SELECT t.id, t.name, t.aco_id FROM tblPictures t
INNER JOIN tblACO a ON (t.aco_id=a.id)
LEFT JOIN tblACOMap m ON (t.aco_id=m.aco_id)
WHERE
(a.approved=0)
AND
((a.owner=6) OR (m.permissions & 2 AND m.aro_id IN (6,5,4,2)))
The problem comes with the default policy of deny along with inheritance;
aro_id 4 or 2 is denied access: set to 0 in permission column.
aro_id 5 got no entry.
Erm, the hour is late, Im confused and tired and not really sure if Ive got a problem here.. Anyway, I dont want to dump this thread yet, so consider this 'On Hold'.....
