Benutzerrechte MySQL erstellen Tabellenseitig

[stOrM]

Moin,
irgendwie komme ich nicht klar, was die Referenzen meiner Tabellen angeht.
Ich muss mal etwas ausholen, damit man nachvollziehen kann worum es geht und was ich zu erreichen versuche.

Gehen wir davon aus wir haben eine Anwendung in der man die Zugriffsrechte der Benutzer derselbigen regeln möchte (mit Regeln ist gemeint, zu welcher Gruppe gehört der Benutzer, was darf dieser z.B. nur ansehen also lesen, schreiben löschen usw.)

Der Sinn soll später halt sein, möglichst einfach einen neuen Benutzer anzulegen, die Gruppe auszuwählen so welcher dieser gehören soll und was dieser Benutzer in welchen Programmmodulen darf. Des weiteren soll dann später anhand dieser Daten die GUI dementsprechend reagieren (wenn nur Leserechte existieren kann ich halt so simple alle "delete" und "create" Buttons deaktivieren...)

Dazu dachte ich mir lege ich vermutlich folgende Tabellen an:

"Users"

Code:

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) CHARACTER SET utf8 NOT NULL,
  `middle_name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `last_name` varchar(45) CHARACTER SET utf8 NOT NULL,
  `login_name` varchar(254) CHARACTER SET utf8 NOT NULL,
  `email` varchar(45) CHARACTER SET utf8 NOT NULL,
  `password` varchar(254) CHARACTER SET utf8 NOT NULL,
  `created_at` date NOT NULL,
  `updated_at` date DEFAULT NULL,
  `deleted_at` date DEFAULT NULL,
  `active` int(11) NOT NULL,
  `u_rolecode` varchar(50) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

"Roles" role_rolecode z.B. "Admin"

Code:

CREATE TABLE IF NOT EXISTS `roles` (
  `role_rolecode` varchar(50) NOT NULL,
  `role_rolename` varchar(50) NOT NULL,
  PRIMARY KEY (`role_rolecode`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

"Role_Rights" Diese Tabelle soll regeln, für welches Module in der Anwendung welche Rechte für den angemeldeten Benutzer vorhanden sind.

Beispiel:
rr_rolecode = Admin
rr_modulecode = Login
rr_create = 0 oder 1 (Kann neuen Benutzer anlegen oder nicht)

Code:

CREATE TABLE IF NOT EXISTS `role_rights` (
  `rr_rolecode` varchar(50) NOT NULL,
  `rr_modulecode` varchar(50) NOT NULL,
   `rr_create` INT(2) NOT NULL,
   `rr_edit` INT(2) NOT NULL,
   `rr_delete` INT(2) NOT NULL,
   `rr_view` INT(2) NOT NULL
  PRIMARY KEY (`role_rolecode`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Problematisch wird bei mir wieder das Verknüpfen der Tabellen anscheinend hab ich da einen groben Schnitzer als ich der Tabelle Users folgendes hinzufügen wollte quittierte mir der MySQL Server dies mit einem: "Cannot add foreign key constraint"

Code:

   FOREIGN KEY (`u_rolecode`) REFERENCES `roles` (`role_rolecode`)  ON UPDATE CASCADE ON DELETE RESTRICT

Also ich bin mir auch generell nicht sicher ob der Aufbau meiner Tabellen optimal oder eher suboptimal ist für das was ich damit erreichen möchte. Wenn jemand einen anderen Vorschlag hat bin ich sehr gerne geneigt das ganze dementsprechend komplett abzuändern.

Jedenfalls komme ich so irgendwie nicht weiter.

[jobo]

Auf die Schnelle
Vielleicht erkennt mysql anhand der PK, dass dieser FK keinen Sinn macht.
Ein FK bei User soll auf den PK bei Roles zeigen?
Soll User nur eine Rolle haben können?
Ich denke Du hast bei dem Thema ein paar N:M Beziehungen und brauchst also Zwischentabellen, die das abbilden.

[stOrM]

Zitat von jobo:

Auf die Schnelle
Soll User nur eine Rolle haben können?
Ich denke Du hast bei dem Thema ein paar N:M Beziehungen und brauchst also Zwischentabellen, die das abbilden.

So sollte es sein jeder User eine Rolle.
N:M = Zwischentabellen

[stOrM]

Also so wüde es zumindest erst einmal funktionieren, nur ist das überhaupt korrekt? Irgendwie bezweifel ich das gerade ganz stark!

Code:

CREATE TABLE `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Code:

