SQL Server 2012 自習書シリーズ No.4 ログイン認証とオブジェクト権限 Published: 2008 年 5 月 12 日 SQL Server 2012 更新版 : 2012 年 9 月 30 日有限会社エスキューエル クオリティ

Transcription

1 SQL Server 0 自習書シリーズ No.4 ログイン認証とオブジェクト権限 Published: 008 年 5 月 日 SQL Server 0 更新版 : 0 年 9 月 0 日有限会社エスキューエル クオリティ

2 この文章に含まれる情報は 公表の日付の時点での Microsoft Corporation の考え方を表しています 市場の変化に応える必要 があるため Microsoft は記載されている内容を約束しているわけではありません この文書の内容は印刷後も正しいとは保障で きません この文章は情報の提供のみを目的としています Microsoft SQL Server Visual Studio Windows Windows XP Windows Server Windows Vista は Microsoft Corporation の米国およびその他の国における登録商標です その他 記載されている会社名および製品名は 各社の商標または登録商標です Copyright 0 Microsoft Corporation. All rights reserved.

3 目次 STEP. データアクセスの概要と自習書を試す環境について データアクセスの概要 自習書を試す環境について 事前作業 (sampledb データベースの作成 )... 8 STEP. つのセキュリティチェック つのセキュリティチェック.... セキュリティモード ( 認証モード ).... SQL Server 認証を使用したログイン データベースユーザーの登録 ( ログインとのマッピング ) オブジェクト権限の付与....6 セキュリティモードを Windows 認証モードへ変更... 7 STEP. ログインアカウントと SQL Server レベルの権限 Windows 認証を使用したログイン.... Windows グループに対するログインアカウントの作成 管理者アカウント パスワードの管理 ( セキュリティポリシー ) SQL Server 認証用アカウントへのポリシーの適用 固定サーバーロール SQL Server レベルの権限 SQL ステートメントでログインアカウントの作成... 6 STEP 4. データベースユーザー データベースユーザー 特殊データベースユーザー (dbo guest) SQL ステートメントでデータベースユーザーの作成... 7 STEP 5. オブジェクト権限とデータベースロール オブジェクト権限 固定データベースロール オブジェクト権限の状態 (GRANT/DENY/REVOKE) SQL ステートメントでオブジェクト権限の設定 ユーザー定義データベースロール データベースレベルの権限 ( ステートメント権限 ) スキーマとは ビューを利用したセキュリティ強化 スキーマが異なる場合のオブジェクト権限の注意事項...

4 STEP. データアクセスの概要 と自習書を試す環境について この STEP では データアクセスの概要と自習書を試す環境について説明しま す この STEP では 次のことを学習します データアクセスの概要 自習書を試す環境について サンプルスクリプトについて 4

5 . データアクセスの概要 つのセキュリティチェック SQL Server では 一般ユーザーがデータベース内のテーブルを操作 (SELECT/INSERT/ UPDATE/DELETE ステートメントを実行 ) するには 次の つのセキュリティチェックを通過しなければなりません 第 のチェック : SQL Server への接続 ( ログイン認証 ) 最初のチェックは SQL Server に対するログイン認証です 認証方法には Windows 認証 と SQL Server 認証 の つがありますが デフォルトでは Windows 認証のみが利用できます SQL Server へ接続するには ログインアカウントを使用して 認証に成功 しなければなりません 認証に失敗した場合は 次のエラーが発生します 第 のチェック : データベースへの接続 SQL Server へログインした後に データベースへ接続するには ログインアカウントが データベースユーザーとして登録 されている必要があります SQL Server へログインできたからといって データベースへ接続できるわけではないことに注意してください データベースへの接続が拒否された場合は 次のエラーが発生します 5

6 第 のチェック : オブジェクト操作データベース内のオブジェクト ( テーブルやビュー ストアドプロシージャなど ) を操作するには 対象となるオブジェクトに対する オブジェクト権限 が与えられている必要があります オブジェクト権限が付与されていない場合は 次のエラーが発生します Note: SQL Server の管理者アカウントはすべての操作が可能 SQL Server の管理者アカウントの場合は 上記の つのセキュリティチェックはまったく関係ありません 管理者アカウントの場合は セキュリティチェックをすべて無視して SQL Server に対してあらゆる操作が実行可能です 既定では SQL Server の管理者アカウントは SQL Server インストール時に 次のように [ データベースエンジンの構成 ] ページで SQL Server の管理者として追加したユーザーと sa アカウントです 6

7 . 自習書を試す環境について 必要な環境 この自習書で実習を行うために必要な環境は次のとおりです OS Windows Server 008 SP 以降または Windows Server 008 R SP 以降または Windows Server 0 または Windows Vista SP 以降または Windows 7 SP 以降または Windows 8 ソフトウェア SQL Server 0 この自習書内での画面やテキストは OS に Windows Server 008 R(x64) ソフトウェアに SQL Server 0 Enterprise エディション (x64) を利用して記述しています そのほか この自習書を試すには サンプルスクリプトをダウンロードして 次のページの事前作業を実行しておく必要があります 7

8 . 事前作業 (sampledb データベースの作成 ) 事前作業 この自習書を進めるには サンプルスクリプトをダウンロードしておく必要があります また Management Studio のクエリエディターを利用して サンプルスクリプト内にある CreateTables.txt を実行し sampledb データベースと 社員 テーブルを作成しておく必要があります ( 実行手順は 次のとおりです ). まずは Management Studio を起動するために [ スタート ] メニューの [ すべてのプログラム ] から [Microsoft SQL Server 0] を選択して [SQL Server Management Studio] をクリックします. 起動後 次のように [ サーバーへの接続 ] ダイアログが表示されたら [ サーバー名 ] へ SQL Server の名前を入力し [ 接続 ] ボタンをクリックします SQL Server の名前を入力 [ 接続 ] ボタンをクリック. 接続完了後 Management Studio が開いたら 次のようにツールバーの [ 新しいクエリ ] 8

9 ボタンをクリックして クエリエディターを開きます 新しいクエリ をクリック クエリエディターが表示される 4. 次に Windows エクスプローラーを起動して サンプルスクリプトをダウンロードしたフ ォルダーを展開し このフォルダー内の CreateTables.txt ファイルをダブルクリックし て開きます ファイルの内容をすべてコピーして クエリエディターへ貼り付けます サンプルスクリプト内の CreateTable.txt ファイルの内容をコピーして貼り付け 結果を確認 貼り付け後 ツールバーの [! 実行 ] ボタンをクリックしてクエリを実行します これにより sampledb という名前のデータベースが作成され その中へ 社員 テーブルが作成さ れます 実行後 社員 テーブルの 6 件のデータが表示されれば 実行が完了です 9

10 STEP. つのセキュリティチェック この STEP では 一般ユーザーがオブジェクトを操作するための つのセキュリ ティチェックについて説明します この STEP では 次のことを学習します セキュリティモード ( 認証モード ) SQL Server 認証を利用した接続 ( ログイン ) データベースへの接続 オブジェクト権限の付与 Windows 認証モードへの変更 0

11 . つのセキュリティチェック つのセキュリティチェック 前述したように SQL Server では 一般ユーザーがデータベース内のテーブルを操作 (SELECT /INSERT/UPDATE/DELETE ステートメントを実行 ) するには つのセキュリティチェックを通過する必要があります 第 のチェック : SQL Server への接続 ( ログイン認証 ) 最初のチェックを通過するには ログイン認証 をパスする必要があります SQL Server のログイン認証には 次の 種類があります Windows 認証 Windows 認証は Windows へログオンするときに使用する Windows ユーザー を使用して SQL Server へログインする方法で マイクロソフトの推奨する認証方法です SQL Server 認証 SQL Server 認証は SQL Server 内へ作成したログインアカウントを使用して SQL Server へログインする方法です UNIX や Mac などの Windows 以外の OS を利用するユーザーが SQL Server へ接続する際に利用できる認証方法です 既定では SQL Server 認証を利用することはできませんが 後述のセキュリティモードを 混合モード へ変更することで 利用できるようになります

12 . セキュリティモード ( 認証モード ) セキュリティモード セキュリティモードは 認証方法 (Windows 認証と SQL Server 認証 ) のどちらを利用できるようにするかを設定するための機能で SQL Server のインストール時に 次の画面で設定しています セキュリティモードには Windows 認証モード と 混合モード の 種類があり 既定で は Windows 認証モードが選択されています それぞれのモードの違いは 次のとおりです Windows 認証モード Windows 認証モードは Windows 認証でのログインのみが許可されるモードです 混合モード (SQL Server 認証と Windows 認証 ) 混合モードは Windows 認証と SQL Server 認証の両方が利用できるモードです Note: SQL Server 認証モード? 6 つ前のバージョンの SQL Server 6.5 のときには SQL Server 認証のみが許可されるモードとして 標準モード が用意され このモードが既定値でした その後 SQL Server 7.0 では セキュリティ上の理由から標準モードが廃止され SQL Server 000 以降のバージョンでは Windows 認証モードが既定値になりました SQL Server 6.5 標準モード デフォルト SQL Server 7.0 廃止 SQL Server 000 以降 混合モード 統合モード 混合モード Windows NT 認証モード デフォルト 混合モード Windows 認証モード デフォルト

13 Let's Try それでは 現在のセキュリティモードを確認してみましょう. セキュリティモードを確認するには Management Studio のオブジェクトエクスプローラーから 次のように SQL Server の名前を右クリックして [ プロパティ ] をクリックします. [ サーバーのプロパティ ] ダイアログが開いたら [ セキュリティ ] ページを開きます SQL Server のインストール時に選択したセキュリティモード ( デフォルトは Windows 認証モード ) が選択されていることを確認できます

14 セキュリティモードの変更 次に セキュリティモードを 混合モード へ変更してみましょう. 混合モードへ変更するには 次のように [SQL Server 認証モードと Windows 認証モード ] を選択して [OK] ボタンをクリックします これにより 次のダイアログが表示されます これは "SQL Server を再起動するまでは セキュリティモードの変更が有効にならない " という主旨のメッセージです メッセージを確認後 [OK] ボタンをクリックします. 次に SQL Server を再起動して セキュリティモードの変更を有効化します SQL Server を再起動するには 次のようにオブジェクトエクスプローラーで SQL Server の名前を右クリックして [ 再起動 ] をクリックします 4

15 . 次のように確認ダイアログが表示されたら [ はい ] ボタンをクリックします 再起動が始まると 次のダイアログが表示されます 再起動が完了すると ダイアログが自動的に閉じて セキュリティモードの変更 ( 混合モー ドへの変更 ) が有効になります これで Windows 認証でも SQL Server 認証でも SQL Server へログインできるようになります 5

16 . SQL Server 認証を使用したログイン SQL Server 認証を使用したログイン 次に SQL Server 認証 を使用して SQL Server へのログインを試してみましょう SQL Server 認証を使用するには SQL Server 上へログインアカウントを作成しておく必要があります Let's Try それでは これを試してみましょう SQL Server 認証を試すには セキュリティモードが 混合モード へ設定されている必要があるので Windows 認証モード を使用している場合は 混合モードへ変更してから 以下の手順を試してください. SQL Server 上へログインアカウントを作成するには 次のようにオブジェクトエクスプローラーの [ セキュリティ ] フォルダーを展開して [ ログイン ] フォルダーを右クリックし [ 新しいログイン ] をクリックします. [ ログイン - 新規作成 ] ダイアログが表示されたら [SQL Server 認証 ] をチェックして [ ログイン名 ] へ sqluser0 など任意の名前を入力します 4 5 6

17 [ パスワード ] と [ パスワードの確認入力 ] へは P@ssword など任意のパスワードを入 力して [ ユーザーは次回ログイン時にパスワードを変更する ] のチェックを外して [OK] ボタンをクリックします Note: [ ユーザーは次回ログイン時にパスワードを変更する ] オプション [ ユーザーは次回ログイン時にパスワードを変更する ] をチェックしている場合は 最初のログイン時に次のダイ アログが表示されて パスワードの変更が促されるようになります Note: [ パスワードポリシーを適用する ] と [ パスワードの期限を適用する ] [ パスワードポリシーを適用する ] と [ パスワードの期限を適用する ] をチェックしている場合は Windows のローカルセキュリティポリシーまたはグループポリシーで設定されたアカウントポリシー ( アカウントのロックアウトや パスワードの長さ パスワードの有効期間など ) を適用させて セキュリティを強化することができます 詳しくは Step で説明します. 次に オブジェクトエクスプローラーで [ ログイン ] フォルダーを展開して 作成したログインアカウントが追加されていることを確認します ログイン フォルダーを展開 作成したログインアカウントが追加されていることを確認 SQL Server 認証でのログイン 続いて 作成したログインアカウント sqluser0 を使用して SQL Server へログインしてみましょう. まずは 次のようにツールバーの [ データベースエンジンクエリ ] ボタンをクリックします 7

18 [ データベースエンジンへの接続 ] ダイアログが表示されたら [ 認証 ] で SQL Server 認証 を選択します [ ログイン ] へ sqluser0 [ パスワード ] へ P@ssword と入力して [ 接続 ] ボタンをクリックします. これにより クエリエディターが起動して 次のように 画面右下へ sqluser0 と表示され sqluser0 ログインアカウントで SQL Server への接続が成功したことを確認できます クエリエディターが起動 オブジェクトエクスプローラーは Administrator で接続しているので 管理操作が実行可能 こちらは sqluser0 で接続したクエリエディター sqluser0 と表示される Note: ログイン失敗時のエラー ログイン時に間違ったパスワードを入力したり 登録されていないログインアカウントを指定した場合は 次の エラーが発生します 8

