Der Ultimative Leitfaden zur Gewährung von Berechtigungen auf allen Benutzertabellen in SQL Server

Die Verwaltung von Berechtigungen in SQL Server ist entscheidend für die Aufrechterhaltung der Integrität und Sicherheit Ihrer Datenbank. Ein häufiges Szenario tritt auf, wenn Sie einem bestimmten Datenbankrolle nahtlos Berechtigungen auf allen Benutzertabellen gewähren müssen. Wenn Sie frustriert sind, weil Sie mehrere Skripte schreiben oder Berechtigungen jedes Mal manuell anpassen müssen, wenn Sie neue Tabellen hinzufügen, sind Sie nicht allein. In diesem Leitfaden werden wir untersuchen, wie Sie ein vielseitiges SQL-Skript erstellen können, das es Ihnen ermöglicht, Berechtigungen für SELECT, REFERENCES, INSERT, UPDATE und DELETE an einer Datenbankrolle auf allen Benutzertabellen mühelos zu gewähren.

Die Herausforderung: Berechtigungen effizient gewähren

SQL Server Management Studio (SSMS) generiert normalerweise Skripte für einzelne Datenbankobjekte, was zeitaufwendig sein kann, wenn man es mit mehreren Tabellen zu tun hat. Außerdem kann das Ändern der Berechtigungen für neu hinzugefügte Tabellen schnell zu einer zeitaufwendigen Aufgabe werden. Was Sie brauchen, ist ein robustes “Fire-and-Forget”-Skript, das ausgeführt werden kann, um Berechtigungen für alle Benutzertabellen anzuwenden, ohne dass das Skript jedes Mal erneut besucht werden muss.

Die Lösung: Ein Stored Procedure für Berechtigungsgewährungen

Um die Gewährung von Berechtigungen effektiv zu automatisieren, können wir ein Stored Procedure erstellen, das einen Cursor verwendet, um durch alle Benutzerobjekte zu schleifen und die gewünschten Berechtigungen zu gewähren. Lassen Sie uns den Prozess in überschaubare Schritte unterteilen:

Schritt 1: Erstellen Sie das Stored Procedure

Beginnen Sie mit der Definition Ihres Stored Procedure. Verwenden Sie den folgenden SQL-Code als Vorlage:

IF EXISTS (
    SELECT 1 FROM sysobjects
    WHERE name = 'sp_grantastic'
    AND type = 'P'
)
DROP PROCEDURE sp_grantastic
GO

CREATE PROCEDURE sp_grantastic
AS
DECLARE
 @object_name VARCHAR(30),
 @time VARCHAR(8),
 @rights VARCHAR(20),
 @role VARCHAR(20)

DECLARE c_objects CURSOR FOR
    SELECT name
    FROM sysobjects
    WHERE type IN ('P', 'U', 'V')
    FOR READ ONLY

BEGIN
    SELECT @rights = 'ALL',
           @role = 'PUBLIC'

    OPEN c_objects
    WHILE (1=1)
    BEGIN
        FETCH c_objects INTO @object_name
        IF @@SQLSTATUS <> 0 BREAK

        SELECT @time = CONVERT(VARCHAR, GetDate(), 108)
        PRINT '[%1!] hitting up object %2!', @time, @object_name
        EXECUTE('GRANT '+ @rights +' ON '+ @object_name+' TO '+@role)
    END

    PRINT '[%1!] fin!', @time

    CLOSE c_objects
    DEALLOCATE CURSOR c_objects
END
GO

GRANT ALL ON sp_grantastic TO PUBLIC
GO

Schritt 2: Verstehen der Komponenten

• Cursor-Deklaration: Der Cursor c_objects wird eingerichtet, um durch alle Benutzertabellen (U), Ansichten (V) und Stored Procedures (P) zu schleifen.
• Ausführung von Grants: Für jedes Objekt, das über den Cursor abgerufen wird, führt das System einen GRANT-Befehl aus, um die definierten Rechte auf die angegebene Rolle anzuwenden.
• Ausgabeinformationen: Das Verfahren gibt Protokolle aus, die den Zeitpunkt der Ausführung und alle Objekte, denen Berechtigungen gewährt wurden, angeben.

Schritt 3: Führen Sie das Verfahren aus

Sobald das Stored Procedure erstellt wurde, können Sie es mit einem einfachen Befehl ausführen:

EXEC sp_grantastic

Mit nur diesem Befehl erhält Ihre Datenbankrolle nahtlos die erforderlichen Berechtigungen auf allen Benutzertabellen.

Fazit

Sie haben gerade gelernt, wie Sie ein leistungsstarkes SQL-Skript erstellen, das Berechtigungen für eine Datenbankrolle auf allen Benutzertabellen auf einfache Weise gewährt. Dieser Ansatz rationalisiert nicht nur Ihren Arbeitsablauf, sondern sorgt auch dafür, dass Ihre Berechtigungen stets auf dem neuesten Stand sind, ohne dass manuelles Eingreifen erforderlich ist. Das nächste Mal, wenn Sie eine neue Tabelle zu Ihrer Datenbank hinzufügen, führen Sie einfach das Stored Procedure erneut aus, und Sie sind bereit.

Viel Spaß beim Programmieren!