CREATE TABLE `roles_rights` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `module_name` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mp_read` int(1) DEFAULT NULL,
  `mp_write` int(1) DEFAULT NULL,
  `mp_delete` int(1) DEFAULT NULL,
  `mp_view` int(1) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `roles_rights_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `roles_rights_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Code:

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) CHARACTER SET utf8 NOT NULL,
  `middle_name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `last_name` varchar(45) CHARACTER SET utf8 NOT NULL,
  `login_name` varchar(254) CHARACTER SET utf8 NOT NULL,
  `email` varchar(45) CHARACTER SET utf8 NOT NULL,
  `password` varchar(254) CHARACTER SET utf8 NOT NULL,
  `created_at` date NOT NULL,
  `updated_at` date DEFAULT NULL,
  `deleted_at` date DEFAULT NULL,
  `active` int(1) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

[jobo]

Irgendwie nicht so richtig.
Sicher das jeder nur eine Rolle bekommen soll?

Wenn Du das Datenmodell erstellst, muss der Scriptaufbau (Reihenfolge) natürlich so sein, dass die Fremdschlüsselanweisungen auf eine bereits angelegte Tabelle zeigen.

Rollen legt man eher so an, dass sie unterschiedliche Themen abdecken und dann per User mehrere in Kombination eine sinnvolle Mischung ergeben.

Dann gibt es eine Reihe von Standardrollen, die die meisten User so bekommen und ein paar Spezialisten bekommen on Top Adminrechte oder wie hier im Forum bspw. Moderationsrechte. Die Rollen können dann auch teilweise überlappen, es zählt, was in Summe, Distinct an Einzelrechten einem User zugeordnet ist.

[stOrM]

Zitat von jobo:

Irgendwie nicht so richtig.
Sicher das jeder nur eine Rolle bekommen soll?

Wenn Du das Datenmodell erstellst, muss der Scriptaufbau (Reihenfolge) natürlich so sein, dass die Fremdschlüsselanweisungen auf eine bereits angelegte Tabelle zeigen.

Rollen legt man eher so an, dass sie unterschiedliche Themen abdecken und dann per User mehrere in Kombination eine sinnvolle Mischung ergeben.

Dann gibt es eine Reihe von Standardrollen, die die meisten User so bekommen und ein paar Spezialisten bekommen on Top Adminrechte oder wie hier im Forum bspw. Moderationsrechte. Die Rollen können dann auch teilweise überlappen, es zählt, was in Summe, Distinct an Einzelrechten einem User zugeordnet ist.

Irgendwie glaub ich meinen wir eventuell das gleiche aber reden dann doch aneinander vorbei.
Allerdings kann ich dir auch nicht ganz folgen was Du mit Mischung meinst.

User A = Admin
User B = Standard Nutzer
User C = Gast

Also bei mir ergibt sich jetzt da keine Mischung, es sei denn Du meinst was komplett anderes als ich, wenn ja vielleicht kanst Du mal zeigen wie Du dir das vorstellst, klingt interessant und ist ggf. wesentlich flexibler bzw. besser umzusetzen.

[jobo]

Zitat von stOrM:

User A = Admin
User B = Standard Nutzer
User C = Gast

Also bei mir ergibt sich jetzt da keine Mischung, es sei denn Du meinst was komplett anderes als ich, wenn ja vielleicht kanst Du mal zeigen wie Du dir das vorstellst, klingt interessant und ist ggf. wesentlich flexibler bzw. besser umzusetzen.

Wenn Du die Rollen pro User nur 1 zu 1 vergeben willst, dann ist das ok, einfacher zu verwalten, aber eben nicht so flexibel. Also vergiß die Mischung.

Nach meiner Vorstellung fehlt eine Tabelle UserRole die eine N:M Verknüpfung zwischen User und Rolle abbildet und damit Mehrfachzuordnung von Rollen zu User erlaubt.

[stOrM]

Zitat von jobo:

Zitat von stOrM:

User A = Admin
User B = Standard Nutzer
User C = Gast

Also bei mir ergibt sich jetzt da keine Mischung, es sei denn Du meinst was komplett anderes als ich, wenn ja vielleicht kanst Du mal zeigen wie Du dir das vorstellst, klingt interessant und ist ggf. wesentlich flexibler bzw. besser umzusetzen.

Wenn Du die Rollen pro User nur 1 zu 1 vergeben willst, dann ist das ok, einfacher zu verwalten, aber eben nicht so flexibel. Also vergiß die Mischung.

Nach meiner Vorstellung fehlt eine Tabelle UserRole die eine N:M Verknüpfung zwischen User und Rolle abbildet und damit Mehrfachzuordnung von Rollen zu User erlaubt.

Ja gut so irre flexibel muss es auch nicht werden, je einfacher umso besser, wenn ich die Materie besser verstehe, könnte ich das immer noch umbauen.
Wo ich gerade gar nicht mehr klar komme ist dein letzter Satz, weil genau das bekomm ich gerade nicht wirklich auf die Kette.

Wie gesagt wenn Du Lust und etwas Zeit hast, ich bin auch für eine andere Idee offen, wenn Du denkst so etwas kann man besser lösen, aber dann müsste ich schon genau sehen wie oder was ich bei mir jetzt umstellen soll Tabellentechnisch, meine Befürchtung ist nur wenn ich dann später auswerten soll wer welche Berechtigung in welchem Modul hat und ich dann mit 4 oder mehr Tabellen wie verrückt Joinen muss dann vermute ich mal brennt mir eine Sicherung durch (soll heissen ich glaub das überfordert mein Verständnis zu Datenbanken extrem)

[jobo]

Mein letzter Satz beschreibt nur, wie ich es machen würde. Du willst aber nur eine 1:1 Beziehung zwischen User und Rolle, also spielt das keine Rolle.
Kurzfristig kann ich Dir da nichts weiter zu sagen. Du kannst erstmal prüfen, ob Dein Verfahren nun funktioniert, insbesondere ist dazu mein Hinweis zu beachten, dass Du die Tabellen immer in der richtigen Reihenfolge erstellen musst, sobald foreign keys angelegt werden. Die Befehle (Create Table , ... contraint) werden Schritt für Schritt umgesetzt. Du kannst also keine fk Konstraints bauen, deren Zieltabellen oder Spalten noch nicht vorhanden sind.
Wenn das Datenmodell steht, kannst Du prüfen, ob es das macht, was Du möchtest.

[jobo]

Code:

CREATE TABLE `xyz_role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `xyz_role_right` (
  `role_right_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,  
  `module_name` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mp_read` int(1) DEFAULT NULL,
  `mp_write` int(1) DEFAULT NULL,
  `mp_delete` int(1) DEFAULT NULL,
  `mp_view` int(1) DEFAULT NULL,
  PRIMARY KEY (`role_right_id`),
  CONSTRAINT `roles_rights_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `xyz_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `xyz_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) ,  -- user kann ohne role angelegt werden, muss aber nicht
  `first_name` varchar(45) CHARACTER SET utf8 NOT NULL,
  `middle_name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `last_name` varchar(45) CHARACTER SET utf8 NOT NULL,
  `login_name` varchar(254) CHARACTER SET utf8 NOT NULL,
  `email` varchar(45) CHARACTER SET utf8 NOT NULL,
  `password` varchar(254) CHARACTER SET utf8 NOT NULL,
  `created_at` date NOT NULL,
  `updated_at` date DEFAULT NULL,
  `deleted_at` date DEFAULT NULL,
  `active` int(1) NOT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `user_role_fk` FOREIGN KEY (`role_id`) REFERENCES `xyz_role` (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Ich hab mal Deine 1:1 Variante etwas korrigiert.
1 User hat
1 Rolle hat
viele Rollenrechte

Habe die Objekte mit xyz_ voran umbenannt, damit die Tabellen nicht wie Funktionen oder keywords heißen. Außerdem nennt man die Tabellen eher im Singular. Für die PK usw. gibt's auch noch Namenskonventionen, kann man sich dann drüber streiten.

Die Änderungen sind nicht getestet, hab kein mysql am Start. Achso, die 'Key role_id (role_id)' Angabe kenne ich nicht, hab ich rausgeworfen. Vielleicht bedeutet das auch was unter mysql.