SQL Loginsystem Datenstruktur

[Kantholy]

Hallo Zusammen,

ich arbeite gerade für meine Firma an einem zentralisierten Loginsystem für all meine Webapplikationen bei der im Moment alle Apps ein seperates Loginsystem haben - was wiederum für die Benutzer extrem viel Aufwand bei Passwortänderungen usw. verursacht.

Deswegen war der Plan, ein zentrales Login für alle Applikation zu basteln um so manchen Komfort zu erlangen (SSO, ein Benutzer, ein Passwort, etc.) und um den administrativen Aufwand zu verringern.

So, das Konzept und ein erster Prototyp davon steht: Die Rechte für die Applikationen basieren auf Gruppen oder Benutzern - deren IDs benötigt werden.

Jetzt gehts darum die Struktur in einer Datenbank - in meinem Fall MySQL - zu managen. Wie man ich das am schlauesten?

Hier mal mein Konzept wie ich es bisher habe:

2 Tabellen, eine für die Benutzer - eine für die Gruppen. (siehe die 2 angehängten Grafiken)

Die Benutzer haben ein feld "groups" - hier speichere ich im Moment alle GruppenIDs mit leerzeichen getrennt ab - lustigerweise passiert das ganze auch nochmal in der gruppentabelle in der ich die "members", also die userIDs - per leerzeichen getrennt - mit abspeichere...