19 データベースへの接続 次に sqluser0 ログインアカウントがデータベースへ接続できるかどうかを確認してみましょう. 次のように USE ステートメントを入力して sampledb データベースへ接続してみます USE sampledb 結果は [ メッセージ ] タブへ データベース 'sampledb' にアクセスできません と表示されて sampledb への接続が失敗したことを確認できます SQL Server へログインできたからといって データベースへ接続できるわけではありません データベースごとに接続のチェック ( 第 のチェック ) が行われています ログインアカウントがデータベースへ接続できるようにするには そのアカウントがデータ ベースユーザーとして登録されている必要があります 9

20 .4 データベースユーザーの登録 ( ログインとのマッピング ) データベースユーザーの登録 ログインアカウントをデータベースユーザーとして登録するには ログインアカウントのプロ パティ画面から行います Let's Try それでは これを試してみましょう. まずは [ ログイン ] フォルダーの sqluser0 ログインアカウントをダブルクリック ( または右クリックして [ プロパティ ] をクリック ) して [ ログインのプロパティ ] ダイアログを開き [ ユーザーマッピング ] ページを開きます sampledb をチェック ダブルクリック 4 このページでは データベースユーザーとして登録したいデータベースをチェックします ここでは sampledb をチェックして [OK] ボタンをクリックします これで sqluser0 ログインアカウントにマッピングされた ( 対応した ) データベースユーザーを sampledb データベース内へ登録 ( 作成 ) することができ データベースへ接続できるようになります. 登録されたデータベースユーザーを確認するには 次のように [ データベース ] フォルダーの [sampledb] データベースを展開して [ セキュリティ ] フォルダーの [ ユーザー ] フォルダーを参照します 0

21 データベース内の [ セキュリティ ] [ ユーザー ] フォルダーを展開 登録されたデータベースユーザー データベースへの接続 続いて sqluser0 ログインアカウントで sampledb データベースへ接続できることを確認してみましょう. 前の手順でエラーとなったクエリエディターで もう一度 USE ステートメントを実行してみましょう USE sampledb 4 データベースへの接続が成功 sqluser0 ログインアカウントで接続したクエリエディターであることを確認 今度は 結果ウィンドウへ コマンドは正常に完了しました と表示されて sampledb デ ータベースへの接続が成功したことを確認できます

22 テーブルデータの参照 次に SELECT ステートメントを実行して テーブルデータを参照できるかどうかを確認してみましょう. 次のように SELECT ステートメントを入力して 社員 テーブルのデータを参照してみます SELECT * FROM 社員 結果は SELECT 権限が ~ 拒否されました とエラーになり データの参照に失敗します データベースへ接続できたからといって データにアクセスできるわけではありません データへアクセスするには オブジェクト権限が付与されている必要があります ( 第 のチェック )

23 .5 オブジェクト権限の付与 オブジェクト権限の付与 データベースユーザーへオブジェクト権限を付与するには それぞれのオブジェクトのプロパテ ィ画面から行います Let's Try それでは これを試してみましょう ここでは 社員 テーブルに対して sqluser0 データベースユーザーが SELECT ステートメントを実行できるようにオブジェクト権限を付与してみましょう. オブジェクト権限を付与するには 次のように [ データベース ] フォルダーの [sampledb] データベースを展開して [ テーブル ] フォルダーの 社員 テーブルを右クリックし [ プロパティ ] をクリックします [ テーブルのプロパティ ] ダイアログが表示されたら [ 権限 ] ページをクリックして [ 検索 ] ボタンをクリックします. これにより [ ユーザーまたはロールの選択 ] ダイアログが表示されるので 次のように [ 参照 ] ボタンをクリックします

24 [ オブジェクトの参照 ] ダイアログが表示されたら 権限を与えたいデータベースユーザーを選択します ここでは sqluser0 をチェックして [OK] ボタンをクリックします. [ ユーザーまたはロールの選択 ] ダイアログへ戻ったら sqluser0 が追加されていることを確認して [OK] ボタンをクリックします 4. [ テーブルのプロパティ ] ダイアログへ戻ったら 次のように sqluser0 データベースユ ーザーを選択した状態で [ 明示的 ] タブをクリックすると 付与できるオブジェクト権限の一 覧が表示されます 4 オブジェクト権限の中から [ 選択 ] を選択して 許可 をチェックし [OK] ボタンをクリ ックします これで sqluser0 データベースユーザーに対して 社員 テーブルを 4

25 SELECT( 選択 ) できる権限を付与することができました SELECT ステートメントの実行 5. 続いて 前の手順でエラーとなったクエリエディターで もう一度 SELECT ステートメン トを実行してみましょう SELECT * FROM 社員 4 社員テーブルのデータを参照できるようになった sqluser0 ログインアカウントで接続したクエリエディターであることを確認 今度はエラーにならず 社員 テーブルのデータを参照できたことを確認できます このように一般ユーザーがテーブルデータを参照するには オブジェクト権限 ( 選択権限 ) が付与されている必要があります オブジェクト権限には データを追加 (INSERT ステートメントの実行 ) するための 挿入 権限や データを更新 (UPDATE ステートメントの実行 ) するための 更新 権限などもありますが 詳しくは Step 5 で説明します Note: アプリケーションへ通達されるエラー VB や C# などで ADO.NET を使用して作成したアプリケーションへ通達されるエラーは この Step で試したものと ほとんど同じです たとえば 次のようにアプリケーションを記述していたとします (Visual Basic 00 を利用 ) Imports System.Data.SqlClient : Using cn As New SqlConnection("Server=localhost;Database=sampleDB;User ID=sqlUser0;Password=P@ssword;") Using cmd As New SqlCommand("SELECT * FROM 社員 ", cn) Try cn.open() Using dr As SqlDataReader = cmd.executereader() While dr.read() Me.ListBox.Items.Add(dr(" 氏名 ").ToString()) End While End Using Catch ex As Exception MessageBox.Show(ex.Message) Finally 5

26 cn.close() End Try End Using End Using 成功時の結果 チェックを通過できなかった場合にアプリケーションへ通達されるエラーは 次のとおりです 第 のチェック ( ログイン失敗時 ) のエラー 第 のチェック ( データベース接続失敗時 ) のエラー 第 のチェック ( オブジェクト権限がない場合 ) のエラー 第 と第 のチェックは クエリエディターで確認したエラーと同じものが通達されていることを確認できます 第 のチェック ( データベース接続失敗時 ) のエラーは 少し紛らわしいかもしれません クエリエディターでは データベース 'sampledb' にアクセスできません と表示されましたが アプリケーションへは データベース 'sampledb' を開けません と通達されています これだけなら良いのですが その後に ログインに失敗しました ユーザー 'sqluser0' はログインできませんでした と続くので 第 のチェック ( ログイン失敗 ) にひっかかってしまったのと勘違いしやすくなっています エラーの発生時は データベース '~' を開けません というキーワードに注目するようにして 問題の切り分けをするとよいでしょう なお 後述のセキュリティモードを Windows 認証モード へ設定している場合は SQL Server 認証でのログインが拒否されるので アプリケーションへは 次のエラーが通達されます 6

27 .6 セキュリティモードを Windows 認証モードへ変更 Windows 認証モードの場合の動作 ここまでの手順は 混合モードを使用して SQL Server 認証でのログインを試してきましたが 次に セキュリティモードを Windows 認証モード へ変更した場合の動作を試してみましょう Let's Try. セキュリティモードを変更するには オブジェクトエクスプローラーから 次のように SQL Server の名前を右クリックして [ プロパティ ] をクリックします 4 [ サーバーのプロパティ ] ダイアログが表示されたら [ セキュリティ ] ページを開いて Windows 認証モード を選択し [OK] ボタンをクリックします. これにより 再起動を促すダイアログが表示されるので [OK] ボタンをクリックします. 次に SQL Server を再起動して セキュリティモードの変更を有効化します SQL Server を再起動するには 次のようにオブジェクトエクスプローラーで SQL Server の名前を右ク リックして [ 再起動 ] をクリックします 7

28 確認ダイアログが表示されたら [ はい ] ボタンをクリックします 再起動が完了すると セキュリティモードが Windows 認証モードへ変更されて Windows 認証でのみログインができるようになります (SQL Server 認証でのログインが拒否されるようになります ) SQL Server 認証でログインが拒否されることの確認 次に SQL Server 認証でログインが拒否されることを確認してみましょう. ツールバーの [ データベースエンジンクエリ ] ボタンをクリックして sqluser0 でログインを試みます [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] で SQL Server 認証 を選択して [ ログイン ] へ sqluser0 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします 8

29 結果は 次のようにエラーが発生して ログインに失敗します このように セキュリティモードを Windows 認証モードへ変更した場合は SQL Server 認証を使用したログインを行うことはできません 次の Step では Windows 認証を使用したログインの方法や ログインアカウントへのパスワードポリシーの設定などを説明します 9

30 STEP. ログインアカウントと SQL Server レベルの権限 この STEP では Windows 認証を使用したログインと 管理者アカウント (sa な ど ) の扱い ログインアカウントに関する詳細設定 ( パスワードポリシーの設定 など ) 固定サーバーロール SQL Server レベルの権限などを説明します この STEP では 次のことを学習します Windows 認証を使用したログイン Windows グループのメンバーでのログイン 管理者アカウント (sa など ) SQL Server 認証ログインアカウントに対するパスワード管理アカウントのロックアウト固定サーバーロール SQL Server レベルの権限 SQL ステートメントでログインの作成 0

31 . Windows 認証を使用したログイン Windows 認証用ログインアカウントの作成 Windows 認証は Windows へログオンしたユーザーアカウントを使用して SQL Server へ接続する方法ですが Windows のユーザーであれば 誰でも SQL Server に接続できるというわけではありません Windows のユーザーに対してWindows 認証用のログインアカウントを作成することによって はじめてそのユーザーが SQL Server へ接続できるようになります Let's Try それでは これを試してみましょう. まずは Windows 上へユーザーアカウントを作成します ユーザーアカウントを作成するには [ スタート ] メニューの [ 管理ツール ] から [ コンピューターの管理 ] をクリックして コンピューターの管理ツールを起動ます. 次に [ ローカルユーザーとグループ ] を展開し [ ユーザー ] フォルダーを右クリックして [ 新しいユーザー ] をクリックします 4 5

32 [ 新しいユーザー ] ダイアログが表示されたら [ ユーザー名 ] へ winuser0 など任意のユーザー名を入力して [ パスワード ] へ Pass など任意のパスワードを入力し [ ユーザーは次回ログオン時にパスワードの変更が必要 ] のチェックを外して [ 作成 ] ボタンをクリックします. 続いて 人目のユーザーアカウントを作成します そのまま [ 新しいユーザー ] ダイアログで [ ユーザー名 ] へ winuser0 など任意のユーザー名を入力して [ パスワード ] へ Pass など任意のパスワードを入力し [ ユーザーは次回ログオン時にパスワードの変更が必要 ] のチェックを外して [ 作成 ] ボタンをクリックします 4 Note: 複雑なパスワードかどうかのチェック Windows のパスワードポリシーで 複雑さの要件を満たす必要があるパスワード を有効化している場合は 推測されやすいパスワードが使用されることを防ぐことができます ( この機能は ワークグループ環境の Windows Server 00/008/008 R の場合は デフォルトで無効に設定されています ) パスワードポリシーは [ スタート ] メニューの 管理ツール にある ローカルセキュリティポリシー ツールから設定することができます 次のように アカウントポリシー を展開して パスワードのポリシー にあるポリシーで 複雑さの要件を満たす必要があるパスワード を 有効 へ変更すれば 複雑なパスワードを有効化することができます ダブルクリック 4

