Définir des autorisations sur tous les objets pour les utilisateurs SQL


1

J'ai une procédure qui parcourt tous les objets de la base de données et leur affecte les autorisations appropriées pour cet objet. Je veux savoir s'il y a une meilleure façon de le faire? J'utilise une base de données modèle pour créer de nouvelles bases de données, donc je dois l'exécuter chaque fois que je crée une nouvelle base de données. Voici une idée de ce qu'il ressemble à (note: il y a un morceau manquant depuis le début qui laisse tomber tous les utilisateurs et reconstitue les utilisateurs nécessaires, ce qui est nécessaire puisque le changement de SID):

CREATE PROCEDURE usp_SetPermissions 
AS 
BEGIN 

DECLARE @CurrentId INT 
DECLARE @ObjectName NVARCHAR(128) 
DECLARE @Message NVARCHAR(160) 
DECLARE @Error INT 
DECLARE @Sql NVARCHAR(256) 

CREATE TABLE #tmpDbObjects 
(
    ID INT IDENTITY(1,1), 
    ObjectName NVARCHAR(128), 
    Completed BIT 
) 

INSERT #tmpDbObjects(ObjectName, Completed) 
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'U' AND is_ms_shipped <> 1 

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects) 
BEGIN 
    -- Pick first uncompleted object 
    SELECT TOP 1 @CurrentId = ID, 
       @ObjectName = ObjectName 
    FROM #tmpDbObjects 

    -- Grant permissions to DB user 
    SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
    EXEC sp_sqlexec @Sql 

    -- Update object completion 
    DELETE #tmpDbObjects 
    WHERE [Id] = @CurrentId 

    -- Clear variables 
    SET @Sql = NULL 
    SET @CurrentId = NULL 
END 

INSERT #tmpDbObjects(ObjectName, Completed) 
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'P' AND is_ms_shipped <> 1 

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects) 
    BEGIN 
    -- Pick first uncompleted object 
    SELECT TOP 1 @CurrentId = ID, 
       @ObjectName = ObjectName 
    FROM #tmpDbObjects 

    -- Grant permissions to DB user 
    SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
EXEC sp_sqlexec @Sql 

    -- Update object completion 
    DELETE #tmpDbObjects 
    WHERE [Id] = @CurrentId 

    -- Clear variables 
    SET @Sql = NULL 
    SET @CurrentId = NULL 
END 

Ce script se poursuit faire des choses similaires pour toutes les vues, fonctions, etc. dans la base de données. Des idées pour accélérer cette chose ou existe-t-il une meilleure façon de le faire?

6

Une seule ligne pour donner des autorisations au niveau du schéma à un rôle

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole 

Et une deuxième ligne pour ajouter des utilisateurs au rôle

EXEC sp_addrolemember 'SomeRole', 'whatever user' 

Et faire dans le modèle si toutes les nouvelles bases de données hériteront .

raisons, vous devez définir les autorisations une fois seulement:

  • Un schéma est un conteneur pour les objets.
  • Les nouveaux objets héritent des autorisations à partir du schéma
  • Un rôle est conteneur pour les utilisateurs
  • Les nouveaux utilisateurs sont ajoutés à un rôle et héritant

Comme vous l'avez trouvé, la migration ou la restauration d'une base de données peut perdre autorisations d'objet lorsqu'elles sont attribuées directement aux utilisateurs. Alors pourquoi vous mettre dans cette position?

Vous pouvez également CREATE LOGIN with a SID donc il en est de même sur tous vos serveurs et vous n'obtenez pas non plus d'utilisateurs orphelins.

Si vous avez posé la bonne question que nous aurions pu vous sauver un certain codage ...

Personnellement et enfin, je dirais que c'est une mauvaise pratique sur « doivent avoir », pas « couverture quoi que ce soit "

  0

donc quelque chose comme: ' SINON EXISTE (CHOISIR 1 À pARTIR sys.database_principals OU [type] = 'R' et [name] = 'ReadOnlyUsers') '' CREATE ROLE [ReadOnlyUsers] '' EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser'' 'GRANT? (sélectionnez? ou?) ON SCHEMA :: dbo TO [ReadOnlyUsers]' 'GO' 'EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser'' Comment différencier EXEC, UPDATE, DELETE, etc. si tous les objets sont sous DBO? Par exemple ReadOnlyUser aurait encore besoin d'EXEC stocké procs. 20 sept.. 112011-09-20 15:27:51

  0

@David George: les permissions de schéma s'appliquent automatiquement quel que soit l'objet: ainsi un proc obtient EXEC et une table obtient S/I/U/D. Votre GRANT serait GRANT EXEX, SELECT, UPDATE, etc ... 20 sept.. 112011-09-20 16:33:55

  0

alors je voudrais exécuter 'GRANT EXEC, SELECT, UPDATE, INSERT, SUPPRIMER sur SCHEMA :: dbo TO [ReadWriteUsers]' Serait-ce permettre une telle autorisation de l'utilisateur à potentiellement DROP une procédure alors? 20 sept.. 112011-09-20 18:54:03

  0

Non, ils ont besoin de ALTER TABLE ou autre pour ça 20 sept.. 112011-09-20 22:29:39