Berechtigungen für Schema

[Mike5689]

Hallo

Ich bin gerade dabei das Backend einer Access Anwendung auf einen MSSQL Server 2019 zu verschieben.
Damit die Benutzer im Frontend nicht an den verknüpften Tabellen rumspielen können und weil ich irgendwann mal das komplette Frontend erneuern möchte, verpacke ich alle SQL Abfragen in gespeicherten Prozeduren. So weit, so gut.

Ich brauche für die Nutzer verschiedene Zugriffs Level, sagen wir Level 1 bis Level 4. Also habe ich 4 Schemas angelegt die Benutzer/Benutzergruppen den Schemas zugeordnet und Ausführungsrechte für die SPs gegeben.
Ich bin allerdings bisher davon ausgegangen das die Prozeduren auch auf Tabellen zugreifen können die in einem anderen Schema sind. das scheint aber nicht so zu sein. Verschiedene Prozeduren funktionieren nämlich nicht mehr wenn ich die Anwendung mit anderem Account aufrufe. Da ist mir aufgefallen das es alle Prozeduren sind die auf dbo. Tabellen zugreifen.

Ist das so? Wenn ja, kann man das einstellen? Oder muss ich die Tabellen ins gleiche Schema packen wie die Prozedur?
Oder mache ich für die Tabellen ein eigenes Schema und gebe dem Benutzer einmal Rechte für das Tabellenschema und Rechte für das Level Schema???
Die Tabellen werden ja auch zum größten Teil in allen Levels gebraucht.

Ich hoffe ihr könnt mir folgen. Ist gar nicht so einfach das zu beschreiben....

Mike

 

[dabadepdu]

SQL verfügt je nach Hersteller über verschiedene Möglichkeiten, Berechtigungen zu erteilen, z.B.

 GRANT SELECT ON SCHEMA :: level1 TO leseuser

Bei MS musst Du unterscheiden, ob Du dabei mit SQL internen Berechtigten arbeitest (wie bei den meisten DB) oder mit Domänen (Windows Domäne). Anhand Deiner bisherigen Angaben würde ich vermuten, dass Du wahrscheinlich keine Domäne eingerichtet hast.

Ein Berechtigungskonzept zu nutzen, ist sicher nicht verkehrt. Also gar nicht. Nur baust Du hier etwas sehr spezifisches für Access, das Du auch wiederum umbauen willst. Wenn Du soviel umbauen willst, würde ich noch mal überlegen, ob Du da nicht andere Wege möglich sind.

 

[Mike5689]

Hallo dabadepdu

Also ich benutze die Windows Authentifizierung. Macht das denn einen Unterschied in den Berechtigungen ob ich nun Win oder Server Authentifizierung nutze?

Wieso ist mein Berechtigungssystem speziell? Verstehe ich nicht? Wenn ich später ein anderes Frontend nutze in dem der Benutzer keine Möglichkeit hat Tabellen zu öffnen kann er sich doch immer noch direkt auf dem SQL Server anmelden um dort sein Unwesen zu treiben. Oder sehe ich das falsch?

Welche andere Wege schlägst du vor?

GRANT SELECT ON SCHEMA :: level1 TO leseuser

Dem Benutzer habe ich das Level1 Schema alle Berechtigungen gegeben (eigentlich sollte ja EXECUTE reichen um die SP auszuführen).
Er kann aber dennoch über die SPs auf keine Tabelle zugreifen die sich in einem anderen Schema als Level 1 befindet.


Gruß,
Mike

 

[dabadepdu]

