システム・ストアドプロシージャを用いたロールの詳細設定：SQL実践講座（19）

[篠原光太郎，＠IT]

　今回は、前回までに実施した「権限設定」を、SQLを使用して実施します。SQLを使用することで、SQL Server Enterprise Managerでは設定不可能な、細かい指定での権限関連の設定が行えます。では早速、例題を実行しながら、SQLの確認をしていきましょう。

SQLを用いたSQL Serverログインの作成

　まず、「suzuki」という名前のSQL Serverログインを新たに作成してみましょう。次の例題を実行します。

【例1】

EXEC sp_addlogin 'suzuki', 'suzuki', 'Northwind', '日本語'

画面1　SQL文でSQLログイン名「suzuki」を作成したところ（画面をクリックすると拡大表示します）

　正しくSQL Serverログインが作成できたかどうかは、実際にSQL Serverログイン名「suzuki」でログインすることで確認できます。SQLクエリアナライザでSQL Serverへログインしてみましょう。パスワードは、「suzuki」です。無事にログインできたでしょうか。

　では、例1のSQL文を見ていきましょう。このSQL文では、「sp_addlogin」という「システム・ストアドプロシージャ」を呼び出し、SQL Serverログインの作成を実行しています。「システム・ストアドプロシージャ」は、今回初めて使用する機能ですね。まず、このシステム・ストアドプロシージャの解説から始めましょう。

システム・ストアドプロシージャ

　SQL Serverには、「Transact-SQL」というプログラミング言語が用意されています。Transact-SQLで作成したプログラムは、SQL Server上に保存し、SQL文から呼び出して実行することが可能です。このプログラムのうち、サブルーチンとして機能するのが「ストアドプロシージャ」、また、関数として機能するのが「ユーザー定義関数」です。どちらも「EXECUTE」というSQL文を使用して機能を呼び出します（例1のように「EXEC」と省略することが可能です）。また、ユーザー定義関数は、SELECT文中でほかの関数と同様に使用することができます。ストアドプロシージャやユーザー定義関数の作成方法は、今後、連載の中で解説していきます。

　ストアドプロシージャには、SQL Serverのインストール時にすでに定義されているものがあります。これを、「システム・ストアドプロシージャ」と呼び、SQLからSQL Serverに対する設定や操作を行う機能を提供します。システム・ストアドプロシージャは「master」データベースに保存されていますので、一覧を表示させてみましょう。

例2：「システム・ストアドプロシージャの一覧表示」

(1) Enterprise Managerを起動する

(2) 左端に表示される「ツリー」で、以下を順に展開する

1.Microsoft SQL Servers
2. SQL Server グループ
3. サーバー名
4. データベース
5. 「master」
6. ストアドプロシージャ

画面2　SQL Server Enterprise Managerで（システム）ストアドプロシージャの一覧を参照する（画面をクリックすると拡大表示します）

　表示される一覧の中に、例1で使用した「sp_addlogin」も含まれていることが確認できますね。これらのシステム・ストアドプロシージャの機能については、SQL Server Books Onlineで、アクティブサブセットを「SQLプログラマ：Transact-SQL」、キーワードを「システムストアドプロシージャ」として検索すると表示させることができますので、確認してみてください。

画面3　各システム・ストアドプロシージャの機能を知るには、SQL Server Books Onlineを参照するといい（画面をクリックすると拡大表示します）

「sp_addlogin」システム・ストアドプロシージャ

　では、例1に戻って、システム・ストアドプロシージャの呼び出しについての解説を続けましょう。ストアドプロシージャの呼び出しは、EXECUTE文を使用します。EXECUTE文の書式は、次のとおりです。

EXECUTE ストアドプロシージャ名 引数1、引数2、引数3……

　sp_addloginシステム・ストアドプロシージャの引数は、次のように定義されています。

sp_addlogin 'ログイン名', 'パスワード', 'デフォルトデータベース', 'デフォルト言語', 'SID', '暗号化オプション'

　「ログイン名」は、作成するSQL Serverログイン名を指定します。例1では、「suzuki」を指定しました。「パスワード」は、暗号化される前のパスワードを指定します。例1ではログイン名と同じ「suzuki」を指定しました。sp_addloginが実行されると、パスワードはデータベース上に暗号化されて保存されます。「デフォルトデータベース」は、これまでの例題で使用している「Northwind」データベースを指定しました。引数に「NULL」を指定すると、デフォルトデータベースは「master」データベースに指定されます。「デフォルト言語」は、日本語環境では通常、「日本語」を指定します。引数に「NULL」を指定すると、サーバでデフォルトに指定した言語（インストール時に選択）が設定されます。

　「SID」と「暗号化オプション」は、例1では省略しました。SQL Serverログインを作成すると、システム内部に「SID」と呼ばれる識別番号が一意に振られます。通常、意識する必要はありませんが、データベース間でSQL Serverログインを移行する際、元のデータベースと同じSIDを設定する必要があるときに指定します。「暗号化オプション」は、「パスワード」に指定したパスワードが、すでに暗号化されているか否かを指定するオプションです。通常、新規のSQL Serverログインを作成する際は、省略してOKです。

SQLによるデータベースユーザーの作成

　では続いて、データベースユーザーを作成しましょう。SQL Server Enterprise ManagerでSQL Serverログインを作成する際は、「データベースアクセス」の設定で該当のデータベースを指定することで、自動的にデータベースユーザーが作成されましたが、SQLで実施する場合は個別に設定する必要があります。

　次の例題を実行してみましょう。

【例3】

EXEC sp_grantdbaccess 'suzuki', 'suzuki'

　sp_grantdbaccessシステム・ストアドプロシージャの引数は、次のように定義されています。

