Geschwindigkeit von Views verbessern

[Norbert5]

Hi!

Mit deiner Anweisung
SELECT d.* FROM datentabelle d, zugrifftabelle z WHERE d.mandant = z.mandant liest du alle Datensätze aus der Datentabelle, das dauert bei Millionen von Datensätzen natürlich einige Zeit. Die Angabe der Zugrifftabelle im FROM-Teil - und damit auch das WHERE-Token bewirken gar nichts mehr, da ja aus z keine Felder selektiert werden und die WHERE-Klausel die Datensätze von D nicht einschränkt.

Vermutlich willst du ja nur die Daten zu "current_user" anzeigen. Wenn dies ein Feld der Zugriffstabelle ist, solltest du die SQL-Anweisung so ändern:

Delphi-Quellcode:

query.SQL := 'SELECT d.* FROM datentabelle d '
            +'LEFT JOIN zugriffstabelle z on d.mandant=z.mandant '
            +'WHERE z.current_user='''+aktuser+'''';

[Errraddicator]

Zitat von mkinzler:

Benötigst du wirklich alle Rechte aller Benutzer?

Irgendwie steh ich grad son bissel auffem Schlauch, bzw. verstehe die Frage nich so ganz. *G*

Der Sinn dieses Patentes is ja, dass eben kein einziger Benutzer alle Rechte hat,
sondern jeder nur "seinen" Teil der Daten sieht.
Und das schon direkt auf Datenbankebene und nicht erst durch die Applikation geregelt.

Von daher weiß ich jetzt leider nich so recht, was Du mit "alle Rechte aller Benutzer" meinst.

[p80286]

where d.mandant='ich' wenn Du nur "Deine" Datensätze sehen willst, dann solltest Du das der DB auch mitteilen.

Gruß
K-H

[Sir Rufo]

Mein Tip bei so einer sicheren Trennung der Tabellen vom Benutzer ist die Verwendung von SP = Stored Procedures.

(wer es noch nicht wissen sollte, eine SP kann auch Datensätze zurückliefern )

Zumal man bei einer SP auch Parameter übergeben kann, die den SELECT auch gleich einschränken.

Beispiel:

hole_mal_daten( <Mandant>, <ZeilenProSeite>, <Seite> )

Mit so einer SP halt man sich nur eine bestimmte Anzahl von Zeilen und kann durch verändern der Seitenzahl schön durchblättern.
In der SP kann ich dann die ganze Filterung reinbauen, damit auch nur diese Daten kommen.

Auch das Schreiben von Daten lässt man über SP's laufen.
Dann kann man den Benutzer getrost von den Tabellen abklemmen.

Aber eine View dafür zu benutzen ist doch sehr ressourcenfressend (wie du ja auch siehst).

cu

Oliver

[Bernhard Geyer]

Zitat von Errraddicator:

Also Clientseitig mache ich ja hier auch noch mal eine Filterung durch die Applikation selbst.
Die kompletten Daten werden also niemals gebraucht.

Also erst dann wenn hunderte MB an Daten schon über das Netzkabel gelaufen sind. Für eine C/S-Anwendung ist das ein sehr schlechter Ansatz.

Zitat von Errraddicator:

Das geht hierbei wirklich um eine reine Datensicherheits / -Berechtigungsgeschichte.
Wir wollen nicht, dass es ÜBERHAUPT irgendeinem Datenbankbenutzer möglich ist SELECT Abfragen direkt auf die Tabellen zu starten. Dies soll immer nur über Views geschehen, die per se nur das anzeigen, was den Benutzer zu interessieren hat.

Hier wäre evtl. der Ansatz einer N-Tier Lösung besser als eine C/S-Anwendung. Denn Client-Seitige Query-Erzeung (sei es auf Views oder direkt auf Tabellen) ist mit entsprechender krimineller Energie immer zu knacken so das man doch alles sieht. Liegt die Business-Logik auf einen (auch physikalisch) gesicherten System so gibt es schon mal sehr viel weniger Angriffspunkte. Hast du ein reines Windows-System so kannst du auch die Authentifizierun Windows überlassen so das hier keine Einfallstore mehr existieren.

[Errraddicator]

So, hab gerade mal die Speichergrenzen von Postgres etwas erweitert (div. Einstellungen verdoppelt) und prompt braucht die Abfrage nur noch ca. halb so lange.
Ein vervierfachen hat dagegen dann allerdings nichts mehr gebracht.

Bzw. Abfrage ist ja nicht ganz richtig, denn das Öffnen der View im pgAdmin dauert jetzt nur noch halb so lange.

Die letztendliche Abfrage des Programms á la "SELECT * FROM datenview WHERE monat="" AND ..."
dauerte ja eh nur ein paar Sekunden und nicht die volle Minute.

Ist aber jetzt auch ne Ecke schneller als vorher.
Von daher schien das echt was zu bringen.

...

Des Weiteren ist uns noch eingefallen, dass wir den Datenbestand als solches noch etwas reduzieren können, da wir in der eiegentlichen Datentabelle gar nicht erst alles brauchen, was da im Moment gepflegt ist.

Dadurch sollte die View dann ja auch wieder schneller werden.

...

Zu den Stored Procedues:
Das ist ja nicht ganz das was ich möchte, denn somit überlasse ich die Trennung welcher DB-Benutzer was sehen darf letztendlich wieder der Applikation die sagt "funktion('meinMandant')";
Es wäre da aber technisch gesehen genauso möglich zu sagen "Funktion('EinAndererMandant')" und das würde trotzdem funktionieren.

...

Das gleiche gilt für den Vorschlag von p80.
Wenn ich die Trennung selbst mache (also in der Applikation oder allg. via SQL), dann bräuchte ich diesen ganzen Heckmeck gar nicht mehr betreiben.

...

@Bernhard
Mit "filterung clientseitig" war das obig geschrieben Select-Kommando gemeint.
Also die Daten fließen definitiv NICHT über das Kabel, bevor sie gefiltert werden, sondern das passiert schon auf DB-Seite.

Und zum zweitem Abschnitt:
Genau darum ging es mir ja bei dieser ganzen Thematik.
Selbst wenn ich jetzt in der Lage bin, Clientseitig beliebige SQL-Kommandos zu erzeugen und abzusetzen, bin ich durch meine View trotzdem nicht in der Lage alle Daten zu sehen, da in der View per se und immer nur die Daten angezeigt werden, welche mich gerade betreffen.

Da nützt dann auch kein SQL Kommando "SELECT * FROM view WHERE mandant='EinAnderer'" was, denn diese Daten sind in der View schlicht und einfach nicht vorhanden.
Und auf die letztendliche Datentabelle hat wie gesagt nur der Admin zugriff und der darf sich gar nicht erst von der Clientseite her anmelden, da wird schon die Anmeldung verweigert.

[Sir Rufo]

Zitat von Errraddicator:

Zu den Stored Procedues:
Das ist ja nicht ganz das was ich möchte, denn somit überlasse ich die Trennung welcher DB-Benutzer was sehen darf letztendlich wieder der Applikation die sagt "funktion('meinMandant')";
Es wäre da aber technisch gesehen genauso möglich zu sagen "Funktion('EinAndererMandant')" und das würde trotzdem funktionieren.

öh, in deiner View ist irgendwie alles enthalten und das ist dann sicherer

So eine SP kann man auch intelligent aufbauen.

In der SP wird z.B. der SQL-Benutzer mit ausgewertet ... was darf der sehen
Oder gib als zusätzliche Parameter einen Benutzernamen und das Passwort (als MD5-Hash) mit und mach daran die Selektion fest.

Eine SP darf durchaus intelligent funktionieren

cu

Oliver

[Errraddicator]

Zitat von Sir Rufo:

öh, in deiner View ist irgendwie alles enthalten und das ist dann sicherer

So eine SP kann man auch intelligent aufbauen.

In der SP wird z.B. der SQL-Benutzer mit ausgewertet ... was darf der sehen
Oder gib als zusätzliche Parameter einen Benutzernamen und das Passwort (als MD5-Hash) mit und mach daran die Selektion fest.

Eine SP darf durchaus intelligent funktionieren

cu

Oliver

@1
Ja ist es, weil in der View nur die Daten meiner Mandanten drin sind.
Ganz ohne SQL-Abfrage, Where-Klausel, Parameter oder sonst was.

Die View weiß also von Haus aus, wer sie öffnet (postgres-funktion current_user) und was dieser sehen darf



@2
Ok, wenn man das so macht hast Du natürlich Recht.
Aber diese doppelt- und dreifach-überprüfung entfällt in meinem Falle halt komplett,
weil das die View das schon intern regelt.

Das was Du geschrieben hast ist ja fast(!) das was mein Programm vorher PHP-Seitig gelöst hat.
Da wurde auch alles immer gegeneinander geprüft und verglichen.

Nur das man es quasi von PHP auf DB Ebene verschoben hätte.

...

Das brauche ich jetzt aber eben eigentlich gar nicht mehr.
Denn ich bin wie gesagt auf Datenbankebene schon so eingeschränkt,
dass ich nicht an die Daten anderer Mandanten (und darum gehts ja) komme.

Dafür muss ich direkt auf die Datentabelle und das darf ausser dem Admin keiner.
(Oder ich schaffe es mich als anderer Datenbankbenutzer anzumelden, aber dagegen kann man dann eh nix machen).

[Sir Rufo]

Ich glaube wir sprechen gerade aneinander vorbei
SELECT * FROM foo WHERE user = current_user Du lieferst über dein View (s.o. mal als Beispiel) alle Daten, die der aktuelle Benutzer sehen darf und schränkst diese dann auf dem Client ein.
Das dauert, weil dafür auf dem Server Speicher reserviert werden muss (viel) und dann müssen alle Daten an den Client gesendet werden (viel und dauert deshalb so lange).
SELECT * FROM foo WHERE user = curent_user AND field1 = Einschraenkung Nimmst du eine SP, dann übergibst du mit dieser SP die Einschränkungskriterien. In der SP wird der gleiche SELECT wie in deiner View ausgeführt, nur zusätzlich mit den übergebenen Einschränkungen (Parameter der SP s.o.).
Der Server muss jetzt nicht mehr so viel Speicher reservieren und auch nicht mehr so viele Daten an den Client senden.
Somit geht das alles flotter, bei gleicher Sicherheit.

[Elvis]

Kannst du ein etwas realistischeres Beispiel geben?
das SQL von ersten Post macht ja reichlich wenig sinn:
SELECT d.* FROM datentabelle d, zugrifftabelle z WHERE d.mandant = z.mandant IOW: Gebe mir x-mal die gleichen Rows aus d, schließlich wird z.mandant nicht eindeutig sein, oder? Und selbst wenn, wo ist denn da die Einschränkung?

Oder dachtest du an sowas?

SQL-Code:

SELECT d.*
FROM   Datenzeugs d 
INNER JOIN UserPrivileges up on up.Privilege = d.RequiredPrivilege
where  up.UserName = Current_User()

Aber mal abgesehen davon, halte ich das für eine grauenvolle Idee...
Direkten Zugriff auf die DB (nicht nur auf die Tabellen) sollte gar kein Normalsterblicher haben.
Wir schreiben fast 2010, nicht 1989. Klassische C/S Systeme in Bereichen wo man sich um Sicherheit und Zuverlässigkeit sorgen muss sind doch nun wirklich mehr als ausgedient.

Ist Zugriff für generische Reporting Tools (Crystal, etc) nötig, dann kann man sich zum Beispiel einen Simple-Mode OleDB Provider bauen. Oder einfach Reporting als Zusatzfeature mit anbieten.
Dann geht das alles geregelt durch den Appserver und nicht daran vorbei in die DB.

Der Server wird stets und ständig aus allen Löchern pfeifen und er wird schneller einen Punkt erreichen, bei dem du noch soviel Hardware danach schmeißen kannst. Du wirst einfach nicht weiter skalieren können, wenn du mit solchen Views den Query-Optimizer boykottierst.
Und das wiederum heißt, dass deine App ein Mindesthaltbarkeitsdatum auf dem Deckelrand gedruckt hat, ab dem man eine neue App brauchen wird, da diese irgendwann mit den wachsenden User und/oder Daten nicht mehr standhalten können wird.