連載
» 2002年01月05日 00時00分 UPDATE

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」を作成したところ(画面をクリックすると拡大表示します) 画面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で(システム)ストアドプロシージャの一覧を参照する(画面をクリックすると拡大表示します) 画面2 SQL Server Enterprise Managerで(システム)ストアドプロシージャの一覧を参照する(画面をクリックすると拡大表示します)

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

画面3 各システム・ストアドプロシージャの機能を知るには、SQL Server Books Onlineを参照するといい(画面をクリックすると拡大表示します) 画面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を使用した権限の設定について解説しました。次回からはストアドプロシージャの作成ついて紹介する予定です



Copyright© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。