SQL Server: ユーザー定義のサーバー ロール
SQL Server 2012 の登場により、ユーザー定義のサーバー ロールを作成できるようになります。これは、SQL Server の管理における大きな前進です。
Denny Cherry
データベース レベルでアクセス許可を付与する処理を容易にするためにユーザー定義のデータベース ロールを作成することは何年も前から可能でした。しかし、インスタンス レベルでのアクセス許可の付与には、昔から 9 つの固定サーバー ロールしかありませんでした (bulkadmin ロールが追加されたのは SQL Server 2005 からなので、SQL Server 2000 以前は 8 つでした)。ところが、SQL Server 2012 では、ついに、ユーザー定義のサーバー ロールを作成できるようになります。
ユーザー定義のサーバー ロールを作成できないことは、長い間 SQL 管理上の悩みの種になっていました。インスタンス レベルで複数のユーザーやグループにアクセス許可を与え、このアクセス許可を正しく同期した状態に保つにはどうすればよいでしょう。たとえば、開発サーバー内のブロッキング情報を表示できるように、システム状態の表示権限を多数のユーザーに許可したいとします。そのためには許可を必要とする各ユーザーまたはドメイン グループに 1 つずつ許可を行う必要があります。
すべてのログインがドメインのログインを基にしている場合、唯一の回避策はその許可を必要とするすべてのユーザーを含むドメイン グループを作成することです (ユーザーによってこの許可を必要としないサーバーがある場合は、おそらくサーバーごとに 1 つのドメイン グループが必要です)。ユーザーをこのグループに配置し、このドメイン グループにマップするログインを作成してから、このグループにサーバー状態の表示権限を許可します。しかし、これには十分な注意が必要です。それまでログイン権限がなかったユーザーにログイン権限を与える可能性があります。ユーザーによっては与える必要のない権限まで許可してしまうことがあります。
T/SQL とロール
ユーザー定義のサーバー ロールは、T/SQL、SQL Server Management Studio の UI、Windows PowerShell など、さまざまな方法で作成できます。ここでは T/SQL の 3 つの異なるコマンドを使ってユーザー定義のサーバー ロールを作成する例を示します。まず、Create Server Role ステートメントでユーザー定義のサーバー ロールを作成します。次に Alter Server Role でユーザーをサーバー ロールに追加します。さらに、Grant ステートメントで必要な権限をロールに付与します。
以下にこれら 3 つのステートメントを使ったコードを示します。ここでは "ViewServerState" というユーザー定義のサーバー ロールを作成しています。ロールに "SomeFakeLogin" というユーザーを追加し、そのロールにサーバー状態の表示権限を許可します。他のユーザーにもこの権限を許可する場合は、Alter Server Role の行でそのユーザーをサーバー ロールに追加するだけです。
USE [master] GO CREATE SERVER ROLE [ViewServerState] AUTHORIZATION [sa] GO ALTER SERVER ROLE [ViewServerState] ADD MEMBER [SomeFakeLogin] GO GRANT VIEW SERVER STATE TO [ViewServerState] GO
Alter Server Role を使ってユーザー定義のサーバー ロールからユーザーを削除することもできます。Add Member 構文の代わりに、Drop Member を使います。
ALTER SERVER ROLE [ViewServerState] DROP MEMBER [SomeFakeLogin] GO
SQL Server の複数のインスタンスに対して 1 つ以上のユーザー定義サーバー ロールを配置するとなると、あまり選択肢はありません。ユーザー定義サーバー ロールを作成するために、各サーバーに 1 回ずつ接続したいとはおそらく思わないでしょう。1 つの選択肢は SQL Server Management Studio の機能を使うことです。これによって複数のインスタンスに対して T/SQL スクリプトを実行できます。
また、SQL Server の Windows PowerShell コンポーネントを使って、新しいユーザー定義サーバー ロールを組織内の SQL Server のすべてのインスタンスに配置できます (このスクリプトを配置するために Windows PowerShell を使う方法は複数あるため、ここではこの手順を取り上げません)。
SQL Server Management Studio
前述の操作を SQL Server Management Studio の UI から簡単に実行できます。ユーザー定義のサーバー ロールを作成するには、オブジェクト エクスプローラー内でインスタンスに接続します。オブジェクト エクスプローラーで [<インスタンス名>]、[セキュリティ]、[サーバー ロール] の順に移動します。[サーバー ロール] を右クリックして、コンテキスト メニューから [新しいサーバー ロール] を選択します。[新しいサーバー ロール] ウィンドウが開いたら、[サーバー ロール名] と [所有者] を指定し、次に [セキュリティ保護可能なリソース] を選択してから、ロールのメンバーに必要な権限を選択します (図 1 参照)。
.jpg)
図 1 特定のユーザーに必要な権限の指定
[全般] ページのフィールドへの入力を終えたら、[メンバー] ページを選択し (図 2 参照)、このユーザー定義のサーバー ロールのメンバーにする SQL Server のログインを指定します。
.jpg)
図 2 メンバーを選択して、このサーバー ロールにアクセス許可を持つユーザーを指定
ロール メンバーを選択したら、[メンバーシップ] ページを選択します。ここでは、このユーザー定義サーバー ロールをメンバーとして含めるサーバー ロールを指定します。この画面でサーバー ロールを指定すると、ユーザー定義サーバー ロールに所属するユーザーにはそのサーバー ロールに許可された権限も付与されます。
ユーザー定義サーバー ロールを作成し、それを ServerAdmin 固定サーバー ロール (図 3 参照) のメンバーにすると、ユーザー定義サーバー ロールに所属するすべてのメンバーは事実上 ServerAdmin 固定サーバー ロールのメンバーではなくなります。入れ子になったドメイン グループやデータベース ロールと同様に、ロールを入れ子にする場合は上位グループから必要のない権限を継承してユーザーに許可しないように、細心の注意が必要です。
.jpg)
図 3 ユーザー定義のロールを他のサーバー ロールに適用可能
T/SQL を使ってサーバー ロールを入れ子にするには、Add Member 構文を指定した Alter Server Role ステートメントを使用します。たとえば、ユーザー定義サーバー ロール ViewServerState を SetupAdmin 固定サーバー ロールのメンバーにするには、SetupAdmin 固定サーバー ロールを変更します。次にユーザー定義のサーバー ロール ViewServerState をメンバーとして追加します。
ALTER SERVER ROLE [setupadmin] ADD MEMBER [ViewServerState] GO
ユーザー定義サーバー ロールにはさまざまな使い方があります。数十種類もあるインスタンス レベルの権限を、ユーザー定義サーバー ロールに許可することにより、それらの権限の管理を簡素化できます。いくつかの権限は許可しても、システム管理者の完全な権限は許可しないような下位 DBA ロールを作成することもできます。システム管理者の完全な権限を持つことなく、AlwaysOn データベースのフェールオーバーを行う権限を許可する (これは SQL Server 内で行う必要があります) ような、AlwaysOnAdmin グループを作成することもできます。
ユーザー定義サーバー ロールにはさまざまな用途があります。管理ワークロードを大幅に軽減できます。不要な権限を間違えて許可してしまうことも減らせるため、新しいサーバー ロールは SQL Server のインスタンスのセキュリティの向上にも役立ちます。
.jpg)
Denny Cherry は、SQL Server の管理に 10 年以上携わっています。専門は、システム アーキテクチャ、パフォーマンス チューニング、レプリケーション、トラブルシューティングです。SQL Server 2008 のマイクロソフト認定資格など、SQL Server 関連の認定資格をいくつか保有しており、数年にわたって Microsoft SQL Server MVP として活躍しています。また、SQL Server の管理や別のテクノロジと SQL Server の統合について扱った技術文書や書籍を多数執筆しています。