Bin mir nicht sicher, ob ich Dich richtig verstehe. Mir ist nicht bekannt, ob die Windows Authentifizierung für SQL Server Zugriff nur für Domänen Systeme gilt/möglich ist (Windows PC mit Anmeldung an der Domäne) oder auch für eine lokale Windows Anmeldung ohne Domänen Anbindung.
Der Unterschied ist fordergründig erstmal, dass die "externe" Anmeldung per Windows Account "implizit", ohne Passwort erfolgt. Eine Zugriffsberechtigung an ein zweites SQLServer Schema hat damit aber erstmal nichts zu tun. Ein Schema ist auch ein SQL Server Objekt. Domänenrechte kommen glaube ich erst mit dem User ins Spiel, der, der an Windows angemeldet ist. Sowas z.B.:

GRANT SELECT ON level1.address TO [<domaine>\RosaQdM];

Das wirkt sich dann so aus, dass ein Nutzer, der sich nicht mit seinem Domänen Account an einem Domänen PC anmeldet, auch keinen Zugriff auf die DB hat. Deaktivierten eines Domänenusers verhindert also auch den DB Zugriff dieses Users. Die DB Rechte müssen dazu administrativ nicht geändert werden. Für lokale Windows Accounts kann das eigentlich nur klappen, wenn auch der SQL Server lokal ist. Keine Ahnung, hab ich nie gemacht.

Mit "speziell" meine ich, dass Du an einigen Stellen Hilfskonstruktionen um Deine DB aufbaust, die auf Dein Problem mit Access ausgerichtet sind. Und dass es so aussieht, dass es lückenhaft ist. (Das Loch in der verschlossenen Tür)
Grob gibt es 2 Szenarien:
- Mehrschicht System: DB Server gewährt Zugriff für bestimmte Vorgänge, Mittelschicht (Appserver / Webserver/ API Server) nutzt das, stellt selbst Schnittstelle nach außen inkl. Login bereit, Client behandelt Login, arbeitet gegen Mittelschicht, kennt nichts vom DBServer
- Client-Server-System: DB Server gewährt explizit Zugriffsrechte auf seine Objekten an benannte Benutzer.

Du bist da gefühlt irgendwo im Mischbetrieb.

 

[Mike5689]

Zu Hause habe ich MSSQL Lokal installiert und teste mit lokalen Benutzer mit Windows Authentifizierung.
In der Firma hat die IT mir eine VM installiert mit MSSQL . Hier greife ich mit Win Auth über die Domänen Accounts zu.
Mit meinem Account funktioniert das auch alles, von allen Rechnern die freigeschaltet sind (ich habe ja sämtliche Rechte für die DB).
Wenn ich unter einem anderen Account anmelde, dem ich natürlich zuvor in der DB die Rechte auf Schema "Level1" gegeben habe ,funktionieren unter dem Account nur die Prozeduren wo die in der Prozedur verwendeten Tabellen auch in Schema "Level1" sind.
Auf meinem Lokalen Rechner zu Hause beobachte ich das gleiche Verhalten.

Mischbetrieb:
Ja Access ist halt ein Sonderfall, das ist mir schon klar. Das System ist aber über Jahre gewachsen und soll auch aktuell noch erweitert werden.
Das ist jetzt halt so. Ich sehe mich aber zeitlich nicht dazu in der Lage Front und Backend auf einen Schlag komplett neu zu machen. Deswegen erstmal nur das Backend, dann die Erweiterungen und irgendwann mal ein neues Frontend.

Ich habe mir zu dem Zweck ein Buch gekauft (Access & SQLServer) und da ist dieses Verfahren (alle Anfragen über gespeicherte Prozeduren zu senden) drin erklärt. Deswegen bin ich davon ausgegangen das es funktioniert. Ich habe wahrscheinlich eine Kleinigkeit übersehen, muss wohl das Buch noch mal lesen....

Das mit den Benutzerrechten ist jetzt auch kein Drama, ich kann den Kollegen auch höhere Rechte gewähren und dann läuft das. Aktuell gibt es kaum Sicherheit und das System läuft seit Jahren. Es ist aber mein persönlicher Ehrgeiz der mich da treibt das möglichst perfekt hin zu bekommen.

Mike

 

[dabadepdu]

