Queries mbt rechtensysteem
SQL Export van de structuur en wat testdata Edit: voor de volledigheid, ik gebruik vandaag MySQL 5.0
Het idee is dat je een gebruiker in groepen kan indelen (meer op meer relatie) en dat deze groepen weer relaties hebben met de privileges. Daarnaast kan je per gebruiker ook nog relaties met privileges opzetten, welke de relaties van de groepen moeten overrulen.
Dus stel dat ik wil bekijken of een bepaalde gebruiker een bepaalde privilege heeft dan volgt er zo'n soort constructie. Waarde is een 1 of 0, of een gebruiker beschikt over een privilege of niet:
1) als er een relatie tussen de gebruiker en de privilege bestaat, neem dan die waarde
2) anders als er een relatie tussen een van de groepen van de gebruiker bestaat, en de waarde is 1 (waar) dan nemen we die waarde (waar)
3 anders nemen we waarde 0 (niet waar)
Code (php)
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
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
Invoer:
@user_id
@privilege_name
0) Zoek privilege id erbij
SELECT
privileges.id as @privilege_id
FROM
privileges
WHERE
privilege.name = @privilege_name
1) Als
SELECT
privileges_users_relations.allowed
FROM
privileges_users_relations
WHERE
privileges_users_relations.user_id = @user_id
AND privileges_users_relations.privilege_id = @privilege_id
Dan geeft privileges_users_relations.allowed terug
2) Anders als
SELECT
'1'
FROM
privileges_groups_relations
WHERE
privileges_groups_relations IN(
SELECT
groups_users_relations.id
FROM
groups_users_relations
WHERE
groups_users_relations.user_id = @user_id
)
AND privileges_groups_relations.privilege_id = @privilege_id
Dan geef 1 terug
3) Anders geef 0 terug.
@user_id
@privilege_name
0) Zoek privilege id erbij
SELECT
privileges.id as @privilege_id
FROM
privileges
WHERE
privilege.name = @privilege_name
1) Als
SELECT
privileges_users_relations.allowed
FROM
privileges_users_relations
WHERE
privileges_users_relations.user_id = @user_id
AND privileges_users_relations.privilege_id = @privilege_id
Dan geeft privileges_users_relations.allowed terug
2) Anders als
SELECT
'1'
FROM
privileges_groups_relations
WHERE
privileges_groups_relations IN(
SELECT
groups_users_relations.id
FROM
groups_users_relations
WHERE
groups_users_relations.user_id = @user_id
)
AND privileges_groups_relations.privilege_id = @privilege_id
Dan geef 1 terug
3) Anders geef 0 terug.
Ik heb de stapjes al gezet, maar weet niet hoe ik een geheel ervan moet maken. Het mooiste zou een enkele query (eventueel met subqueries) zijn, of een FUNCTION/PROCEDURE
Ander probleem is dat ik voor het beheer een overzicht, bijvoorbeeld een matrix wil maken van alle groepen, en of ze een privilege hebben of niet. Horizontaal moeten dan alle privileges komen, en verticaal alle groepen. Nu bestaan er alleen relaties tussen groepen en privileges waarvan de groep de privilege wel heeft. Hier heb ik inmiddels wel een query voor, maar echt efficient lijkt hij niet. Wat vinden jullie ervan?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
privileges.name,
groups.name,
COUNT(privileges_groups_relations.id)
FROM
privileges
LEFT JOIN
groups ON 1=1
LEFT JOIN
privileges_groups_relations ON
privileges_groups_relations.privilege_id = privileges.id
AND privileges_groups_relations.group_id = groups.id
GROUP BY
CONCAT(privileges.id, '-', groups.id)
privileges.name,
groups.name,
COUNT(privileges_groups_relations.id)
FROM
privileges
LEFT JOIN
groups ON 1=1
LEFT JOIN
privileges_groups_relations ON
privileges_groups_relations.privilege_id = privileges.id
AND privileges_groups_relations.group_id = groups.id
GROUP BY
CONCAT(privileges.id, '-', groups.id)
Gewijzigd op 01/01/1970 01:00:00 door Jelmer -
Je geeft de volgende query:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT
privileges.id as @privilege_id
FROM
privileges
WHERE
privilege.name = @privilege_name
privileges.id as @privilege_id
FROM
privileges
WHERE
privilege.name = @privilege_name
Als ik zo even in je script kijk zie ik geen unique (a.k.a. alternative key) op privilege.name wat betekend dat je hier nogal rare resultaten kan verwachten.
Maar waarom maak je eigenlijk niet van privilege.name de primary key? Waarom al die id kolomen, wat is daar de meerwaarde van? Je geeft een pracht beschrijving van wat je wil maar er komen nergens id's in voor dus waarrom voeg je die toe?
Als je minder id's gebruikt is het resultaat waarschijnlijk dat je minder queries/joins nodig hebt en dus wordt het gemakkelijker (en een minimaal snelheids winstje). Voor primary key velden loont het overigens wel de moeite om char ipv. varchar velden te gebruiken.
Verder gebruik ik koppeltabellen zoals groups en groups_users_relations om zo geen dubbele data in groups_users_relations te hebben. Ik zou de tabel groups kunnen laten vallen, en groups_users_relations kunnen voorzien van een varchar-veld met groupname. Dat zou simpeler zijn, maar dan heb ik geen controle meer op typfouten (variaties in groupname) en je krijgt dubbele data. Daarom heb ik alle tabellen zoveel mogelijk genormaliseerd.
Ik heb nu overigens nog een query gemaakt die een matrix met alle groepen, met de bijbehorende gebruikers ophaalt, en de privileges. En of de gebruiker er specifieke instellingen voor heeft. Probleem is inderdaad de grote lading joins:
Code (php)
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
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
USE Experimenteel_IHGAccessControl
SELECT
groups.name as groupname,
users.name as username,
privileges.name as privilege,
COUNT(privileges_groups_relations.id) as group_is_allowed,
privileges_users_relations.allowed as user_is_allowed,
IF(
ISNULL(privileges_users_relations.allowed),
COUNT(privileges_groups_relations.id),
privileges_users_relations.allowed
) as allowed
FROM
privileges
RIGHT JOIN groups ON
1=1
RIGHT JOIN groups_users_relations ON
groups_users_relations.group_id = groups.id
RIGHT JOIN users ON
users.id = groups_users_relations.user_id
LEFT JOIN privileges_groups_relations ON
privileges_groups_relations.group_id = groups.id
AND privileges_groups_relations.privilege_id = privileges.id
LEFT JOIN privileges_users_relations ON
privileges_users_relations.user_id = users.id
AND privileges_users_relations.privilege_id = privileges.id
GROUP BY
CONCAT(groups.id, '-', users.id, '-', privileges.id)
SELECT
groups.name as groupname,
users.name as username,
privileges.name as privilege,
COUNT(privileges_groups_relations.id) as group_is_allowed,
privileges_users_relations.allowed as user_is_allowed,
IF(
ISNULL(privileges_users_relations.allowed),
COUNT(privileges_groups_relations.id),
privileges_users_relations.allowed
) as allowed
FROM
privileges
RIGHT JOIN groups ON
1=1
RIGHT JOIN groups_users_relations ON
groups_users_relations.group_id = groups.id
RIGHT JOIN users ON
users.id = groups_users_relations.user_id
LEFT JOIN privileges_groups_relations ON
privileges_groups_relations.group_id = groups.id
AND privileges_groups_relations.privilege_id = privileges.id
LEFT JOIN privileges_users_relations ON
privileges_users_relations.user_id = users.id
AND privileges_users_relations.privilege_id = privileges.id
GROUP BY
CONCAT(groups.id, '-', users.id, '-', privileges.id)
Resultaat:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
+---------------+-------------+---------------+------------------+-----------------+---------+
| groupname | username | privilege | group_is_allowed | user_is_allowed | allowed |
+---------------+-------------+---------------+------------------+-----------------+---------+
| Groep Alles | Gebruiker A | afwassen | 1 | 0 | 0 |
| Groep Alles | Gebruiker A | koffie zetten | 1 | NULL | 1 |
| Groep Alles | Gebruiker B | afwassen | 1 | NULL | 1 |
| Groep Alles | Gebruiker B | koffie zetten | 1 | NULL | 1 |
| Groep Beperkt | Gebruiker B | afwassen | 1 | NULL | 1 |
| Groep Beperkt | Gebruiker B | koffie zetten | 0 | NULL | 0 |
+---------------+-------------+---------------+------------------+-----------------+---------+
| groupname | username | privilege | group_is_allowed | user_is_allowed | allowed |
+---------------+-------------+---------------+------------------+-----------------+---------+
| Groep Alles | Gebruiker A | afwassen | 1 | 0 | 0 |
| Groep Alles | Gebruiker A | koffie zetten | 1 | NULL | 1 |
| Groep Alles | Gebruiker B | afwassen | 1 | NULL | 1 |
| Groep Alles | Gebruiker B | koffie zetten | 1 | NULL | 1 |
| Groep Beperkt | Gebruiker B | afwassen | 1 | NULL | 1 |
| Groep Beperkt | Gebruiker B | koffie zetten | 0 | NULL | 0 |
+---------------+-------------+---------------+------------------+-----------------+---------+
Gewijzigd op 01/01/1970 01:00:00 door Jelmer -
Je moet de tabel groups zeker niet laten vallen, daarin staan je groups.name en zoals je al zegt die heb je nodig om tik fouten te voorkomen en wel met een foreign key.
Je tabel wordt er alleen maar beter op genormaliseerd want daarbij wordt er nooit gesproken over het toevoegen van een 'id' kolom.
Uiteraard zou je datamodel wel beter genormaliseerd zijn, maar normalisatie kun je ook te ver doordrijven. Soms moet je ervoor kiezen om te denormaliseren om zo de performance te behouden. Vandaar dat het gebruik van een integer als foreign key bijna nooit een slechte keuze is.
Quote:
Uiteraard levert een char een betere performance als een varchar, helaas zal dat in dit geval niet echt een optie zijn. Een char verlangt namelijk een vaste lengte terwijl een naam van een privilege juist variabel is.Voor primary key velden loont het overigens wel de moeite om char ipv. varchar velden te gebruiken.
Gewijzigd op 01/01/1970 01:00:00 door Joren de Wit
Blanche schreef op 24.02.2008 12:40:
@Boaz: je hebt het over optimalisatie van je queries en raadt dan tevens aan om een varchar als foreign key te gebruiken. Dit gaat niet echt lekker samen. Een integer (id) is namelijk als foreign key vele malen efficienter dan een varchar als het gaat om queries met joins erin.
Zoals je later ook al quote raad ik aan om er een char van te maken en absoluut geen varchar. Een char reserveerd namelijk
Edit:
Wie zegt dat je in een char ook alle chars moet vullen?
Edit:
Een iteger is ook zeker geen slechte keuze als foreign key, maar dan moet je ze wel hebben, als je ze gaat toevoegen wordt het wat anders.
Of je dit doorschieten is in normaliseren? ik denk het niet omdat het in het aantal joins kan schelen, je hoeft namelijk niet meer te joinen op goups om de goups.name op te halen en zo kan het nog wel meer joins schelen.
Gewijzigd op 01/01/1970 01:00:00 door Bo az
Ik heb trouwens een werkende query waarmee ik aan de hand van een user_id en een privilege_name kan uitrekenen of de user de privilege heeft, maar het is wel een beest(je)
Code (php)
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
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
USE Experimenteel_IHGAccessControl
SET sql_mode = STRICT_ALL_TABLES;
SET @user_id = 3;
SET @privilege = 'afwassen';
SELECT
IF(
!ISNULL(privileges_users_relations.allowed),
privileges_users_relations.allowed,
COUNT(privileges_groups_relations.id) > 0
) as allowed
FROM
privileges
LEFT JOIN groups_users_relations ON
groups_users_relations.user_id = @user_id
LEFT JOIN privileges_groups_relations ON
privileges_groups_relations.group_id = groups_users_relations.group_id
AND privileges_groups_relations.privilege_id = privileges.id
LEFT JOIN privileges_users_relations ON
privileges_users_relations.user_id = @user_id
AND privileges_users_relations.privilege_id = privileges.id
WHERE
privileges.name = @privilege
GROUP BY
privileges.id
SET sql_mode = STRICT_ALL_TABLES;
SET @user_id = 3;
SET @privilege = 'afwassen';
SELECT
IF(
!ISNULL(privileges_users_relations.allowed),
privileges_users_relations.allowed,
COUNT(privileges_groups_relations.id) > 0
) as allowed
FROM
privileges
LEFT JOIN groups_users_relations ON
groups_users_relations.user_id = @user_id
LEFT JOIN privileges_groups_relations ON
privileges_groups_relations.group_id = groups_users_relations.group_id
AND privileges_groups_relations.privilege_id = privileges.id
LEFT JOIN privileges_users_relations ON
privileges_users_relations.user_id = @user_id
AND privileges_users_relations.privilege_id = privileges.id
WHERE
privileges.name = @privilege
GROUP BY
privileges.id
Voor de id-loze database is het me nog niet gelukt eentje te maken. Een uitdraai van mijn database met id's en zoals Boaz hem voorstelde kan je daar dus vinden :)