33 設定を有効化した場合は パスワードは次の つの条件を満たさなければならなくなります. 以下の 4 種類のうち 種類以上の文字を含んでいること A~Z( 大文字の英字 ) a~z( 小文字の英字 ) 0~9( 数字 や!( 感嘆符 ) $( ドル記号 ) #( 番号記号 ) %( パーセント記号 ) などの記号. ユーザーアカウントの名前と同じパスワードには設定できない 4. 続いて 作成した Windows ユーザーアカウントに対して SQL Server 上へログインアカウントを作成します 次のように Management Studio のオブジェクトエクスプローラーで [ セキュリティ ] フォルダーを展開して [ ログイン ] フォルダーを右クリックし [ 新しいログイン ] をクリックします 5. [ ログイン - 新規作成 ] ダイアログが表示されたら [Windows 認証 ] を選択して [ 検索 ] ボタンをクリックします

34 6. [ ユーザーまたはグループの選択 ] ダイアログが表示されたら [ 選択するオブジェクト名を 入力してください ] へ作成した Windows ユーザーの名前 winuser0 を入力して [ 名 前の確認 ] ボタンをクリックします これにより ユーザー名が コンピューター名 \winuser0 へ変更されることを確認して [OK] ボタンをクリックします 7. [ ログイン - 新規作成 ] ダイアログへ戻ったら [OK] ボタンをクリックしてダイアログを 閉じます 8. 次に オブジェクトエクスプローラーで [ ログイン ] フォルダーを展開して 作成したログ インアカウントが追加されていることを確認します 4

35 作成したログインアカウントが表示される Windows 認証を使用して SQL Server へのログインを試す 次に Windows ユーザー winuser0 を使用して SQL Server へログインしてみましょう. まずは Administrator でログオンしている Windows をログオフします. 次に 作成した Windows ユーザー winuser0 ( パスワードは Pass) で Windows へログオンします. Windows へのログオンが完了したら [ スタート ] メニューの [ すべてのプログラム ] [Microsoft SQL Server 0] から [SQL Server Management Studio] をクリックして Management Studio を起動します [ サーバーへの接続 ] ダイアログが表示されたら [ 認証 ] で Windows 認証 を選択します Windows 認証の場合はグレーで表示される Windows 認証を選択した場合は Windows へログオンしているユーザーが SQL Server へのログインを試みるので [ ユーザー名 ] と [ パスワード ] がグレーで表示されて 入力で 5

36 きないようになっています [ ユーザー名 ] には 現在ログオンしている Windows ユーザーの名前が コンピューター名 \winuser0 と表示されていることも確認できます 確認後 [ 接続 ] ボタンをクリックします 4. SQL Server へのログインが成功すると 次のようにオブジェクトエクスプローラーが表示されます SQL Server へ接続しているログインアカウント名が表示 SQL Server の名前の隣へ 接続を確立したログインアカウントの名前が表示されることから winuser0 でのログインが成功したことを確認することができます 5. 次に sampledb データベースへ接続してみましょう ツールバーの [ 新しいクエリ ] ボタンをクリックして クエリエディターを起動し USE sampledb と記述して [! 実行 ] ボタンをクリックします 4 結果はエラーとなり sampledb データベースへの接続が失敗していることを確認できます Step で試したように SQL Server へログインできたからといって データベースに接続できるわけではありません データベースへ接続するには ログインアカウントがデータベースユーザーとして登録されている必要があります winuser0 ユーザーでログインを試す 次に もう つ作成した Windows ユーザー winuser0 を使用して SQL Server へのログインを試してみましょう このユーザーに対しては SQL Server 上へログインアカウントを作成していないところがポイントです 6

37 . まずは winuser0 ユーザーでログオンしている Windows をログオフします. 次に winuser0 ユーザー ( パスワードは Pass) で Windows へログオンします. Windows へのログオンが完了したら [ スタート ] メニューの [ すべてのプログラム ] [Microsoft SQL Server 0] から [SQL Server Management Studio] をクリックして Management Studio を起動します [ サーバーへの接続 ] ダイアログでは [ 認証 ] で Windows 認証 を選択して [ 接続 ] ボタンをクリックします すると 今後は 次のようにエラーメッセージが表示されることを確認できます このように Windows ユーザーとして登録しただけでは SQL Server へログインすることはできません Windows ユーザーへ対応したログインアカウントを SQL Server 上へ作成して初めてログインできるようになります メッセージを確認後 [OK] ボタンをクリックしてダイアログを閉じます 4. 最後に Windows をログオフして Administrator ユーザーで Windows へログオンしておきます 7

38 . Windows グループに対するログインアカウントの作成 Windows グループに対するログインアカウントの作成 SQL Server のログインアカウントは Windows のグループに対しても作成することができます Windows グループに対してログインアカウントを作成しておけば そのグループ内のすべての Windows ユーザーが SQL Server へログインできるようになるので 大変便利です Let's Try それでは これを試してみましょう ( この手順は Administrator として Windows へログオンした状態で行ってください ). まずは コンピューターの管理ツールを使用して Windows グループを作成します [ スタート ] メニューの [ 管理ツール ] から [ コンピューターの管理 ] をクリックして コンピューターの管理ツールを起動ます. 次に [ ローカルユーザーとグループ ] を展開し [ グループ ] フォルダーを右クリックして [ 新しいグループ ] をクリックします 5 4 winuser0;winuser0 と入力して 名前の確認 をクリック 6 8

39 [ 新しいグループ ] ダイアログが表示されたら [ グループ名 ] へ sqlg など任意のグループ名を入力して [ 追加 ] ボタンをクリックします これにより [ ユーザーの選択 ] ダイアログが表示されるので [ 選択するオブジェクト名を入力してください ] へ作成したユーザー名を winuser0;winuser0 とセミコロンで区切って入力し [ 名前の確認 ] ボタンをクリックします それぞれのユーザー名が コンピューター名 \winuser0x と表示されたら [OK] ボタンをクリックします. [ 新しいグループ ] ダイアログへ戻ったら [ 所属するメンバー ] へ winuser0 と winuser0 が追加されていることを確認して [ 作成 ] ボタンをクリックします 以上で Windows グループの作成が完了です 4. 続いて 作成した Windows グループに対して SQL Server 上へログインアカウントを作成します 次のように Management Studio のオブジェクトエクスプローラーから [ ログイン ] フォルダーを右クリックして [ 新しいログイン ] をクリックします 5. [ ログイン - 新規作成 ] ダイアログが表示されたら [Windows 認証 ] を選択して [ 検索 ] ボタンをクリックします 9

40 6. [ ユーザーまたはグループの選択 ] ダイアログが表示されたら [ オブジェクトの種類 ] で [ グ ループ ] をチェックして [OK] ボタンをクリックします 7. [ ユーザーまたはグループの選択 ] ダイアログへ戻ったら [ 選択するオブジェクト名を入力 してください ] へ作成した Windows グループ名 sqlg を入力して [ 名前の確認 ] ボタ ンをクリックします 40

41 これにより オブジェクト名が コンピューター名 \sqlg へ変更されることを確認して [OK] ボタンをクリックします 4 8. [ ログイン - 新規作成 ] ダイアログへ戻ったら [OK] ボタンをクリックしてダイアログを 閉じます Windows グループのユーザーで SQL Server へのログインを試す 次に sqlg グループ内のユーザー winuser0 を使用して SQL Server へのログインを試してみましょう このユーザーは 以前の Step でログインが失敗していたユーザーです. まずは Administrator でログオンしている Windows をログオフします. 次に winuser0 ユーザー ( パスワード Pass) で Windows へログオンします 4

42 . Windows へのログオンが完了したら [ スタート ] メニューから Management Studio を 起動します [ サーバーへの接続 ] ダイアログでは [ 認証 ] で Windows 認証 を選択し て [ 接続 ] ボタンをクリックします 4. 今度は ログインに成功して 次のようにオブジェクトエクスプローラーが表示されます SQL Server の名前の隣へユーザー名 ( コンピューター名 \winuser0) が表示されることから winuser0 ユーザーでの SQL Server へのログインが成功したことを確認できます このように Windows グループへ対してログインアカウントを作成しておけば そのグループのメンバーが全員ログインできるようになるので 大変便利です 4

43 . 管理者アカウント 管理者アカウント SQL Server 0 のインストールの完了後は 以下のログインアカウントが自動的に作成され ています SQL Server が内部的に使用するログインアカウント ローカルコンピュータ上のシステムアカウント 管理者アカウント (sa) 管理者アカウント ( インストール時に指定 ) このうち SQL Server に対してあらゆる操作が可能な管理者アカウントは 次の つです インストール時に管理者として追加したアカウント SQL Server のインストール時に管理者として追加したアカウント ( 画面は SERVER\Administrator) は管理者アカウントとして登録されています sa アカウント sa は System Administrator の略で SQL Server 認証用の管理者アカウントとして登録され 4

44 ています SQL Server のインストール時に セキュリティモードで Windows 認証モードを選 択している場合は 無効 に設定された状態で作成されます 無効なログインアカウント ログインアカウントは 無効 に設定して そのアカウントでログインできないように設定することもできます SQL Server のインストール時に Windows 認証モードを選択している場合は sa アカウントは 無効 に設定されて登録されています 無効に設定されたアカウントは 次のように表示されます 無効 に設定されているアカウントは 赤い矢印が表示される アカウントの 有効 と 無効 を切り替えるには ログインアカウントの [ プロパティ ] ダイアログから行うことができます ただし セキュリティモードが Windows 認証モードに設定されている場合は SQL Server 認証用のログインアカウントを 有効 に設定したとしても 利用することはできません (Step の最後で SqlUser0 ログインアカウントがログインに失敗することを確認しました ) これは sa アカウント ( 管理者アカウント ) であっても例外ではありません sa アカウントの有効化 それでは これを試してみましょう ( この手順は Administrator として Windows へログオンした状態で行う必要があります ). まず Management Studio を起動して [ ログイン ] フォルダーにある sa アカウントをダブルクリック ( または 右クリックして [ プロパティ ] をクリック ) します ダブルクリック. [ ログインのプロパティ ] ダイアログが表示されたら [ 状態 ] ページを開き [ ログイン ] を 有効 へ変更します 44

45 . 続いて [ 全般 ] ページを開いて [ パスワード ] と [ パスワードの確認入力 ] へ P@ssword など 任意のパスワードを入力して [OK] ボタンをクリックします これで sa アカウントを 有効 な状態へ変更することができます Note: sa のパスワードは必ず設定するように過去 (00 年 ) には sa のパスワードが設定されていない SQL Server を狙ったワームが問題となったことがありました ワームは 増殖機能を持った悪意のあるプログラムのことです このワームは SQL Server に対して sa パスワードなしでログインを試みて トロイの木馬の設置などを行っていました しかし このワームに侵入されたのは sa のパスワードを設定していなかったマシンです パスワードを適切に設定していれば 侵入を防ぐことができたのです したがって sa には必ずパスワードを設定し かつ次の Step で説明するパスワードの管理を行っておくことが非常に重要です 45

46 sa アカウントでのログイン 続いて 有効化した sa アカウントを使用して SQL Server へログインできるかどうかを試してみましょう. まずは 次のようにツールバーの [ データベースエンジンクエリ ] ボタンをクリックます [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] へ SQL Server 認証 を選択して [ ログイン ] へ sa [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします 結果は 次のようにエラーメッセージが表示されて ログインに失敗することを確認できます セキュリティモードが Windows 認証モード の場合は sa であってもログインすることはできません メッセージを確認後 [OK] ボタンをクリックしてダイアログを閉じ [ データベースエンジンへの接続 ] ダイアログで [ キャンセル ] ボタンをクリックします 混合モードへの変更 次に セキュリティモードを 混合モード へ変更した場合の動作を確認してみましょう. 混合モードへ変更するには オブジェクトエクスプローラーから 次のように SQL Server の名前を右クリックして [ プロパティ ] をクリックします 46

47 4 [ サーバーのプロパティ ] ダイアログが表示されたら [ セキュリティ ] ページを開いて SQL Server 認証モードと Windows 認証モード を選択して [OK] ボタンをクリックします. これにより 再起動を促すダイアログが表示されるので [OK] ボタンをクリックします. 続いて SQL Server を再起動して セキュリティモードの変更を有効化します SQL Server を再起動するには オブジェクトエクスプローラーで SQL Server の名前を右クリックして [ 再起動 ] をクリックします 確認ダイアログが表示されたら [ はい ] ボタンをクリックします 47

48 再起動が完了すると セキュリティモードが 混合モード へ変更されて Windows 認証 でも SQL Server 認証でもログインができるようになります sa アカウントでのログイン 混合モードへ変更後 sa アカウントを使用して ログインできかどうかを試してみましょう. まずは ツールバーの [ データベースエンジンクエリ ] ボタンをクリックます [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] へ SQL Server 認証 を選択して [ ログイン ] へ sa [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします. 今度は エラーにならず 次のようにクエリエディターが起動して 画面右下へ sa と表示され sa アカウントとして SQL Server へのログインが成功したことを確認できます クエリエディターが起動 sa で接続したクエリエディター sa と表示される. 次に sampledb データベースへ接続して 社員 テーブルのデータを参照できるかど うかを確認してみましょう 次のように入力します 48

49 USE sampledb SELECT * FROM 社員 結果ウィンドウへは 社員 テーブルのデータが表示されて 問題なくデータを参照できたことを確認できます このように SQL Server の管理者アカウントの場合は つのセキュリティチェックに関係なく SQL Server に対してあらゆる操作を実行することが可能です Note: Windows 認証モードを推奨 セキュリティモードは Windows 認証モードを利用することをお勧めします (Windows 認証モードは マイク ロソフトが推奨するセキュリティモードです ) 理由は Windows 認証モードのほうが 混合モードよりもセキ ュリティを向上させることができるためです 混合モード (SQL Server 認証 ) を利用した場合は VB や C# で ADO.NET を使用して 記述されたアプリケーションは 次のようにログインアカウントの名前とパスワードを アプリケーション内へ記述しなければなりません Using cn As New SqlConnection("Server=localhost;Database=sampleDB;User ID=sa;Password=P@ssword") cn.open() : End Using この文字列 ( 接続文字列と呼ばれます ) は 別ファイル (App.Config など ) へ保管して さらに暗号化すること も可能ですが ログインアカウントの名前とパスワードがネットワーク上に流れるリスクを考慮しなければなり ません ( ネットワーク通信を暗号化するなど ) これに対して Windows 認証での接続の場合は 次のようにアプリケーション内へパスワードを記述する必要は ありません Using cn As New SqlConnection("Server=localhost;Database=sampleDB;Integrated Security=SSPI;") また パスワードがネットワーク上を流れることもありません ( パスワード認証は Windows OS レベルでの認 証で行われるためです ) なお いずれのモードを利用する場合でも セキュリティ強化のためには 次の Step.4 で説明するパスワード 管理をしっかりと行っておくことが重要です 49

50 .4 パスワードの管理 ( セキュリティポリシー ) パスワードの管理 パスワード管理は 悪意のある攻撃からの不正アクセスへの対策 およびセキュリティ強化のために非常に重要です 管理者アカウントのパスワードが推測または盗難された場合は 管理者として SQL Server へ侵入されてしまい 機密情報が盗まれたり データが破壊されたりする可能性があるからです パスワードを推測されないようにするためには 最低限 次のことを実践しておくことが重要です パスワードを定期的に変更する パスワードの変更時は 前回とは異なるものへ設定する 大文字と小文字 数字 記号を交えて 推測し難く かつ長いパスワードにする パスワードを何回か間違えたら そのアカウントをロックアウトする ( 使用不能する ) これらのパスワード管理には Windows のパスワード管理機能の セキュリティポリシー (Acrive Directory ドメイン環境の場合は グループポリシー機能 ) を利用することができます セキュリティポリシーは [ スタート ] メニューの 管理ツール にある ローカルセキュリティポリシー ツールを使用して 設定することができます このツールでは アカウントポリシー 内の パスワードのポリシー でパスワードの有効期限や最小パスワードの長さ パスワードの履歴の記録による同じパスワードへの変更防止などを設定できます また アカウントロックアウトのポリシー では 何回パスワードを間違えるとアカウントをロ ックアウトするのかを設定できます 50

51 Let's Try それでは これを試してみましょう ここでは アカウントのロックアウトのポリシーを設定してみましょう. まずは [ スタート ] ニューの [ 管理ツール ] から [ ローカルセキュリティポリシー ] をクリックして ローカルセキュリティポリシー ツールを起動します. ツールが起動したら [ アカウントポリシー ] フォルダーを展開し [ アカウントロックアウトのポリシー ] をクリックして 右ペインに表示される一覧の中から [ アカウントのロックアウトのしきい値 ] をダブルクリックします ダブルクリック. [ アカウントのロックアウトのしきい値のプロパティ ] ダイアログが表示されたら [ アカウ ントのロックアウト ] へ と入力します これで ログオン時にパスワードを 回以上間違えた場合に そのアカウントをロックアウト ( 使用不能 ) にすることができます [OK] ボタンをクリックすると 次のように [ 提案された値は変更します ] ダイアログが表示されます 5

52 これは ロックアウトされている時間 ( ロックアウト期間 ) が 0 分間で 0 分後には ロ ックアウトがリセット ( 解除 ) されるということを示しています 確認後 [OK] ボタンをクリックしてダイアログを閉じます Note: ロックアウト期間を無制限にする ロックアウト期間とリセットは 別の値へ変更することもできます また ロックアウト期間を 0 分へ設定し た場合は 管理者が明示的にロックアウトを解除するまで ロックアウトさせておくことができます 5

53 .5 SQL Server 認証用アカウントへのポリシーの適用 SQL Server 認証用アカウントへのポリシーの適用 ローカルセキュリティポリシー ツールで設定したポリシー ( ロックアウトの設定など ) は SQL Server 認証用のログインアカウントに対しても適用させることができます ( これは SQL Server 005 から提供された機能です ) セキュリティポリシーを SQL Server 認証用アカウントへ適用するには ログインアカウントのプロパティ画面で パスワードポリシーを適用する および パスワードの期限を適用する にチェックします Let's Try それでは これを試してみましょう 前の Step で設定した アカウントのロックアウトのポリシー を SQL Server 認証用のアカウントへ適用してみましょう. まずは Management Studio のオブジェクトエクスプローラーから [ セキュリティ ] フォルダーの [ ログイン ] フォルダーを展開して sa アカウントをダブルクリックします パスワードの有効期限 ポリシーを適用したい場合は こちらもチェックしておく ダブルクリック [ ログインのプロパティ ] ダイアログが表示されたら [ パスワードポリシーを適用する ] が チェックされていることを確認して [OK] ボタンをクリックします. 次に ツールバーの [ データベースエンジンクエリ ] ボタンをクリックます 5

54 . [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] で SQL Server 認証 を選 択して [ ログイン ] へ sa [ パスワード ] へわざと間違ったパスワード (aaa など ) を入 力し [ 接続 ] ボタンをクリックします 間違ったパスワードを入力 ログイン失敗のエラーが表示されることを確認します [OK] ボタンをクリックして あと 回 わざと間違ったパスワードでログインを試みて 合計 回のログインが失敗した状態にします 4. 次に 正しいパスワード P@ssword を入力して [ 接続 ] ボタンをクリックし ログインを試みます 正しいパスワードを入力 しかし 結果は次のエラーが表示されて ログインが失敗します 54

55 メッセージより sa アカウントがロックアウトされていることを確認できます ロックアウトされたアカウントは 0 分間ログインできなくなります ( ログインできない時間はポリシーで変更できます ) このように SQL Server 認証用のログインアカウントに対しても [ パスワードポリシーを適用する ] をチェックしておくことで Windows のパスワードポリシーを適用させることができるので セキュリティを強化することができます ロックアウトを解除するには ログインアカウントがロックアウトされた状態を 管理者が解除したい場合は 次のように操作します. オブジェクトエクスプローラーでロックアウトされたログインアカウントをダブルクリックして [ ログインのプロパティ ] ダイアログを表示します ダブルクリック [ 全般 ] ページでは 任意のパスワードを新しく設定します ( ロックアウトを解除するには 新しいパスワードを再設定する必要があります ) 次に [ 状態 ] ページを開いて [SQL Server 認証 ] の [ ログインをロックアウトする ] のチェックをオフにし [OK] ボタンをクリックします これで ロックアウトを解除することができます 55

56 .6 固定サーバーロール 固定サーバーロール 固定サーバーロールは SQL Server に対する管理権限 (SQL Server レベルの権限 ) をパッケージ化したもので ログインアカウントをグループ分けする目的で利用することができます 固定サーバーロールは 次の表のように 全部で 8 種類あり この中で重要になるのは sysadmin ロールです このロールは SQL Server に対してあらゆる管理操作が可能になる管理者用のグループです サーバーロール sysadmin serveradmin setupadmin securityadmin processadmin dbcreator diskadmin bulkadmin 操作権限 SQL Server に対してすべての操作を行うことができる SQL Server の環境設定オプションを設定でき SQL Server をシャットダウンできる リンクサーバーと起動プロシージャを管理できる ログインアカウントの作成と削除を行える また CREATE DATABASE 権限を与えることもできる SQL Server 内で実行できるプロセスを管理できる データベースの作成と変更ができる ディスクファイルを管理できる BULIK INSERT ステートメントを実行できる sysadmin ロールへ割り当てられたログインアカウントを確認するには 次のようにオブジェク トエクスプローラーの [ サーバーロール ] フォルダーで sysadmin ロールをダブルクリッ クします sysadmin ロールをダブルクリック sysadmin ロールへ含まれるログインアカウント sa とインストール時に管理者として追加したアカウントなどが含まれる 56

57 デフォルトでは インストール時に管理者として追加したアカウントと sa アカウントなどがこのロールへ割り当てられているので これらのアカウントは 管理者アカウントとして SQL Server に対してあらゆる操作が実行可能です そのほかのロールは 社内に複数のデータベース管理者がいて 管理作業を分担したい場合などに利用します 例えば ログインアカウントの作成だけを任せたい管理者がいる場合には securityadmin ロールへ追加するといった使い方ができます Let's Try それでは 固定サーバーロールを試してみましょう ここでは 新しくログインアカウントを作成して そのアカウントを sysadmin ロールへ割り当ててみましょう. まずは 新しいログインアカウントを作成します 次のようにオブジェクトエクスプローラーの [ ログイン ] フォルダーを右クリックして [ 新しいログイン ] をクリックします [ ログイン - 新規作成 ] ダイアログが表示されたら [SQL Server 認証 ] をチェックして [ ログイン名 ] へ sqluser0 など任意の名前を入力します [ パスワード ] と [ パスワードの確認入力 ] へは P@ssword など任意のパスワードをして [ ユーザーは次回ログイン時にパスワードを変更する ] のチェックを外して [OK] ボタンをクリックします. 次に 作成した sqluser0 ログインアカウントを sysadmin ロールへ割り当てるために [ サーバーロール ] ページを開きます 57

58 サーバーロールの一覧から sysadmin ロールをチェックして [OK] ボタンをクリックします これにより sqluser0 ログインアカウントを sysadmin ロールへ割り当てて SQL Server の管理者として振る舞わせることができます. 次に ツールバーの [ データベースエンジンクエリ ] ボタンをクリックして sqluser0 ログインアカウントで SQL Server へログインしてみましょう [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] で SQL Server 認証 を選 択して [ ログイン ] へ sqluser0 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします 58

59 4. 続いて クエリエディターで sampledb データベースの 社員 テーブルを参照して みましょう USE sampledb SELECT * FROM 社員 4 社員テーブルのデータを参照できることを確認 sqluser0 ログインアカウントで接続したクエリエディターであることを確認 社員 テーブルのデータを参照できたことを確認できます このように sysadmin ロールのメンバーは つのセキュリティチェックに関係なく データベースへの接続や テーブルデータの参照が可能です ( 管理者として SQL Server に対してあらゆる操作が可能です ) 59

60 .7 SQL Server レベルの権限 SQL Server レベルの権限 固定サーバーロールは SQL Server レベルの権限をまとめてグループ化したものですが SQL Server レベルの権限は つ つを特定のログインアカウントに対して付与することも可能です SQL Server レベルの権限には データベースの作成 や シャットダウン ( サーバーの停止 ) ログインの変更 など 全部で約 0 種類あります SQL Server レベルの権限を設定するには オブジェクトエクスプローラーで SQL Server の名前を右クリックして [ プロパティ ] をクリックし [ サーバーのプロパティ ] ダイアログを表示して [ 権限 ] ページを開きます 権限を設定したいログインアカウントをクリックして選択 設定できる SQL Server レベルの権限の一覧 ( 約 0 種類 ) 許可 をチェックすると 権限を付与できる [ ログインまたはロール ] の一覧からログインアカウントを選択して 与えたい権限の [ 許可 ] をチェックすれば 権限を付与することができます 現在付与されている権限の確認 ログインアカウントに対して 現在付与されている権限を確認したい場合は 同じダイアログで 次のように [ 有効 ] タブをクリックします 60

61 選択したログインアカウントがもっている権限 作成後 何の権限もロールも与えていないログインアカウントは つの権限のみ与えられている 作成後 何の権限も付与していないログインアカウントは CONNECT SQL (SQL Server への接続 ) 権限と VIEW ANY DATABASE ( データベースの表示 ) 権限のみを持っています 固定サーバーロールは これらの権限をパッケージ化したものなので sqluser0 ログインアカウントのように sysadmin ロールへ割り当てたアカウントの有効な権限を確認すると 次のようにすべての権限 ( 約 0 種類 ) が付与されていることを確認することができます sysadmin ロールを割り当てたログインアカウント すべての SQL Server レベル権限をもっている 6

62 .8 SQL ステートメントでログインアカウントの作成 SQL ステートメントでログインアカウントの作成 ログインアカウントは CREATE LOGIN というステートメントを使用して作成することもで きます 構文は次の通りです -- SQL Server 認証用のログインアカウントを作成する場合 CREATE LOGIN ログイン名 WITH PASSWORD = ' パスワード ' [, オプション ] -- Windows 認証用のログインアカウントを作成する場合 CREATE LOGIN ログイン名 FROM WINDOWS Note: SQL Server 000 での sp_addlogin と sp_grantlogin SQL Server 000 のときは SQL Server 認証用のログインアカウントの作成に sp_addlogin Windows 認証用のログインアカウントの作成に sp_grantlogin システムストアドプロシージャを使用していました SQL Server 0 でも このストアドプロシージャを使用することができますが 将来のバージョンでは なくなる可能性があることと パスワードポリシーなどの設定が行えないなど あくまでも下位互換性のために用意された機能なので CREATE LOGIN ステートメントを利用することをお勧めします スクリプト生成機能 Management Studio のオブジェクトエクスプローラーで GUI で作成したログインアカウン トは 次のように操作して スクリプト (CREATE LOGIN) を生成することも可能です CREATE LOGIN ステートメントが生成される パスワード部分は セキュリティのため ランダムに生成されている スクリプトを生成したいログインアカウントを右クリック 6

63 固定サーバーロールへの追加 : ALTER SERVER ROLE 固定サーバーロールへのメンバーの追加を SQL ステートメントから行いたい場合は 次のよう に ALTER SERVER ROLE ステートメントを利用します ALTER SERVER ROLE ロール名 ADD MEMBER ログイン名 SQL Server レベルの権限の付与 : GRANT SQL Server レベルの権限の付与を SQL ステートメントから行いたい場合は 次のように GRANT ステートメントを利用します GRANT 権限 TO ログイン名 付与した権限を取り消したい場合は 次のように REVOKE ステートメントを利用します REVOKE 権限 FROM ログイン名 ログインアカウントの一覧の取得 : server_principals ログインアカウントの一覧を SQL ステートメントで取得したい場合は 次のように sys.server_principals カタログビューを参照します SELECT * FROM sys.server_principals Note: プリンシパル (Principal) とは SQL Server では ログインアカウントのことを サーバープリンシパル とも言います ログイン失敗時のエラーメッセージや オンラインブック ( ヘルプ ) の表記でも プリンシパルという言葉が頻繁に出てきますので 覚えておくことをお勧めします 6

64 STEP 4. データベースユーザー この STEP では データベースユーザーや dbo/guest という特殊データベー スユーザー スクリプト生成機能などについて説明します この STEP では 次のことを学習します データベースユーザー 特殊データベースユーザー (dbo guest) スクリプト生成 64

65 4. データベースユーザー データベースユーザー Step で試したように データベースへ接続するには ログインアカウントがデータベースユーザーとして登録 ( マッピング ) されている必要があります Step では 次のようにログインアカウントのプロパティ画面からデータベースユーザーの作成を行いました sampledb をチェック ダブルクリック 4 作成されたデータベースユーザーは 次のように該当データベースを展開して [ セキュリティ ] フォルダーの [ ユーザー ] フォルダーから確認できました ユーザー フォルダー 65

66 データベースユーザーの作成 データベースユーザーは 次のように [ ユーザー ] フォルダーを右クリックして [ 新しいユーザ ー ] をクリックしても作成することができます Windows グループへ対応したデータベースユーザー Step. で試したように ログインアカウントは Windows のグループに対して作成することもできます (Step. では sqlg という名前の Windows グループを作成して それに対するログインアカウントを作成しました ) このログインアカウントに対して データベースへの接続の許可を与えるには やはりデータベースユーザーを作成します では これを試してみましょう. まず オブジェクトエクスプローラーで sampledb データベースの[ セキュリティ ] フォルダーを展開し [ ユーザー ] フォルダーを右クリックして [ 新しいユーザー ] をクリックします [ データベースユーザー - 新規 ] ダイアログが表示されたら [ ユーザー名 ] へ sqlg と 66

67 入力し [ ログイン名 ] の... ボタンをクリックします. [ ログインの選択 ] ダイアログが表示されたら [ 参照 ] ボタンをクリックします [ オブジェクトの参照 ] ダイアログでは コンピューター名 \sqlg ログインアカウントをチェックして [OK] ボタンをクリックします [ ログインの選択 ] ダイアログへ戻ったら [OK] ボタンをクリックして ダイアログを閉じます. [ データベースユーザー - 新規 ] ダイアログへ戻ったら ログイン名へ コンピューター名 \sqlg ログインアカウントが入力されていることを確認して [OK] ボタンをクリックします 以上で Windows グループの sqlg へ対応した sqlg という名前のデータベースユーザーを作成することができました これで sqlg グループのメンバー (winuser0 と winuser0) は 全員 sampledb データベースへ接続できるようになります Windows グループなのにユーザーと呼ぶことに違和感がある方もいるかもしれません データベースユーザーは データベースへのアクセス許可を与えられたログインアカウント と 覚えておくと分かりやすいかもしれません 67

68 4. 特殊データベースユーザー (dbo guest) 特殊データベースユーザー データベースの作成時には デフォルトでは 以下のデータベースユーザーが自動的に登録され ています dbo はデータベースに対してすべての権限を付与されている guest はデフォルトは無効 データベースユーザーが作成されていないログインアカウントが利用できる SQL Server のシステムオブジェクトの所有者として内部利用される dbo dbo は database owner の略で データベースに対するすべての権限が与えられた特殊なデータベースユーザーです データベースを作成したログインアカウントへ自動的にマッピング ( 対応 ) され このユーザーを削除することはできません なお sa アカウントなどの sysadmin 固定サーバーロールのメンバーは SQL Server に対するあらゆる操作が可能な管理者アカウントなので データベースに対しては dbo としてデータベースを操作することができます dbo であれば たとえオブジェクトに対する操作権限が与えられていない場合でも 自由にテーブルを操作することができます guest guest は ログインアカウントとの対応を持たない特殊なデータベースユーザーで デフォルトでは無効に設定されています このユーザーも削除することはできません guest ユーザーを有効にすると データベースユーザーとして登録されていないログインアカウントでも guest( 匿名ユーザー ) としてデータベースへアクセスできるようになります したがって セキュリティ強化を考えると [ 無効 ] のままにしておくことをお勧めします Let's Try それでは guest ユーザーを試してみましょう. まずは 新しくログインアカウントを作成します 次のようにオブジェクトエクスプローラーの [ ログイン ] フォルダーを右クリックして [ 新しいログイン ] をクリックします 68

69 4 5 6 [ ログイン - 新規作成 ] ダイアログでは [SQL Server 認証 ] をチェックして [ ログイン名 ] へ sqluser0 など任意の名前を入力します [ パスワード ] と [ パスワードの確認入力 ] へは P@ssword など任意のパスワードをして [ ユーザーは次回ログイン時にパスワードを変更する ] のチェックを外して [OK] ボタンをクリックします. 次に 作成した sqluser0 ログインアカウントで SQL Server へログインします 次のように ツールバーの [ データベースエンジンクエリ ] ボタンをクリックします [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] で SQL Server 認証 を選択して [ ログイン ] へ sqluser0 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします. 続いて クエリエディターで sampledb データベースへ接続してみます USE sampledb 69

70 4 接続失敗 sqluser0 ログインアカウントで接続したクエリエディターであることを確認 結果は エラーとなり データベースへの接続が失敗したことを確認できます 4. 次に guest ユーザーを有効化して sqluser0 ログインアカウントが guest として接続できるようにしてみましょう guest ユーザーを有効化するには 次のようにクエリエディターから GRANT ステートメントを実行して CONNECT ( 接続 ) 権限を付与します ( このステートメントは管理者アカウントで接続したクエリエディターから実行する必要があります ) GRANT CONNECT TO GUEST コマンドが正常に完了しました と表示されれば guest ユーザーの有効化が成功してい ます これにより guest ユーザーのアイコンが次のように変わります guest ユーザーが無効な場合 ( デフォルト ) guest ユーザーが有効な場合 5. 次に もう一度 sqluser0 ログインアカウントで sampledb データベースへの接続 70

71 を試してみます 次のように ツールバーの [ データベースエンジンクエリ ] ボタンをクリ ックします [ 認証 ] で SQL Server 認証 を選択して [ ログイン ] へ sqluser0 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします 6. 接続後 クエリエディターで sampledb データベースへ接続します USE sampledb 接続成功 今度は 接続が成功したことを確認できます (guest ユーザーとして接続しています ) このあと 社員 テーブルを参照するには 社員 テーブルのオブジェクト権限が guest ユーザーに対して設定されている必要があります このように guest ユーザーを有効化すると データベースユーザーとして登録されていないユーザーでも データベースへ接続できるようになります セキュリティ面では ゆるくなってしまいますので guest ユーザーは無効のまま利用することをお勧めします 有効化した guest ユーザーを無効化する場合は 次のように REVOKE ステートメントを実行します REVOKE CONNECT FROM GUEST 7

72 4. SQL ステートメントでデータベースユーザーの作成 SQL ステートメントでデータベースユーザーの作成 データベースユーザーは CREATE USER というステートメントを使用して作成することも できます 構文は次の通りです USE データベース名 CREATE USER ユーザー名 FOR LOGIN ログイン名 [ WITH オプション ] Note: SQL Server 000 での sp_grantdbaccess SQL Server 000 のときは データベースユーザーの作成に sp_grantdbaccess システムストアドプロシージャを使用していました SQL Server 0 でも このストアドプロシージャを使用することができますが 将来のバージョンでは なくなる可能性があることと 次の Step で説明するスキーマの設定が行えないなど あくまでも下位互換性のために用意された機能なので CREATE USER ステートメントを利用することをお勧めします データベースユーザーの一覧を取得 : sys.database_principals データベースユーザーを一覧を SQL ステートメントで取得したい場合は 次のように sys.database_principals カタログビューを参照します SELECT * FROM sys.database_principals Note: データベースユーザーも プリンシパル (Principal) ログインアカウントは サーバープリンシパル データベースユーザーは データベースプリンシパル と呼ばれています また どちらも単に プリンシパル と呼ばれることもあります 後述のデータベースロールもプリンシパルと呼ばれ プリンシパル という言葉は SQL Server へアクセスするユーザー ( アカウント ) の総称として利用されています Note: データベースを移動した場合に発生する 不明なデータベースユーザー 開発機でバックアップしたデータベースを 本番環境へ復元する場合や その逆の操作を行うと SQL Server 認証用のログインアカウントに関連付けられたデータベースユーザーが 不明なデータベースユーザー として扱われてしまいます ログインアカウントとデータベースユーザーの関連付け ( マッピング ) は ログインアカウントの SID( 内部的な 7

73 Security ID) を利用して行われていることが原因で発生しています SID は database_principals ビューで参照する ことができます ( ログインアカウントに割り当てられた SID は server_principals ビューで参照することができま す ) SQL Server 認証用のログインアカウントに割り当てられる SID は マシンが異なると 違うものが割り当てられる ので 開発機と本番環境 ( 移行元と移行先 ) では ログインアカウントの SID が異なることになります これによっ て 不明なデータベースユーザーが発生し このままでは 該当データベースユーザーが利用できない状態となります ( データベースへの接続が行えません ) sp_change_users_login 不明なデータベースユーザーが存在するかどうかを確認するには 次のように sp_change_users_login システム ストアドプロシージャを利用します USE データベース名 EXEC sp_change_users_login 'Report' 不明なデータベースユーザーが存在した場合に それを解消するには 次のように sp_change_users_login システ ムストアドプロシージャを実行します USE データベース名 EXEC sp_change_users_login 'Update_One', ' 不明なデータベースユーザー名 ', ' 新しいログインアカウント名 ' Update_One を指定して sp_change_users_login を実行すると 不明なデータベースユーザーの古い SID( 移行 元の SID) を新しいログインアカウントの SID( 移行先の SID) へ更新し 再マッピングをしてくれるようになりま す これで不明なデータベースユーザーが解消されて 移行元と同じようにデータベースユーザーを利用できるように なります 同じパスワード /SID のログインアカウントの作成 : sp_help_revlogin sp_change_users_login によるログインアカウントの再マッピングは データベースユーザーの数が多い場合には 大変な作業です これを解決するには 同じパスワードで かつ同じ SID のログインアカウントを作成することです これを行うには マイクロソフトのサポート技術情報 (KB: Knowledge Base) の文書番号 9899 で提供されている sp_help_revlogin というストアドプロシージャを使用します KB9899: SQL Server 005 のインスタンス間でログインおよびパスワードを転送する方法 この文書で提供されるスクリプトを丸ごとコピーし Management Studio のクエリエディターへ貼り付けて実行する と sp_help_revlogin ストアドプロシージャを作成することができます ( 文書は SQL Server 005 用ですが SQL Server 0 でも同様に利用することができます ) 作成後は 以下のように実行すれば 同一のパスワード /SID の ログインアカウントを作成するためのスクリプト (CREATE LOGIN ステートメント ) が生成されます USE master EXEC sp_help_revlogin 後は 生成された CREATE LOGIN ステートメントを移行先で実行すれば 同一のパスワード /SID のログインアカウントを作成できようになり 不明なデータベースユーザーの問題を解消することができます 7

74 包含データベース (Contained Database) による不明なデータベースユーザーの解消 SQL Server 0 からは ログインアカウントに依存しない ( マッピングが不要な ) データベースユーザーを作成す ることができる 包含データベース ( Contained Database) 機能も提供されました ( 独立したデータベースユーザー をデータベース内に含められることから Contained と名付けられています ) 包含データベースを利用するには sp_configure を利用して SQL Server の構成オプションで Contained Database Authentication( 包含データベース認証 ) を有効化 ( へ設定 ) しておく必要があります これは次のように実行します EXEC sp_configure 'contained database authentication', RECONFIGURE 包含データベースへ設定するには 次のようにデータベースのプロパティを開いて [ オプション ] ページから [ コンテ インメントの種類 ] で 部分 を選択します ログインアカウントに依存しないデータベースユーザーを作成するには 次のように新しいユーザーの作成ダイアログ で [ ユーザーの種類 ] で パスワードを持つユーザー を選択します これで データベースの中に包含されたデータベースユーザーの作成が完了です 74

75 SQL ステートメントを利用して 包含データベース内にデータベースユーザーを作成する場合には 次のように CREATE USER ステートメントを実行します USE sampledb go CREATE USER testuser WITH PASSWORD = 'P@ssword' WITH PASSWORD でパスワードを設定することで データベースの中に包含されたデータベースユーザーを作成する ことができます 作成したユーザー (testuser の場合 ) で SQL Server へ接続するには 次のように [ サーバーへの接続 ] ダイアログ で SQL Server 認証 を選択して [ ログイン ] と [ パスワード ] に作成したユーザーの名前とパスワードを入力し [ オプション ] ボタンをクリックします [ 接続プロパティ ] タブが表示されたら [ データベースへの接続 ] へ接続先となるデータベース名 ( 画面は sampledb) を入力して [ 接続 ] ボタンをクリックします これで作成したユーザーでデータベースへ接続することができます このように包含データベースを利用すれば データベースユーザーがデータベース内に包含されるので マッピングの切れたユーザーが発生することはありません 包含データベースは tempdb の照合順序に依存しない一時テーブルの作成ができることも大きなメリットです ( 照合順序については 本自習書シリーズの Transact-SQL 入門 編で説明 ) 従来のバージョンでは CREATE TABLE ステートメントを利用して作成した一時テーブルの照合順序は tempdb の照合順序を継承するため データベース移行時に 移行元と移行先で tempdb の照合順序が異なる場合に 照合順序の不一致が発生するという問題がありました この問題は 包含データベースを利用することで解決することができます 包含データベースは tempdb の照合順序に依存しない一時テーブルの作成ができるので 一時テーブルの照合順序は 包含データベースに設定された照合順序を継承することが可能です このように SQL Server 0 では 包含データベースが提供されたことによって データベースの移動 ( 開発機から本番機へデータベースを移動したい場合など ) が非常に簡単に行えるようになりました 75

76 STEP 5. オブジェクト権限と データベースロール この STEP では オブジェクト権限やデータベースロール スキーマなどについ て説明します この STEP では 次のことを学習します オブジェクト権限固定データベースロールオブジェクト権限の状態 (GRANT/DENY/REVOKE) ユーザー定義データベースロールデータベースレベルの権限スキーマビューを利用したセキュリティ強化スキーマが異なる場合のオブジェクト権限 76

77 5. オブジェクト権限 オブジェクト権限 Step で試したように データベース内のオブジェクトを操作するには データベースユーザーに対してオブジェクト権限が付与されている必要があります オブジェクト権限には テーブルに対する 選択 や 挿入 更新 削除 権限などがあり データベースユーザーは 選択権限が付与されていればそのテーブルに対して SELECT ステートメントを実行でき 更新権限が付与されていれば UPDATE ステートメントを実行できるようになります Step では オブジェクト権限の設定は オブジェクトのプロパティ画面から 次のように行いました

78 5. 固定データベースロール 固定データベースロール 固定データベースロール は データベースに対する操作権限をパッケージ化したもので データベースユーザーをグループ分けする目的で利用することができます 固定データベースロールには 全部で 0 種類 ( 以下 ) あり この中で重要なのは db_owner と public ロールです データベースロール db_accessadmin db_backupoperator db_datareader db_datawriter 操作権限データベースユーザーの追加と削除が行えるデータベースのバックアップを実行できるデータベース内のすべてのユーザーテーブルを参照できるデータベース内のすべてのユーザーテーブルでデータの追加 変更 削除が行える db_ddladmin オブジェクトの作成と削除が行える (DDL 操作の実行が可能 ) db_denydatareader データベース内のどのテーブルも参照できない ( 拒否 ) db_denydatawriter データベース内のどのテーブルも追加 変更 削除できない ( 拒否 ) db_owner db_securityadmin pulic データベースに対してのすべての権限を持つ このロールのメンバは dbo として振る舞うことができる 権限の設定とロールのメンバー管理を行える 特殊なデータベースロールで すべてのデータベースユーザーが含まれる public ロールからデータベースユーザーを削除することはできない db_owner ロールは データベースに対してすべての権限を持つグループです このロールのメンバーは dbo として振る舞うことができます public ロールは すべてのデータベースユーザーが含まれる特殊なデータベースロールです データベースにアクセスできるすべてのユーザーが自動的に含まれ メンバーを削除することはできません 固定データベースロールは 次のように各データベース内の [ セキュリティ ] [ ロール ] [ データベースロール ] フォルダーから参照 / 変更することができます 78

79 5. オブジェクト権限の状態 (GRANT/DENY/REVOKE) オブジェクト権限の状態 オブジェクト権限の設定時は 許可 (GRANT) と 拒否 (DENY) 取り消し (REVOKE) の 種類の状態があります 拒否 (DENY) 許可 (GRANT) どちらもチェックされていない場合は 取り消し (REVOKE) 許可がチェックされている場合は 許可 (GRANT) 拒否がチェックされている場合は 拒否 (DENY) どちらもチェックされていない場合は 取り消し (REVOKE) 状態です それぞれの 違いは 後ほど試します ロールへ権限が与えられている場合の動作 前述したように public ロールは すべてのデータベースユーザーが含まれる特殊なデータベースロールです したがって public ロールに対して オブジェクト権限が許可 (GRANT) されている場合は すべてのデータベースユーザーが許可されることになります たとえば 次のように public ロールへ選択 (SELECT) 権限が許可 (GRANT) され sqluser0 ユーザーには取り消し (REVOKE) が設定されている場合があるとします public sqluser0 ユーザー この場合は sqluser0 ユーザーは 選択権限を許可された状態と同じになります 拒否が優先 許可 ( GRANT) と拒否 ( DENY) の両方が設定されている場合は 拒否が優先されます たとえば 次のように public ロールへ選択 (SELECT) 権限が許可 (GRANT) され sqluser0 ユーザーには拒否 (DENY) が設定されている場合があるとします public sqluser0 ユーザー 79

80 この場合は 拒否が優先されて sqluser0 ユーザーは 選択権限が拒否された状態になります Let's Try それでは オブジェクト権限を試してみましょう. まずは 社員 テーブルを右クリックして [ プロパティ ] をクリックし [ テーブルのプロパティ ] ダイアログの [ 権限 ] ページを開きます 4 sqluser0 ユーザーに対して SELECT( 選択 ) 権限が 許可 (GRANT) されている 現在は sqluser0 ユーザーに対して 選択 権限が 許可 (GRANT) されていることを確認できます (Step で設定しました ). 続いて public ロールに対して オブジェクト権限を設定するために 次のように [ ユーザーまたはロール ] の [ 検索 ] ボタンをクリックします. [ ユーザーまたはロールの選択 ] ダイアログが表示されたら [ 参照 ] ボタンをクリックしま 80

81 す [ オブジェクトの参照 ] ダイアログでは public ロールをチェックして [OK] ボタンをクリックします [ ユーザーまたはロールの選択 ] ダイアログへ戻ったら [OK] ボタンをクリックしてダイアログを閉じます 4. [ テーブルのプロパティ ] ダイアログへ戻ったら public ロールを選択した状態で 更新 と 挿入 の 許可 をそれぞれチェックします 5. 続いて sqluser0 ユーザーへ権限を設定するために sqluser0 ユーザーを選択して 選択 の 許可 がチェックされていることを確認し 更新 の 拒否 をチェックしま す 8

82 sqluser0 ユーザーを選択 更新 の 拒否 をチェックする 選択 の 許可 がチェックされていることを確認 4 設定後 [OK] ボタンをクリックしてダイアログを閉じます 設定した権限をまとめると 次のようになります public sqluser0 ユーザー sqluser0 でのログイン 次に sqluser0 ログインアカウントでログインして オブジェクト権限の設定を確認してみましょう. まずは ツールバーの [ データベースエンジンクエリ ] ボタンをクリックします 8

83 [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] で SQL Server 認証 を選択して [ ログイン ] へ sqluser0 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします. クエリエディターが表示されたら sampledb データベースへ接続して 社員 テーブルを参照する SELECT ステートメントを実行してみましょう USE sampledb SELECT * FROM 社員 WHERE 社員番号 = この操作は成功し sqluser0 ユーザーが 社員テーブルに対して 選択 権限を持ってい ることを確認できます. 次に INSERT ステートメントを実行して データを追加してみましょう INSERT INTO 社員 VALUES (7, ' 山田花江 ', , '985/04/0') この操作も成功し sqluser0 ユーザーが 社員テーブルに対して 挿入 権限を持ってい ることを確認できます sqluser0 ユーザーには 直接 挿入権限は許可されていませんが 8

84 public ロールに対しては挿入権限が許可されているためです 4. 次に UPDATE ステートメントを実行してみましょう UPDATE 社員 SET 給与 = WHERE 社員番号 = この操作は失敗し sqluser0 ユーザーは 更新 権限が拒否されていることを確認できま す オブジェクト権限は 次のように設定していました public sqluser0 ユーザー public ロールに対しては 更新権限を 許可 していますが sqluser0 ユーザーに対し ては 拒否 しています 拒否 は 許可 よりも優先されるので sqluser0 ユーザー は UPDATE ステートメントに失敗しています Note: public ロールで拒否を設定した場合は すべてのユーザーが拒否される public ロールに対して権限を拒否した場合は すべてのデータベースユーザーが拒否されることに注意する必要 があります これは次のような状況です public sqluser0 ユーザー public ロールに対しては 選択権限を 拒否 して sqluser0 ユーザーに対しては 許可 していますが この場合 sqluser0 ユーザーは SELECT ステートメントを実行することができません ( 拒否が優先されます ) また その他のすべてのユーザーも SELECT ステートメントを実行することができなくなります public ロールに対して権限を設定する場合は すべてのユーザーへ影響があることに注意するようにしてください なお 管理者アカウント (sysdamin ロールのメンバーや dbo ユーザー ) の場合は例外で public ロールに対して 拒否 が設定されていても 関係なくオブジェクトを参照することができます 管理者アカウントは オブジェクト権限に関しても まったく関係なくすべてのオブジェクトを操作することが可能です 84

85 5.4 SQL ステートメントでオブジェクト権限の設定 SQL ステートメントでオブジェクト権限の設定 オブジェクト権限は GRANT ステートメントを使用して 許可 を与えることもできます 構文は次の通りです GRANT 権限 ON オブジェクト名 TO データベースユーザー名またはロール名 権限を 拒否 したい場合は DENY ステートメントを使用します DENY 権限 ON オブジェクト名 TO データベースユーザー名またはロール名 権限を 取り消し たい場合は REVOKE ステートメントを使用します REVOKE 権限 ON オブジェクト名 FROM データベースユーザー名またはロール名 スクリプト生成機能 Management Studio で設定したオブジェクト権限は スクリプト生成機能を利用して GRANT や DENY ステートメントを生成することも可能です これは 次のように権限の設定ダイアログで ツールバーの [ スクリプト ] ボタンをクリックします GUI で設定したオブジェクト権限をもとに GRANT や DENY REVOKE ステートメントを生成してくれる なお [ スクリプト ] ボタンによるスクリプトの生成は このダイアログを利用して権限を変更したものに対してのみ生成でき 既存の設定をもとに生成することはできません 既存の設定をもとにスクリプトを生成したい場合には データベースを右クリックして [ タスク ] メニューから [ スクリプトの生成 ] をクリックし スクリプト生成ウィザードを起動して [ 詳細設定 ] オプションで [ オブジェクトレベル権限のスクリプトを作成 ] を True へ設定します 85

86 5.5 ユーザー定義データベースロール ユーザー定義データベースロール ユーザー定義データベースロールは ユーザーが作成することができる データベースロール です データベースユーザーをグループ化して オブジェクト権限をまとめて設定したい場合などに利用すると便利です Let's Try それでは これを試してみましょう. ユーザー定義データベースロールを作成するには オブジェクトエクスプローラーで sampledb データベースの[ セキュリティ ] ロール フォルダーを展開し [ データベースロール ] フォルダーを右クリックして [ 新しいデータベースロール ] をクリックします ロール データベースロール フォルダを右クリックして 新しいデータベースロール をクリック [ データベースロール - 新規 ] ダイアログが表示されたら [ ロール名 ] へ sqlusers など任意のロール名を入力して [ 追加 ] ボタンをクリックします. [ データベースユーザーまたはロールの選択 ] ダイアログが表示されたら [ 参照 ] ボタンをクリックします 86

87 [ オブジェクトの参照 ] ダイアログでは sqluser0 ユーザーをチェックして [OK] ボタンをクリックします [ データベースユーザーまたはロールの選択 ] ダイアログへ戻ったら [OK] ボタンをクリックしてダイアログを閉じます. [ データベースロール - 新規 ] ダイアログへ戻ったら [ このロールのメンバー ] へ sqluser0 が追加されていることを確認して [OK] ボタンをクリックします これにより sqlusers データベースロールが作成されて sqluser0 ユーザーをメンバーとして登録することができました 4. 次に 作成した sqlusers データベースロールに対してロールに権限を設定します 社員 テーブルを右クリックして [ プロパティ ] をクリックし [ テーブルのプロパティ ] の [ 権限 ] ページを開きます 87

88 4 public ロールに対して 更新と挿入権限が 許可 (GRANT) されている 現在は public ロールに対して 更新 と 挿入 権限が 許可 (GRANT) されていることを確認できます (Step 5. で設定 ) 5. また 次のように sqluser0 ユーザーをクリックすると sqluser0 に対して 選択 権限が 許可 更新 権限が 拒否 (REVOKE) されていることを確認できます (Step 5. で設定 ) sqluser0 に対して 選択権限が 許可 (GRANT) 更新権限が 拒否 (REVOKE) されている 6. 続いて sqlusers ロールに対して オブジェクト権限を設定するために 次のように [ ユー 88

89 ザーまたはロール ] の [ 検索 ] ボタンをクリックします 7. [ ユーザーまたはロールの選択 ] ダイアログが表示されたら [ 参照 ] ボタンをクリックしま す [ オブジェクトの参照 ] ダイアログでは sqlusers ロールをチェックして [OK] ボタンをクリックします [ ユーザーまたはロールの選択 ] ダイアログへ戻ったら [OK] ボタンをクリックしてダイアログを閉じます 8. [ テーブルのプロパティ ] ダイアログへ戻ったら [ ユーザーまたはロール ] で sqlusers ロールを選択した状態で 選択 の 拒否 をチェックします 89

90 設定後 [OK] ボタンをクリックしてダイアログを閉じます 設定した権限をまとめると 次のようになります public ロール sqlusers ロール sqluser0 ユーザー sqluser0 でのログイン 次に sqluser0 ログインアカウントでログインして オブジェクト権限の設定を確認してみましょう. まずは ツールバーの [ データベースエンジンクエリ ] ボタンをクリックします [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] で SQL Server 認証 を選択して [ ログイン ] へ sqluser0 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします. クエリエディターが表示されたら sampledb データベースへ接続して 社員 テーブルを参照する SELECT ステートメントを実行してみましょう USE sampledb SELECT * FROM 社員 WHERE 社員番号 = 90

91 この操作はエラーとなり sqluser0 ユーザーが 社員テーブルに対して選択権限を拒否さ れていることを確認できます sqlusers ロールに対しては 拒否 していたので こちらが 優先されています 9

92 5.6 データベースレベルの権限 ( ステートメント権限 ) データベースレベルの権限 ここまでは オブジェクトの操作 (SELECT/INSERT/UPDATE/DELETE) に関する権限を説明しましたが テーブルなどのオブジェクトを作成 / 変更 / 削除 (CREATE/ALTER/DROP) する場合にも権限が必要になります これらの権限は データベースレベルの権限 または ステートメント権限 と呼ばれます データベースレベルの権限は 次のように [ データベースのプロパティ ] ダイアログの [ 権限 ] ページから設定することができます 60 種類以上の権限 権限には テーブルの作成 や ビューの作成 ユーザーの変更 など 60 種類以上あり 固定データベースロールにはこれらの権限がグループ化してあらかじめ割り当てられています 新しく作成したデータベースユーザーには デフォルトでは 接続 ( データベースへの接続 ) 権限のみが付与されています Let's Try それでは これを試してみましょう. まずは 新しくログインアカウントを作成します 次のようにオブジェクトエクスプローラーの [ ログイン ] フォルダーを右クリックして [ 新しいログイン ] をクリックします 9

93 4 5 6 [ ログイン - 新規作成 ] ダイアログでは [SQL Server 認証 ] をチェックして [ ログイン名 ] へ sqluser04 など任意の名前を入力します [ パスワード ] と [ パスワードの確認入力 ] へは P@ssword など任意のパスワードを入力して [ ユーザーは次回ログイン時にパスワードを変更する ] のチェックを外します. 続いて データベースユーザーとして登録するために [ ユーザーマッピング ] ページを開きます sampledb データベースをチェックして [OK] ボタンをクリックします. 次に 作成した sqluser04 ユーザーに対して テーブルの作成 の権限を与えます 次の 9

94 ように sampledb データベースを右クリックして [ プロパティ ] をクリックし [ データ ベースのプロパティ ] の [ 権限 ] ページを開きます 4 5 [ ユーザーまたはロール ] で sqluser04 を選択して テーブルの作成 の 許可 をチ ェックし [OK] ボタンをクリックします これで sqluser04 ユーザーへテーブルの作成権 限を付与することができました テーブルの作成 設定後 sqluser04 ユーザーがテーブルを作成できるかどうかを試してみましょう. まずは ツールバーの [ データベースエンジンクエリ ] ボタンをクリックします [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] で SQL Server 認証 を選択して [ ログイン ] へ sqluser04 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします 94

95 . クエリエディターが表示されたら 次のように入力して 社員 テーブルを作成します USE sampledb CREATE TABLE 社員 ( 社員番号 int, 氏名 char(50) ) しかし 結果は スキーマ名 "dbo" を使用する権限がない という主旨のエラーが発生します 実は テーブルを作成することは テーブルの作成 権限を付与しただけでは行うことができません テーブルを作成できるようにするには 次に説明する スキーマの所有者 へ設定するか スキーマに対する 変更 権限が付与されている必要があります Note: テーブルの作成権限が付与されていない場合のエラー テーブルの作成 権限を付与していない場合のエラーメッセージは 次のように表示されます 95

96 5.7 スキーマとは スキーマとは スキーマは オブジェクトをグループ化するための " 箱 "( コンテナー ) のようなものです スキーマを利用して データベース内のオブジェクトをグループ化 データベース スキーマ A テーブル スキーマ B テーブル ビュー ストアドプロシージャ ビュー ストアドプロシージャ データベース内のオブジェクト ( テーブルやビュー ストアドプロシージャ ) は スキーマ内へ格納されて 正しくは次のように記述して オブジェクトを識別します スキーマ名. オブジェクト名したがって 同じデータベース内のテーブルでも dbo. 社員 と AAA. 社員 では異なるテーブルになり 前者は dbo スキーマ 内へ格納された 社員 テーブル 後者は AAA スキーマ 内へ格納された 社員 テーブルになります 既定のスキーマ : デフォルトは dbo スキーマ SELECT * FROM 社員 のように スキーマ名を省略してオブジェクトへアクセスした場合は ユーザーへ設定された 既定のスキーマ が補われます デフォルトでは dbo スキーマ が設定されているので ( 内部的には ) dbo. 社員 と補われてアクセスしています また 既定のスキーマが BBB と設定されている場合は BBB. 社員 テーブルと補われ もしこのテーブルが存在しない場合は dbo スキーマが補われて dbo. 社員 テーブルへアクセスします 既定のスキーマは 次のようにデータベースユーザーのプロパティ画面の [ 全般 ] ページから確認 / 変更することができます 96

97 ユーザーをダブルクリックして プロパティを表示 Note: スキーマのメリットスキーマは SQL Server 005 から提供された機能で SQL Server 000 まではありませんでした SQL Server 000 までは オブジェクトへアクセスする際に dbo. 社員 や User. 社員 のように ユーザー名. オブジェクト名 と記述して ユーザー名でオブジェクトを識別 ( グループ化 ) していました しかし ユーザー名でオブジェクトを識別する場合は ユーザーが変更された場合に アプリケーションへも影響してしまいます たとえば SELECT * FROM User. 社員 と記述していたアプリケーションがある場合に ユーザー名を User へ変更したとすると アプリケーション内の User. 社員 と記述していた部分をすべて User. 社員 へ書き換えなければなりません また ユーザー User を削除する場合には そのユーザーが作成したオブジェクトをすべて削除しないと 削除することができませんでした こういったユーザーへ依存した状態を解消してくれる機能が スキーマ です スキーマ内へオブジェクトを格納しておけば ユーザーの変更に影響されることなく オブジェクトをグループ化できるようになります これがスキーマのメリットです スキーマの作成 新しくスキーマを作成するには データベース内の [ セキュリティ ] フォルダーを展開し [ スキ ーマ ] を右クリックして [ 新しいスキーマ ] をクリックします 97

98 Let's Try それでは スキーマを作成してみましょう. 次のように sampledb データベースの[ セキュリティ ] フォルダーを展開して [ スキーマ ] を右クリックし [ 新しいスキーマ ] をクリックします [ スキーマ - 新規作成 ] ダイアログが表示されたら [ スキーマ名 ] へ sqluser04 など任意のスキーマ名を入力し [ スキーマの所有者 ] へ前の Step で作成したデータベースユーザーの名前 sqluser04 を入力して [OK] ボタンをクリックします. 作成後 [ スキーマ ] フォルダーを展開すると sqluser04 スキーマが追加されていることを確認できます 既定のスキーマの設定. 次に 作成した sqluser04 スキーマ を sqluser04 ユーザー の既定のスキーマへ設定してみましょう 次のように sampledb データベース内の [ セキュリティ ] [ ユーザー ] フォルダーを展開して sqluser04 ユーザーをダブルクリックし データベースユー 98

99 ザーのプロパティ画面の [ 全般 ] ページを表示します 4 ダブルクリック [ 既定のスキーマ ] が dbo スキーマへ設定されていることを確認して [...] ボタンをク リックします 4. [ スキーマの選択 ] ダイアログが表示されたら [ 参照 ] ボタンをクリックします [ オブジェクトの参照 ] ダイアログでは [sqluser04] スキーマをチェックして [OK] ボタンをクリックします [ スキーマの選択 ] ダイアログへ戻ったら [OK] ボタンをクリックしてダイアログを閉じます 5. [ データベースユーザー ] ダイアログへ戻ったら [ 既定のスキーマ ] が sqluser04 スキーマへ変更されていることを確認して [OK] ボタンをクリックします 99

100 既定のスキーマを利用したテーブルの作成 次に 前の Step でエラーとなったテーブルの作成を試してみましょう. まずは ツールバーの [ データベースエンジンクエリ ] ボタンをクリックして sqluser04 ログインアカウントでログインします ( パスワードは P@ssword). クエリエディターで 次のように入力して 社員 テーブルを作成します ( 今回は 社員 ではなく 社員という名前にしてください ) USE sampledb CREATE TABLE 社員 ( 社員番号 int, 氏名 char(50) ) 00

101 今度は エラーにはならず テーブルの作成が成功したことを確認できます. 続いて sampledb データベースの [ テーブル ] フォルダーを右クリックして [ 最新の情報 に更新 ] をクリックします 最初からあった社員テーブル sqluser04 が作成した社員テーブル dbo. 社員 と sqluser04. 社員 の つのテーブルが表示されることを確認できます 後者が sqluser04 ユーザーが作成したテーブルです sqluser04 ユーザーには 既定のス キーマを sqluser04 スキーマ へ設定しているので sqluser04. が補われています 既定のスキーマを利用したテーブルへのアクセス. 次に 引き続き sqluser04 で起動したクエリエディターで 次のように入力します SELECT * FROM 社員 スキーマ名を指定しないで実行 sqluser04. 社員 テーブルへアクセス 結果は 社員番号 と 氏名 列だけの空のテーブルが表示されて sqluser04. 社員 テーブルが参照されたことを確認できます このように スキーマ名を省略した場合は 既定のスキーマが補われて テーブルへのアクセスが行われます. 次に SELECT ステートメントのテーブル名を dbo. 社員 へ変更して実行してみましょう SELECT * FROM dbo. 社員 0

102 これは SELECT( 選択 ) 権限がないというエラーになって失敗します sqluser04 ユーザーは dbo スキーマ内の社員テーブルに対しては 選択権限が付与されていないからです このように 同じ名前のテーブルであっても 格納されたスキーマが違う場合は 別のテーブルとみなされることに注意してください Note: オブジェクトの作成には スキーマの所有者 か スキーマに対する変更権限 が必要オブジェクトは スキーマ内へ作成するものなので オブジェクトを作成するには スキーマの所有者 ( スキーマの管理者 ) であるか スキーマに対する 変更 (Alter) 権限が必要になります sqluser04 ユーザーがテーブルを作成できたのは sqluser04 スキーマの所有者であるためです スキーマに対する変更権限を付与したい場合は 次のように操作します 4 5 スキーマをダブルクリック 6 Note: データベースロールをスキーマの所有者にすることも可能 スキーマの所有者には データベースロールを指定することもできます これを利用すると 複数のユーザーをスキーマの所有者へ設定したい場合に大変便利です 0

103 5.8 ビューを利用したセキュリティ強化 ビューを利用したセキュリティ強化 ビューを利用すると 次のようにテーブルに対するアクセスを拒否して ビューのみへアクセスさせることができます ( ビューについては 本自習書シリーズの SQL 基礎の基礎 で説明しています ) SELECT * FROM 社員 権限 User X を 拒否 社員 テーブル User X 権限 User X を 許可 社員 View ビュー SELECT * FROM 社員 view 給与と入社日を隠したビュー テーブルに対する 選択 権限を 拒否 (DENY) へ設定し ビューに対する 選択 権限を 許可 (GRANT) へ設定すれば ユーザーがテーブルへ直接アクセスすることを禁止して 見せても良い列だけを見せられるようになります ( この例では 給与と入社日を UserX から隠すことができています ) Let's Try それでは これを試してみましょう ここでは 社員テーブルをもとにしたビューを作成して 上の図と同じように権限を設定し ビューのみへアクセスできることを確認しましょう. まずは ツールバーの [ データベースエンジンクエリ ] ボタンをクリックして Windows 認証 を使用して Administrator( 管理者アカウント ) でクエリエディターを起動します 0

104 . クエリエディターが開いたら 次のように入力して 社員 テーブルから 社員番号 と 氏 名 のみを取得したビューを作成します USE sampledb go CREATE VIEW 社員 view AS SELECT 社員番号, 氏名 FROM 社員 ここでは スキーマ名を省略していますが 管理者アカウントでオブジェクトを作成した場合は dbo スキーマ 内へ格納されます. [ ビュー ] フォルダーに作成した dbo. 社員 view ビューが追加されていることを確認できます ( 表示されない場合は [ ビュー ] フォルダーを右クリックして [ 最新の情報に更新 ] をクリックします ) 4. 次に 新しくログインアカウントを作成します [ セキュリティ ] フォルダーの [ ログイン ] を右クリックして [ 新しいログイン ] をクリックします 04

105 4 5 6 [ ログイン - 新規作成 ] ダイアログでは [SQL Server 認証 ] をチェックして [ ログイン名 ] へ sqluser05 など任意の名前を入力します [ パスワード ] と [ パスワードの確認入力 ] へは P@ssword など任意のパスワードを入力して [ ユーザーは次回ログイン時にパスワードを変更する ] のチェックを外します 5. 続いて データベースユーザーとして登録するために [ ユーザーマッピング ] ページを開きます sampledb データベースをチェックして [OK] ボタンをクリックします 05

106 6. 次に 作成した sqluser05 ユーザーに対して dbo. 社員 view ビューへの 選択 権限 を 許可 します 次のように [ ビュー ] フォルダーの dbo. 社員 view ビューを右クリ ックして [ プロパティ ] をクリックします [ ビューのプロパティ ] ダイアログでは [ 権限 ] ページを開き [ 検索 ] ボタンをクリックします 7. [ ユーザーまたはロールの選択 ] ダイアログが表示されたら [ 参照 ] ボタンをクリックします [ オブジェクトの参照 ] ダイアログでは [sqluser05] ユーザーのチェックをして [OK] ボタンをクリックします [ ユーザーまたはロールの選択 ] ダイアログへ戻ったら [OK] ボタンをクリックしてダイアログを閉じます 8. [ ビューのプロパティ ] ダイアログへ戻ったら [ ユーザーまたはロール ] へ sqluser05 ユーザーが追加されていることを確認して 選択 権限の [ 許可 ] をチェックし [OK] ボタンをクリックします 06

107 これで sqluser05 ユーザーに対して 社員 view ビューへの選択権限を付与することができました 9. 次に sqluser05 ユーザーに対して 社員 テーブルへの選択権限を 拒否 します 選択権限を拒否するには [ テーブル ] フォルダーの dbo. 社員 テーブルを右クリックして [ プロパティ ] をクリックします [ テーブルのプロパティ ] ダイアログでは [ 権限 ] ページを開き [ 検索 ] ボタンをクリック します 07

108 0. [ ユーザーまたはロールの選択 ] ダイアログが表示されたら [ 参照 ] ボタンをクリックしま す [ オブジェクトの参照 ] ダイアログでは [sqluser05] ユーザーをチェックして [OK] ボタンをクリックします [ ユーザーまたはロールの選択 ] ダイアログへ戻ったら [OK] ボタンをクリックしてダイアログを閉じます. 次のように [ ユーザーまたはロール ] へ sqluser05 ユーザーが追加されていることを確認して 選択 権限の [ 拒否 ] をチェックして [OK] ボタンをクリックします これで sqluser05 ユーザーに対して 社員 テーブルへの選択権限を拒否することができました. 設定後 sqluser05 でログインして テーブルやビューを参照できるかどうかを試してみましょう ツールバーの [ データベースエンジンクエリ ] ボタンをクリックして sqluser05 ログインアカウントでログインします 08

109 [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] で SQL Server 認証 を選択して [ ログイン ] へ sqluser05 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします. クエリエディターが開いたら 次のように入力して 社員 テーブルを SELECT します USE sampledb SELECT * FROM 社員 結果は エラーとなり 選択権限が 拒否 されていることを確認できます 4. 次に 社員 view ビューに対して SELECT してみましょう SELECT * FROM 社員 view 今度は 成功して 社員番号 と 氏名 を取得できていることを確認できます このように ビューを利用すると テーブルの見せたい列のみを見せられるようになり 一般ユーザーに対しては 機密情報などを隠すことができるようになります ( セキュリティを強化できます ) 09

110 Note: ストアドプロシージャによるセキュリティの強化ストアドプロシージャについては 本自習書シリーズの 開発者のための Transact-SQL 応用 編で詳しく説明していますが ストアドプロシージャを利用しても ビューと同じようにセキュリティ強化を実現することができます ストアドプロシージャに対する 実行 (EXECUTE) 権限を付与して テーブルに対する権限を 拒否 (DENY) しておけば テーブル構造を隠ぺいしたデータアクセスを実現することができます このようにビューとストアドプロシージャを利用して セキュリティ強化を行うことは非常に重要です アプリケーション ( を利用するユーザーやアプリケーション内で利用している内部ユーザー ) からは テーブルへ直接アクセスできないようにし ビューやストアドプロシージャのみへアクセスできるようにするわけです こうすることで アプリケーションユーザーからの想定外の攻撃を防ぐことができます これは J-SOX 法 ( 日本版 SOX 法 ) や情報漏えい対策など コンプライアンス ( 法令遵守 ) を実現するうえで欠かせない実装になります コンプライアンスでは アカウント管理とオブジェクト権限による管理が非常に重要になります また コンプライアンスでは 特権ユーザー (sa や管理者アカウント ) による操作の監査 ( ログ記録 ) も非常に重要になります これらの機能については 本自習書シリーズの セキュリティ 編で詳しく説明していますので こちらもぜひご覧いただければと思います 0

111 5.9 スキーマが異なる場合のオブジェクト権限の注意事項 スキーマが異なる場合のオブジェクト権限 前の Step で試したビューによるセキュリティ強化は 同じスキーマ内でのみ有効であることに注 意する必要があります 異なるスキーマの場合は 次のような状況が発生してしまいます dbo スキーマ dbo. 社員 権限 User X を 拒否 dbo. 社員 テーブル User X 権限 User X を 許可 dbo. 社員 View ビュー dbo. 社員 view AAA スキーマ AAA. 社員 view 権限 User X を 許可 AAA. 社員 view ビュー dbo. 社員 テーブルをもとにしたビュー この図は AAA スキーマ内へ作成された AAA. 社員 view ビューが dbo スキーマ内の dbo. 社員 テーブルをもとに作成されている場合を表しています この場合は User X ユーザーは AAA. 社員 view ビューに対する選択(SELECT) 権限を付与されていたとしても dbo. 社員 テーブルに対する選択権限が拒否されている場合は ビューを参照することができません あくまでも ビューにのみ権限を付与すればよいのは 同じスキーマ内のオブジェクト同士である場合にだけ有効であることに注意してください Let's Try それでは これを試してみましょう 異なるスキーマを試すために 前の Step で作成した sqluser04 スキーマ ( 所有者は sqluser04 ユーザー ) を利用して ビューを作成してみましょう. まずは sqluser04 ユーザーへ ビューの作成 権限を付与するために sampledb データベースを右クリックして [ プロパティ ] をクリックします

112 4 5 [ データベースのプロパティ ] ダイアログでは [ 権限 ] ページを開き [ ユーザーまたはロール ] で sqluser04 ユーザーを選択して ビューの作成 権限の 許可 をチェックし [OK] ボタンをクリックします. 次に ツールバーの [ データベースエンジンクエリ ] ボタンをクリックして sqluser04 ログインアカウントでログインします [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] へ SQL Server 認証 を選択して [ ログイン ] へ sqluser04 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします. クエリエディターが開いたら dbo. 社員 テーブルをもとに sqluser04. 社員 view ビューを作成します USE sampledb go CREATE VIEW sqluser04. 社員 view AS SELECT 社員番号, 氏名, 入社日 FROM dbo. 社員

113 4. 次に オブジェクトエクスプローラーで [ ビュー ] フォルダーを右クリックして [ 最新の情 報に更新 ] をクリックします 作成した sqluser04. 社員 view ビューが追加されていることを確認できます 5. 次に sqluser05 ユーザーに対して sqluser04. 社員 view ビューへの選択権限を 許可 します 権限を許可するには 次のように [ ビュー ] フォルダーの sqluser04. 社員 view ビューを右クリックして [ プロパティ ] をクリックします [ ビューのプロパティ ] ダイアログでは [ 権限 ] ページを開き [ 検索 ] ボタンをクリックし ます

114 6. [ ユーザーまたはロールの選択 ] ダイアログが表示されたら [ 参照 ] ボタンをクリックしま す [ オブジェクトの参照 ] ダイアログでは [sqluser05] ユーザーをチェックして [OK] ボタンをクリックします [ ユーザーまたはロールの選択 ] ダイアログへ戻ったら [OK] ボタンをクリックしてダイアログを閉じます 7. [ ビューのプロパティ ] ダイアログへ戻ったら [ ユーザーまたはロール ] へ sqluser05 ユーザーが追加されていることを確認して 選択 権限の [ 許可 ] をチェックし [OK] ボタンをクリックします これで sqluser05 ユーザーに対して sqluser04. 社員 view ビューへの選択権限を 許可 (GRANT) することができました 8. 設定後 sqluser05 でログインして ビューを参照できるかどうかを試してみましょう ツールバーの [ データベースエンジンクエリ ] ボタンをクリックして sqluser05 ログインアカウントでログインします 4

115 [ データベースエンジンへの接続 ] ダイアログでは [ 認証 ] で SQL Server 認証 を選択して [ ログイン ] へ sqluser05 [ パスワード ] へ P@ssword と入力し [ 接続 ] ボタンをクリックします 9. クエリエディターが開いたら 次のように入力して sqluser04. 社員 view テーブルを SELECT します USE sampledb SELECT * FROM sqluser04. 社員 view 結果は エラーとなり ビューが参照できないことを確認できます これは 社員 テーブ ルと 社員 view ビューの格納されているスキーマが異なるためです ( 前者は dbo 後者 は sqluser04) スキーマが異なる場合は ビューへの権限許可だけではアクセスできない dbo スキーマ sqluser05 dbo. 社員 権限 User X を 拒否 dbo. 社員 テーブル sqluser04 スキーマ sqluser04. 社員 view 権限 User X を 許可 sqluser04. 社員 view ビュー dbo. 社員 テーブルを参照するビュー ユーザーがビューを参照できるようにするには ビュー内で参照しているテーブルに対する選択権限を許可 (GRANT) してあげなければなりません しかし これではセキュリティ強化 ( テーブルを隠す ) という目的は達成できませんので ビューを同じスキーマ内で作成しておくことが 基本的な利用方法になります 5

116 おわりに 最後までこの自習書の内容を試された皆さま いかがでしたでしょうか? ログイン認証とオブジェクト権限は SQL Server を利用するうえで 最も基本となる部分です 管理者にとっても アプリケーション開発者にとっても 非常に重要なものばかりですので しっかりとマスターしておくことをお勧めします このあとも さらに順序立てて SQL Server を学びたいという方のために 以下の自習書を用意していますので ぜひチャレンジしてみてください Transact-SQL 入門 SQL Server を操作するための独自の言語 Transact-SQL について 詳しく学ぶことができる自習書です データ型や照合順序 関数 変数など SQL Server を利用したアプリケーションを開発する上では必須となる機能を説明しています また SQL Server を運用管理する上でも Transact-SQL を学ぶことは重要なので 運用管理者にもお勧めの自習書となっています 開発者のための Transact-SQL 応用 Transact-SQL の応用編です 動的 SQL や ストアドプロシージャ トランザクション エラー処理といった 応用的な Transact-SQL の利用方法を学ぶことができます バックアップと復元 SQL Server のバックアップと復元機能について 詳しく学ぶことができる自習書です より 実践的な運用管理手法を学ぶことができます 監視ツールの基本操作 SQL Server でのパフォーマンス監視およびトラブルシューティングツールの利用方法を学ぶことができる自習書です パフォーマンスチューニングに従事される方や 日々の性能監視の仕方を学びたい方に役立つ内容となっています 6

117 執筆者プロフィール 有限会社エスキューエル クオリティ ( SQLQuality( エスキューエル クオリティ ) は 日本で唯一の SQL Server 専門の独立系コンサルティング会社です 過去のバージョンから最新バージョンまでの SQL Server を知りつくし 多数の実績と豊富な 経験を持つ OS や.NET にも詳しい SQL Server の専門家 ( キャリア 7 年以上 ) がすべての案件に対応 します 人気メニューの パフォーマンスチューニングサービス は 00% の成果を上げ 過去すべてのお客様環境で驚異的な性能向上を実現 チューニングスキルは世界トップレベルを自負 検索エンジンでは ( 英語情報を含めて ) ヒットしないノウハウを多数保持 ここ数年は BI/DWH システム構築支援のご依頼 が多い 主なコンサルティング実績 大手映像制作会社の BI システム構築支援 ( 会計 / 業務システムにおける予実管理 / 原価管理など ) 大手流通系の DWH/BI システム構築支援 (POS データ / 在庫データ分析 ) 大規模テラバイト級データウェアハウスの物理 論理設計支援および運用管理設計支援大手アミューズメント企業の BI システム構築支援 ( 人事システムにおける人材パフォーマンス管理 ) 外資系医療メーカーの Analysis Services による 販売分析 システムの構築支援 ( 売上 / 顧客データ分析 ) 9 TB データベースの物理 論理設計支援 ( パーティショニング対応など ) ハードウェアリプレイス時のハードウェア選定 ( 最適なサーバー ストレージの選定 ) 高可用性環境の構築 SQL Server 000( ビット ) から SQL Server 008(x64) への移行 / アップグレード支援 複数台の SQL Server の Hyper-V 仮想環境への移行支援 ( サーバー統合支援 ) 時間かかっていた日中バッチ実行時間を わずか 5 分へ短縮 (95.8% の性能向上 ) ピーク時の CPU 利用率 00% のシステムを わずか 0% にまで軽減し 大幅性能向上平均 85.ms かかっていた処理を わずか 9.ms へ短縮 (78.8% の性能向上 ) Java 環境 (Tomcat Seasar SDao) の SQL Server パフォーマンスチューニング etc コンサルティング時の作業例 ( パフォーマンスチューニングの場合 ) アプリケーションコード (VB C# Java ASP VBScript VBA) の解析 / 改修支援 ストアドプロシージャ / ユーザー定義関数 / トリガー (Transact-SQL) の解析 / 改修支援インデックスチューニング /SQL チューニング / ロック処理の見直し 現状のハードウェアで将来のアクセス増にどこまで耐えられるかを測定する高負荷テストの実施 IIS ログの解析 / アプリケーションログ (log4net/log4j) の解析 ボトルネックハードウェアの発見 / ボトルネック SQL の発見 / ボトルネックアプリケーションの発見 SQL Server の構成オプション / データベース設定の分析 / 使用状況 (CPU, メモリ, ディスク, Wait) 解析 定期メンテナンス支援 ( インデックスの再構築 / 断片化解消のタイミングや断片化の事前防止策など )etc 松本美穂 ( まつもと みほ ) 有限会社エスキューエル クオリティ代表取締役 Microsoft MVP for SQL Server(004 年 4 月 ~) 経産省認定データベーススペシャリスト /MCDBA/MCSD for.net/mcitp Database Administrator SQL Server の日本における最初のバージョンである SQL Server 4.a から SQL Server に携わり 現在 SQL Server を中心とするコンサルティングを行っている 得意分野はパフォーマンスチューニングと Reporting Services コンサルティング業務の傍ら 講演や執筆も行い マイクロソフト主催の最大イベント Tech Ed などでスピーカーとしても活躍中 SE や ITPro としての経験はもちろん 記名 / 無記名含めて多くの執筆実績も持ち 様々な角度から SQL Server に携わってきている 著書の SQL Server 000 でいってみよう と ASP.NET でいってみよう ( いずれも翔泳社刊 ) は トップセラー ( 前者は 8,500 部 後者は 6,500 部発行 ) 近刊に SQL Server 0 の教科書 ( ソシム刊 ) がある 松本崇博 ( まつもと たかひろ ) 有限会社エスキューエル クオリティ取締役 Microsoft MVP for SQL Server(004 年 4 月 ~) 経産省認定データベーススペシャリスト /MCDBA/MCSD for.net/mcitp Database Administrator SQL Server の BI システムとパフォーマンスチューニングを得意とするコンサルタント 過去には 約,000 本のストアドプロシージャのチューニングや テラバイト級データベースの論理 物理設計 運用管理設計 高可用性設計 BI DWH システム設計支援などを行う アプリケーション開発 (ASP/ASP.NET C# VB 6.0 Java Access VBA など ) やシステム管理者 (IT Pro) 経験もあり SQL Server だけでなく アプリケーションや OS Web サーバーを絡めた 総合的なコンサルティングが行えるのが強み Analysis Services と Excel による BI システムも得意とする マイクロソフト認定トレーナー時代の 998 年度には Microsoft CPLS トレーナーアワード (Trainer of the Year) を受賞 7