sp_grantdbaccess 'ログイン名', 'データベースユーザー名'

　sp_grantdbaccessシステム・ストアドプロシージャは、現在指定されているデータベースに対して「データベースユーザー名」を作成し、「ログイン名」のSQL Serverログインがアクセス可能であるように設定します。「ログイン名」には、SQL Serverログイン名を指定します。例3では、例1で作成した「suzuki」を指定しました。「データベースユーザー名」は、通常はSQL Serverログイン名と同じ名前を指定します。例3では、「suzuki」を指定しました。

SQLによる権限の付与

　続いて、データベースユーザーに対して権限の付与を実施しましょう。ここでは、先ほど作成したデータベースユーザー「suzuki」が、ビュー「CardInfoView」の参照と更新ができるように、SELECT権限を付与します。

　次の例題を実行してみましょう。

【例4】

GRANT SELECT,UPDATE ON CardInfoView TO suzuki

　GRANT文の書式は、次のように定義されています。

GRANT 権限1, 権限2, …… ON オブジェクト名 TO データベースユーザー名

　GRANT文は、データベースユーザーに対して、データベースオブジェクトへの権限の付与を実行します。「権限」は、参照はSELECT、更新はUPDATE、追加はINSERT、削除はDELETEを指定します。例4では、参照と更新でしたので、「SELECT」と「UPDATE」を指定しました。「オブジェクト名」には、テーブル名かビュー名を指定します。例題4では、「CardInfoView」を指定しました。「データベースユーザー名」には、権限を付与するデータベースユーザー名を指定します。例4では、「suzuki」を指定しました。

　同じGRANT文を使用して、ストアドプロシージャの実行権限も指定することが可能ですが、詳細は次回以降の「ストアドプロシージャの作成」で説明します。

SQLによる権限の拒否

　では続いて、データベースユーザーに対して権限の拒否を実施しましょう。ここでは、先ほど作成したデータベースユーザー「suzuki」に対して、ビュー「CardInfoView」の削除の権限を拒否してみます。

　次の例題を実行してみましょう。

【例5】

DENY DELETE ON CardInfoView TO suzuki

　基本的な使用に関しての書式は、「GRANT」と完全に同じです。

DENY 権限1, 権限2, …… ON オブジェクト名 TO データベースユーザー名

SQLによる権限設定の削除

　設定した権限を削除するには、次の例6のように「REVOKE」文を使用します。ここで、権限の「削除」と「拒否」は、意味が違うことをあらためて確認してみてください。例6では、例4で権限を付与した更新権限を削除します。

　次の例題を実行してみましょう。

【例6】

REVOKE UPDATE ON CardInfoView TO Suzuki

　基本的な使用に関しての書式は、「GRANT」と完全に同じです。

REVOKE 権限1, 権限2, …… ON オブジェクト名 TO データベースユーザー名

SQLによるロールの作成

　では次に、SQLによるロールの作成を実施してみましょう。ここでは、ロール「CardInfoAdmin」を作成します。

　次の例題を実行してみましょう。

【例7】

EXEC sp_addrole 'CardInfoAdmin'

　sp_addroleシステム・ストアドプロシージャの引数は、次のように定義されています。

sp_addrole 'ロール名', 'オーナー名'

　「ロール名」は、作成するロール名です。例7では、「CardInfoAdmin」を指定しました。「オーナー名」は省略可能です。省略した場合のオーナーは、「dbo」に指定されます。

SQLによるロールへのメンバー追加

　引き続き、作成したロール「CardInfoAdmin」へ、データベースユーザー「suzuki」を追加してみましょう。次の例題を実行してみましょう。

【例8】

EXEC sp_addrolemember 'CardInfoAdmin', 'suzuki'

　sp_addrolememberシステムストアドプロシージャの引数は、次のように定義されています。

sp_addrolemember 'ロール名', 'データベースユーザー名'

　「ロール名」は、メンバーを追加するロール名です。例8では、「CardInfoAdmin」を指定しました。「データベースユーザー名」は追加するメンバーのデータベースユーザー名です。例8では、「suzuki」を指定しました。

次回の予定

今回は、SQLを使用した権限の設定について解説しました。次回からはストアドプロシージャの作成ついて紹介する予定です

「次回」へ

「SQL実践講座」バックナンバー

• SQL Serverで「デッドロック」を回避する
• トランザクションの一貫性を保証するロック
• トランザクションを用いて注文登録をする
• トランザクションでデータの不整合を防ぐ
• テーブルで複数の処理を実行させるトリガー
• ユーザー定義関数を作成するストアドファンクション
• ストアドプロシージャによる繰り返し処理
• 条件分岐のあるストアドプロシージャ
• ストアドプロシージャの作成
• システム・ストアドプロシージャを用いたロールの詳細設定
• ロールを利用したグループ単位での権限設定
• SQL Serverのオブジェクトに権限を設定する
• Enterprise Managerによるビューの作成
• 作成したSELECT文をDBに登録する「ビュー」
• データの更新と主キーの重要性
• テーブル中のデータ識別に必要な主キーを定義する
• データの登録を行うINSERT文
• CREATE文をさらに使いこなそう
• CREATE文でテーブルを作成する
• SELECT文を統合する「UNION」
• サブクエリーの応用「相関サブクエリー」
• SELECT文の結果を抽出条件に使う
• テーブル結合のバリエーションを増やす
• テーブル結合の仕組みを理解する
• 異なるテーブル同士を結合する「JOIN」句
• 集計を行う「GROUP BY」句
• SELECT文で並べ替えを行うには？
• SQLの基礎 「SELECT」文を覚えよう