SQLユーザーにすべてのオブジェクトに対する権限を設定する


1

データベース内のすべてのオブジェクトをループ処理し、そのオブジェクトに適切な権限を割り当てる手順があります。私はこれを行うためのより良い方法があるかどうか知りたいですか?モデルデータベースを使用して新しいデータベースを作成するので、新しいデータベースを作成するたびにこれを実行する必要があります。これがどのように見えるかのアイデアです。

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

このスクリプトは、データベース内のすべてのビュー、機能などについて同様のことを続けます。これをスピードアップするためのアイデアはありますか、またはこれを行うためのより良い方法はありますか?

6

スキーマレベルの権限をロールに付与する単一行

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

ロールにユーザーを追加するための2行目

EXEC sp_addrolemember 'SomeRole', 'whatever user'

そして、すべての新しいデータベースが継承するように、これをモデルで実行します。

理由は、権限を1回だけ設定する必要があることです。

  • スキーマはオブジェクトのコンテナです。
  • 新しいオブジェクトはスキーマから権限を継承します
  • ロールはユーザーのコンテナーです
  • 新しいユーザーがロールに追加されて継承

ご存じのとおり、データベースを移行または復元すると、ユーザーに直接割り当てられたときにオブジェクトのアクセス許可が失われることがあります。それでは、どうしてそんな立場にいるのでしょうか。

CREATE LOGIN with a SIDでもCREATE LOGIN with a SIDません。すべてのサーバーで同じで、孤立したユーザーにはなりません。

もしあなたが正しい質問をしたのであれば、私たちはあなたにコーディングをいくらか節約することができたでしょう...

個人的にそして最後に、私はこれが「する必要がある」の上で悪い習慣ではなく、「何もしない」ことを言うと思います

  0

だから何かのように:NOTが存在する場合 '' 'ROLEをCREATE [ReadOnlyUsers]' 'EXEC(WHERE [タイプ] = 'R' AND [名] = 'ReadOnlyUsers' sys.database_principalsから1を選択します) sp_addrolemember 'db_datareader'、 'ReadOnlyUser'' 'GRANT?(選択または?)SCHEMA :: dbo TO [ReadOnlyUsers]' ' GO' 'EXEC sp_addrolemember 'ReadOnlyUsers'、 'ReadOnlyUser'' すべてのオブジェクトがdboの下にある場合、EXEC、UPDATE、DELETEなどを区別する方法はありますか?例えば、ReadOnlyUserは、依然として、格納されたprocsをEXECする必要があります。 20 9月. 112011-09-20 15:27:51

  0

@David George:スキーマのアクセス許可はオブジェクトに関係なく自動的に適用されるため、procはEXECを取得し、テーブルはS/I/U/Dを取得します。あなたのGRANTは、GRANT EXEX、SELECT、UPDATEなどON ... 20 9月. 112011-09-20 16:33:55

  0

ですので、 'READ EXEC、SELECT、UPDATE、INSERT、DELETEをSCHEMA :: dbo TO [ReadWriteUsers] 'に実行します。その後、手順を削除しますか? 20 9月. 112011-09-20 18:54:03

  0

いいえ、ALTER TABLEなどが必要です 20 9月. 112011-09-20 22:29:39