Comments
Description
Transcript
SQL Server 2008 徹底検証シリーズ
SQL Server 2008 徹底検証シリーズ 大規模データ ウェアハウス実践ガイド（環境構築 - 詳細編） アクセス制御の実装手順 Published．2008 年 11 月 20 日 © 2008 Microsoft Corporation. All rights reserved. 本書に記載した情報は、本書各項目に関する発行日現在の Microsoft の見解を表明するものです。Microsoft は絶えず変化する市 場へ対応しなければならないため、ここに記載した情報に対していかなる責務を負うものではなく、提示された情報の信憑性につい ては保証できません。 本評価ガイドは情報提供のみを目的としています。Microsoft は、明示的または暗示的を問わず、本書にいかなる保証も与えるもの ではありません。 すべての当該著作権法を遵守することはユーザーの責務です。Microsoft 書面による明確な許可なく、本書の如何なる部分について も、転載や検索システムへの格納または挿入を行うことは、どのような形式または手段（電子的、機械的、複写、レコーディング、 その他）および目的であっても禁じられています。これらは著作権保護された権利を制限するものではありません。 Microsoft は、本書の内容を保護する特許、特許出願書、商標、著作権、またはその他の知的財産権を保有する場合があります。 Microsoft から書面によるライセンス契約が明確に供給される場合を除いて、本書の提供はこれらの特許、商標、著作権、またはそ の他の知的財産へのライセンスを与えるものではありません。 Microsoft、Windows、MSDN、Visual Studio、SQL Server は、米国および（または）その他の国において、Microsoft Corporation の登録商標または商標です。 その他記載されている実際の社名および製品名は、各社の商標です。 Microsoft Corporation ・ One Microsoft Way ・ Redmond、 WA 98052-6399 ・ US 2 アクセス制御の実装手順 この資料では、CQI-DWH（データ ウェアハウス）シナリオにおける、アクセス制御の実装手順を詳し く説明します。リンクレポートによるアクセス制御、Reporting Services から RDBMS へ接続する際 のアクセス制御として「ストアド プロシージャ」の実装方法、Reporting Services から Analysis Services へアクセスする際の「ロール」の実装方法などを詳しく説明します。 説明内容は、以下のとおりです。 目次 1．アクセス制御の実装概要 ................................................................................................... 4 1.1 1.2 1.3 1.4 1.5 1.6 1.7 今回のアクセス制御要件 Active Directory の有無について 「AD あり」の場合のアクセス制御の実装概要 「AD なし」の場合のアクセス制御の実装概要 レポート マネージャについて レポート マネージャからの操作 リンクレポートの操作 4 5 6 14 25 26 29 2．アクセス制御の実装手順 ................................................................................................. 32 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 アクセス制御の実装手順 リンクレポートのパラメータの設定 ホームとアイテムに対するアクセス制御（ロールの割り当て） ロールの種類 アイテムに対するアクセス制御の初期設定 ホームに対するセキュリティ設定 フォルダに対するセキュリティ設定 リンクレポート（レポート）に対するセキュリティ設定 レポートの直 URL だけを公開する場合のセキュリティ設定 アクセス制御設定（URL 指定） 32 32 36 36 37 40 42 44 46 47 3．RDBMS へ接続する際のアクセス制御の実装方法 ........................................................ 48 3.1 3.2 3.3 3.4 RDBMS へ接続する際のストアド プロシージャによるアクセス Reporting Services へ実装する場合の考慮事項 Reporting Services でのユーザー名の取得方法 ストアド プロシージャの処理の概要 48 49 50 52 4．Analysis Services アクセス制御の実装方法 .................................................................. 58 4.1 ロールによるアクセス制御 4.2 Analysis Services アクセス制御の設定方法 3 58 59 1．アクセス制御の実装概要 第 1 章では、アクセス制御の実装手順の概要を説明します（具体的な実装手順は、第 2 章以降で説明し ます） 。 1.1 今回のアクセス制御要件 今回の検証では、下記のアクセス制御要件を満たす実装方法を確認しました。 すべてのデータへアクセス可能な「コンテンツ マネージャ」と各担当領域のデータのみ アクセス可能な「End User」がいる 役割 アクセス可否 コンテンツ マネージャ 全てのデータにアクセス可能 地域担当 各担当地域のデータのみアクセス可能 支店担当 各担当支店のデータのみアクセス可能 ・個人情報にはアクセス不可 ・MCustomer は、「氏名」 のみ参照可 ・1地域には、複数の支店が含まれている End User ユーザーからのアクセス パターン 今回の検証では、ユーザーからのアクセスは、次の 3 つのパターンとしています Web ブラウザから Reporting Services 経由で RDBMS へアクセス Web ブラウザから Reporting Services 経由で Analysis Services へアクセス Excel ピボット テーブルから Analysis Services へアクセス RDBMS Web ブラウザ SSRS SSAS * SSRS ： Reporting Services SSAS ： Analysis Services Excel RDBMS に対するユーザーのアクセスは、すべて Reporting Services を経由するように制限 し、Reporting Services から RDBMS へのアクセスは専用の「固定ユーザー」を使用します。 固定ユーザーの Windows アカウントにマップされたログインのみを作成することで、他のユ ーザーからのアクセスを制御することができます。 Reporting Services から Analysis Services へのアクセスにも、専用の「固定ユーザー」を使 用します。 4 1.2 Active Directory の有無について Active Directory（以下、AD）を認証基盤として利用する場合は、以下の 3 つの利用パターン が考えられます。 Active Directory （AD） が入っている場合 AD SSRS RDBMS (1) ユーザーや各サーバー群が、すべて同一ドメインに 所属している場合 本資料では、この場合（緑枠図） を 「AD あり」 と呼ぶ SSAS AD SSRS RDBMS (2) サーバー群は、同一 AD に入っているが、 ユーザーは、サーバー群の AD に入っていない場合 SSAS AD なし SSRS RDBMS (3) 全く AD がない場合 本資料では、この場合（赤枠図） を 「AD なし」 と呼ぶ SSAS 本資料では、ユーザーとサーバー群が同一 Active Directory ドメインに所属している場合を 「AD あり」 、全く AD がない場合を「AD なし」とし、この 2 つのケースでの検証を実施し て、アクセス制御の実装方法を確認しました。 以降では、AD あり／なしの場合のアクセス制御の実装方法の概要を説明します。 5 1.3 「AD あり」の場合のアクセス制御の実装概要 今回の環境で用意したユーザー 「AD あり」の環境で、用意したユーザーは、以下のとおりです。 ドメイン名： SQL 役割 アクセス可否 ユーザー名 コンテンツマネージャ すべてのデータにアクセス可能 sql¥Master_User1 地域名称 各担当地域のデータのみアクセス可能 神奈川 End User 地域担当 sql¥B_User1 千葉 sql¥D_User1 地域名称 支店名称 支店担当 横浜本部 sql¥B01_User1 川崎支社 sql¥B02_User1 千葉中央支社 sql¥D01_User1 各担当支店のデータのみアクセス可能 神奈川 （個人情報にはアクセス不可） 千葉 千葉エリア支社 sql¥D02_User1 ユーザーとグループ グループは、以下の構成にしています。 ASサーバーのローカルグループ ドメイン グループ ドメインユーザー ユーザー リスト 各ユーザーは、以下のグループへ所属させています。 Analysis Services サーバー上のローカル グループのメンバー ロー カル グループ メ ンバー ドメ イン グループ 備考 Master_Group DWH-R90003¥Master_User1 sql¥Master_Group コンテンツ マネージャ B_Group DWH-R90003¥B_User1 sql¥B_Group 神奈川 地域担当 D_Group DWH-R90003¥D_User1 sql¥D_Group 千葉 B01_Group DWH-R90003¥B01_User1 sql¥B01_Group 横浜本部 B02_Group DWH-R90003¥B02_User1 sql¥B02_Group 川崎支社 支店担当 D01_Group DWH-R90003¥D01_User1 sql¥D01_Group 千葉中央支社 D02_Group DWH-R90003¥D02_User1 sql¥D02_Group 千葉エリア支社 * DWH-R90003: Analysis Services サーバー名、SQL: ドメイン名 6 AD のグローバル グループのメンバー グローバル グループ メンバー 備考 Master_Group sql¥Master_User1 コンテンツ マネージャ B_Group sql¥B_User1 D_Group sql¥D_User1 千葉 B01_Group sql¥B01_User1 横浜本部 B02_Group sql¥B02_User1 D01_Group sql¥D01_User1 千葉中央支社 D02_Group sql¥D02_User1 千葉エリア支社 地域担当 支店担当 神奈川 川崎支社 ユーザー認証テーブル 今回の検証では、以下のユーザー認証テーブルを作成しています（ストアド プロシージャを 使用したアクセス制御で使用します）。 地域コード 支店コード 地域名称 支店名称 UserName B 神奈川 sql¥B_User1 D 千葉 sql¥D_User1 sql¥Master_User1 B B01 神奈川 横浜本部 sql¥B01_User1 B B02 神奈川 川崎支社 sql¥B02_User1 D D01 千葉 千葉中央支社 sql¥D01_User1 D D02 千葉 千葉エリア支社 sql¥D02_User1 各 UserName に 対 し て 、 空 白 の 部 分 は す べ て ア ク セ ス 可 能 で す 。 た と え ば 、 sql¥Master_User1 はすべてのデータへアクセス可能、sql¥B_User1 は「地域名称」が「神 奈川」であるすべての支店へアクセス可能です。 ストアド プロシージャの具体的な実装方法については、3 章で説明します。 固定ユーザーについて 固定ユーザーは、Reporting Services（SSRS）からデータソースへの接続ユーザーのことを指 します。 Reporting Services から RDBMS への接続には、Sql¥DBDataReader を使用 Reporting Services から Analysis Services への接続には、Sql¥ASDataReader を使用 SSRS Sql¥DBDataReader RDBMS Sql¥ASDataReader SSAS 7 Reporting Services から RDBMS への接続ユーザーの設定方法 Reporting Services から RDBMS への接続ユーザー「SQL¥DBDataReader」に対しては、 次のように設定します。 ログイン アカウントとして、SQL¥DBDataReader を登録 サーバー ロールは、Public へ設定 データベース ユーザー マッピングは、MSCQIDWH データベースに対して設定 既定のスキーマは、dbo SQL¥DBDataReader のログインを作成 サーバーロールはPublicに設定 MSCQIDWHのデータロールメンバーシ ップを Db_datareaderに設定 8 Reporting Services から Analysis Services への接続ユーザーの設定方法 Reporting Services から Analysis Services への接続ユーザー「SQL¥ASDataReader」に対 しては、次のように設定します。 1. すべてのデータを読み取れる「ロール」を作成 2. ロールのメンバシップにユーザー「Sql¥ASDataReader」を追加する 3. ディメンション データの設定で、上記ユーザーがすべてのディメンション データを読み 取れるように設定する（ロール作成後の既定の設定は、すべてのディメンション データ を読み取れる設定になっているため）。 ロールの設定方法については、4 章で詳しく説明します。 9 Reporting Services から RDBMS へのアクセス制御の実装方法 Reporting Services から RDBMS へのアクセス制御は、以下のように実装します。 Reporting Services の認証方式には「Windows 認証」を使用 Reporting Services から RDBMS への接続ユーザーには「固定ユーザー」を使用。前述 の「SQL¥DBDataReader」 RDBMS のアクセス制御 ログイン ユーザーを元にして、事前に用意したユーザー アクセス権管理テーブルを使 用し、ストアド プロシージャ内でデータを絞り、レポートで表示するデータセットを作 成する レポート内で「ログイン ユーザー名」を取得し、ストアド プロシージャ内でログイン ユ ーザー名を引数にして、ユーザー アクセス権管理テーブル内のテーブルを検索し、権限 設定を取得、ユーザーごとに行レベルのアクセス制御を行う SSRS RDBMS 固定ユーザー ユーザーアクセス権管理テーブル 具体的な実装方法は、2 章以降で詳しく説明します。 Reporting Services から Analysis Services へのアクセス制御の実装方法 Reporting Services から Analysis Services へのアクセス制御は、以下のように実装します。 Reporting Services の認証方式には「Windows 認証」を使用 Reporting Services から Analysis Services への接続ユーザーには 「固定ユーザーを使用」。 前述の「SQL¥ASDataReader」 Analysis Services のアクセス制御には「リンクレポート」を使用 SSRS SSAS 固定ユーザー リンク レポートを使用 具体的な実装方法は、2 章以降で説明します。 リンクレポートとは ユーザーは、リンクレポートへアクセスすると、リンクレポートに埋め込まれたパラメータで レポートを実行することができます。 10 Parameter支店 = A支店 [既定値] Parameter支店 = NULL A支店 売上 A支店で レポートを実行 レポート ユーザー 売上 レポート リンクレポート B支店で レポートを実行 B支店 売上 レポート レポート リンクレポート Parameter支店= B支店 リンクレポートを利用したアクセス制御の実装方法 リンクレポートを利用してアクセス制御を実装するには、レポートを、以下のような構造で配 置します。 A 支店のユーザーは、Folder_1 のリンクレポートにのみアクセス可能に設定 B 支店のユーザーは、Folder_2 のリンクレポートにのみアクセス可能に設定 ホーム フォルダ Folder_1 A 支店のユーザー リンクレポート A_Branch Parameter支店 = A支店 Folder_2 B 支店のユーザー B_Branch Parameter支店 = B支店 ユーザーは、必ず「Parameter 支店=自分の支店」でレポートを実行することになり、他店舗 のデータを参照できなくなります。 11 Excel から Analysis Services へのアクセス制御の実装方法 Excel から Analysis Services へのアクセス制御は、以下のように実装します。 Active Directory（AD）ユーザー アカウント／グループに対するロールの設定は、ロー カル グループまたはドメイン グループに対して設定を行うことができます。 Excel からレポート アクションを使用する場合 レポート アクションでは、参照している階層をパラメータとしてレポートに渡すだけなので、 前述の「Reporting Services から RDBMS へのアクセス制御」で記載した実装を行っていれ ばアクセス制御が可能です。 レポート アクションの利用例 ↓ 35 12 個人情報保護のためのアクセス制御の実装方法 レポートで表示するデータセットを作成するストアド プロシージャ内で下記のような処理を 実装します。 個人情報が見られるユーザー → 個人情報の入ったカラム情報をそのまま出す 個人情報が見られないユーザー → 個人情報の入ったカラム情報をすべてアスタリスクで返す SQL 文を実装 まとめ: 「AD あり」の場合のアクセス制御の実装方法 RDBMS へのアクセス制御は「ストアド プロシージャ」を使用 ストアド プロシージャ内で行レベル セキュリティと個人情報の制御を実装 Analysis Services へのアクセス制御は「リンクレポート」を使用 RS 認証 RS → RDBMS のユーザー RS→SSAS のユーザー RS→SSAS の アクセス制御 RS → RDBMS のアクセス制御 Excel → AS のアクセス制御 Windows認証 固定ユーザー 固定ユーザー リンクレポート使用 ログインユーザーを 元にストアドプロシー ジャを呼んでその中 で絞る アカウントに対す るロールの設定 SSRS RDBMS ユーザーアクセス権管理テーブル SSAS 13 1.4 「AD なし」の場合のアクセス制御の実装概要 今回の検証では、 「AD なし」の環境（Active Directory がないワークグループ環境）につい ても動作検証を実施しています。この場合の実装方法は、以下のとおりです。 使用したユーザー（Analysis Services サーバー） 「AD なし」の環境で使用したユーザー（Analysis Services サーバー上）は、以下のとおり です。 Analysis Services サーバー名： DWH-R90003 役割 アクセス可否 ユーザー名 コンテンツ マネージャ すべてのデータにアクセス可能 DWH-R90003¥Master_User1 地域名称 地域担当 各担当地域のデータのみアクセス可能 神奈川 DWH-R90003¥B_User1 千葉 DWH-R90003¥D_User1 地域名称 支店名称 End User 各担当支店のデータのみアクセス可能 （個人情報にはアクセス不可） 神奈川 千葉 横浜本部 DWH-R90003¥B01_User1 川崎支社 DWH-R90003¥B02_User1 千葉中央支社 DWH-R90003¥D01_User1 千葉エリア支社 DWH-R90003¥D02_User1 ユーザーとグループ（Analysis Services サーバー） グループは、以下の構成にしています。 SSAS サーバーのローカル グループ ローカル ユーザー ユーザー リスト（Analysis Services サーバー） 各ユーザーは、以下のグループへ所属させます。 Analysis Services サーバー上のローカル グループのメンバー ロー カル グループ メ ンバー 備考 Master_Group DWH-R90003¥Master_User1 コンテンツ マネージャ B_Group DWH-R90003¥B_User1 神奈川 地域担当 D_Group DWH-R90003¥D_User1 千葉 B01_Group DWH-R90003¥B01_User1 横浜本部 B02_Group DWH-R90003¥B02_User1 川崎支社 支店担当 D01_Group DWH-R90003¥D01_User1 千葉中央支社 D02_Group DWH-R90003¥D02_User1 千葉エリア支社 14 支店担当 使用したユーザー（Reporting Services サーバー） Reporting Services サーバー上に用意したユーザーは、以下のとおりです（Analysis Services サーバー上と同じ名前／パスワードのユーザーを作成します） 。 Reporting Services サーバー名： DWH-DL58501 役割 アクセス可否 ユーザー名 コンテンツ マネージャ すべてのデータにアクセス可能 DWH-DL58501¥Master_User1 地域名称 地域担当 各担当地域のデータのみアクセス可能 神奈川 DWH-DL58501¥B_User1 千葉 DWH-DL58501¥D_User1 地域名称 支店名称 End User 神奈川 各担当支店のデータのみアクセス可能 （個人情報にはアクセス不可） 千葉 支店担当 横浜本部 DWH-DL58501¥B01_User1 川崎支社 DWH-DL58501¥B02_User1 千葉中央支社 DWH-DL58501¥D01_User1 千葉エリア支社 DWH-DL58501¥D02_User1 ユーザーとグループ（Reporting Services サーバー） グループは、以下の構成にしています。 SSRS サーバーのローカル グループ ローカル ユーザー ユーザー リスト（Reporting Services サーバー） 各ユーザーは、以下のグループへ所属させます。 Reporting Services サーバー上のローカル グループのメンバー ロー カル グループ メ ンバー 備考 Master_Group DWH-DL58501¥Master_User1 コンテンツ マネージャ B_Group DWH-DL58501¥B_User1 神奈川 地域担当 D_Group DWH-DL58501¥D_User1 千葉 B01_Group DWH-DL58501¥B01_User1 横浜本部 B02_Group DWH-DL58501¥B02_User1 川崎支社 支店担当 D01_Group DWH-DL58501¥D01_User1 千葉中央支社 D02_Group DWH-DL58501¥D02_User1 千葉エリア支社 ユーザー認証テーブル 以下のユーザー認証テーブルを作成します（ストアド プロシージャを使用したアクセス制御 で使用します） 。 15 地域コード 支店コード 地域名称 支店名称 UserName B 神奈川 DWH-DL58501¥B_User1 D 千葉 DWH-DL58501¥D_User1 DWH-DL58501¥Master_User1 B B01 神奈川 横浜本部 DWH-DL58501¥B01_User1 B B02 神奈川 川崎支社 DWH-DL58501¥B02_User1 D D01 千葉 千葉中央支社 DWH-DL58501¥D01_User1 D D02 千葉 千葉エリア支社 DWH-DL58501¥D02_User1 各 UserName に対して、空白の部分はすべてアクセス可能です。たとえば、DWH-DL58501¥ Master_User1 はすべてのデータへアクセス可能、DWH-DL58501¥B_User1 は「地域名称」 が「神奈川」であるすべての支店へアクセス可能です。 具体的な実装方法については、3 章で詳しく説明します。 固定ユーザーについて 固定ユーザーは、Reporting Services（SSRS）からデータソースへの接続ユーザーのことを指 します。以下のように設定しています。 作成場所 ユ ー ザー 説明 DWH-58501¥DBDataReader Reporting Services から DB への接続に利用 パススルー認証で DB サーバーへ接続 DWH-58501¥ASDataReader Reporting Services から Analysis Services への接続に利用 パススルー認証で AS サーバーへ接続 RS サーバー上へ作成したユーザー DB サーバー上へ作成したユーザー DWH-R90001¥DBDataReader AS サーバー上へ作成したユーザー DWH-R90003¥ASDataReader SSRS DWH-58501¥DBDataReader RDBMS DWH-58501¥AS DataReader SSAS 16 Reporting Services から RDBMS への接続ユーザーの設定方法 Reporting Services から RDBMS への接続ユーザー「DWH-R90001¥DBDataReader」に対 しては、次のように設定します。 ログイン アカウントとして、DWH-R90001¥DBDataReader を登録 サーバー ロールは、以下を設定 DWH-R90001¥DBDataReader と SQL¥DBDataReader を Public へ設定 データベース ユーザー マッピングは、MSCQIDWH データベースに対して設定 既定のスキーマは、dbo DWH-R90001¥DBDataReader のログインを作成 サーバーロールはPublicに設定 MSCQIDWH のデータベース ロール メンバーシップを Db_datareader に設定 17 Reporting Services から Analysis Services への接続ユーザーの設定方法 SSRS から Analysis Services への接続ユーザー「DWH-R90003¥ASDataReader」に対して は、次のように設定します。 1. すべてのデータを読み取れる「ロール」を作成 2. ロールのメンバシップにユーザー「DWH-R90003¥ASDataReader」を追加する 3. ディメンション データの設定で、上記ユーザーがすべてのディメンション データを読み 取れるように設定する（ロール作成後の既定の設定は、すべてのディメンション データ を読み取れる設定になっているため）。 ロールの設定方法については、4 章で詳しく説明します。 18 Reporting Services から RDBMS へのアクセス制御の実装方法（AD なし） 「AD なし」の環境で、Reporting Services から RDBMS へのアクセス制御は、以下のよう に実装します。 Reporting Services の認証方式には「基本認証」を使用（設定は後述） Reporting Services から RDBMS への接続ユーザーには「固定ユーザー」を使用。 前述の「DWH-R90001¥DBDataReader」 RDBMS のアクセス制御 ログイン ユーザーを元にして、事前に用意したユーザー アクセス権管理テーブルを使 用し、ストアド プロシージャ内でデータを絞り、レポートで表示するデータセットを作 成する レポート内で「ログイン ユーザー名」を取得し、ストアド プロシージャ内でログイン ユ ーザー名を引数にして、ユーザー アクセス権管理テーブル内のテーブルを検索し、権限 設定を取得、ユーザーごとに行レベルのアクセス制御を行う No. RS 認証 RS から RDBMS への接続ユーザー RDBMS のデータに対するアクセス制御 1 基本認証 （RS にローカル ユーザーを登録） 固定ユーザー ログインユーザーを元にストアドプロシージャを 読んでその中で絞る SSRS RDBMS 固定ユーザー ユーザーアクセス権管理テーブル 具体的な実装方法は、2 章以降で詳しく説明します。 Reporting Services で基本認証を構成する方法 Reporting Services で基本認証を構成する方法は、次のとおりです。 1. テキスト エディタで SSRSReportServer.config を開きます。 2. <Authentication> を探します。 3. 下記のコードを <Authentication> の既存のエントリ上に貼り付けます <AuthenticationTypes> <RSWindowsBasic /> </AuthenticationTypes> 4. ファイルを保存します。 5. スケール アウト配置を構成した場合は、配置内の他のレポート サーバーに対して上記の 手順を繰り返します。 6. レポート サーバーを再起動して、現在開いているセッションを消去します。 詳細は、オンライン ブックの以下を参照してください 19 Reporting Services で基本認証を構成する方法 http://msdn.microsoft.com/ja-jp/library/cc281309.aspx Reporting Services から Analysis Services へのアクセス制御の実装方法 Reporting Services から Analysis Services へのアクセス制御は、以下のように実装します。 Reporting Services の認証方式には「基本認証」を使用 Reporting Services から Analysis Services への接続ユーザーには 「固定ユーザーを使用」。 前述の「DWH-R90003¥ASDataReader」 Analysis Services のアクセス制御には「リンクレポート」を使用 No RS 認証 RS から SSAS への接続ユーザー SSASのアクセス制御 1 基本認証 （RSにローカル ユーザーを登録） 固定ユーザー リンクレポート使用 SSRS SSAS 固定ユーザー リンク レポートを使用 具体的な実装方法は、2 章以降で詳しく説明します。 20 Excel から Analysis Services へのアクセス制御の実装方法 Excel から Analysis Services へのアクセス制御は、以下のように実装します。 ユーザー アカウント／グループに対するロールの設定は、ローカル グループまたはロー カル ユーザーに対して設定を行うことができます。 SSASのアクセス制御 アカウント／グループに対するロールの設定 ユーザーは、AS サーバーのローカル ユーザー／パスワードを指定して接続します。 この方法では、パスワードが平文で接続 ファイル（.odc ファイル）へ設定されるため、 特別な注意が必要であり、可能な限り「AD あり」を推奨します。 この方法では、ユーザーは、Excel の「データ」タブから「その他のデータソース」→「Analysis Services」を選択して、データ接続ウィザードで Analysis Services 上のローカル ユーザー とパスワードを指定して接続します。この際に、以下を設定します。 ［パスワードをファイルへ保存する］をチェック ［常にこのファイルを使用してデータを更新する］をチェック 21 Excel からレポート アクションを使用する場合 レポート アクションでは、参照している階層をパラメータとしてレポートに渡すだけなので、 前述の「Reporting Services から RDBMS へのアクセス制御」で記載した実装を行っていれ ばアクセス制御が可能です。 レポート アクションの利用例 22 個人情報の制御の実現方法 レポートで表示するデータセットを作成するストアド プロシージャ内で下記のような処理を 実装します。 個人情報が見られるユーザー → 個人情報の入ったカラム情報をそのまま出す 個人情報が見られないユーザー → 個人情報の入ったカラム情報をすべてアスタリスクで返す SQL 文を実装 まとめ: 「AD なし」の場合のアクセス制御の実装方法 RDBMS へのアクセス制御は「ストアド プロシージャ」を使用 ストアド プロシージャ内で行レベル セキュリティと個人情報の制御を実装 Analysis Services へのアクセス制御は「リンクレポート」を使用 RS 認証 RS → RDBMS のユーザー RS→SSAS のユーザー RS→SSAS の アクセス制御 RS → RDBMS のアクセス制御 Excel → AS のアクセス制御 基本認証 固定ユーザー 固定ユーザー リンクレポート使用 ログインユーザー を元にストアドプロ シージャを呼んで その中で絞る アカウントに対す るロールの設定 SSRS RDBMS ユーザーアクセス権管理テーブル SSAS 23 その他のアクセス制御方式の紹介（補足） Reporting Services Web サービスによるアクセス制御 Reporting Services Web サービスを利用して、レポートを実行／表示する Web アプリケーシ ョンケーションによってアクセスを制御することも可能です。 ReportViewerコントロールを使用 レポート SSRS RDBMS 固定 ユーザーア クセス権テーブル により 、 レポートに与えるパラ メータを制御 ユーザーア クセス権管理テーブル SSAS Reporting Services Web サービスの呼び出しは、ReportViewer コントロールをホストす ることでラップ可能 ユーザー アクセス権テーブルにより、レポートに与えるパラメータを制御 クライアントから Reporting Services へは、直接アクセスさせない アクセス制御のロールの数が多くても対応可能 Forms 認証による独自認証の組み込みも可能 ただし、リンクレポートの数が 100 くらいまでならば、リンクレポートで対応した方が工 数が少なくて済むため、今回の検証ではリンクレポートで対応 24 1.5 レポート マネージャについて アクセス制御の設定には、Reporting Services の「レポート マネージャ」を利用するので、 ここでは、レポート マネージャの利用方法を説明します。 レポート マネージャで行えること レポート マネージャで行えることは、以下のとおりです。 レポートの表示や検索 フォルダ階層の作成 アクセス制御の設定 など レポート マネージャのルート ノードの名前は、「ホーム」です。ホームへフォルダを作成し て、格納するレポートやアイテムの整理を行うことができます。 レポート マネージャの起動 レポート マネージャを起動するには、Internet Explorer（IE）から次の URL へアクセスし ます。 http://<ComputerName>/reports 今回の検証で使用した環境では、以下の URL で開くことができます。 AD （Active Directory） レポートマネージャ URL AD あり http://DWH-DL58501/reports AD なし http://DWH-DL58503/reports レポートマネージャの 「ホーム」 25 4 1.6 レポート マネージャからの操作 データソースの設定 Reporting Services では、データを参照するには「データソースの設定」が必要です。 前述したように、今回の検証では、 「AD あり」と「AD なし」の場合の両ケースとも、RDBMS ／Analysis Services へ接続するためのデータソースには、 「固定ユーザー」を使用して、接続 する設定を行っています。 例） B_User1でログインした場合 RDBMS SSRS AD あり （DWH-DL58503） Sql¥DBDataReader Windows認証 sql¥ASDataReaderr sql¥B_User1 SSRS AD なし 固定ユーザーで 認証 SSAS （DWH-DL58501） DWH-DL58501¥DBDataReader Windows認証 DWH-DL58501¥DBDataReader DWH-DL58501¥B_User1 設定方法 データソースに対して、固定ユーザーを設定するには、設定対象のデータソースのプロパティ を開き、 「レポート サーバーへ保存され、セキュリティで保護された資格情報」を選択し、 「デ ータソースへの接続時に Windows 資格情報として使用する」チェックボックスをチェックし ます。 26 今回作成したデータソース 今回の検証では、以下のデータソースを作成しています。 データソース名 サーバー 対象 MSCQIDWH DWH-R90001 MSCQIDWH （データベース） MSCQIDWH_AS DWH-R90003 MSCQIDWH_AS （SSASキューブ） フォルダに関する操作 フォルダを作成する目的は、フォルダの追加／変更／削除によって、レポート サーバーのフ ォルダを階層構造化するためです。 今回作成したフォルダの構成（地域／支店フォルダ） 今回は、地域／支店ごとにフォルダを作成し、その中へリンクレポートを格納します。また、 作成したフォルダに対して、地域／支店ユーザー グループに対してロールの設定を行います。 以下のような構成でフォルダを作成します。 ホーム MSDWHCQI レポート配置により作成 → 配置されたレポートを格納 新規作成 データソース B_Kanagawa → 神奈川 （地域） D_Chiba → 千葉 （地域） → データソースを格納 B01_Yokohama → 横浜本部 （支店） B02_Kawasaki → 川崎支社 （支店） D01_ChibaChuo → 千葉中央支社 （支店） D02_ChibaArea → 千葉エリア支社 （支店） 作成手順 フォルダを作成する手順は、次のとおりです。 1. 2. Master_User1（コンテンツ マネージャ）でレポート マネージャを開きます。 ［新しいフォルダ］を選択します。 27 この作業は、フォルダを作成する場所 （ホーム／フォルダ） で行います。 3. ［名前］へフォルダ名を入力、任意で［説明］を入力します。ここでは、 「B_Kanagawa」 と入力しています。 4. 「B_Kanagawa」フォルダが作成されました。 5. 他のフォルダについても、同様の手順で作成します。 28 1.7 リンクレポートの操作 リンクレポートは、既存のレポートへのアクセス ポイントを提供し、元のレポートとは異な るパラメータ値またはプロパティで既存のレポートのレポート定義への参照を行います。 既存のレポートでパラメータが使用されている場合は、既存のレポートで指定されているパラ メータ値とは異なる値を設定することができます（既存のレポートからは、複数のリンクレポ ートを作成することができます） 。 リンクレポートは、以下のように利用することができます。 レポートの既定値を「地域」によって変更したい場合 レポートに対して、異なるセキュリティをかけたい場合 レポート名を変更したい場合 既存のレポートとは、別の場所へ格納したい場合 ただし、リンク レポートをもとに別のリンク レポートを作成することはできません。 リンクレポートに関する操作 リンクレポートに関しては、以下の操作が可能です。 リンクレポートの作成 リンクレポートの削除 リンクレポートのパラメータの変更 リンクレポートの概念 リンクレポートは、概念的には、プログラムを実行したり、ファイルを開いたりするために使 用するプログラムのショートカットに似ています。 Parameter年月 = 10月 Parameter支店 = A支店 10月A支店 売上 10月・ A支店で レポートを実行 レポート ユーザーA ［既定値］ Parameter年月 = NULL Parameter支店 = NULL 月別売上 レポート リンクレポート 11月・ Ｂ支店で レポートを実行 11月A支店 売上 レポート リンクレポート Parameter年月= 11月 Parameter支店= A支店 29 レポート 今回作成したリンクレポート 今回の検証では、以下のようにリンクレポートを作成しました。 フォルダ ホーム B_Kanagawa B01_Yokohama CQI_RS1 CQI_RS1 CQI_RS5 B02_Kawasaki CQI_RS1 CQI_RS6 D_Chiba リンクレポート D01_ChibaChuo CQI_RS1 CQI_RS5 CQI_RS1 D02_ChibaArea CQI_RS6 CQI_RS1 ※ リンクレポートの名前は、元のレポートの名前と同じにしています。 リンクレポートの作成手順 リンクレポートの作成手順は、次のとおりです。 1. リンクレポートの作成元のレポートを選択（ここでは CQI_RS1 を選択）して、 ［編集］を クリックします。この作業は、ツールバーの右端の［詳細の表示／非表示］をクリックして、 詳細を表示した状態で行う必要があります。 「CQI_RS1」レポートに基づいたリン クレポートを作成します。 2. ［名前］にリンクレポート名を入力、任意で［説明］を入力します。 ・「CQI_RS1」という名前でリンクレ ポートを作成します。 ・「Kanagaewa_Report」という説 明をつけます。 3. ［場所の変更］をクリックします。 30 リンクレポートの作成場所（ホーム ／フォルダ）を指定します。 4. リンクレポートを作成するフォルダを選択します。 B_Kanagawaフォルダにリンクレ ポートを作成します。 5. ［OK］ボタンをクリックします。 6. 指定場所にリンクレポートが作成されました。 指定したB_Kanagawaフォルダにリ ンクレポートが作成されました。 31 2．アクセス制御の実装手順 ここでは、アクセス制御の具体的な実装手順について説明します。 2.1 アクセス制御の実装手順 アクセス制御では、次の 2 つを実施します。 (1) リンクレポートのパラメータの設定 パラメータの既定値を設定し、非表示にする 目的： ユーザーに他のデータを参照できないようにパラメータを固定値／非表示化 (2) ホームとアイテムに対するロールの割り当て レポートへのロールの割り当て 目的： フォルダ内にあるレポートの参照の可否を設定する フォルダへのロールの割り当て 目的： フォルダの参照可否を設定する 2.2 リンクレポートのパラメータの設定 既定値の設定／変更 既存のレポートで設定したレポート パラメータの既定値は、レポート マネージャ上で設定／ 変更することができます。 ※ ただし、 「固定値」から「クエリ ベース」への変更はできませ ん。 パラメータを非表示へ設定 レポート マネージャ上でリンクレポートのパラメータを設定後、ユーザーからパラメータ値 を変更できないようにするために、非表示へ設定します。 今回設定したレポート パラメータ 今回の検証では、以下のようにリンクレポートの店舗階層パラメータの既定値を設定しました。 32 ホーム B_Kanagawa B01_Yokohama フォルダ リンクレポート CQI_RS1 CQI_RS1 ［M Store］.［店舗階層］.［地域］.&［B］ ［M Store］.［店舗階層］.［支店］.&［B01］ 既定値 B02_Kawasaki CQI_RS5 ［M Store］.［店舗階層］.［地域］.&［B］ CQI_RS1 CQI_RS6 ［M Store］.［店舗階層］.［地域］.&［B］ D_Chiba ［M Store］.［店舗階層］.［支店］.&［B02］ D01_ChibaChuo CQI_RS1 CQI_RS1 ［M Store］.［店舗階層］.［地域］.&［D］ CQI_RS5 ［M Store］.［店舗階層］.［支店］.&［D01］ D02_ChibaArea ［M Store］.［店舗階層］.［地域］.&［D］ CQI_RS6 CQI_RS1 ［M Store］.［店舗階層］.［地域］.&［D］ ［M Store］.［店舗階層］.［支店］.&［D02］ 設定手順 設定手順は、次のとおりです。 1. レポートの［編集］をクリックします。 B_Kanagawaフォルダ配下にあるリンクレ ポート「CQI_RS1」の「店舗階層」パラメーター の既定値を設定します。 2. ［プロパティ］タブで［パラメータ］を選択します。 3. すでに既定値が設定されている場合は、 ［既定値あり］のチェックを外して、 ［適用］をク リックします。 33 ［適用］を押下したあと、ブラウザの更新ボタンまたはＦ５キーで画面の更新を行う。 →更新を行わないと、既定値が「クエリベース」のままとなり、入力できない。 4. 画面を更新します。 5. ［既定値あり］へチェックを入れて、既定値を入力します。 「店舗階層」パラメーターの既定値として 「［M Store］.［店舗階層］.［地域］.&［B］」（神奈川地域）を設定します。 6. ［非表示］と［ユーザーにメッセージを表示］のチェックを外します。 設定した 地域／支店 以外のデータを見れないようにするため、パラメータの値を変更できな いように 「非表示」・「ユーザーにメッセージを表示」 のチェックボックスを OFF に設定します。 ⇒ 「非表示」のチェックボックスを OFF に設定するのみでは、URL からのパラメータの指定 は可能な状態です。「ユーザーにメッセージを表示」 のチェックボックスを併せて OFF に設 定して、ユーザーからは操作できないようにする必要があります。 34 7. 既定値を設定して、パラメータを非表示へ設定できました。 35 2.3 ホームとアイテムに対するアクセス制御（ロールの割り当て） レポートやフォルダへのアクセスは、グループまたはユーザーへロールを割り当てることでホ ームまたはアイテムへのアクセス制御設定ができます。 ロールの定義 ロールの割り当て コンテンツ マネージャ ロール 閲覧者ロール ※ ユーザー①はすべてのアイテムに対して コンテ ンツ マネジャ ロールを割り当てる。 ホーム ユーザー① タスク1 グループⅠ 閲覧者ロール ユーザー② コンテンツ マネージャ ロール タスク2 レポートA フォルダA ホームのセキュ リティを継承 ユーザー③ レポートのセキュ リティを継承 グループⅡ 閲覧者ロール タスク3 ユーザー④ レポートB フォルダB ユーザー⑤のみ 閲覧 グループⅡのみ 閲覧 ユーザー⑤ 閲覧可能範囲 閲覧可能フォルダのみにロールの割り当てを行うと、各ユーザーの閲覧可能範囲は、以下のよ うになります。 グループⅠ 2.4 グループⅡ ユーザー① ユーザー② ユーザー③ ユーザー④ ユーザー⑤ ホーム コンテンツ マネージャ 閲覧者 閲覧者 閲覧者 閲覧者 フォルダA コンテンツ マネージャ 閲覧者 閲覧者 閲覧者 閲覧者 レポートA コンテンツ マネージャ 閲覧者 閲覧者 閲覧者 閲覧者 フォルダB コンテンツ マネージャ 閲覧者 閲覧者 レポートB コンテンツ マネージャ 閲覧者 ロールの種類 ロールには、 「アイテム レベルのロール」と「システム レベルのロール」があります。 アイテム レベルのロール アイテム レベルのロール（アイテム ロール）には、レポート サーバーで格納および管理さ れているアイテムに関連するタスクが含まれています。 レポートの管理 フォルダの表示 リンクレポートの作成 など システム レベルのロール システム レベルのロール（システム ロール）の定義には、サイト全体に適用するタスクが含 まれています。 36 レポート サーバーのプロパティを表示 ロールの管理 レポート サーバーのセキュリティを管理 など ホームまたはアイテムに対してアクセス制御を行う場合には、 「アイテム ロール」を割り当て ます。 定義済みロール レポート マネージャには、既に定義済みのロールがいくつか用意されています。 参考： http://msdn.microsoft.com/ja-jp/library/ms157363(SQL.100).aspx 今回は、管理者グループに対して「コンテンツ マネージャ」ロールを、一般ユーザー グルー プに対して「閲覧者」ロールを割り当てます。 ＡＤ ＡＤ あり ＡＤ なし ユーザーグループ 役割 割り当てるアイテムロール sql¥Master_Group 管理者 コンテンツ マネージャ ロール sql¥Master_Group 以外 一般ユーザー 閲覧者ロール DWH-DL58501¥Master_Group 管理者 コンテンツ マネージャ ロール DWH-DL58501¥Master_Group 以外 一般ユーザー 閲覧者ロール ※ サーバーの Administrators ローカル グループのメンバーも、コンテンツ マネージャとして すべてのアイテムにコンテンツ マネージャ ロールを保有しています。 2.5 アイテムに対するアクセス制御の初期設定 フォルダやリンクレポート、レポート等のアイテムに対するセキュリティの初期設定は、親ア イテムのアクセス設定を継承しています。 例 ・ホーム配下のフォルダとレポートなどは、ホームの設定を継承 ・B_Kanagawa フォルダ配下のフォルダとレポートなどは、B_Kanagawa の設定を継承 親アイテムを継承しない設定 親アイテムからの継承とは別の設定にする（アイテムごとに個別の設定へ変更する）には、各 アイテムの［プロパティ］の［セキュリティ］から「アイテムのセキュリティを編集」をクリ ックします。 37 設定手順 アクセス制御の設定手順は、AD あり／AD なしともほぼ同様で、指定するユーザーが AD ユ ーザーか、ローカル ユーザーかの違いのみです。以降は、AD ありの場合の設定手順です。 1. ［アイテムのセキュリティを編集］を選択することで、個別の設定へ編集することが可能 です。 Default では親フォルダのセキュリティを受け継ぐ 設定（親フォルダと同様の設定）になっています。 親アイテムの設定を継承しない 2. ［OK］をクリックします。 親のセキュリティを継承する場合（既定へ戻す場合） 1. ［親のセキュリティに戻す］を選択します。 CQI_RS1のセュリティ設定を親フォルダ （B_Kanagawa）と同じ設定に戻します。 親アイテムの設定を継承させる 2. ［OK］をクリックします。 38 3. フォルダと同じセキュリティ設定へ戻りました。 今回設定したアクセス制御 今回の検証では、以下のようにユーザー グループに対してロールを割り当てました。 ホーム B_Kanagawa ●Master_Group ◆B_Group ◆B01_Group ◆B02_Group ◆D_Group ◆D01_Group ◆D02_Group ●Master_Group ◆ B_Group ◆ B01_Group ◆ B02_Group D_Chiba ●Master_Group ◆ B_Group B01_Yokohama ●Master_Group ◆B_Group ◆B01_Group レポート ●Master_Group ◆D_Group ◆D01_Group ●Master_Group ◆D_Group ◆D02_Group MSDWHCQI ●Master_Group ●Master_Group ◆ B_Group ◆ B01_Group ◆ B02_Group ◆ D_Group ◆ D01_Group ◆ D02_Group フォルダ （親フォルダの アクセス制御を継承） D02_ChibaArea （親フォルダの アクセス制御を継承） データソース ●コンテンツ マネージャ ロール ◆閲覧者ロール ●Master_Group ◆ D_Group D01_ChibaChuo （親フォルダの アクセス制御を継承） B02_Kawasaki ●Master_Group ◆B_Group ◆B02_Group ●Master_Group ◆ D_Group ◆ D01_Group ◆ D02_Group データソース （親フォルダの アクセス制御を継承） （親フォルダの アクセス制御を継承） CQI_Meisai （親フォルダの アクセス制御を継承） CQI_RSXX ●Master_Group 45 39 2.6 ホームに対するセキュリティ設定 レポート マネージャの「ホーム」に対するセキュリティ設定を行います。 グループまたはユーザーへのロールの割り当て グループまたはユーザーからロールのはく奪 設定手順 設定手順は、次のとおりです。この作業は、コンテンツ マネージャ ロールを持つユーザー （Master_User1）から実行します。 1. ホームの［プロパティ］タブをクリックします。 2. ［新しいロールの割り当て］をクリックします。 3. ロールを与えるグループ名またはユーザー名を入力して、与えるロールへチェックを入れ ます。ここでは、sql¥B_Group グループを指定しています。 Sql¥B_Groupに対して「閲覧者」 ロールを割り当てます。 4. ホームに対して、sql¥B_Group へ閲覧者ロールが割り当てられました。 40 ホームからロールをはく奪する場合 1. グループまたはユーザーから、ロールをはく奪する場合は、解除するグループまたはユーザ ーへチェックを入れて、 ［削除］をクリックします。 Administrator以外のグループから 「閲覧者」ロールをはく奪します。 2. ［OK］をクリックします。 3. 選択したグループからロールがはく奪されました。 41 2.7 フォルダに対するセキュリティ設定 レポート マネージャのフォルダに対するセキュリティ設定を行います。 グループまたはユーザーへのロールの割り当て グループまたはユーザーからロールをはく奪 設定手順 設定手順は、次のとおりです。この手順は、コンテンツ マネージャ ロールを持つユーザー （Master_User1）から実行します。また、詳細を表示した状態で設定します。詳細の表示／ 非表示は、レポート マネージャの右上の「詳細の表示」で切り替えが可能です。 フォルダに対するロールの割り当て 1. ロールの割り当てを行うフォルダの［編集］をクリックします。 「B01_Yokohama」フォルダに対して 「sql¥B01_Group」に閲覧者ロールを割り当てます。 2. ［セキュリティ］で、 ［新しいロールの割り当て］をクリックします。 3. ロールを割り当てるユーザーまたはグループを入力して、割り当てるロールへチェックを 入れ、 ［OK］をクリックします。 42 4. グループにロールが割り当てられました。 フォルダからロールをはく奪する場合 1. フォルダからロールをはく奪するには、セキュリティ設定を行うフォルダの［編集］をクリ ックします。 「 B_Kanagawa」フォルダに対して 「sql¥B01_Group」から閲覧者ロールをはく奪します。 2. グループまたはユーザーから、ロールをはく奪する場合は、解除するグループ／ユーザー へチェックを入れて、 ［削除］をクリックします。 3. 選択したユーザー／グループのロールがはく奪されました。 43 2.8 リンクレポート（レポート）に対するセキュリティ設定 レポート マネージャのリンクレポート（レポート）に対するセキュリティ設定を行います。 リンクレポートおよびレポートともに、設定手順は同様です。 グループまたはユーザーへのロールの割り当て グループまたはユーザーからロールをはく奪 リンクレポート（レポート）に対するロールの割り当て この作業は、コンテンツ マネージャ ロールを持つユーザー（Master_User1）から実行しま す。また、詳細を表示した状態で設定します。詳細の表示／非表示は、レポート マネージャ の右上の「詳細の表示」で切り替えることができます。 1. レポートのプロパティで［新しいロールの割り当て］を選択します。 B_Kanagawaフォルダ配下の 「CQI_RS1」に対して「sql¥B01_Group」 に閲覧者ロールを割り当てます。 2. ロールを与えるグループ名またはユーザー名を入力して、与えるロールへチェックを入れ ます。ここでは、sql¥B01_Group グループを指定しています。 3. レポートに対して、sql¥B01_Group へ閲覧者ロールが与えられました。 44 リンクレポート（レポート）からロールをはく奪する場合 1. リンクレポートからロールをはく奪する場合は、レポートの［編集］をクリックします。 リンクレポート「CQI_RS1」からロー ルをはく奪します。 2. グループまたはユーザーからロールをはく奪する場合は、解除するグループ／ユーザーへ チェックを入れて、 ［削除］をクリックします。 「sql¥B01_Group」と「sql¥B02_Group」から 「閲覧者ロール」をはく奪します。 3. 選択したユーザー／グループのロールがはく奪されました。 45 2.9 レポートの直 URL だけを公開する場合のセキュリティ設定 アイテムに対するアクセス制御設定 ホームを公開せず、レポートの URL を直接指定して実行する場合（Report Viewer など） 、閲 覧可能フォルダのみにロールを割り当て、レポートはフォルダのセキュリティを継承させます。 ホームまたはアイテムへのアクセス制御設定ができます。 ロールの定義 ロールの割り当て コンテンツ マネージャ ロール 閲覧者ロール ※ ユーザー① はすべてのアイテムに対して コンテ ンツ マネージャ ロールを割り当てる ホーム ユーザー① タスク1 グループⅠ 閲覧者ロール ユーザー② コンテンツ マネージャ ロール タスク2 レポートA フォルダA フォルダのセキュリティ を継承 ユーザー③ グループⅡ タスク3 閲覧者ロール ユーザー④ レポートB フォルダB フォルダのセキュリティ を継承 ユーザー⑤ 閲覧可能範囲 閲覧可能フォルダのみにロールの割り当てを行うと、各ユーザーの閲覧可能範囲は、以下のよ うになります。 グループⅠ ユーザー① ユーザー② ユーザー③ グループⅡ ユーザー④ ユーザー⑤ ホーム コンテンツ マネージャ フォルダA コンテンツ マネージャ 閲覧者 レポートA コンテンツ マネージャ 閲覧者 フォルダB コンテンツ マネージャ 閲覧者 レポートB コンテンツ マネージャ 閲覧者 ホームを使用する場合、同一フォルダ内で、レポートは表示せず、下位のフォルダに移動する ためのフォルダを表示させるような設定を行う必要があります。 レポートの直 URL のみを公開する場合、ユーザーが閲覧可能なレポートが格納されたフォル ダのみに権限を与えるだけで設定が可能となり、ホームを使用した場合よりも設定を簡略化す ることができます。 46 2.10 アクセス制御設定（URL 指定） 今回設定したアクセス制御 以下のようにユーザーグループに対してロールを割り当てます。 ホーム B_Kanagawa ●Master_Group ●Master_Group ◆ B_Group D_Chiba （親フォルダの アクセス制御を継承） B01_Yokohama ●Master_Group ◆B_Group ◆B01_Group レポート ●Master_Group ◆D_Group ◆D01_Group ●Master_Group ◆D_Group ◆D02_Group MSDWHCQI （親フォルダのアクセス制御を継承） （親フォルダの アクセス制御を継承） フォルダ （親フォルダの アクセス制御を継承） D02_ChibaArea （親フォルダの アクセス制御を継承） データソース ●コンテンツ マネージャ ロール ◆閲覧者ロール （親フォルダの アクセス制御を継承） D01_ChibaChuo （親フォルダの アクセス制御を継承） B02_Kawasaki ●Master_Group ◆B_Group ◆B02_Group ●Master_Group ◆ D_Group データソース （親フォルダの アクセス制御を継承） 47 （親フォルダの アクセス制御を継承） CQI_Meisai ◆ B_Group ◆ B01_Group ◆ B02_Group CQI_RSXX ◆ D_Group （親フォルダの ◆ D01_Group アクセス制御を継承） ◆ D02_Group 3．RDBMS へ接続する際のアクセス制御の実装方法 3.1 RDBMS へ接続する際のストアド プロシージャによるアクセス 今回の検証のように、使用ユーザーによってデータの閲覧を制限する場面では、ほとんどの場 合で行レベルのアクセス制御を行う必要があり、場合によっては閲覧許可の条件の仕様が煩雑 になることもあります。 そのような場合は、データセットのクエリにストアド プロシージャを実装することで、パフ ォーマンスおよび拡張性で非常に大きなメリットを得られます。 ストアド プロシージャを利用するメリットは、次のとおりです。 単一の SELECT 文では実現不可能な仕様でも、プログラミングが可能になるため、ほと んどの仕様へ対応できます。 コストが高いテーブル結合や副問い合わせの処理を、複数ステップへ分割したり、プロ グラミングに置き換えることでパフォーマンスが向上します。 パフォーマンス チューニングで考えられる手法の幅が大幅にアップします。 Reporting Services の実装箇所（データセットの設定） ストアド プロシージャを利用するには、データセットの［クエリ］を変更します。 ［クエリの 種類］には、 「テキスト」や「テーブル」を利用するのが一般的ですが、これを「ストアド プ ロシージャ」へ設定することで、ストアド プロシージャを利用できるようになります。 「 クエ リの種類」 を 「 ストアド プロシ ージ ャ」 へ設定する ストアド プロシージャ を指定する データセットへの実装の詳細は、別冊の「レポート作成手順（Reporting Services 構築手順） 」 を参考にしてください。 48 3.2 Reporting Services へ実装する場合の考慮事項 ストアド プロシージャを Reporting Services へ実装する場合の考慮事項は、次のとおりです。 ストアド プロシージャの種類 ユーザー定義関数にする必要はなく、ストアド プロシージャで問題ありません。 引数 Reporting Services に実装する際に、引数の変数名と同じ名称のレポート パラメータが作 成されるので、それを考慮した命名を行います。 レポートの設定で、引数へ対応するレポート パラメータの変更は可能ですが、初回は必ず 引数の変数名のレポート パラメータが作成されます（通常はそのまま使用します）。 引数の変数名は、必ず半角英数で命名します。 レポート パラメータは、URL の一部となるので、半角英数以外の文字が含まれるとエン コーディングが発生します。不要なエンコーディングを避けるために、変数名は半角英数 を使用します。 アウトプット データ返却用に OUTPUT 用の引数を用意する必要はありません。 アウトプットを伴う SQL およびコマンドは、1 回しか使用できません。複数回使用した場 合は、レポートで正しくデータを受けることができません。 たとえば、SELECT～FROM 文はアウトプットが発生するので 1 回しか使用できませんが、 SELECT ～INTO 文や SELECT @Param=Column FROM 文は、アウトプットが発生し ないので複数回使用することが可能です。また、xp_cmdshell 等のアウトプットが発生す るコマンドについても、同様に使用が制限されます。 ユーザー名の取得について ストアド プロシージャでは、引数で渡されたユーザー名によってアクセス制御を行います。 このユーザー名は、Reporting Services から渡す必要があります。 Reporting Services でユーザー名を取得して、ストアド プロシージャへ渡すには、以下の 準備をする必要があります。 ストアド プロシージャには、クエリ パラメータ経由で値を引き渡すので、引き渡し用 のクエリ パラメータを用意します。今回は［UserName］というクエリ パラメータを 作成します。 クエリ パラメータには、組み込みフィールド［UserID］の値を設定します。このフィ ールドでレポートを実行しているユーザーの ID が取得可能です。 Windows 認証を使用している場合は、ユーザーのドメイン アカウント、基本認証を使 用している場合は、ユーザー名を取得できます。ユーザー名は、Domain¥UserName ま たは ServerName¥UserName の形で取得されます。 49 3.3 Reporting Services でのユーザー名の取得方法 Reporting Services で、ユーザー名を取得する方法は、次のとおりです。 UserName パラメータの設定 1. パラメータ「UserName」を右クリックして、［パラメータのプロパティ］を選択します。 2. パラメータのプロパティの［既定値］で、オプションから「値の指定」を選択し、 ［追加］ を選択後、右側の「fx」をクリックします。 3. ［カテゴリ］の「組み込みフィールド」を選択し、 ［アイテム］から「UserID」を選択し 50 ます。 ［アイテム］から「UserID」を選択すると、式として 自動的に入力されます。 4. 値が設定されていることを確認して、 ［OK］でプロパティを閉じます。 51 3.4 ストアド プロシージャの処理の概要 ストアド プロシージャの処理の概要は、次のとおりです。 1. 引数のユーザー名によるアクセス権限の判断 ① クエリ パラメータ「UserName」経由で、組み込みフィールド「UserID」の値が Reporting Services から渡されます。 ② ① の値でテーブル「UserMapping」を検索して、権限設定を取得します。 ③ ユーザーごとに行レベルのアクセス制御を実現します。 2. ユーザー名以外の引数による各抽出条件の判断 ① 1. 以外のクエリ パラメータに、本レポートを呼び出す別のレポートやキューブのアク ション設定から値が渡されます。 ② 各クエリ パラメータ経由でマッピングされた引数（基本的にクエリ パラメータと同 名）へ値が渡されます。 ③ フィルタの指定に、キューブの階層が使用されているため、それへ対応して各引数（レ ポート パラメータも同様）は、～key（条件の値） 、～level（階層のレベル）の対にな っています。～level の値によって、抽出対象のカラムを決定します。 3. 上記の条件に沿った DML の作成および実行 ① 上記の条件を満たす SQL の文字列を組立て実行します。 引数のユーザー名によるアクセス権限の判断 テーブル［UserMapping］のレイアウトは、次のとおりです。 列名 データ型 地域コード NCHAR（1） 支店コード NCHAR（3） 地域名称 NVARCHAR（8） 支店名称 NVARCHAR（20） UserName NVARCHAR（50） アクセス制御の実現方法 テーブル「UserMapping」のカラム「UserName」と引数「@UserName」が一致する行を検 索します。 一致する行がない場合 すべてのデータに対して、参照権限はありません。 データ抽出の DML の WHERE 句の地域コードと支店コードにありえない値を設定し データがヒットしないように設定します。 一致する行がある場合 地域コード、支店コードのいずれも設定されていない場合、データの参照制限はありませ ん。データ抽出の DML の WHERE 句には何も設定しません。 地域コード、支店コードのいずれか、あるいは両方が設定されている場合、データ抽出の 52 DML の WHERE 句に地域コード、支店コードのいずれか、あるいは両方を設定します。 支店コードが設定される場合、顧客名を‘**********’で返すように設定します。 T-SQL の実装例 T-SQL の実装例は、次のとおりです。 -- ユーザー管理テーブル取得 SELECT @ChiikiCD = ［地域コード］, @ShitenCD = ［支店コード］ FROM ［UserMapping］ WHERE UPPER (［UserName］) = UPPER (@UserName) ; IF @@ROWCOUNT > 0 BEGIN IF @ChiikiCD IS NOT NULL AND @ChiikiCD <>'' BEGIN --地域コードの条件をセット IF @sSQL_WHERE = '' SET @sSQL_WHERE = @sSQL_WHERE + N'WHERE '; ELSE SET @sSQL_WHERE = @sSQL_WHERE + N'AND '; SET @sSQL_WHERE = @sSQL_WHERE + N'stor.地域コード= @TblRegion '; END; IF @ShitenCD IS NOT NULL AND @ShitenCD <> '' BEGIN --支店コードの条件をセット IF @sSQL_WHERE = '' SET @sSQL_WHERE = @sSQL_WHERE + N'WHERE '; ELSE SET @sSQL_WHERE = @sSQL_WHERE + N'AND '; SET @sSQL_WHERE = @sSQL_WHERE + N'stor.支店コード= @TblBranch '; END; END; ELSE BEGIN --ユーザーが登録されていない場合はありえない条件をセット IF @sSQL_WHERE = '' SET @sSQL_WHERE = @sSQL_WHERE + N'WHERE '; ELSE SET @sSQL_WHERE = @sSQL_WHERE + N'AND '; SET @sSQL_WHERE = @sSQL_WHERE + N'stor.地域コード IS NULL AND stor.支店コード IS NULL '; END; ユーザー名以外の引数による各抽出条件の判断 WHERE 句の設定は、～key（条件の値） 、～level（階層のレベル）の値によって決定するこ とができます。これはキューブの階層構造へ対応するためです。例として、店舗階層の 53 Store_key と Store_level の関係のイメージを、以下に示します。 Store_level Store_key項目 全て （条件なし） 0 地域 1 支店 2 店舗 3 店舗階層の例 抽出条件の判断方法を「店舗階層」を例に説明します。 Store_level が Null あるいは 0 の場合、抽出条件は指定されていません。 データ抽出の DML の WHERE 句には何も設定しません。 Store_level > 0 の場合 Store_level = 1 の場合、データ抽出の DML の WHERE 句に「地域コード」を Store_key の値で抽出するように設定します。 Store_level = 2 の場合、データ抽出の DML の WHERE 句に「支店コード」を Store_key の値で抽出するように設定します。 Store_level = 3 の場合、データ抽出の DML の WHERE 句に「店舗 ID」を Store_key の値で抽出するように設定します。 T-SQL の実装例 --店舗階層 IF (@Store_level IS NOT NULL) AND (@Store_level > 0) BEGIN IF @Store_level = 1 --地域 BEGIN IF @sSQL_WHERE = '' SET @sSQL_WHERE = @sSQL_WHERE + N'WHERE '; ELSE SET @sSQL_WHERE = @sSQL_WHERE + N'AND '; SET @sSQL_WHERE = @sSQL_WHERE + N'stor.地域コード= @Region '; END; ELSE IF @Store_level = 2 --支店 BEGIN IF @sSQL_WHERE = '' SET @sSQL_WHERE = @sSQL_WHERE + N'WHERE '; ELSE SET @sSQL_WHERE = @sSQL_WHERE + N'AND '; SET @sSQL_WHERE = @sSQL_WHERE + N'stor.支店コード= @Branch '; END; ELSE IF @Store_level = 3 --店舗 BEGIN IF @sSQL_WHERE = '' SET @sSQL_WHERE = @sSQL_WHERE + N'WHERE '; ELSE SET @sSQL_WHERE = @sSQL_WHERE + N'AND '; SET @sSQL_WHERE = @sSQL_WHERE + N'sales.店舗ID = CAST (@Store AS INT) '; END; END; その他の階層について その他の階層についても、同様に抽出条件の判断を設定します。それぞれの階層が使用してい る～key と ～level の関係のイメージは、次のとおりです。 54 時間階層 Time_level Time_key項目 全て （条件なし） 0 年 1 半期 2 四半期 3 月 4 住所階層 Address_level Address_key項目 全て （条件なし） 0 地域 1 支店 2 商品階層 ProductNow_level ProductNow_key項目 全て （条件なし） 0 大分類 1 中分類 2 小分類 3 商品 4 支払階層 Check_key項目 全て Check_level （条件なし） 0 支払 1 年齢階層 Age_level Age_key項目 全て （条件なし） 0 年齢層 1 年齢帯 2 性別階層 Seibetsu_key項目 全て Seibetsu_level （条件なし） 0 性別 1 55 DML の作成および実行 手順 1～2 で作成した抽出条件を付加した DML を作成して、実行します。 T-SQL の実装例 -- sp_executesqlに渡す変数定義 SET @sSQL_PARAMDIF = N'@TblRegion nchar(),@TblBranch nchar(),' + N'@TimeFrom date,@TimeTo date,' + N'@Region nchar(),' + N'@Branch nchar(),' + N'@Store nchar(),' + N'@Area nvarchar(),' + N'@Pref nvarchar(),' + + + + N'@Big nchar(),' N'@Middle nchar(),' N'@Small nchar(),' N'@Product nchar(),' + N'@Check int,' + N'@Agroup nvarchar(),' + N'@Abelt nvarchar(),' + N'@Seibetsu nchar()'; IF @sSQL_WHERE <> '' BEGIN SET @sSQL = @sSQL + @sSQL_WHERE; END; EXECUTE sp_executesql @sSQL, @sSQL_PARAMDIF, @TblRegion = @ChiikiCD, @TblBranch = @ShitenCD, @TimeFrom = @dtTimeFrom, @TimeTo = @dtTimeTo, @Region = @Store_key, @Branch = @Store_key, @Store = @Store_key, @Area = @Address_key, @Pref = @Address_key, @Big = @ProductNow_key, @MIddle = @ProductNow_key, @Small = @ProductNow_key, @Product = @ProductNow_key, @Check = @Check_key, @Agroup = @Age_key, @Abelt = @Age_key, @Seibetsu = @Seibetsu_key ; Tips: 検索パフォーマンスを向上させるためのポイント テーブル「SaleFact」は、カラム［年月日］をキー（パーティション キー）としてパーティ ション分割されています。このようにパーティション分割されているテーブルで、パーティシ ョン キーに対して抽出条件を指定して検索を行う場合は、オプティマイザに正しくパーティ ショニングを判断させるための注意点があります。 パーティション キーを範囲指定する場合は、between よりも < >（=）を使用します。 56 パーティション キーでディメンションを結合し、ディメンションのカラムで検索すると、 パーティションによる検索を行いません。そのような場合は、まずディメンションを別ス テップで参照して、パーティション キーに指定できる値を取得し、直接パーティション キーへ値を指定して検索を行うように設定します。 SQL を文字列として実行する際の注意点 sp_executesql を使用するように設定します。 可変する WHERE 句の検索条件の値は必ずパラメータ化へ設定します。 上記は、いずれもキャッシュ ヒット率を向上させるためです。今回のようにテーブル結合が 多い場合は、コストが高くなると予想されるので、その場合はキャッシュにヒットする／しな いでパフォーマンスに大きな差が現れます。 ※ sp_executesql に関する詳細は、オンライン ブックの以下を参考にしてください。 http://msdn.microsoft.com/ja-jp/library/ms188001(SQL.100).aspx 57 4．Analysis Services アクセス制御の実装方法 4.1 ロールによるアクセス制御 Excel ピボット テーブルから Analysis Services に対するアクセス制御は、ロールの設定に よって行います。 AD ありの場合 ドメイン アカウントに対するロールの設定 ドメイン アカウントを含むグローバル／ローカル グループで設定可能 AD なしの時 ローカル アカウントに対するロールの設定 ローカル アカウントを含むローカル グループで設定可能 使用したユーザーとグループ 今回の検証では、次のようにユーザーとグループを作成し、Analysis Services サーバーのロ ーカル グループをロールのメンバーシップへ追加しました。 アクセス制御は、以下のように行います。 ローカル グループに対してアクセス制御を行う 直接ドメイン グループに対してアクセス制御を行うことも可能 AD ありの場合 AD なしの場合 Analysis Services サーバーのローカル グループ Analysis Services サーバーのローカル グループ ドメイン グループ ドメイン ユーザー ローカル ユーザー 58 4.2 Analysis Services アクセス制御の設定方法 Analysis Services アクセス制御の設定方法は、次のとおりです。 1. ロールを作成する 2. ロールのメンバシップへローカル グループを追加する 3. ディメンション データの設定で、設定したユーザーが使用できるディメンションの項目 を設定します。 設定手順 1. ロールを作成するには、ロールを右クリックして、 ［新しいロール］を選択します。 2. ロール名を入力します。 3. メンバシップを追加します。 59 アクセス制御したいユーザー／グループを選択します 4. オブジェクトの種類を選択します。 5. グループへチェックを入れます デフォルトでは、グループ に チェックが入っていないため、 チェックを入れないとグループを 登録することができません 6. 追加したいグループを入力して、［名前の確認］をクリックします。 7. グループが追加されました。 60 8. そのほかの各ページは、次のように設定されています。 61 9. ［ディメンション データ］ページへ移ります 設定したユーザーが使用できるディメンションの項目を設定します 10. アクセス制御したいディメンションを選択します。 62 11. アクセス制御したいディメンションを選択すると、ディメンション内の階層／属性がリス トボックスへ表示され、選択した階層／属性のメンバの一覧が表示されます。 12. ［すべてのメンバの選択を解除する］を選択します。 63 これにより、すべてのメンバのチェックが外れます。 特定の属性だけを見せたい、という要件 の場合、全てのメンバの選択を解除する ようにしておかないと、ディメンション（階 層）に新しい属性が加わった際に、その 属性を使用できるようになってしまうため、 ここですべてのメンバの選択を解除する ようにしておきます。 こうしておくことで、新しく加わった属性を 使用できないようにすることができます 13. ［表示部分の合計を表示する］を選択します。 64 「表示部分の合計を表示する」 を チェックしておくことで、このディメ ンションを分析軸として使用してい ない場合にも選択した属性のみの 集計が行われるようになります 作成したロールとロール メンバシップ 今回の検証では、以下のロールを作成し、各ロールのロール メンバシップへ下記のローカル グループを追加しました。 作成したロール ロール メンバシップへ 追加したローカルグループ 神奈川地域担当 B_Group 千葉地域担当 D_Group 横浜本部担当 B01_Group 川崎支社担当 B02_Group 千葉中央支社担当 D01_Group 千葉エリア支社担当 D02_Group 全地域統括担当 Master_Group 以上 65