SQL Serverの権限管理：GRANT EXECUTEでストアドプロシージャを安全に制御

2024-05-02

SQL Server ですべてのストアドプロシージャに EXECUTE 権限を付与する方法

SQL Server では、GRANT EXECUTE ステートメントを使用して、ユーザーまたはロールに特定のストアドプロシージャ、またはすべてのストアドプロシージャに対する EXECUTE 権限を付与することができます。この権限があると、ユーザーまたはロールは、そのプロシージャを実行することができます。

すべてのストアドプロシージャに EXECUTE 権限を付与するには、次の T-SQL ステートメントを使用できます。

GRANT EXECUTE ON SCHEMA::PROCEDURE TO [ユーザーまたはロール名];

このステートメントでは、次のことを指定する必要があります。

SCHEMA: プロシージャを含むスキーマの名前。既定値は現在のスキーマです。
PROCEDURE: すべてのストアドプロシージャを表すワイルドカード文字 *。
[ユーザーまたはロール名]: 権限を付与するユーザーまたはロールの名前。

例

次のステートメントは、MyApp データベースのすべてのストアドプロシージャに MyUsers ロールに EXECUTE 権限を付与します。

GRANT EXECUTE ON SCHEMA::PROCEDURE TO MyUsers;

注意事項

このステートメントを実行するには、少なくとも EXECUTE 権限を持つ必要があります。
ユーザーまたはロールにすでに EXECUTE 権限が付与されている場合は、このステートメントを実行しても権限は変更されません。
このステートメントは、システムプロシージャには影響しません。

代替方法

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'GRANT EXECUTE ON OBJECT_ID(N''[' + SCHEMA_NAME() + '].[' + NAME + ']'')
    TO [MyUsers];';

EXEC sp_executesql @sql;

このステートメントは、動的 SQL を使用して、すべてのストアドプロシージャに対する EXECUTE 権限を MyUsers ロールに付与します。

サンプルコード：SQL Server ですべてのストアドプロシージャに EXECUTE 権限を付与する

以下のサンプルコードは、MyUsers ロールにMyApp データベース内のすべてのストアドプロシージャに対する EXECUTE 権限を付与します。

-- すべてのストアド プロシージャの名前を取得する
DECLARE @sql NVARCHAR(MAX);

SET @sql = N'
SELECT ''GRANT EXECUTE ON OBJECT_ID(N''[' + SCHEMA_NAME() + '].[' + NAME + ']'')
    TO [MyUsers];''
FROM sys.procedures;
';

-- 動的 SQL を使用して権限を付与する
EXEC sp_executesql @sql;

このコードの説明:

DECLARE @sql NVARCHAR(MAX); ステートメントは、@sql という名前の変数を宣言します。この変数は、動的 SQL ステートメントを格納するために使用されます。
SET @sql = N'GRANT EXECUTE...'; ステートメントは、@sql 変数に次の動的 SQL ステートメントを割り当てます。

GRANT EXECUTE ON OBJECT_ID(N''[' + SCHEMA_NAME() + '].[' + NAME + ']'')
    TO [MyUsers];

このステートメントは、次のことを行います。 * SCHEMA_NAME(): 現在のスキーマの名前を取得します。 * NAME: sys.procedures テーブルの NAME 列からストアドプロシージャの名前を取得します。 * OBJECT_ID: ストアドプロシージャの名前を使用して、そのオブジェクト ID を取得します。 * GRANT EXECUTE: 指定されたオブジェクト ID に対する EXECUTE 権限を MyUsers ロールに付与します。

EXEC sp_executesql @sql; ステートメントは、@sql 変数に格納されている動的 SQL ステートメントを実行します。

以下の代替コードは、同じ結果を得ることができます。

GRANT EXECUTE ON SCHEMA::PROCEDURE TO MyUsers;

このステートメントは、すべてのストアドプロシージャを表すワイルドカード文字 * を使用して、MyUsers ロールに EXECUTE 権限を付与します。

SQL Server ですべてのストアドプロシージャに EXECUTE 権限を付与するその他の方法

前述の方法に加えて、SQL Server ですべてのストアドプロシージャに EXECUTE 権限を付与する方法は他にもいくつかあります。以下に、いくつかの例を紹介します。

SQL Server Management Studio (SSMS) を使用する

SSMS を使用して、すべてのストアドプロシージャに EXECUTE 権限を付与するには、次の手順を実行します。

SSMS で、権限を付与するデータベースに接続します。
オブジェクトエクスプローラー で、データベース ノードを展開します。
セキュリティ ノードを展開します。
ロール フォルダを展開します。
権限を付与するロールを右クリックし、 コンテキストメニュー から プロパティ を選択します。
プロパティ ダイアログボックスで、 アクセス許可 ページを選択します。
オブジェクトの種類 ボックスの一覧から ストアドプロシージャ を選択します。
明示的な権限 グリッドで、EXECUTE チェックボックスをオンにします。
OK をクリックして変更を保存します。

$sql = "GRANT EXECUTE ON SCHEMA::PROCEDURE TO [MyUsers];"
Invoke-SqlCmd -Database MyDatabase -SqlQuery $sql

このコマンドは、次のことを行います。

Invoke-SqlCmd コマンドレットを使用して、MyDatabase データベースに対して $sql 変数に格納されている T-SQL ステートメントを実行します。

Transact-SQL スクリプトを作成して、すべてのストアドプロシージャに EXECUTE 権限を付与することができます。スクリプトには、次の T-SQL ステートメントを含める必要があります。

GRANT EXECUTE ON SCHEMA::PROCEDURE TO [MyUsers];

このスクリプトを SQL Server Management Studio またはコマンドプロンプトから実行できます。

最適な方法の選択

使用する方法は、ニーズと環境によって異なります。 SSMS を使用すると、GUI を使用して権限を簡単に付与できます。 PowerShell を使用すると、スクリプトを使用して権限を自動的に付与することができます。 Transact-SQL スクリプトを使用すると、よりきめ細かい制御を行うことができます。

sql sql-server t-sql