今回は、前回までに実施した「権限設定」を、SQLを使用して実施します。SQLを使用することで、SQL Server Enterprise Managerでは設定不可能な、細かい指定での権限関連の設定が行えます。では早速、例題を実行しながら、SQLの確認をしていきましょう。
SQLを用いたSQL Serverログインの作成
まず、「suzuki」という名前のSQL Serverログインを新たに作成してみましょう。次の例題を実行します。
【例1】
EXEC sp_addlogin 'suzuki', 'suzuki', 'Northwind', '日本語'
正しく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. ストアドプロシージャ
表示される一覧の中に、例1で使用した「sp_addlogin」も含まれていることが確認できますね。これらのシステム・ストアドプロシージャの機能については、SQL Server Books Onlineで、アクティブサブセットを「SQLプログラマ：Transact-SQL」、キーワードを「システムストアドプロシージャ」として検索すると表示させることができますので、確認してみてください。
「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 Serverで「デッドロック」を回避する
- トランザクションの一貫性を保証するロック
- トランザクションを用いて注文登録をする
- トランザクションでデータの不整合を防ぐ
- テーブルで複数の処理を実行させるトリガー
- ユーザー定義関数を作成するストアドファンクション
- ストアドプロシージャによる繰り返し処理
- 条件分岐のあるストアドプロシージャ
- ストアドプロシージャの作成
- システム・ストアドプロシージャを用いたロールの詳細設定
- ロールを利用したグループ単位での権限設定
- SQL Serverのオブジェクトに権限を設定する
- Enterprise Managerによるビューの作成
- 作成したSELECT文をDBに登録する「ビュー」
- データの更新と主キーの重要性
- テーブル中のデータ識別に必要な主キーを定義する
- データの登録を行うINSERT文
- CREATE文をさらに使いこなそう
- CREATE文でテーブルを作成する
- SELECT文を統合する「UNION」
- サブクエリーの応用「相関サブクエリー」
- SELECT文の結果を抽出条件に使う
- テーブル結合のバリエーションを増やす
- テーブル結合の仕組みを理解する
- 異なるテーブル同士を結合する「JOIN」句
- 集計を行う「GROUP BY」句
- SELECT文で並べ替えを行うには？
- SQLの基礎 「SELECT」文を覚えよう
Copyright © ITmedia, Inc. All Rights Reserved.