Rechteverwaltung für Microsoft SQL Server - dFPUG

Rechteverwaltung für Microsoft SQL Server
Armin Kast
Rechteverwaltung im SQL Server 7 / SQL Server 2000 in der Praxis
Rechte auf DB und Tabellen
Entwickler / Support wird kontaktiert und
der Fehler beschrieben.......
Damit ein Anwender mittels Clientsoftware
auf einen SQL Server 7 zugreifen kann,
benötigt dieser Rechte auf die Datenbank
und die darin enthaltenen Tabellen. Diese
Rechte können in unterschiedlichster Art
vergeben werden. Entweder man verwendet
die visuellen Werkzeuge des SQL Servers 7
wie z.B. den Enterprise Manager, oder man
schreibt ein Script mit welchem dann die
darin verankerten Rechte zum SQL Server 7
übertragen werden. Wie auch immer die
Rechte auch auf den SQL Server 7
gekommen sind, vielfach möchte man diese
auch wieder auslesen und prüfen ob ein
Nutzer auch wirklich die gültigen Rechte
besitzt. Stellen wir uns folgendes Szenario
vor:
So oder ähnlich kann es sehr schnell gehen
wenn
man
nicht
geeignete
Schutzmechanismen,
oder
gar
eine
komplette
Nutzerverwaltung
im
Administrationsteil
einer
Applikation
implementiert.
Wie kann man nun die Rechte aus dem SQL
Server herauslesen und prüfen ob die
geeigneten Rechte vorhanden sind. Zum
einen durch eine kleinen Test in welchem
man versucht einen Dummydatensatz, den
man z.B. in einer Tabelle platziert hat, zu
schreiben.
SQLEXEC(hnd,“UPDATE xyz SET testfeld = 1 WHERE NR=-1 “)
Das programmierte Chaos
Ein Entwickler hat seine fertige Applikation
beim Kunden installiert und mit dem SQL
Server-Administrator des Kunden die
Datenbank mit den zugehörigen Tabellen,
Constraints und Trigger eingerichtet. Die
Rechte wurden anschließend mit einen
Script, für den vom Kunden definierten
Anwenderkreis, auf dem SQL Server
eingetragen. Der Administrator erhält durch
Einweisung Kenntnis über die Pflege und
Neueinrichtung von weiteren Benutzern.
Ein paar Tage später soll der Administrator
einen weitern Benutzer einrichten. Hierbei
unterläuft ihm ein Fehler und er setzt
Schreibrechte ( INSERT / UPDATE /
DELETE ) von Anwendern auf nur
Leserechte ( SELECT ) zurück. Die
Applikation bricht beim aktualisieren von
Daten mit einer Fehlermeldung ab. Der
03-09 Rechteverwaltung für Ms SQL Server
Ein Lösungsansatz
Liefert SQLEXEC() ein positives Ergebnis
so konnte auf die Tabelle geschrieben
werden. Dies ist aber nicht der Weisheit
letzter Schluss, da auch der Testdatensatz
gelöscht werden könnte. Auch der Versuch
mittels
SQLEXEC(hnd,“INSERT
VALUE(„Test“,1,2,3)“)
INTO
xyz
ist keine gute Variante da dieser Satz auch
anschließend wieder gelöscht werden muss.
Bricht die Applikation an dieser Stelle ab,
steht der Datensatz in der Tabelle.
Der richtige Weg...
Also müssen wird auf die Systemobjekte des
SQL Servers zurückgreifen und die dort
gespeicherten Rechte auslesen. Die Rechte
auf die Tabellen eines Anwenders stehen in
FoxX-Professional
Seite 1
Systemtabellen des SQL Servers. In der
unten stehenden Grafik ist die Zuordnung
der Systemtabellen, welche sich in jeder
Datenbank befinden, abgebildet. Die
Vorgehensweise ist folgende. Zuerst müssen
wir wissen welcher Anwender sich am
System angemeldet hat. Dies erfahren wir
über die SUSER_SID()- bzw. über die
Über die SID ermitteln wir die UID, welche
in der Systemtabelle SYSUSERS steht. Im
weiteren verlauf können wir dann die
Rechte, welche sich in der Systemtabelle
SYSOBJECTS sowie SYSPROTECTS
befinden, auf die Tabellen dieses Anwenders
ermitteln. Hierzu kommen folgende
Funktionen des SQL Servers zum Einsatz:
SUSER_SID() liefert die
angemeldeten Anwenders.
SID
des
PATINDEX('%pattern%', expression) Gibt
für
alle
gültigen
Textsowie
Zeichendatentypen die Startposition des
ersten Auftretens eines Musters (Pattern) in
einem angegebenen Ausdruck bzw. NULL,
wenn das Muster nicht gefunden wird,
zurück. Da die Muster aber mit Sicherheit
existieren können wir immer davon
ausgehen ein Ergebnis in numerischer Form,
nämlich genau 1, zu erhalten.
SUSER_NAME()- Funktion.
SELECT SO.name,
MAX(PATINDEX('193',STR(SP.action,3,0))) AS SEL,
MAX(PATINDEX ('195',STR(SP.action,3,0))) AS INS,
MAX(PATINDEX ('196',STR(SP.action,3,0))) AS DEL,
MAX(PATINDEX ('197',STR(SP.action,3,0))) AS UPDT
FROM Sysobjects SO, Sysprotects SP
WHERE SP.id = SO.id AND
SP.uid IN ( SELECT uid FROM Sysusers
WHERE SID = SUSER_SID() )
AND SP.protecttype <> 206
GROUP BY SO.name
Dieser etwas komplexere SELECT Befehl
liest uns für den angemeldeten Anwender
alle Rechte „193=SELECT, 195=INSERT
196=DELETE und 197=UPDATE“ auf die
Tabellen aus und stellt das Ergebnis in der
Form dar, das auf den Tabellennamen das
Recht in numerischer Schreibweise folgt.
Wichtig ist die Abfrage „SP.protecttype <>
206“ da ansonsten evtl. entzogene
Privilegien nicht berücksichtigt werden!
Die Abfrage speichert den Wert für
SELECT/INSERT/UPDATE/DELETE
der jeweilige Tabelle mit 1 insofern hierfür
ein Recht vergeben wurde und den Wert 0
wenn kein Recht existiert. Nun kann mittels
einfacher Abfrage auf dieses Ergebnis des
Rechtstatus vom Anwenders geprüft
03-09 Rechteverwaltung für Ms SQL Server
werden. Befindet sich die zu prüfende
Tabelle nicht in der Abfrage so hat der
Anwender keinerlei Rechte hierauf, da in der
Tabelle SYSPROTECTS nur die vergebenen
Rechte gespeichert werden. Will man dies
ändern so braucht man nur aus dem
Objektkatalog SYSOBJECTS alle Tabellen
mit dem Ergebnis zu verknüpfen ( LEFT
OUTER JOIN ) und hat dann alle Tabellen
komplett mit den gewährten Rechten
dargestellt. Hierzu verwendet man die unten
aufgeführte SQL- Anweisung.
Diese Art der Rechtprüfung hat sich in der
Praxis schon häufig bewährt da der
Anwender hiermit vor den fehlenden
Berechtigungen aus der Applikation heraus
gewarnt werden kann um dann den
FoxX-Professional
Seite 2
Administrator zu verständigen. Diese
Abfrage gewährleistet aber nicht, dass
eventuell festgelegte Restriktionen auf
Feldebene erfasst wurden. Hierzu muss in
der Systemtabelle SYSPROTECTS die
Spalte „Columns“ ausgewertet werden in
welcher sich das Bitmuster der Felder
befindet! Ebenso muss angemerkt werden,
das diese Abfrage nur für Einzelanwender
und nicht für Gruppen- bzw. Rollenrechte
gilt!
Abfragen der Rechte auf alle Tabellen in der Datenbank:
SELECT SO.name,
ISNULL(MAX(PATINDEX('193',STR(SP.action,3,0))),0)
ISNULL(MAX(PATINDEX('195',STR(SP.action,3,0))),0)
ISNULL(MAX(PATINDEX('196',STR(SP.action,3,0))),0)
ISNULL(MAX(PATINDEX('197',STR(SP.action,3,0))),0)
FROM Sysobjects SO
LEFT JOIN Sysprotects SP
ON SO.id = SP.id
AND SP.uid IN ( SELECT uid FROM Sysusers
WHERE SID = SUSER_SID() )
AND SP.protecttype <> 206
GROUP BY SO.name,SO.xtype
HAVING SO.xtype = 'U'
Da das LEFT JOIN Ergebnis NULL-Werte
zurückliefert,
werden
diese
mittels
ISNULL()-Funktion in einen numerischen
Wert gewandelt ( aus NULL wird 0 ) und
können so Problemlos im Ergebnis
dargestellt werden. Die HAVING-Klausel
Sysobjects.xtype = 'U' sorgt dafür, das nur
die Tabellen angezeigt werden. Um z.B. alle
zugelassenen Sichten darzustellen wird
Sysobjects.xtype = 'V' angegeben. Aber
nochmals zum Verständnis. Diese Abfragen
gelten nur für gewährte Rechte an einen
einzelnen Anwender. Rechte welche durch
Rollen geerbt wurden können so nicht
erfasst
werden.
Ein
Tabelleneigner
(DataBaseOwner) wird seine Tabellen
immer lesen und schreiben können ohne ein
explizietes Recht darauf zu besitzen. Wird
eine Rolle angelegt und der Rolle Rechte auf
Tabellen gewährt (Grants), so erben die
Mitglieder dieser Rolle alle Rechte der Rolle.
In der Tabelle SYSUSERS kann in der
Spalte (Attribut) „issqlrole“ bzw. „isapprole“
gesehen werden welche Rollen existieren.
Mit SELECT name FROM SYSUSERS
WHERE issqlrole = 1 können alle Rollen
03-09 Rechteverwaltung für Ms SQL Server
AS
AS
AS
AS
SEL,
INS,
DEL,
UPDT
abgefragt werden. Die Existenz von
Applikationsrollen werden mittels SELECT
name FROM SYSUSERS WHERE isapprole
= 1 geprüft. Applikationsrollen sind sehr
interessante Rollen, da man nur mittels der
Applikation auf die Daten zugreifen kann.
Versucht ein Anwender einer Applikationsrolle mittels anderer Produkte auf die Daten
zuzugreifen so wird dieses vom SQL Server
blockiert. Nur die definierte Applikation darf
die Daten nutzen. Mittels Applikationsrollen
kann dann die Anwendungssicherheit
gesteigert werden.
Alle Rollenrechte erfassen
Um nun alle Rollenrechte zu erfassen
müssen wir unsere SQL- Abfrage um die
Systemtabelle SYSMEMBERS erweitern um
somit die Zuordnung des Anwenders zur
Rolle zu ermitteln. Hierzu ist die groupuid der
memberuid, welcher der uid des Anwenders
entspricht, gegenübergestellt. Somit erhalten
wir die groupid welchen der Anwender
zugeordnet ist in unsere Ergebnismenge.
FoxX-Professional
Seite 3
Abfrage der Rechte (SELECT / INSERT / UPDATE / DELETE) auf die Tabellen der Rolle(n)
denen ein Anwender zugeordnet ist. Hiermit werden nicht die Einzelrechte berücksichtigt!
SELECT SO.name,SO.xtype,
ISNULL(MAX(PATINDEX('193',STR(SP.action,3,0))),0) AS SEL,
ISNULL(MAX(PATINDEX('195',STR(SP.action,3,0))),0) AS INS,
ISNULL(MAX(PATINDEX('196',STR(SP.action,3,0))),0) AS DEL,
ISNULL(MAX(PATINDEX('197',STR(SP.action,3,0))),0) AS UPDT
FROM Sysobjects SO
LEFT JOIN Sysprotects SP
ON SO.id = SP.id
AND SP.uid IN ( SELECT groupuid FROM sysmembers
WHERE memberuid = ( SELECT uid FROM Sysusers WHERE SID =
SUSER_SID() ) AND groupuid >= 16400 )
AND SP.protecttype <> 206
GROUP BY SO.name,SO.xtype
HAVING SO.xtype = 'U'
Das Ergebnis wird in der folgenden Form dargestellt.
name
xtype SEL
INS
DEL
-----------kunden
positionen
artikel
plz
blz
vorlauf
dta_store
belege
----- ---U
1
U
1
U
1
U
1
U
1
U
1
U
0
U
1
---1
1
1
0
0
0
0
1
---0
1
0
0
0
0
0
1
Auch wenn durch mehrere Rollen die
Rechte auf die Tabellen für den Anwender
unterschiedlich vergeben wurden, wird das
höchste Tabellenrecht in der abgerufenen
Darstellung angezeigt. Wie zu erkennen ist
kann, in dem aufgeführten Beispiel, auf die
Tabelle dta_store nicht zugegriffen werden,
da das SELECT- Recht = 0 ist. Hier kann
eine Applikation durch das fehlende Recht
UPDT
----1
1
1
0
0
0
0  keine Rechte
1
mit einer Fehlermeldung abbrechen wobei
wir wieder am Eingangspunkt des Artikels
angelangt wären. Durch die im Artikel
angegebenen SQL- Abfragen kann der
Anwendungsentwickler die Rechte während
der Laufzeit kontrollieren und somit die
Fehlerquote im Berechtigungssektor um ein
vielfaches herabsetzen.
Eine erweiterte Abfrage welche sowohl die Rollen, als auch die Einzelrechte beinhaltet sieht
folgendermaßen aus:
SELECT SO.name,SO.xtype,
ISNULL(MAX(PATINDEX('193',STR(SP.action,3,0))),0) AS SEL,
ISNULL(MAX(PATINDEX('195',STR(SP.action,3,0))),0) AS INS,
ISNULL(MAX(PATINDEX('196',STR(SP.action,3,0))),0) AS DEL,
ISNULL(MAX(PATINDEX('197',STR(SP.action,3,0))),0) AS UPDT
FROM Sysobjects SO LEFT JOIN Sysprotects SP ON SO.id = SP.id
AND ( SP.uid IN ( SELECT groupuid FROM sysmembers WHERE memberuid = (
SELECT uid FROM Sysusers WHERE SID = SUSER_SID() ) AND groupuid >= 16400 )
OR SP.uid IN ( SELECT uid FROM Sysusers WHERE SID = SUSER_SID()) )
AND SP.protecttype <> 206 GROUP BY SO.name,SO.xtype
HAVING SO.xtype = 'U'
03-09 Rechteverwaltung für Ms SQL Server
FoxX-Professional
Seite 4
Zur Anregung hier nun eine Abfrage in
welcher mit Bitdarstellung in einer Spalte
gearbeitet wird. In der Bitreihe 8 4 2 1 ( 2^3
2^2 2^1 2^0 ) setzen wir SELECT = 2^0 =
1 , INSERT = 2^1 = 2 , DELETE = 2^2 =
4 und UPDATE = 2^3 = 8. Diese Reihe
liesse sich leicht für weitere Berechtigungen
erweitern. So lassen sich die Rechte in einer
Spalte
zusammenfassen.
In
einer
Applikation brauchte man nun, wenn alle
Rechte gefordert sind, den Wert 15
abzufragen ( SEL + INS + DEL + UPDT
= 1 + 2 + 4 + 8 = 15 ). Hat der Anwender
keine 15, so sind die erforderliche Rechte
nicht an ihn vergeben! Mittels BITAND()
und INT() Funktion lassen sich dann die
Rechte wieder ermitteln. Wollen wir z.B. das
UPDATE- Recht prüfen so schreiben wir
INT(BITAND( 15 , 8 )/8). 15 ist das
Ergebnis welches die Abfrage liefern könnte
und 8 das zu prüfende BIT ( 2^3 ) für das
UPDATE Recht. Ist das Ergebnis eine 1 so
ist an dieser Stelle ein BIT gesetzt und der
Anwender hat das Recht. Ist das Ergebnis
eine 0 so existiert kein UPDATE Recht.
SELECT LEFT(SO.name,30) Name,
ISNULL(MAX(PATINDEX('193',STR(SP.action,3,0))),0) * 1 +
ISNULL(MAX(PATINDEX('195',STR(SP.action,3,0))),0) * 2 +
ISNULL(MAX(PATINDEX('196',STR(SP.action,3,0))),0) * 4 +
ISNULL(MAX(PATINDEX('197',STR(SP.action,3,0))),0) * 8 AS Recht
FROM Sysobjects SO LEFT JOIN Sysprotects SP ON SO.id = SP.id
AND ( SP.uid IN ( SELECT groupuid FROM sysmembers WHERE memberuid = (
SELECT uid FROM Sysusers WHERE SID = SUSER_SID() ) AND groupuid >= 16400 )
OR SP.uid IN ( SELECT uid FROM Sysusers WHERE SID = SUSER_SID()) )
AND SP.protecttype <> 206 GROUP BY SO.name,SO.xtype
HAVING SO.xtype = 'U'
Abfrage der Rechte als Ergebnis
in einer Spalte:
und somit
aufspüren.
fehlende
Berechtigungen
Letztendlich erhalten wir eine Abfrage in
welcher aus allen Tabellen alle möglichen
Einzel- als auch Rollen- Rechte gelesen
werden. Mittels dieses Ergebnis kann der
Anwendungsentwickler mit ein paar
Routinen die Rechte des Anwenders prüfen
Zum Schluß sei noch anzumerken das einige
dieser SQL-Abfragen auch mit dem SQL
Server 6.5 (SP5a) funktionieren. Die SQLAbfragen, bei welchen die Rollen ausgelesen
werden, funktionieren aber nur mit SQL
Server 7 bzw. SQL Server 2000.
SELECT LEFT(SO.name,30) Name,
ISNULL(MAX(PATINDEX('193',STR(SP.action,3,0))),0) * POWER(2,0) +
ISNULL(MAX(PATINDEX('195',STR(SP.action,3,0))),0) * POWER(2,1) +
ISNULL(MAX(PATINDEX('196',STR(SP.action,3,0))),0) * POWER(2,2) +
ISNULL(MAX(PATINDEX('197',STR(SP.action,3,0))),0) * POWER(2,3) +
ISNULL(MAX(PATINDEX('198',STR(SP.action,3,0))),0) * POWER(2,4) +
ISNULL(MAX(PATINDEX('203',STR(SP.action,3,0))),0) * POWER(2,5) +
ISNULL(MAX(PATINDEX('207',STR(SP.action,3,0))),0) * POWER(2,6) +
ISNULL(MAX(PATINDEX('222',STR(SP.action,3,0))),0) * POWER(2,7) +
ISNULL(MAX(PATINDEX('224',STR(SP.action,3,0))),0) * POWER(2,8) +
ISNULL(MAX(PATINDEX('228',STR(SP.action,3,0))),0) * POWER(2,9) +
ISNULL(MAX(PATINDEX('233',STR(SP.action,3,0))),0) * POWER(2,10) +
ISNULL(MAX(PATINDEX('235',STR(SP.action,3,0))),0) * POWER(2,11) +
ISNULL(MAX(PATINDEX('236',STR(SP.action,3,0))),0) * POWER(2,12)
AS Recht
FROM Sysobjects SO LEFT JOIN Sysprotects SP
ON SO.id = SP.id
AND ( SP.uid IN ( SELECT groupuid FROM sysmembers WHERE memberuid = (
SELECT uid FROM Sysusers WHERE SID = SUSER_SID() ) AND groupuid >= 16400 )
OR SP.uid IN ( SELECT uid FROM Sysusers WHERE SID = SUSER_SID()) )
AND SP.protecttype <> 206
GROUP BY SO.name,SO.xtype
HAVING SO.xtype = 'U'
03-09 Rechteverwaltung für Ms SQL Server
FoxX-Professional
Seite 5