1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104 |
SELECT
theAro.model AS RequesterModel,
theAro.foreign_key AS RequesterId,
theAco.model AS ControlModel,
theAco.foreign_key AS ControlId,
permissions._create AS CanCreate,
permissions._read AS CanRead,
permissions._update AS CanEdit,
permissions._delete AS CanDelete
FROM
acos AS theAco
INNER JOIN
acos AS ruleAco ON (
ruleAco.lft <= theAco.lft AND ruleAco.rght >= theAco.rght)
INNER JOIN
aros_acos AS permissions ON (
permissions.aco_id = ruleAco.id)
INNER JOIN
aros AS ruleAro ON (
permissions.aro_id = ruleAro.id)
INNER JOIN
aros AS theAro ON (
theAro.lft >= ruleAro.lft AND theAro.rght <= ruleAro.rght)
WHERE
theAro.model = "$string" AND
theAro.foreign_key = $int AND
permissions._update != 0 /* AND
NOT EXISTS ( similar query, checking that the found rule is the bottom rule) */
****OR****
# Even more expensive but (probably) accurate
SELECT
*
FROM
acos AS theAco
WHERE
(SELECT
permissions._update
FROM
aros_acos AS permissions
INNER JOIN
acos AS ruleAco ON (
ruleAco.id = permissions.aco_id)
INNER JOIN
aros AS ruleAro ON (
permissions.aro_id = ruleAro.id)
INNER JOIN
aros AS theAro ON (
theAro.lft >= ruleAro.lft AND theAro.rght <= ruleAro.rght)
WHERE
ruleAco.lft <= theAco.lft AND
ruleAco.rght >= theAco.rght AND
theAro.model = "$string" AND
theAro.foreign_key = $int
ORDER BY
theAro.lft DESC, ruleAco.lft DESC
LIMIT 1
) = 1
****OR****
# List permissions per object FOR a specific USER. Remove WHERE clause FROM the main query TO find ALL permissions FOR a specific USER.
# Remove the theAro.model AND theAro.foreign_key conditions FROM the sub SELECT TO find ALL permissions FOR ALL users AND SET the db building an impossibly big temp TABLE :)
SELECT
theAco.id AS AcoId,
permissions.id AS RuleId,
permissions.aro_id AS RuleAroId,
permissions.aco_id AS RuleAcoId,
permissions._create AS CanCreate,
permissions._read AS CanRead,
permissions._update AS CanEdit,
permissions._delete AS CanDelete
FROM
acos AS theAco
INNER JOIN
aros_acos AS permissions ON (
permissions.id = (
SELECT
permissions.id
FROM
aros_acos AS permissions
INNER JOIN
acos AS ruleAco ON (
ruleAco.id = permissions.aco_id)
INNER JOIN
aros AS ruleAro ON (
permissions.aro_id = ruleAro.id)
INNER JOIN
aros AS theAro ON (
theAro.lft >= ruleAro.lft AND theAro.rght <= ruleAro.rght)
WHERE
ruleAco.lft <= theAco.lft AND
ruleAco.rght >= theAco.rght AND
theAro.model = "$string" AND
theAro.foreign_key = $int
ORDER BY
theAro.lft DESC, ruleAco.lft DESC
LIMIT 1
)
)
WHERE
permissions._update = 1 |