Ich seh aber jetzt schon in meinem Konzept, dass ich 1. ne fette Redundanz hab (weil ich sowohl bei den users als auch bei den gruppen datensätzen die Rechte speichere...

Ich hab mir schon überlegt ne extra Tabelle dafür anzulegen, die aus 2 feldern besteht, also "uid" und "gid", und dann eben die Rechte für die Gruppen darin abspeichere...

so, das Ding ist jetzt nur - ich will unbedingt "manager" für die gruppen haben, was natürlich auch unter Umständen mehrere Personen sein können" - soll ich dafür dann nochmal ne extra Tabelle mit diesen 2 feldern anlegen?

Und last but not least - wie frage ich dann am schlauesten am effizientesten diese Rechte ab? Im Moment isses für mich leicht, ein Datensatz abfragen und meine semi-serialisierten Felder einfach mit php explode(' ', $row['feld']); zu nem Array wandeln...


P.S.: Ich muss gleich dazu sagen: Ich hab wohl paar Mal den Begriff "Normalform" rumfliegen sehen, hab aber keinen Plan davon

Ich bedanke mich schonmal im vorraus für jede kompetente Hilfe die ihr mir geben könnt.

 

[Nase]

Ich kann jetzt auf die Schnelle dein Gesamtprojekt nicht überblicken, dafür fehlt es an Details und Einarbeitungszeit. Allerdings mache ich es so, Daten niemals per Sonderzeichen getrennt in einem Tabellenfeld abzuspeichern. Das führt immer zu Problemen. Außerdem musst du bei jedem Auslesevorgang immer diesen String auseinanderreißen und die Daten neu verarbeiten. Spontan würde ich dir empfehlen:

Eine Tabelle für die Benutzer inkl. Name, Passwort, Logindatum und was es sonst noch an Benutzerinformationen gibt.
Eine Tabelle mit den Gruppen, die Gruppen-ID, Gruppenname enthält.
Eine Tabelle mit den Rechten. Gruppen-ID, User-ID, Zugriffsrecht.
Eine Tabelle mit deinen Managern. Gruppen-ID, User-ID.

Beim mysql_select kannst du dann alle Tabellen miteinander verknüpfen. Außerdem kannst du unabhängig voneinander auf die unterschiedlich Daten zugreifen.

 

[Doeni]

Ja Normalform, das wär nen Anfang.
Hast du keinen Kollegen der sich da etwas auskennt?

Prinzipiell würd ich die Rechte nur über Gruppen gewähren.
Den Gruppen IDs zuordnen (zB AA-ZZ)
und die User dann in ner extra Tabelle den Gruppen zuordnen:

Tabelle USER_GRUPPE

UID | GID

234 | AA
432 | BA
432 | ZA


etc...

 

[ICH_BIN_LETZTER]

Hi,
das mit den Normalformen solltest du dir aber dringen aneignen. Der Wiki Artikel sollte dafür reichen um ein Verständnis zu bekommen.

Also zu den Tabellen:

User
User2Rights (hier nur die UserID und eine RightID)
User2Groups (hier nur die UserID und eine GroupID)
Rights
Groups

so das sollte erstmal für ein grobes System reichen.
Abfragen dann einfach per SQL. Also SELECT bla FROM bla WHERE bla ........ (hier ist notfalls ausprobieren und basteln gefragt )

so ich hoffe ich konnt emal fürs erste helfen

 

[Doeni]

Welchen Sinn soll das eigentlich haben die Rechte direkt den Usern zuzuordnen und dann den User in ne Gruppe zu stecken? Dann brauch ich doch keine Gruppen....

 

[ICH_BIN_LETZTER]

naja falls du in den Gruppen nochmal abstufen willst, oder bestimmten Nutzern wegen Vergehen oder Bonis mehr oder weniger Rechten in einer Gruppe geben willst

 

[Kantholy]

mit Rechten hatte ich vor in der Datenbank garnix zu machen, die Rechte werden von den Applikationen verwaltet... je nachdem in welchen Gruppen der Benutzer drinnen ist bekommt der dann die rechte... quasi "hardcoded"

okay Leute - ich danke euch für eure Hilfe bis jetzt, ich seh schon dass ich auf dem richtigen Weg war und ihr habt das hiermit nur nochmal bestätigt.

 

[Doeni]

Ich seh aber jetzt schon in meinem Konzept, dass ich 1. ne fette Redundanz hab (weil ich sowohl bei den users als auch bei den gruppen datensätzen die Rechte speichere...

Ich hab mir schon überlegt ne extra Tabelle dafür anzulegen, die aus 2 feldern besteht, also "uid" und "gid", und dann eben die Rechte für die Gruppen darin abspeichere...

mit Rechten hatte ich vor in der Datenbank garnix zu machen, die Rechte werden von den Applikationen verwaltet... je nachdem in welchen Gruppen der Benutzer drinnen ist bekommt der dann die rechte... quasi "hardcoded"

???

okay Leute - ich danke euch für eure Hilfe bis jetzt, ich seh schon dass ich auf dem richtigen Weg war und ihr habt das hiermit nur nochmal bestätigt.

Der Laster kam mit 200 auf mich zu.
Ohne das ganze in die Normalform zu bringen bist du gar nicht aufm richtigen Weg.

 

[Kantholy]

???
Der Laster kam mit 200 auf mich zu.
Ohne das ganze in die Normalform zu bringen bist du gar nicht aufm richtigen Weg.

Ich hab mir schon überlegt ne extra Tabelle dafür anzulegen, die aus 2 feldern besteht, also "uid" und "gid", und dann eben die Rechte für die Gruppen darin abspeichere...

okay, das mit den Rechte habe ich vllt blöd formuliert in meinem ersten Post... es soll quasi nur eine Beziehung zwischen den Benutzern und den Gruppen existieren und anhand dessen wird dann auf Applikationsbasis ausgewertet ob der User die Rechte für die entsprechende Anwendung hat, wenn nicht wird er wieder aufs Loging zurück-redirected mit Errorcode von wegen "hey, du hast hier leider keine Berechtigung für"...

Wenn er aber jetzt quasi in einer entsprechenden Gruppe ist, darf er auf die Anwendung zugreifen und diese gibt dem dann wieder entsprechend seiner Gruppen- oder UserID bzw. administrative Rechte für die Anwendung.

ich hoffe jetzt ist klarer geworden was ich überhaupt vor hatte... sorry für die allgemeine Verwirrung

 

[Doeni]

Dann brauchst du im Prinzip 3 Tabellen.
Eine für die User, eine für die Gruppen und eine für die Zuordnung User-Gruppe (siehe erster Post von mir)

 

[Kantholy]

hab ich jetzt auch gemacht:

 

[Doeni]

Ein Manager managed immer nur bestimmte Gruppen oder alle?
Sonst würd ich eine Gruppe Manager anlegen

Jetzt musst du nur noch deinen Text in Spalten trennen und nicht durch Leerzeichen.

 

[Kantholy]

nope, es soll quasi ein manager oder mehrere manager für eine gruppe deligiert werden... natürlich kann auch ein und die selber person in mehreren gruppen mitglied bzw. manager sein

vorraussetzung ist bei dieser tabelle nur dass der "manager" auch member der gruppe ist... was aber auch sinn macht wie ich finde... ich hab jedenfalls keinen Fall wo eine bestimmte Person eine Anwendung managen darf aber selbst keinen Zugriff hat

die "text" properties vom 1. Post hab ich schon gelöscht

und jetzt gehts ans programmieren.

 

[Doeni]

Dann viel Spass und bei gelegenheit mal die Sache mit den Normalformen ansehn, für Datenbanken essentiell.

 

[DerEineDa]

Autsch! Stopp!!

Deine group_members-Tabelle hat ja mit "id" einen eigenen Primärschlüssel, während user_id und group_id normale Attribute sind! So macht man das nicht, das ist ganz böse. Das ergibt mindestens zwei Probleme:

* Du kannst Gruppenzuordnungen aus Versehen doppelt ablegen.
* group_id und user_id werden nicht indiziert. Jeder SELECT oder JOIN auf diese Tabelle - und die wirst du oft brauchen - wird sehr langsam sein.

So, hier ein paar Grundlagen: Du hast hier eine typische n:m-Beziehung: Ein Benutzer kann in mehreren Gruppen sein und eine Gruppe kann mehrere Benutzer beinhalten. Um eine n:m-Beziehung aufzulösen brauchst du eine Zwischentabelle, wie du schon ganz richtig mit group_members angelegt hast. Eben diese Tabelle muss nun einen zusammengesetzten Primärschlüssel haben, d.h. group_id und user_id bilden einen gemeinsamen Primärschlüssel. Du musst einfach beide Spalten als Primärschlüssel definieren.

In einem ER-Diagramm haben die Beziehungen zu dieser Tabelle auch einen soliden Strich, anstatt einen gestrichelten, wie normale Beziehungen. Das resultiert daher, dass diese Zwischentabellen nicht für sich alleine stehen können, sondern immer Informationen aus den "Nachbartabellen" benötigen. Solche Beziehungen nennt man dann "identifizierende Beziehung" oder eben "identifying relationship".

Deine Spalte "id" aus group_members muss also komplett raus! Stattdessen user_id und group_id zum gemeinsamen Primärschlüssel machen!

Und danach bitte den Wikipedia Artikel über Normalformen lesen. Die ersten drei sollte jeder beherrschen, der eine Datenbank erstellen will.

 

[Kantholy]

einen Primärschlüssel über mehrere Spalten?! sowas hab ich ja noch nie gehört bzw. gesehen...

Es scheint auf jeden Fall zu funktionieren

 

[Doeni]

Das hatte ich glatt übersehen.

So ein kombinierter Primärschlüssel ist gerade bei n:m Verknüpfungen standard.
Allerdings würd ich nie mehr als 3 Spalten zu einem Primärschlüssel zusammenfassen.

Die Anforderungen an einen Primärschlüssel sind natürlich auch immer zu beachten

Guck mal hier: http://www.sql-und-xml.de/sql-tutorial/
unter "Grundlagen für relationale Datenbank-Systeme und Entwurf der Speicherstruktur"

 

[Kantholy]

okay, jetzt bin ich schon wesentlich weiter - ich wusste dass ich hier kompetente Hilfe erhalte..
Jetzt les ich mich erstmal in diese Normalformen da ein.

besten Dank CB Community auf euch ist Verlass.

 

[Rossibaer]

DerEineDa:

Also nur mal zum Mitschneiden:

USERS (USER_ID, USER_NAME [,..]) PK auf USER_ID
GROUPS (GROUP_ID, GROUP_NAME [,..]) PK auf GROUP_ID
RELATIONS (USER_ID, GROUP_ID) PK auf USER_ID, GROUP_ID

User 'Zorro' hat welche Gruppen:

SELECT A.GROUP_NAME
FROM GROUPS A
JOIN RELATIONS B ON B.GROUPS_ID=A.GROUPS_ID
JOIN USERS C ON C.USER_ID=B.USER_ID
WHERE C.USER_NAME='Zorro'

Gruppe 'Manager' hat welche User:

SELECT A.USER_NAME
FROM USERS A
JOIN RELATIONS B ON B.USERS_ID=A.USERS_ID
JOIN GROUPS C ON C.GROUPS_ID=B.GROUPS_ID
WHERE C.GROUP_NAME='Zorro'

Bei der RELATIONS Tabelle hätte ich jetzt einen Foreign Key auf GROUP_ID der GROUPS Tabelle und einen Foreign Key auf USER_ID der USERS Tabelle gelegt. Das ganze hätte ich dann noch mit nem Unique Index auf USER_ID und GROUP_ID in der RELATIONS Tabelle abgesichert. Zusammengesetzte Primary Keys sind mir da völlig neu. Geht das auch bei SqlServer / Oracle, wenn ja welche Version? Rein interessenhalber. Man lernt nie aus...

 

[DerEineDa]

Klingt alles korrekt, abgesehen davon, dass du im zweiten Beispiel nicht 'Zorro' sondern 'Manager' meinst. Zusammengesetzte Primärschlüssel gab es schon immer bei jedem SQL-System. Sonst kann man ja solche n:m-Beziehungen nicht richtig modellieren. Den UNIQUE-Contraint auf die beiden Primärschlüssel der RELATIONS Tabelle kannst du dir sparen, denn der zusammengesetzt Primärschlüssel garantiert dir genau das. Genau genommen ist in den meisten DBMS ein Primärschlüssel nichts anderes als eine Spalte mit einem implizierten UNIQUE-Contraint, einem Index auf diese Spalte und ein NOT NULL Constraint.

Nun tut mal nicht alle so, als wäre ein zusammengesetzter Primärschlüssel etwas so besonderes. Das macht man immer so, sobald man ein solches Szenario hat - was so gut wie immer vorkommt. Das lernt jeder Fachinformatiker (und sogar Informatikkaufmann) im ersten Lehrjahr