Imposta le autorizzazioni su tutti gli oggetti per gli utenti SQL


1

Ho una procedura che scorre tutti gli oggetti nel database e assegna loro le autorizzazioni appropriate per quell'oggetto.Voglio sapere se c'è un modo migliore per farlo?Uso un database modello per creare nuovi database, quindi devo eseguirlo ogni volta che creo un nuovo database.Ecco un'idea di come appare (nota: manca un pezzo all'inizio che abbandona tutti gli utenti e ricrea gli utenti necessari, necessario in seguito al cambiamento del 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

Questo script continua a fare cose simili per tutte le viste, le funzioni, ecc. Nel database.Qualche idea per accelerare questa cosa o c'è un modo migliore per farlo?

6

Riga singola per assegnare autorizzazioni a livello di schema a un ruolo

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

E una seconda riga per aggiungere utenti al ruolo

EXEC sp_addrolemember 'SomeRole', 'whatever user'

E fallo nel modello in modo che tutti i nuovi database ereditino.

Motivi, dovresti impostare le autorizzazioniuna voltasolo:

  • Uno schema è un contenitore per oggetti.
  • I nuovi oggetti ereditano le autorizzazioni dallo schema
  • Un ruolo è un contenitore per gli utenti
  • I nuovi utenti vengono aggiunti a un ruolo e ereditati

Come hai trovato, la migrazione o il ripristino di un database può perdere le autorizzazioni dell'oggetto se assegnato direttamente agli utenti.Allora perché metterti in quella posizione?

Puoi ancheCREATE LOGIN with a SIDquindi è uguale su tutti i server e non si ottengono neanche gli utenti orfani.

Se avessi fatto la domanda giusta avremmo potuto risparmiarti qualche codice ...

Personalmente e alla fine, direi che questa è una cattiva pratica su "necessità di avere", non "coperta fare qualsiasi cosa"

  0

Quindi qualcosa del tipo: 'SE NON ESISTE (SELEZIONA 1 DA sys.database_principals WHERE [tipo] = 'R' AND [nome] = 'ReadOnlyUsers')' 'CREATE ROLE [ReadOnlyUsers]' 'EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser' '' GRANT? (Selezionare? O?) ON SCHEMA :: dbo TO [ReadOnlyUsers] '' GO' 'EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser'' Come distinguere EXEC, UPDATE, DELETE, ecc. Se tutti gli oggetti sono sotto dbo però?Ad esempio ReadOnlyUser avrebbe ancora bisogno di proc memorizzati da EXEC. 20 set. 112011-09-20 15:27:51

  0

@ David David: le autorizzazioni dello schema si applicano automaticamente indipendentemente dall'oggetto: così un proc ottiene EXEC e una tabella ottiene S/I/U/D.Il tuo GRANT sarà GRANT EXEX, SELECT, UPDATE, ecc ... 20 set. 112011-09-20 16:33:55

  0

quindi eseguirò 'GRANT EXEC, SELECT, UPDATE, INSERT, DELETE su SCHEMA :: dbo TO [ReadWriteUsers]' Ciò consentirebbe a quell'utente il permesso di eseguire potenzialmente DROP una procedura? 20 set. 112011-09-20 18:54:03

  0

No, hanno bisogno di ALTER TABLE o simili 20 set. 112011-09-20 22:29:39