SQL kullanıcıları için tüm nesnelerde izinleri ayarlama


1

Veritabanındaki tüm nesneler arasında dolaşan ve bu nesneye uygun izinler atanan bir yordam var.Bunu yapmanın daha iyi bir yolu olup olmadığını bilmek istiyorum?Yeni veritabanları oluşturmak için model bir veritabanı kullanıyorum, bu yüzden her seferinde yeni bir veritabanı oluştururken bunu çalıştırmalıyım.İşte neye benzediği hakkında bir fikir (not: tüm kullanıcıları bırakıp gerekli kullanıcıları yeniden yaratan başlangıçta eksik olan bir yığın var; bu SID'nin değişmesinden bu yana gerekli):

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

Bu komut dosyaları, veritabanındaki tüm görünümler, işlevler vb. İçin benzer şeyler yapmaya devam eder.Bu şeyi hızlandırmak için herhangi bir fikir veya bunu yapmak için daha iyi bir yolu var mı?

6

Şema düzeyinde bir role izin vermek için tek satır

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

Ve kullanıcıları role eklemek için ikinci bir satır

EXEC sp_addrolemember 'SomeRole', 'whatever user'

Ve bunu modelde yapın, böylece tüm yeni veritabanları miras edinir.

Sebepler, izinleri yalnızca bir kez belirlemelisiniz:

  • Şema, nesneler için bir kaptır.
  • Yeni nesneler şemadan izinleri devralır
  • Bir rol kullanıcılar için kapsayıcıdır
  • Yeni kullanıcılar bir role eklenir ve miras alınır

Gördüğünüz gibi bir veritabanını geçirmek veya geri yüklemek, doğrudan kullanıcılara atandığında nesne izinlerini kaybedebilir.Peki neden kendini bu pozisyona soktun?

Ayrıca CREATE LOGIN with a SID da CREATE LOGIN with a SID böylece tüm sunucularınızda aynıdır ve artık kullanıcılar da alamazsınız.

Doğru soruyu sorsaydın, sana biraz kodlama kazandırabilirdik ...

Şahsen ve son olarak, bunun “battaniye bir şey yapmama” değil “sahip olma ihtiyacı” konusundaki kötü bir uygulama olduğunu söyleyebilirim.

  0

Öyle bir şey: '' VARMADIĞINIZ VAR (Sys.database_principals NEREDEN 1 SEÇİLİRSİNİZ?] '' ROL'U OLUŞTURUN [ReadOnlyUsers] 'EXEC sp_addrolemember' db_datareader ',' EXEC sp_addrolemember 'db_datareader', 'EXO sp_addrolemember' '' GRANT? (Seçin? Veya?) ŞEMA ÜZERİNDE :: dbo [ReadOnlyUsers] - 'GO'' EXEC sp_addrolemember 'ReadOnlyUsers', 'ReadOnlyUser' vb. Eğer tüm nesneler nasıl ayrılır? EXEC, UPDATE, DELETE, vb. dbo altında olsa?Örneğin, ReadOnlyUser'ın EXEC'e depolanmış işlemleri yapması gerekir. 20 eyl. 112011-09-20 15:27:51

  0

@Did George: şema izinleri nesneye bakılmaksızın otomatik olarak uygulanır: böylece bir proc EXEC alır ve bir tablo S/I/U/D alır.BÜYÜK HEDEF EXEX, SELECT, GÜNCELLEME vs AÇIK olacaktır ... 20 eyl. 112011-09-20 16:33:55

  0

bu yüzden 'GRANT EXEC, SELECT, UPDATE, INSERT, SCHEMA üzerinde DELETE komutunu çalıştıracağım: dbo 'a [ReadWriteUsers]' a izin verin. 20 eyl. 112011-09-20 18:54:03

  0

Hayır, ALTER TABLE ya da bunun gibi bir şeye ihtiyaçları var. 20 eyl. 112011-09-20 22:29:39