Le Guide Ultime pour Accorder des Autorisations sur Toutes les Tables Utilisateur dans SQL Server

Gérer les autorisations dans SQL Server est crucial pour maintenir l’intégrité et la sécurité de votre base de données. Un scénario courant se présente lorsque vous devez accorder des autorisations sur toutes les tables utilisateur à un rôle de base de données spécifique sans effort. Si vous vous retrouvez frustré à l’idée d’écrire plusieurs scripts ou d’ajuster manuellement les autorisations chaque fois que vous ajoutez de nouvelles tables, vous n’êtes pas seul. Dans ce guide, nous allons examiner comment créer un script SQL polyvalent qui vous permet d’accorder les autorisations SELECT, REFERENCES, INSERT, UPDATE et DELETE à un rôle de base de données sur toutes les tables utilisateur avec facilité.

Le Défi : Accorder des Autorisations Efficacement

SQL Server Management Studio (SSMS) génère généralement des scripts pour les objets de base de données individuels, ce qui peut être fastidieux lorsque vous traitez avec plusieurs tables. De plus, modifier les autorisations pour les tables nouvellement ajoutées peut rapidement devenir une tâche chronophage. Ce dont vous avez besoin, c’est d’un script robuste de type “fire-and-forget” qui peut être exécuté pour appliquer des autorisations sur toutes les tables utilisateur sans avoir besoin de revisiter le script à chaque fois.

La Solution : Une Procédure Stockée pour les Accords d’Autorisation

Pour automatiser efficacement l’accord d’autorisations, nous pouvons créer une procédure stockée qui utilise un curseur pour parcourir tous les objets utilisateur et accorder les autorisations souhaitées. Décomposons le processus en étapes gérables :

Étape 1 : Créer la Procédure Stockée

Commencez par définir votre procédure stockée. Utilisez le code SQL suivant comme modèle :

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!] accès à l'objet %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

Étape 2 : Comprendre les Composants

• Déclaration de Curseur : Le curseur c_objects est configuré pour parcourir toutes les tables utilisateur (U), les vues (V), et les procédures stockées (P).
• Exécution des Accords : Pour chaque objet récupéré via le curseur, le système exécute une commande GRANT, appliquant les droits définis au rôle spécifié.
• Informations de Sortie : La procédure imprime des journaux indiquant l’heure d’exécution et les objets auxquels des autorisations ont été accordées.

Étape 3 : Exécuter la Procédure

Une fois la procédure stockée créée, vous pouvez l’exécuter avec une simple commande :

EXEC sp_grantastic

Avec juste cette commande, votre rôle de base de données recevra les autorisations nécessaires sur toutes les tables utilisateur sans effort.

Conclusion

Vous venez d’apprendre comment créer un puissant script SQL qui accorde des autorisations à un rôle de base de données sur toutes les tables utilisateur de manière simple. Cette approche non seulement rationalise votre flux de travail, mais garantit également que vos autorisations sont toujours à jour sans intervention manuelle. La prochaine fois que vous ajoutez une nouvelle table à votre base de données, il vous suffit de réexécuter la procédure stockée et vous êtes prêt à partir.

Bonne programmation !