Berechtigungen für Access-Objekte per SQL Server II: Formulare

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Im Beitrag „Berechtigungen für Access-Objekte per SQL Server I: Tabellen“ haben wir ein Datenmodell entwickelt für die Verwaltung der Berechtigungen verschiedener Benutzergruppen auf die Formulare, Berichte und Steuer-elemente einer Access-Anwendung – gesteuert über die jeweilige Anmeldung an der SQL Server-Datenbank. Im zweiten Teil dieser Beitragsreihe verknüpfen wir die Access-Anwendung mit diesen Tabellen und erstellen die Formulare, die zur Bearbeitung der für die Berechtigungsverwaltung notwendigen Tabellen erforderlich sind.

Für die im ersten Teil dieser Beitragsreihe namens Berechtigungen für Access-Objekte per SQL Server I: Tabellen, die Sie unter www.access-im-unternehmen.de/1159 finden, benötigen wir prinzipiell zwei verschiedene Datenbanken: eine, mit der wir die Objektberechtigungen verwalten und eine, welche die Berechtigungen umsetzt. Die Formulare, mit denen wir die Objekte, Benutzergruppen und Berechtigungen einander zuordnen wollen, müssen nicht unbedingt in der eigentlichen Anwendung stecken. Andererseits kann es auch nicht schaden, damit gleich auszuprobieren, ob die Berechtigungen funktionieren – denn diese sollen weder durch Mitglieder der Gruppe Bestellannahme noch der Gruppe Management verwendet werden, sondern nur von den Administratoren. Wir werden also noch eine dritte Gruppe namens Berechtigungsadministratoren zu Windows hinzufügen, die wir dann auch in den SQL Server übernehmen. Das erledigen Sie in der Eingabeaufforderung wie folgt:

net.exe LOCALGROUP /ADD Berechtigungsadministratoren

Im SQL Server Management Studio fügen wir diese Anmeldung wie folgt hinzu (Details siehe Beitrag SQL Server: Sicherheit und Benutzerverwaltung, www.access-im-unternehmen.de/1154) – muss durch den entsprechenden Servernamen ersetzt werden:

USE [master]
GO
CREATE LOGIN [<Server>\Berechtigungsadministratoren] FROM WINDOWS WITH DEFAULT_DATABASE=[Suedsturm_SQL]
GO
USE [Suedsturm_SQL]
GO
CREATE USER [<Server>\Berechtigungsadministratoren] FOR LOGIN [<Server>\Berechtigungsadministratoren]
GO
ALTER ROLE [db_datareader] ADD MEMBER [<Server>\Berechtigungsadministratoren]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [<Server>\Berechtigungsadministratoren]
GO

Auf den folgenden Seiten zeigen wir Ihnen, wie Sie erstens die Verknüpfung zu den relevanten Tabellen herstellen – also zu den Tabellen, die wir im ersten Teil entworfen haben -, und dann die Formulare programmieren, die zur Verwaltung der Berechtigungen erforderlich sind.

Verknüpfung herstellen

