Nudge am 27.03.2007

MySQL Mustersuche, nur andersherum

in Linux, MySQL | Tags: MySQL, Query, Rechteverwaltung, Regular Expression

Gestern habe ich mich mit folgender kleiner Aufgabe beschäftigt: Lese- und “Ausführungs”-Berechtigung von Intranet-Seiten für die Mitarbeiter. Die 08/15-Lösung: Eine Tabelle, die ungefähr so aussieht:

1
2
3
4
5
6
7
8
CREATE TABLE IntraBerechtigungen (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Mitarbeiter varchar(255) NOT NULL,
  Seite varchar(255) NOT NULL,
  Lesen char(1) DEFAULT 'N',
  Ausfuehren char(1) DEFAULT 'N',
  Timestamp timestamp
);
CREATE TABLE IntraBerechtigungen (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Mitarbeiter varchar(255) NOT NULL,
  Seite varchar(255) NOT NULL,
  Lesen char(1) DEFAULT 'N',
  Ausfuehren char(1) DEFAULT 'N',
  Timestamp timestamp
);

Dann trägt man einfach seine Leute und die entsprechenden Seiten ein. Aber was passiert, wenn jemand neues hinzukommt? Wenn eine Seite hinzukommt oder wegfällt? Viel manuelle Arbeit. Mit Mustern wollte ich daher diese Mühe sparen.

Wenn man sich die mysql-System-Tabellen user oder db mal anschaut, findet man ja häufig Einträge wie “%” für den Host – d.h. hier ist der Zugriff von überall aus gestattet. Dasselbe könnte man hier auch anwenden, zB: Erlaube lesende Zugriffe für “%” auf die Seiten “CRM%.php”. Das würde die Tabelleneinträge in sinnvollem Maße begrenzen und mir erlauben, den Überblick zu behalten.

Der Unterschied zur normalen Mustersuche: Das Muster steckt sonst im PHP-Code, und die Strings in den Spalten der Tabellen. Hier steckt das Muster auf einmal in der Datenbank, aber die Strings im PHP-Code (zB als Benutzername irgendwo in $_SESSION). Das bedeutet leider auch, dass der herkömmliche SELECT

1
2
3
SELECT Lesen, Ausfuehren
FROM IntraBerechtigungen
WHERE ('Heinz' LIKE Mitarbeiter) AND ('/test.php' LIKE Seite);
SELECT Lesen, Ausfuehren
FROM IntraBerechtigungen
WHERE ('Heinz' LIKE Mitarbeiter) AND ('/test.php' LIKE Seite);

nicht mehr funktioniert. MySQL meldet dabei, dass ‘Heinz’ und ‘/test.php’ keine validen Spaltennamen sind – womit es leider auch Recht hat.

Daher muss man die Konstruktion der Ergebnistabelle ein Stück vorziehen, indem man die Spalten zunächst konstruiert, und dann erst auswerten:

1
2
3
4
5
6
7
SELECT SUM(Lesen), SUM(Ausfuehren), 'Heinz' AS wer, '/test.php' AS was
FROM `IntraBerechtigungen`
WHERE EXISTS (
  SELECT *
  FROM IntraBerechtigungen
  WHERE (wer LIKE Mitarbeiter) AND (was LIKE Seite)
);
SELECT SUM(Lesen), SUM(Ausfuehren), 'Heinz' AS wer, '/test.php' AS was
FROM `IntraBerechtigungen`
WHERE EXISTS (
  SELECT *
  FROM IntraBerechtigungen
  WHERE (wer LIKE Mitarbeiter) AND (was LIKE Seite)
);

Damit die Überlagerung verschiedener Einträge (zB “%” darf “%.php” lesen, “Heinz” darf “%.php” lesen und ausführen) funktioniert, müssen allerdings die Felder Lesen und Ausführen auf INT(1) geändert werden. NULL oder 0 bedeutet verboten, 1 oder höher bedeutet erlaubt. Durch SUM(Lesen) kann man dann herausfinden, ob Nutzer “Heinz” in irgendeiner Form berechtigt ist, die Seite “/test.php” zu lesen oder auszuführen.

Auch Gruppenberechtigungen kann man damit abbilden. Dazu kann man in der Tabelle IntraBerechtigungen eine Spalte ‘Gruppe’ einführen, und die Nutzer, zB über ein simplen assoziativen Array in PHP, in Gruppen einteilen. *

1
2
3
4
5
$NUTZERGRUPPEN = array(
  'Heinz' => 'Sales',
  'Werner' => 'Sales',
  'Inge' => 'Verkauf'
);
$NUTZERGRUPPEN = array(
  'Heinz' => 'Sales',
  'Werner' => 'Sales',
  'Inge' => 'Verkauf'
);

* Es gibt ja immer wieder Leute, die meinen, so etwas muss man auch in MySQL abbilden, am besten in der 5. Normalform: id für die Gruppe, id für den Mitarbeiter, Verknüpfungstabelle, womöglich noch die Sprachabhängigen Attribute auslagern. Ich halte eine solche Grundeinstellung hochgefährlich für den laufenden Betrieb: Nicht nur die ids können bei einem Crash flöten gehen, auch die Verknüpfungstabellen in einer solchen zerpflückten Datenbank kann keiner mehr ohne Handbuch verstehen. Und statt den 20 Chars des Namen die immerhin 8 Bytes (64-bit-sicher!) INT für die id zu verwenden, spart 12 Byte. Erst kürzlich habe ich eine Platte von 400 GB für etwa 80 Euro erworben. Was sind da 12 Byte? Man spart diese Beträge durch schnellere Bearbeitung allemal. Abgesehen davon wäre für die Auflösung der Gruppenberechtigungen in einem solchen Fall ein Cross-Join über 3 Tabellen notwendig. Direkt in PHP getippt, spart man der DB wie auch dem Benutzer am Bildschirm wertvolle Zeit, indem man auf solche Schulbuchtechniken verzichtet.


Das mark ich mir: Alltagz Mr Wong Yigg Del.icio.us Yahoo MyWeb Blinklist Google folkd
 

Leave a Reply

Your email address will not be published. Required fields are marked *