DB Serverrechte über Gruppen oder andere Verfahren zu verteilen ist an sich nicht problematisch, im Gegenteil. Man kann auch sehr gut Abfragen (Views) nur für Lesezugriff (Reports), Detail- oder Aggregatangaben (Manager Views), Sachbearbeitung usw. zentral und pflegeleicht oder schichtweise bereitstellen. Es war ging mir hauptsächlich um den Hinweis, nicht zu speziell etwas für Access zu bauen, wenn eh alles umgebaut wird.
An der Stelle machen auch am ehesten (jenseits von Access), insert, update, delete Prozeduren Sinn, weil sie ähnlich wie Views ein Interface darstellen und auch unabhängig vom Quellview funktionieren. So kannst Du unterschiedlich angereicherte Datenmengen (Views mit der gleichen Kerntabelle) darstellen und sie mit der gleichen Prozedur aktualisieren, also z.B. reine Kundendaten, Kundendaten mit letztem Kontakt, Kundendaten mit Verkaufszahlen, .. 3-x Views mit der gleichen Update Prozedur. (Bei Verkaufszahlen o.ä. ist naturgemäß klar, dass man die nicht updaten kann).
In einer Prozedur kannst Du auch einen Typwechsel behandeln, z.B. eine Spalte vom Typ money ist in der Prozedur ein decimal-in Parameter und wird dort vor dem Update der Tabelle wieder auf money konvertiert (falls sich keine andere Lösung findet).

 

[Mike5689]

Danke für deine Erklärungen. Ich habe den kompletten SQL Code und Abfragelogik aus Access rausgenommen und alles in SPs auf den DB Server gespeichert. Das sind jetzt weniger DB Aufrufe als vorher weil ich viele Sachen direkt in den Prozeduren auf dem Server machen kann. Da erhoffe ich mir einen Performance Gewinn der hoffentlich die Arbeit über VPN angenehmer macht. Zum 2. bilde ich mir ein das ich dadurch beim Wechsel auf ein anderes Frontend schon einiges an Arbeit gespart habe weil ich nur noch die SPs aufrufen muss.
Da es für mich aber Neuland ist weiß ich nicht ob mein Plan aufgeht.

Mike

 

[t-sql]

SQL verfügt je nach Hersteller über verschiedene Möglichkeiten, Berechtigungen zu erteilen, z.B.

 GRANT SELECT ON SCHEMA :: level1 TO leseuser

Bei MS musst Du unterscheiden, ob Du dabei mit SQL internen Berechtigten arbeitest (wie bei den meisten DB) oder mit Domänen (Windows Domäne). Anhand Deiner bisherigen Angaben würde ich vermuten, dass Du wahrscheinlich keine Domäne eingerichtet hast.

Ein Berechtigungskonzept zu nutzen, ist sicher nicht verkehrt. Also gar nicht. Nur baust Du hier etwas sehr spezifisches für Access, das Du auch wiederum umbauen willst. Wenn Du soviel umbauen willst, würde ich noch mal überlegen, ob Du da nicht andere Wege möglich sind.

Für die Rechtevergabe im SQL Server ist es egal ob über eine AD oder SQL Server authentifiziert wird.

 

[t-sql]

Hallo

Ich bin gerade dabei das Backend einer Access Anwendung auf einen MSSQL Server 2019 zu verschieben.
Damit die Benutzer im Frontend nicht an den verknüpften Tabellen rumspielen können und weil ich irgendwann mal das komplette Frontend erneuern möchte, verpacke ich alle SQL Abfragen in gespeicherten Prozeduren. So weit, so gut.