Als Erstes benötigen wir eine Verknüpfung von einer Access-Datenbank zu den im ersten Teil der Beitragsreihe erstellten Tabellen. Dazu bemühen wir einfache ODBC-Verknüpfungen. Diese legen wir mit einem Tool an, dass wir bereits im Beitrag SQL Server-Tools vorgestellt haben (http://www.access-im-unternehmen.de/1061). Genaugenommen können wir die dort erstellte Datenbank auch als Grundlage für unsere Datenbank zur Verwaltung der Berechtigungen nutzen, denn sie enthält einige praktische Werkzeuge, die wir noch brauchen.

Das beginnt mit dem Formular frmVerbindungszeichenfolgen aus Bild 1, mit dem wir uns gleich eine passende Verbindungszeichenfolge zusammenstellen.

Bild 1: Einrichten der Verbindungszeichenfolge

Nachdem wir dies erledigt haben, können wir gleich das Formular frmTabellenVerknuepfen öffnen, mit der wir die soeben angelegte Verbindung auswählen. Es erscheinen alle Tabellen der Datenbank Suedsturm_SQL, von denen wir die Tabellen tblBenutzergruppen, tblBerechtigungsstufen, tblObjekte, tblObjekteBenutzergruppenBerechtigungsen und tblObjekttypen auswählen – bei gedrückter Umschalttaste (siehe Bild 2). Diese Tabellen erscheinen kurz darauf im Navigationsbereich der Datenbank.

Bild 2: Hinzufügen der Verknüpfungen

Warum ODBC-Verknüpfungen

Aus Performance-Gesichtspunkten wäre es günstiger, über Abfragen und gespeicherte Prozeduren auf die Tabellen der SQL Server-Datenbank zuzugreifen. Allerdings werden wir nicht so viele Objekte, Benutzer oder Berechtigungen anlegen, dass die Performance bei Nutzung von ODBC-Verknüpfungen in die Knie geht – und wir werden auch nicht mit vielen Benutzern gleichzeitig auf diese Daten zugreifen. Also ist die einfache Variante akzeptabel. Wenn wir später die Berechtigungen beim Anzeigen des Ribbons oder beim öffnen von Formularen und Berichten abfragen, werden wir auf die performantere Variante zurückgreifen.

Tabellen füllen

Im ersten Teil dieser Beitragsreihe haben wir die Tabellen vorgestellt, mit denen wir die Objekte, die Berechtigungsstufen, die Berechtigungen und die Objekttypen speichern wollen. Nun kann es bei einer umfangreichen Datenbank relativ aufwendig werden, alle Objekte wie Formulare und Berichte sowie die enthaltenen Steuer-elemente in die Tabelle tblObjekte zu schreiben, um dann über die anderen Tabellen die Berechtigungen zuzuordnen.

Also programmieren wir uns eine Prozedur, mit der wir die Formulare und Berichte sowie die enthaltenen Steuer-elemente in die Tabelle tblObjekte schreiben können.

Diese heißt ObjekteSchreiben und erwartet einen optionalen Parameter, mit dem Sie den Namen eines oder mehrerer Formulare angeben können, die nicht von der Prozedur berücksichtigt werden sollen, die also nicht samt Steuerelementen in die Tabelle tblObjekte geschrieben werden sollen. Der Aufruf der Prozedur sieht also so aus, wenn alle Formulare berücksichtigt werden sollen:

ObjekteSchreiben

Wenn Sie die Prozedur, wie wir es später tun werden, von einem Formular wie frmObjektberechtigungen aus aufrufen werden, können Sie dieses auf folgende Weise ausschließen:

ObjekteSchreiben "frmObjektberechtigungen"

Wenn Sie mehr als ein Formular ausschließen wollen, geben Sie die Namen der Formulare einfach in einer durch Kommata getrennten Liste an:

ObjekteSchreiben "frmObjektberechtigungen", "frmArtikel", ...

Die Prozedur ObjekteSchreiben finden Sie in Listing 1. Damit Sie kein, ein oder mehrere Formulare von der Verarbeitung ausschließen können, verwenden wir ein ParamArray als Parameter. In einer For…Next-Schleife durchläuft die Prozedur alle Formulare der aktuellen Anwendung. Dabei ermitteln wir die Anzahl der zu durchlaufenden Formulare über die Count-Eigenschaft der Auflistung CurrentProject.AllForms.

Public Sub ObjekteSchreiben(ParamArray strNicht() As Variant)
     Dim db As DAO.Database, frm As Form, strForm As String
     Dim i As Integer, j As Integer, ctl As Control
     Dim bolNicht As Boolean, strControl As String, rst As DAO.Recordset
     Set db = CurrentDb
     For i = 0 To CurrentProject.AllForms.Count - 1
         strForm = CurrentProject.AllForms(i).Name
         bolNicht = False
         For j = LBound(strNicht) To UBound(strNicht)
             If strForm = strNicht(j) Then
                 bolNicht = True
                 Exit For
             End If
         Next j
         If Not bolNicht Then
             DoCmd.OpenForm strForm, acDesign
             Set frm = Forms(strForm)
             On Error Resume Next
             db.Execute "INSERT INTO tblObjekte(Bezeichnung, ObjekttypID) VALUES(''''" & strForm & "'''', 1)", dbFailOnError
             db.Execute "INSERT INTO tblObjekte(Bezeichnung, Uebergeordnet, ObjekttypID) VALUES(''''Form'''', ''''" _
                 & strForm & "'''', 4)", dbFailOnError
             For Each ctl In frm.Controls
                 If Not ctl.ControlType = 100 Then
                     strControl = ctl.Name
                     db.Execute "INSERT INTO tblObjekte(Bezeichnung, Uebergeordnet, ObjekttypID, SteuerelementtypID) " _
                         & "VALUES(''''" & strControl & "'''', ''''" & strForm & "'''', 4, " & ctl.ControlType & ")", dbFailOnError
                 End If
             Next ctl
             On Error GoTo 0
             Set rst = db.OpenRecordset("SELECT * FROM tblObjekte WHERE Uebergeordnet = ''''" & strForm _
                 & "'''' AND ObjekttypID = 4 AND NOT Bezeichnung = ''''Form''''", dbOpenDynaset, dbSeeChanges)
             Do While Not rst.EOF
                 Set ctl = Nothing
                 On Error Resume Next
                 Set ctl = frm.Controls(rst!Bezeichnung)
                 Debug.Print Err.Number, Err.Description
                 On Error GoTo 0
                 If ctl Is Nothing Then
                     db.Execute "DELETE FROM tblObjekte WHERE ObjektID = " & rst!ObjektID, dbFailOnError Or dbSeeChanges
                 End If
                 rst.MoveNext
             Loop
             DoCmd.Close acForm, strForm
         End If
     Next i
     FormularePruefen
End Sub

Listing 1: Prozedur zum Einlesen der Formulare und Steuer-elemente in die Tabelle tblObjekte

Danach erfassen wir den Namen des ersten Formulars und speichern diesen in der Variablen strForm. Die Boolean-Variable bolNicht stellen wir auf den Wert False ein, da wir in der folgenden Schleife alle Elemente des per Parameter übergebenen ParamArrays durchlaufen und prüfen, ob das in strForm gespeicherte Formular einem der auszuschließenden Formulare entspricht. Dies erledigen wir in einer weiteren For…Next-Schleife, diesmal mit der Laufvariablen j.

In dieser Schleife durchlaufen wir alle Elemente des ParamArrays und referenzieren diese über den mit j ermittelten Indexwert. Stimmen strForm und das aktuelle Element aus strNicht(j) überein, stellen wir bolNicht auf True ein und verlassen die Schleife. Erhalten wir dabei den Wert True für die Variable bolNicht, dann wird der Inhalt der folgenden If…Then-Bedingung nicht ausgeführt, denn das aktuelle Formular samt Steuerelementen soll nicht eingelesen werden.

Anderenfalls beginnt hier jedoch der Einlesevorgang. Dabei öffnen wir das Formular aus strForm in der Entwurfsansicht und referenzieren es mit der Objektvariablen objForm. Danach deaktivieren wir die eingebaute Fehlerbehandlung, da es sein kann, dass beim Versuch, das aktuelle Formular als neuen Datensatz in die Tabelle tblObjekte zu schreiben, ein Fehler wegen einer Schlüsselverletzung ausgelöst wird.

Dies kommt vor, wenn Sie beispielsweise neue Formulare oder Steuer-elemente hinzugefügt haben und diese Elemente erneut einlesen. Wir haben der Prozedur keine überprüfung hinzugefügt, mit der wir das Vorhandensein eines Formulars oder Steuerelements prüfen, sondern lassen es auf den Fehler ankommen, den wir dann ignorieren. Die INSERT INTO-Anweisung schreibt den Namen des Formulars in das Feld Bezeichnung eines neuen Datensatzes der Tabelle tblObjekte.

Außerdem trägt es dort den Wert 1 für Formular in das Feld ObjekttypID ein. Die zweite INSERT INTO-Anweisung trägt einen zweiten Datensatz für dieses Formular ein, wobei allerdings das Feld Bezeichnung mit dem Wert Form und das Feld Uebergeordnet mit dem Namen des Formulars gefüllt wird. Wozu wir das benötigen, erfahren Sie weiter unten.

Danach durchlaufen wir in einer For Each-Schleife alle in dem mit frm referenzierten Formulare enthaltenen Steuer-elemente. Dabei prüfen wir zunächst, ob es sich nicht um ein Bezeichnungsfeld handelt – diese sollen hier nicht berücksichtigt werden. Ist das aktuelle Steuerelement kein Bezeichnungsfeld, schreiben wir zunächst den Namen des aktuellen Steuerelements in die Variable strControl.

Danach fügen wir, wiederum bei deaktivierter Fehlerbehandlung, einen neuen Datensatz für das aktuelle Steuer-element in die Tabelle tblObjekte ein. Diesmal landet der Name des Steuerelements im Feld Bezeichnung. Der Name des übergeordneten Formulars landet im Feld Uebergeordnet. Für den Objekttyp landet der Wert 4 für Steuer-element im Feld ObjekttypID. Außerdem speichern wir noch den Steuerelementtyp aus der Eigenschaft ControlType, und zwar im Feld Steuer-ele-ment-typ-ID.

Danach aktivieren wir die Fehlerbehandlung wieder und öffnen ein Recordset, das alle Elemente der Tabelle tblObjekte enthält, deren Feld Uebergeordnet dem Namen des aktuell untersuchten Formulars entspricht und die im Feld ObjekttypID den Wert 4 aufweisen – mit Ausnahme der Datensätze, die im Feld Bezeichnung den Wert Form enthalten.

Das Recordset enthält also jeweils einen Datensatz für alle Steuer-elemente des aktuellen Formulars. Damit durchlaufen wir dann in einer Do While-Schleife alle Datensätze dieses Recordsets. Wir leeren die Objektvariable ctl und versuchen dann, bei deaktivierter Fehlerbehandlung das Steuer-element mit dem Namen aus dem Feld Bezeichnung im Formular zu referenzieren.

Entspricht ctl danach dem Objekt Nothing, ist also leer, ist das Steuer-element zwar noch in der Tabelle tblObjekte gespeichert, aber nicht mehr im angegebenen Formulare vorhanden – vermutlich hat der Entwickler es in der Zwischenzeit gelöscht. In diesem Fall können wir auch den dazu gehörenden Datensatz aus der Tabelle tblObjekte löschen.

Danach schließen wir schließlich noch das aktuell untersuchte Formular und durchlaufen die Schleife für die weiteren Formulare, bis wir alle Formular durchlaufen haben.

Auf gelöschte Formulare prüfen

Eine weitere Prozedur, die wir mit der letzten Anweisung der zuvor beschriebenen Prozedur aufrufen, heißt FormularePruefen (siehe Listing 2). Sie untersucht alle Einträge der Tabelle tblObjekte, die den Wert 1 im Feld Objekttyp-ID aufweisen dahingehend, ob die angegebenen Formular noch in der Datenbank vorhanden sind. Ist dies nicht der Fall, werden die Formulare-Einträge auch aus der Tabelle tblObjekte gelöscht – inklusive der Steuerelement-Einträge für diese Formulare.

Public Sub FormularePruefen()
     Dim db As DAO.Database
     Dim i As Integer
     Dim bolBehalten As Boolean
     Dim rst As DAO.Recordset
     Set db = CurrentDb
     Set rst = db.OpenRecordset("SELECT * FROM tblObjekte WHERE ObjekttypID = 1", dbOpenDynaset, dbSeeChanges)
     Do While Not rst.EOF
         bolBehalten = False
         For i = 0 To CurrentProject.AllForms.Count - 1
             If CurrentProject.AllForms(i).Name = rst!Bezeichnung Then
                 bolBehalten = True
                 Exit For
             End If
         Next i
         If bolBehalten = False Then
             db.Execute "DELETE FROM tblObjekte WHERE Uebergeordnet = ''''" & rst!Bezeichnung _
                 & "''''", dbFailOnError Or dbSeeChanges
             db.Execute "DELETE FROM tblObjekte WHERE ObjektID = " & rst!ObjektID, dbFailOnError Or dbSeeChanges
         End If
         rst.MoveNext
     Loop
End Sub

Listing 2: Prozedur zum Prüfen und gegebenenfalls Löschen von Einträgen für Formulare aus der Tabelle tblObjekte

Wenn Sie die Prozedur ObjekteSchreiben einmal aufgerufen haben, etwa für die Beispieldatenbank zu diesem Beitrag, finden Sie Daten wie die aus Bild 3 in der Tabelle tblObjekte.

Bild 3: Daten über die mit Berechtigungen zu versehenden Objekte

Formular zum Verwalten der Berechtigungen

Das Formular zum Verwalten der Berechtigungen heißt frmBerechtigungen. Im linken Teil beherbergt es ein Listenfeld mit allen Formularen, die wir aus Einträgen der Tabelle tblObjekte ermitteln, für die das Feld ObjektID den Wert 1 enthält. Den Entwurf dieses Formulars finden Sie in Bild 4.

Bild 4: Entwurf des Formulars frmBerechtigungen

Damit das Listenfeld namens lstFormulare die gewünschten Daten anzeigt, weisen wir diesem die folgende Abfrage als Datensatzherkunft zu:

SELECT DISTINCT tblObjekte.Bezeichnung FROM tblObjekte WHERE ObjekttypID = 1 ORDER BY tblObjekte.Bezeichnung;

Nach einem Wechsel in die Formularansicht liefert das bereits die Ansicht aus Bild 5.

Bild 5: Das Listenfeld lstFormulare mit den Daten der Tabelle tblObjekte

Steuer-elemente im Listenfeld anzeigen

Als Nächstes wollen wir die Steuer-elemente zu dem jeweils im Listenfeld lstFormulare markierten Formular im zweiten Listenfeld lstSteuerelemente anzeigen. Da beim öffnen noch kein Element des ersten Listenfeldes markiert ist, wollen wir einfach alle Elemente der Tabelle tblObjekte im zweiten Listenfeld anzeigen. Dazu fügen wir der Eigenschaft Datensatzherkunft des Listenfeldes lstElemente die folgende SQL-Abfrage hinzu:

SELECT tblObjekte.ObjektID, [Bezeichnung]  & " (" & [Uebergeordnet] & ")" AS ElementObjekt,  tblObjekte.Bezeichnung FROM tblObjekte;

Sobald der Benutzer jedoch einen Eintrag des Listenfeldes lstFormulare markiert, sollen nur noch die Elemente dieses Formulars im zweiten Listenfeld lstElemente angezeigt werden. Deshalb hinterlegen wir für die Ereignis-eigenschaft Nach Aktualisierung eine Ereignisprozedur, die das Listenfeld lstElemente so filtert, dass nur noch die zum gewählten Formular gehörenden Elemente angezeigt werden – und so, dass die Bezeichnungsfelder nicht mit angezeigt werden:

Private Sub lstFormulare_AfterUpdate()
     Dim strObjekte As String
     Dim var As Variant
     For Each var In Me!lstFormulare.ItemsSelected
         strObjekte = strObjekte & ", ''''" _
             & Me!lstFormulare.ItemData(var) & "''''"
     Next var
     If Len(strObjekte) > 0 Then
         strObjekte = Mid(strObjekte, 3)
     End If
     Me!lstElemente.RowSource = "SELECT ObjektID,  Bezeichnung FROM tblObjekte WHERE Uebergeordnet  IN (" & strObjekte & ")"
End Sub

Die Ansicht im Formular frmBerechtigungen sieht dann nach der Auswahl eines der Formulare im linken Listenfeld wie in Bild 6 aus.

Bild 6: Elemente eines Formulars

Das Form-Element

Hier taucht nun neben den Steuerelementen des markierten Formulars auch der Eintrag Form auf. Damit wollen wir eine einheitliche Möglichkeit schaffen, nicht nur die Berechtigungen für die Steuer-elemente zu definieren, sondern auch für das Formular selbst.

Listenfeld der Benutzergruppen

Ganz rechts im Formular haben wir zwei weitere Listenfelder vorgesehen. Das obere soll die Benutzergruppen anzeigen, die unter Windows eingerichtet sind und die wir temporär in der Tabelle tblBenutzergruppen speichern.

Dazu müssen wir diese allerdings erst einmal ermitteln.

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo