Ustaw uprawnienia dla wszystkich obiektów dla użytkowników SQL


1

Mam procedurę, która zapętla wszystkie obiekty w bazie danych i przypisuje im odpowiednie uprawnienia do tego obiektu.Chcę wiedzieć, czy jest lepszy sposób, aby to zrobić?Używam bazy danych modelu do tworzenia nowych baz danych, więc muszę to uruchamiać za każdym razem, gdy tworzę nową bazę danych.Oto wyobrażenie o tym, jak to wygląda (uwaga: od początku brakuje fragmentu, który odrzuca wszystkich użytkowników i odtwarza niezbędnych użytkowników; jest to konieczne od zmiany 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

Skrypty te kontynuują wykonywanie podobnych czynności dla wszystkich widoków, funkcji itp. W bazie danych.Jakieś pomysły na przyspieszenie tego lub czy jest lepszy sposób na zrobienie tego?

6

Pojedyncza linia, aby nadać uprawnienia na poziomie schematu roli

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

I druga linia, aby dodać użytkowników do roli

EXEC sp_addrolemember 'SomeRole', 'whatever user'

I zrób to w modelu, aby wszystkie nowe bazy danych dziedziczyły.

Powody, dla których należy ustawić uprawnienia tylko raz :

  • Schemat jest pojemnikiem na obiekty.
  • Nowe obiekty dziedziczą uprawnienia ze schematu
  • Rola to kontener dla użytkowników
  • Nowi użytkownicy są dodawani do roli i dziedziczą

Jak już zauważyłeś, migracja lub przywrócenie bazy danych może spowodować utratę uprawnień do obiektu, jeśli zostaną one przypisane bezpośrednio do użytkowników.Dlaczego więc postawiłeś się w takiej pozycji?

Możesz także CREATE LOGIN with a SID aby był taki sam na wszystkich serwerach i nie dostaniesz osieroconych użytkowników.

Gdybyś zadał prawidłowe pytanie, moglibyśmy ci zaoszczędzić trochę kodowania ...

Osobiście i na koniec powiedziałbym, że jest to zła praktyka dotycząca „potrzeby posiadania”, a nie „kocowania czegokolwiek”

  0

Więc coś takiego: 'JEŚLI NIE JEST WYSTĄPIENIA (WYBIERZ 1 Z Sys.database_principals WHERE [typ] = 'R' AND [nazwa] = 'ReadOnlyUsers')' 'UTWÓRZ ROLĘ [ReadOnlyUsers]' 'EXEC sp_addrolemember 'db_datareader', 'ReadOnlyUser' '' GRANT? (Wybierz? Lub?) ON SCHEMA :: dbo DO [ReadOnlyUsers] '' GO' 'EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser'' Jak odróżnić EXEC, UPDATE, DELETE itd., Jeśli wszystkie obiekty są pod dbo?Na przykład ReadOnlyUser nadal potrzebuje EXEC przechowywanych proc. 20 wrz. 112011-09-20 15:27:51

  0

@David George: uprawnienia do schematu obowiązują automatycznie niezależnie od obiektu: więc proc dostaje EXEC, a tabele S/I/U/D.Twój GRANT byłby GRANT EXEX, SELECT, UPDATE itp. ON ... 20 wrz. 112011-09-20 16:33:55

  0

więc uruchomiłbym 'GRANT EXEC, SELECT, UPDATE, INSERT, DELETE na SCHEMA :: dbo TO [ReadWriteUsers]' Czy pozwoliłoby to temu użytkownikowi na potencjalne DROP w procedurze? 20 wrz. 112011-09-20 18:54:03

  0

Nie, potrzebują do tego ALTER TABLE lub takiego 20 wrz. 112011-09-20 22:29:39