Ich brauche für die Nutzer verschiedene Zugriffs Level, sagen wir Level 1 bis Level 4. Also habe ich 4 Schemas angelegt die Benutzer/Benutzergruppen den Schemas zugeordnet und Ausführungsrechte für die SPs gegeben.
Ich bin allerdings bisher davon ausgegangen das die Prozeduren auch auf Tabellen zugreifen können die in einem anderen Schema sind. das scheint aber nicht so zu sein. Verschiedene Prozeduren funktionieren nämlich nicht mehr wenn ich die Anwendung mit anderem Account aufrufe. Da ist mir aufgefallen das es alle Prozeduren sind die auf dbo. Tabellen zugreifen.

Ist das so? Wenn ja, kann man das einstellen? Oder muss ich die Tabellen ins gleiche Schema packen wie die Prozedur?
Oder mache ich für die Tabellen ein eigenes Schema und gebe dem Benutzer einmal Rechte für das Tabellenschema und Rechte für das Level Schema???
Die Tabellen werden ja auch zum größten Teil in allen Levels gebraucht.

Ich hoffe ihr könnt mir folgen. Ist gar nicht so einfach das zu beschreiben....

Mike

Schau dir das mal an http://andreas-wolter.com/schema-design-fuer-sql-server-empfehlungen-fuer-schema-design-mit-sicherheit-im-blick/ dort wird es sehr gut beschrieben

 

[Mike5689]

Schau dir das mal an http://andreas-wolter.com/schema-design-fuer-sql-server-empfehlungen-fuer-schema-design-mit-sicherheit-im-blick/ dort wird es sehr gut beschrieben

Danke, das ist genau das was ich brauche. Genau so wollte ich da machen. Werde mir das heute Abend mal in Ruhe durchlesen...

Mike

 

[dabadepdu]

Für die Rechtevergabe im SQL Server ist es egal ob über eine AD oder SQL Server authentifiziert wird.

Ja, natürlich, dem Server ist egal, welche Rechte vergeben werden. Es sind aber total unterschiedliche Konzepte aus Client Sicht, die unterschiedlich bedient werden müssen. Darauf wollte ich lediglich hinweisen.

 

[t-sql]

Ja, natürlich, dem Server ist egal, welche Rechte vergeben werden. Es sind aber total unterschiedliche Konzepte aus Client Sicht, die unterschiedlich bedient werden müssen. Darauf wollte ich lediglich hinweisen.

Nee müssen sie nicht und haben auch mit dem Client nichts zu tun. Klar ist, das bei SQL Authentifizierung andere Logins genutzt werden. Das hat aber mit dem Berechtigungsvergabe auf den Schemata nichts zu tun. Da isses Wurscht ob AD oder nicht.

 

[dabadepdu]

Nee müssen sie nicht und haben auch mit dem Client nichts zu tun

Gut, wie meldet sich ein User außerhalb des AD an, wenn es nur AD Berechtigungen gibt?

 

[Mike5689]

Gut, wie meldet sich ein User außerhalb des AD an, wenn es nur AD Berechtigungen gibt?

Das verstehe ich nicht, warum gibt es nur AD Berechtigungen?
Ich muss doch jeden Benutzer oder Benutzergruppe doch erstmal auf dem DB Server anmelden. Erst danach kann ich den Benutzer erst Rechte zuweisen oder ihm ein Schema zuweisen. Oder liege ich jetzt wieder total daneben?

Mike

 

[dabadepdu]

Das verstehe ich nicht, warum gibt es nur AD Berechtigungen?

Sorry, das habe ich nicht gesagt. Es ist OT, es geht da nur um die Sichtweisen von t-sql und mir.

Es ist auf dem Server beides möglich und dem Server egal (ich wiederhole mich). Für den Fall, dass jemand aus einem Nicht AD Bereich anmelden will, macht es halt einen Unterschied. Das ist m.E. ein konzeptioneller Unterschied, wenn und ob man AD verwendet oder nicht, mal abgesehen von den unterschiedlichen Anmeldemodalitäten. Wenn vorhandene AD Strukturen und Berechtigungsbedarf der Anwendung sich decken, braucht man halt nur 1x die AD Gruppe zu berechtigen und ist fertig.