Go to Qiita Advent Calendar 2024 Top
LoginSignup
15
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Microsoft Power BIAdvent Calendar 2023

Day 17
@k_maki

Power BIの行レベル セキュリティ（RLS）を理解して動的なRLSを実装する

Last updated at Posted at 2023-12-16

はじめに

こういう人向け

この記事はググった結果や生成AIの出力で満足しない人向け。どうしてそうなるのか、結果だけではなくロジックを知りたい人はついてきてね💖

なお、生成AIの結果が当てにならないのと同様、この記事も当てにならないことに留意。

注意！

行レベル セキュリティ（Row Level Security, RLS）は管理や（動的な場合）ロジックが複雑化しがち。何も考えずに使うのはおススメできない。使うのであれば、しっかりロジックを理解して、複雑さを許容できるか判断してから使うこと。以下は考えるべきポイント。

RLSでよいか？セマンティック モデルを分ける等の他の方法で良いかも考える。セマンティック モデルを分けるのは準備が大変だが、シンプルかつ確実な方法。プレミアムであれば、XLMAエンドポイント経由で、セマンティック モデルに対する操作をスクリプトで自動化できる。

動的なRLSは評価コンテキストを正確に理解する必要がある。理解に自信がないなら、他の方法も考えるべき。

動的なRLSは、もともとビジネス ロジックだけを実装していたセマンティック モデルに、情報管理のロジックを組み込むもの。従って、セマンティック モデルが複雑化しがち。避けられる複雑性は避けるべき。

前提知識

行レベルセキュリティを理解して、使いこなすには、以下の理解が必要。

  • スタースキーマとその重要性
  • 評価コンテキスト（行コンテキスト、フィルターコンテキスト）
  • コンテキストトランジション

「え？行レベルセキュリティってそんなに難しいの？」って思うかもしれないけど、読み進めればわかる。実際難しい。。メジャー然りで、Power BIをフル活用するには評価コンテキストの理解は絶対必要なので、まだの人は諦めて勉強しましょう。

この辺が怪しいなと思う人は、以下で学びましょう。

スタースキーマ:
https://learn.microsoft.com/ja-jp/power-bi/guidance/star-schema
https://qiita.com/yugoes1021/items/12a40a5089fc5b5167c5
https://marshal115.hatenablog.com/entry/2021/05/21/232230
https://qiita.com/PowerBIxyz/items/cc98414a1e71d837fd32

フィルターコンテキスト:
https://qiita.com/k_maki/items/f3d9b91a7546b1dd35d5

コンテキストトランジション:
https://qiita.com/k_maki/items/c24c5258fccb45cd4582

実現したいこと（ビジネス要件）

やりたいことは以下。技術を身に着けるのはそれなりにエネルギーが必要。エネルギーを生み出すのはモチベーション！ということ。

  • 自分の部署だけのデータを見れるようにする。
  • ユーザーと部署のデータは自分で用意する（Excelとかで）。Entra ID（旧Azure AD）から作れれば良いが、やり方知らん。
  • ユーザーが所属する部署の変化を考慮する。

なお、以下は本記事では取り扱わない：

  • 自分の役職以下とかのバリエーションはやらない。
  • データ接続方式はインポートのみ

サンプルのデータモデルの説明

'売上'テーブル: 従業員別の売り上げを格納したファクト テーブル。売上 = SUM([_売上])メジャーを定義。

日付 従業員KEY _売上
2022/4/1 文/山 100
2022/5/1 文/栗 200
2022/6/1 文/富 300
2022/7/1 美/海 4000
2022/8/1 美/良 5000
2023/4/1 美/山 1000
2023/5/1 文/栗 200
2023/6/1 文/富 300
2023/7/1 美/海 4000
2023/8/1 文/良 500

'従業員'テーブル: 従業員の所属を格納したディメンション テーブル。SCD Type 2を最近覚えたので早速使ってみた。

従業員KEY 従業員ID 名前 部署 適用開始日 適用終了日 userprincipalname
文/山 山 山岡 文化部 2022/4/1 2023/3/31 shiro.yamaoka@touzai.co.jp
文/栗 栗 栗田 文化部 2022/4/1 2999/12/31 yuko.kurita@touzai.co.jp
文/富 富 富井 文化部 2022/4/1 2999/12/31 tomio.tomii@touzai.co.jp
美/海 海 海原先生 美食倶楽部 2022/4/1 2999/12/31 yuzan.kaibara@touzai.co.jp
美/良 良 良三 美食倶楽部 2022/4/1 2023/3/31 ryouzo.okaboshi@touzai.co.jp
美/山 山 山岡 美食倶楽部 2023/4/1 2999/12/31 shiro.yamaoka@touzai.co.jp
文/良 良 良三 文化部 2023/4/1 2999/12/31 ryouzo.okaboshi@touzai.co.jp

（[備考]列は省略）

一応、カレンダーテーブルも作っているが、今回の話に関係しないので、説明は省略。

データモデルは以下の図。SCDなので、ファクトとディメンションは、ナチュラルキー（従業員ID）ではなく、代理キー（従業員KEY）で結ぶ。もちろん、多対一の単方向のスタースキーマ。基本に忠実なベストプラクティスですよ！

データ モデル_trimed.png

なお、ビジュアルは売上をテーブルビジュアルで表示しただけのシンプルなもの。

ビジュアル_trimed.png

基礎 ― そもそもRLSとは？（よくある説明）

RLSとは？

行レベル セキュリティ（Row Level Security, RLS）とは、ユーザーまたはグループ毎に閲覧可能なデータを行単位で制御する機能のこと。これにより、同じセマンティック モデル、レポートを使いながら、ユーザーの属性に応じた情報管理が可能になる。逆に言うと、RLSを使わない場合、ユーザーの属性（例えば所属部署）に応じて複数のセマンティック モデルやレポートを用意しなければならなくなる。RLSを上手く使うと情報管理が楽になりそうだ。

やりたいこと

先のサンプルのデータモデルでやりたいことは、各ユーザーが今の所属のデータのみを見れるようにすること。例えば、山岡さんは文化部から異動して現時点では美食倶楽部になっているので、過去分を含む美食倶楽部のデータのみを見れるようにしたい。

これをRLSで実現しよう。

ロールとセキュリティ フィルターの設定 in Power BI desktop

リボンの「モデリング」タブ（レポートビューの場合。他のビューの場合は「ホーム」タブ）の、「ロールの管理」をクリックし、RLS設定画面を開く。この画面でロールを作成する。ロールごとにテーブルにセキュリティ フィルターを設定する。

美食倶楽部のデータだけを見れるようにしたければ、'従業員'テーブルに次のセキュリティ フィルターを設定する。

「美食倶楽部」ロールのセキュリティ フィルター
 [部署] == "美食倶楽部"

ロールの管理_美食倶楽部_trimed.png

ロールの検証 in Power BI Desktop

「表示方法」をクリックし、「ロールとして表示」画面を開く。先ほど作成したロールにチェックを入れれば、ロールを検証できる。美食倶楽部に紐づくデータだけ表示されればOK。

ビジュアル_美食倶楽部ロール適用後_trimed.png

ビジュアルだけではなく、データビューで表示されるテーブルそのものも制限されている。

従業員テーブル_美食倶楽部ロール適用後_trimed.png

よし、ちゃんと動いてそうだ。

セキュリティ フィルターの伝播

ここで、データビューで'売上'テーブルを見てみると、こちらも部署が美食倶楽部のものだけに制限されている。

売上テーブル_美食倶楽部ロール適用後_trimed.png

これは、リレーションシップを通じて、ディメンション テーブルに設定したセキュリティ フィルターがファクト テーブルに伝播しているということ。なので、ファクト テーブルにセキュリティフィルターを設定する必要は無い。

ビジュアルのデータが制限されていたのは、フィルターされたディメンション、ファクト テーブルを元にビジュアルが作成されていたから。

また、パフォーマンスの観点から、ファクト テーブルにセキュリティ フィルターを設定することは望ましくない（後述）。

リレーションシップを通じて、ディメンション テーブルに設定したセキュリティ フィルターがファクト テーブルに伝播する話って、MS Learnの『Power BI での行レベルのセキュリティ (RLS)』に明示的に書かれていないんだよね。プロの間では何となくわかるだろ的なものだろうけど、初めて学ぶ人にはその行間が読めずに困る、本当に。。。

セキュリティ フィルターの方向

セキュリティ フィルターの方向は、元のリレーションのクロス フィルターの方向とは別に設定可能。既定ではクロス フィルターの方向が一方向か双方向かにかかわらず、セキュリティ フィルターの方向は一方向となっている。セキュリティ フィルターの方向を双方向とするには、リレーションの設定画面で「両方向にセキュリティフィルターを適用する」にチェックを入れる。

リレーションシップの編集_trimed.png

ただし、元のリレーションでも双方向はほとんど使われないため（ベストプラクティスではない）、セキュリティ フィルターでもあんまし使わない方がいい気がする。RLSのビジネス要件やパフォーマンス次第では使うのかも？？

ロールの割当 in Power BI Service

作成したロールへのユーザーの割り当てはPower BI Service上で行う。

まずは.pbixファイルをワークスペースに発行する。発行したら、ワークスペースをPower BI Serviceで開き、セマンティック モデルの [その他のオプション] メニューを選択。 このメニューは、ナビゲーション メニューまたはワークスペース ページのどちらから選択した場合でも、セマンティック モデル名にカーソルを合わせると表示される。

pbi service 1.png

[セキュリティ] を選択。

pbi service 2_マーカー_resize.png

移動先の行レベルのセキュリティ ページで、ロールにユーザーの割り当てを行う。この際、ユーザー単位以外に、セキュリティ グループ単位で割り当ても可能。セキュリティグループが何かは公式ドキュメントを参照（詳しくは知らない😗）。

ロールの検証 in Power BI Service

Power BI Desktopと同様、Power BI Service上でもロールの検証が可能。

行レベルのセキュリティ ページで、ロールの [その他のオプション] メニューを選択。[ロールとしてテスト] をクリックすると、そのロールを検証できる。ユーザーを切り替えて、自分以外のユーザーとして検証することも可能。

その他の細かいTips

自分に関係しそうなものだけピックアップしているので、ちゃんとLearnを読んでね。

  • 適用されるのはWSの閲覧者ロールのユーザーのみ。セマンティック モデルのビルトアクセス許可がある場合も可EXCELで分析時に適用。
  • 共同作成者以上は適用されない。
  • 複数のロールに割り当てられたら和集合
  • RLSは行の制限。列やメジャーの制限はOLS（Object Level Security）で。
  • 行データは表示される。表示させないなら、集約したセマンティック モデルを用意？
  • EXCELで作成したセマンティック モデルには設定不可。
  • ロールの割当はユーザー以外にセキュリティグループも可。配布グループ、メールが有効なグループ、Azure Active Directory セキュリティ グループは可。Power BIで作成されたグループ、Microsoft 365グループは不可。

よくある説明の感想

自分で説明を書いてきて言うのもおかしいが、なるほど、わからん。

セキュリティ フィルターがどう動いているか？？

[部署]="美食倶楽部"という単純なセキュリティ フィルターだからわかったような気もするが、どう動いているかちゃんとわかっていないから、複雑なセキュリティ フィルターを作れない。（後で気づいたが「行コンテキストなの？フィルター コンテキストなの？」ってこと。）

大量のユーザーなんてやってられない

ユーザーを一人一人ロールに割り当てるのはきつい。異動のたびにユーザーの割り当てを変更しなければならないことを考えるともはや非現実的。絶対みす

セキュリティ グループ単位でやれということか。それはそれでユーザー管理がEntra ID(Azure AD)の設定と連動するので、ミスはなくなるが、セキュリティ グループだけではできない細かい設定をしたかったり、ユーザーが複数のセキュリティ グループに割り当てられた場合に意図しない結果となる懸念も（ロールは和集合なので。せめて積集合なら安心できるのに。）。

よいやり方がないか調べてみると動的なRLSとかいうものを使えばよさそうだ。だがしかし。。

動的なRLSの公式説明がわからん

動的な行レベルセキュリティで何とかなるとかMS Learnに書いてあるが、記述は以下のみ。

username() または userprincipalname() DAX 関数を使用し、適切な関係を構成することで、Power BI Desktop 内で動的セキュリティを有効にできます。

え？MSさん、説明する気あります？？

動的なRLSの他サイト記事もわからん

ググって出てきたコードを真似る → なるほど、やっぱりわからん。なぜなら、どうしてそうなるかが書いていないから。。

基礎の基礎 ー セキュリティ フィルターを深堀り

ヒントは公式にあった

RLS は、すべての DAX クエリにフィルターを自動的に適用することによって機能します。

ルールは、Data Analysis Expressions (DAX) フィルター式を使用して、モデル テーブルにフィルターを適用します。
（中略）
ルール式は、行コンテキスト内で評価されます。 行コンテキストは、その行の列の値を使用して各行について式が評価されることを意味します。 式から TRUE が返されたときは、ユーザーがその行を見ることができます。

RLSの動作から推測

この節は抽象的なので、先に応用で具体例を見てからの方がわかりやすいかも。

以下はRLSの動作からの推測。参考記事なし。パフォーマンス アナライザーでRLS適用前後のDAX QUERYを確認してみたが、違いが確認できなかった。DAX StudioでRLS適用前後を確認すると、クエリの数が増えていたこと、最後のクエリにWHERE句が追加されていることは確認できた。ちゃんと理解できたらまた記事を書くかもしれない。

あのセキュリティフィルターを定義するDAX式が何なのかを理解する。

実はセキュリティフィルターとしてエディターに入力しているのは、FILTER関数の第2引数と同じ。選んだテーブルが第一引数、ルール式が第2引数になるということ。つまり、FILTER(選んだテーブル, ルール式)がRLS適用後のテーブル。

そして、RLS適用後のテーブル（≒ディメンション テーブル）からリレーションシップでつながったテーブル（≒ファクト テーブル）にもRLSが伝播する（セキュリティ フィルターの伝播）。これがセマンティック モデルへのRLS適用。これがビジュアル生成/更新（=DAXクエリ実行）のたびに、DAXクエリ実行前に行われる（実際はキャッシュを利用したりするため、毎回ではないらしい）。セマンティック モデルへのRLS適用→DAXクエリ実行→ビジュアル生成/更新という流れ。

少し話がそれたが、FILTER(選んだテーブル, ルール式)がRLS適用後のテーブルということは、RLS適用後のテーブルはFILTER関数を使った計算テーブルで再現可能ということ。RLSを適用するのはディメンション テーブルだから、ファクト テーブルよりもレコード数が少ないので、確認も容易でしょ。なお、USERPRINCIPALNAME関数だけは計算テーブルで使えないため、そこは定数値で代用する。

何を言いたいかというと、RLSのDAXを書くのはFILTER関数で計算テーブルを作成するのと同じということ。そう考えると、いつものDAX脳でRLSを理解できたことになる。ロールの確認をせずにFILTER関数で計算テーブルを作成して確認できるから、トライ&エラーも簡単。それっぽい計算テーブルができたら、ロールのルールにFILTER関数の第2引数を入れるだけでＯＫ。何だ、簡単じゃん。

逆に言うと、DAX脳になっていないと、複雑なRLSや、パフォーマンスが良いRLSは構築できない。つまり、FILTER関数の第二引数は行コンテキストで評価されるということ、行コンテキストがリレーションシップをフォローしないこと、フィルター コンテキスト（リレーションシップのフォロー）を使うにはコンテキスト トランジションを経ることを理解していなければならない。前提知識に評価コンテキスト等の理解が必要と書いたのはそういうこと。

。。。こういう話って知っている人には当たり前なのに、知らない人からすると訳わからんということが多い。RLSの動作に関するMS Learnの記述が散在しているのも原因だな。。

応用

先のビジネス要件を動的行レベルセキュリティで達成しよう。やりたいことは以下であった：

  • 自分の部署だけのデータを見れるようにする。
  • ユーザーと部署のデータは自分で用意する（Excelとかで）。
  • 部署の変化を考慮する。

データモデルとテーブルも再掲

'売上'テーブル:

日付 従業員KEY _売上
2022/4/1 文/山 100
2022/5/1 文/栗 200
2022/6/1 文/富 300
2022/7/1 美/海 4000
2022/8/1 美/良 5000
2023/4/1 美/山 1000
2023/5/1 文/栗 200
2023/6/1 文/富 300
2023/7/1 美/海 4000
2023/8/1 文/良 500

（売上 = SUM([_売上])メジャーを定義）

'従業員'テーブル:

従業員KEY 従業員ID 名前 部署 適用開始日 適用終了日 userprincipalname
文/山 山 山岡 文化部 2022/4/1 2023/3/31 shiro.yamaoka@touzai.co.jp
文/栗 栗 栗田 文化部 2022/4/1 2999/12/31 yuko.kurita@touzai.co.jp
文/富 富 富井 文化部 2022/4/1 2999/12/31 tomio.tomii@touzai.co.jp
美/海 海 海原先生 美食倶楽部 2022/4/1 2999/12/31 yuzan.kaibara@touzai.co.jp
美/良 良 良三 美食倶楽部 2022/4/1 2023/3/31 ryouzo.okaboshi@touzai.co.jp
美/山 山 山岡 美食倶楽部 2023/4/1 2999/12/31 shiro.yamaoka@touzai.co.jp
文/良 良 良三 文化部 2023/4/1 2999/12/31 ryouzo.okaboshi@touzai.co.jp

（[備考]列は省略）

データ モデル

データ モデル_trimed.png

ビジュアル

ビジュアル_trimed.png

こうやる

まずはRLSを再現する計算テーブルをFILTER関数で作ってみる。なぜ計算テーブルを作るかというと、ロールの検証なしでトライ&エラーできて楽だから。注意点は、計算テーブルではUSERPRINCIPALNAME関数が使えないため、変数でそれっぽくしてあげる必要があること。

山岡さん（現所属：美食倶楽部）に適用されるRLSを再現した計算テーブル作ってみよう。こんな感じ。

'従業員_RLS再現'テーブル
従業員_RLS再現 = FILTER(
    '従業員',
    (
        VAR _userprincipalname = "shiro.yamaoka@touzai.co.jp"

        VAR _current_depts = CALCULATETABLE(
            VALUES('従業員'[部署]),
            ALL('従業員'),
            '従業員'[userprincipalname] = _userprincipalname, 
            '従業員'[適用終了日]=Date(2999,12,31)
        )

        VAR _ret = '従業員'[部署] in _current_depts

        RETURN _ret
    )
)

でき上がりを確認してみる。ええやん💖
（image to be inserted）

この計算テーブルのFILTER関数の第二引数をRLSのセキュリティ フィルターとすればよい。USERPRINCIPALNAME関数を使うように変更することを忘れずに。

セキュリティ フィルター
VAR _current_depts = CALCULATETABLE(
    VALUES('従業員'[部署]),
    ALL('従業員'),
    '従業員'[userprincipalname] = USERPRINCIPALNAME(), 
    '従業員'[適用終了日]=Date(2999,12,31)
)

VAR _ret = '従業員'[部署] in _current_depts

RETURN _ret

ロールの検証で試してみる。ロールとともに「その他のユーザー」にチェックを入れて、ユーザーのメールアドレスに"shiro.yamaoka@touzai.co.jp"を入力する。ええやん💖
（image to be inserted）

なお、_current_depts変数はLOOKUPVALUE関数で計算しても構わないが、このあとコンテキスト トランジションの話をしたいので、CALCULATETABLE関数としている（内部的にはほぼ同じだし）。あと、こっちの方が所属する部署が複数のパターンにも対応できるし。

なぜ動くのか？

各行で、行コンテキストをCALCULATE関数でフィルターコンテキストに変換して（コンテキスト トランジション）、現時点でRLS適用中のユーザーが属する部署を取得。その後、その行の従業員の部署が取得した部署に含まれるかを判定している。

具体的に説明しよう。もう一度計算テーブルのDAX式を見てみる：

'従業員_RLS再現'テーブル
従業員_RLS再現 = FILTER(
    '従業員',
    (
        VAR _userprincipalname = "ryouzo.okaboshi@touzai.co.jp"

        VAR _current_depts = CALCULATETABLE(
            VALUES('従業員'[部署]),
            ALL('従業員'),
            '従業員'[userprincipalname] = _userprincipalname, 
            '従業員'[適用終了日]=Date(2999,12,31)
        )

        VAR _ret = '従業員'[部署] in _current_depts

        RETURN _ret
    )
)

STEP 1: FILTER関数はテーブルをイテレートし行コンテキストを生成している

FILTER関数は'従業員'テーブルをイテレートしている。つまり、'従業員'テーブルの各行で行コンテキストを生成し、FILTER関数の第２引数はその行コンテキストで評価されているということ。

STEP 2: コンテキスト トランジション

FILTER関数の第２引数内の_current_depts変数の定義で、CALCULATETABLE関数を使用している。このCALCULATETABLE関数は行コンテキストをフィルター コンテキストに変換している（コンテキスト トランジション）。なので、FILTER関数でイテレートしている行の列と値の組合わせが、フィルター コンテキストとして追加されている。'従業員'テーブルの4行目の場合は、_current_depts変数は次のように評価されているということ。

VAR _current_depts = CALCULATETABLE(
    CALCULATETABLE(
        VALUES('従業員'[部署]),
        '従業員'[従業員KEY] = "美/海",							 
        '従業員'[従業員ID] = "海",
        '従業員'[名前] = "海原",
        '従業員'[部署] = "美食倶楽部",
        '従業員'[適用開始日] = DATE(2022, 4, 1),
        '従業員'[適用終了日] = DATE(2999, 12, 31),
        '従業員'[userprincipalname] = "yuzan.kaibara@touzai.co.jp",
        '従業員'[備考] = "「美食を芸術の域まで高める条件は、それは唯一、人の心を感動させることだ。そして人の心を感動させることが出来るのは、人の心だけなのだ。材料や技術だけでは駄目だっ！！それがわからぬ人間が究極のメニューなどとぬかしおって、おまえには味を語る資格はないっ！！」"
    ),
    ALL('従業員'),
    '従業員'[userprincipalname] = _userprincipalname, 
    '従業員'[適用終了日]=Date(2999,12,31)
)

（備考のとこがうっとうしいが、名言なので仕方ない）

STEP 3: CALCULATETABLE関数の引数によるコンテキストの変換

外側のCALCULATE関数の第２引数ALL('従業員')で、コンテキスト トランジションで生成されたフィルター コンテキストをキャンセル。第３、４引数が生成するフィルター コンテキストのみで評価されている状態になる。

VAR _current_depts = CALCULATETABLE(
    VALUES('従業員'[部署]),
    '従業員'[userprincipalname] = _userprincipalname, 
    '従業員'[適用終了日]=Date(2999,12,31)
)

結果、従業員KEYが美/山の行が抽出される。
（image to be inserted）従業員KEYが美/山のみ抽出されている図

したがって、_current_depts変数には「美食倶楽部」が格納される。この結果は、FILTER関数がイテレートしている行に依らないことに留意。

STEP 4: FILTER関数の第２引数

FILTER関数がイテレートしている行に依らず、_current_depts変数には「美食倶楽部」が格納されていることから、FILTER関数の結果は次と同じになる：

'従業員_RLS再現'テーブル
従業員_RLS再現 = FILTER(
    '従業員',
    '従業員'[部署] in {"美食倶楽部"}
)

ここまで来れば簡単。'従業員'テーブルのうち、部署が"美食倶楽部"の行が抽出される。

セキュリティ フィルター、データモデル、パフォーマンスの関係

この節の内容は不確かなので、読み飛ばしてもらってもOK。

今回はセキュリティ フィルターが複雑になってしまったが、データ モデルをシンプルに保つことができるので、これで良いと思っている。RLS用に別途テーブルとリレーションを用意して、セキュリティ フィルターの方をシンプルする方法もあるが、今度はデータモデルが複雑になる。メジャーを書くときに「ん？」とならないで欲しいので。

ただ、セキュリティ フィルターが複雑な分、パフォーマンスはよろしくない。今回のケースでは、行ごとにCALCULATETABLE内でALL関数を使用しているため、「従業員」テーブルの行数の２乗も行をスキャンしている。行数が増えたらパフォーマンスには気を付けないとね。

言わずもがなであるが、ファクト テーブルへのRLS設定はNG。RLS下ではセキュリティフィルターがFILTER関数で実行されることはこれまで述べてきた。ファクト テーブル全体にFILTER関数が適用されるなんて、、、非効率すぎる。

とか書いているけれど、↓によるとRlSはキャッシュを活用したりしているので、そう単純でもないみたい。Formula EngineとかStrage EngineとかxmSQLとかよくわからないので、分かったら追記するか別記事を書くかも（↓ではRLS適用時のクエリパフォーマンスはDAX Studioでは調査不可能とかいているが、備忘に書いているとおり調査可能のようにも思えるし）。
https://www.sqlbi.com/articles/security-cost-in-analysis-services-tabular/

備忘

Qiita投稿をメモ帳代わりにしてみる。

RLSの間違った理解

最初は、CALCULATE(テーブル, セキュリティ フィルター)と思っていた。そう思って作成した誤ったセキュリティ フィルターは以下：

誤ったセキュリティ フィルター
VAR _current_depts = CALCULATETABLE(
    VALUES('従業員'[部署]), 
    '従業員'[userprincipalname] = USERPRINCIPALNAME(), 
    '従業員'[適用終了日]=Date(2999,12,31)
)
VAR _ret ='従業員'[部署] in _current_depts
RETURN _ret

上記でRLSを適用すると、次の計算テーブルと同じ結果が得られると思っていた。

従業員_RLS再現（誤り） = 
VAR _userprincipalname = "shiro.yamaoka@touzai.co.jp"

VAR _rls_table = CALCULATETABLE(
    '従業員',
    (
        VAR _current_depts = CALCULATETABLE(
            VALUES('従業員'[部署]), 
            '従業員'[userprincipalname] = _userprincipalname, 
            '従業員'[適用終了日]=Date(2999,12,31)
        )
        VAR _ret ='従業員'[部署] in _current_depts
        RETURN _ret
    )
)

RETURN _rls_table

でも、違った。この計算テーブルの結果自体は意図したものとなっている。
（image to be inserted）

しかし、実際にRLSを検証すると、従業員テーブルはRLS適用中のユーザーの最新の行しか表示されない。
（image to be inserted）

これは以下の計算テーブルの結果と同じ。間違った計算テーブルとの違いは、'従業員'テーブルへのフィルター処理はCALCULATE関数ではなく、FILTER関数で行っている点。

従業員_RLS再現（誤り） = 
VAR _userprincipalname = "shiro.yamaoka@touzai.co.jp"

VAR _rls_table = FILTER(
    '従業員',
    (
        VAR _current_depts = CALCULATETABLE(
            VALUES('従業員'[部署]), 
            '従業員'[userprincipalname] = _userprincipalname, 
            '従業員'[適用終了日]=Date(2999,12,31)
        )
        VAR _ret ='従業員'[部署] in _current_depts
        RETURN _ret
    )
)

RETURN _rls_table

なぜ望んだ結果にならないかというと、_current_depts変数 はFILTER関数の行コンテキストで評価されるため、CALCULATETABLE関数内でコンテキストトランジションが生じているから。コンテキストトランジションにより、FILTER関数でイテレートしている行の列と値の組合わせが、フィルター コンテキストとして追加されている。

例えば、'従業員'テーブルの4行目の場合は、_current_depts変数は次のように評価されているということ。

VAR _current_depts = CALCULATETABLE(
    CALCULATETABLE(
        VALUES('従業員'[部署]),
        '従業員'[従業員KEY] = "美/海",							 
        '従業員'[従業員ID] = "海",
        '従業員'[名前] = "海原",
        '従業員'[部署] = "美食倶楽部",
        '従業員'[適用開始日] = DATE(2022, 4, 1),
        '従業員'[適用終了日] = DATE(2999, 12, 31),
        '従業員'[userprincipalname] = "yuzan.kaibara@touzai.co.jp",
        '従業員'[備考] = "「美食を芸術の域まで高める条件は、それは唯一、人の心を感動させることだ。そして人の心を感動させることが出来るのは、人の心だけなのだ。材料や技術だけでは駄目だっ！！それがわからぬ人間が究極のメニューなどとぬかしおって、おまえには味を語る資格はないっ！！」"
    ),
    '従業員'[userprincipalname] = _userprincipalname, 
    '従業員'[適用終了日]=Date(2999,12,31)
)

（備考はやはり名言。何度読んでも良い。）

'従業員'[従業員KEY] = "美/海"かつ'従業員'[userprincipalname] = "shiro.yamaoka@touzai.co.jp"を満たす行は存在しないため、_current_depts変数の値はブランクとなる。よって、4行目は'従業員'[部署] in _current_deptsを満たさないため、FILTER関数の戻り値には含まれない。

他の行も同様に考えると、_current_depts変数がブランクとならないのは、6行目、つまり現在の山岡さんの情報を格納した行のみとなる。結果、FILTER関数の戻り値も6行目のみとなり、これがRLSを検証して得られた結果となっている。

でも、RLSはCALCULATE(テーブル, セキュリティ フィルター)で計算されている方がパフォーマンス的に良さそう（行数の2倍しかスキャンしないから）なので、何でFILTER関数で実装しているのかがよくわからんと今でも思っているところ。

DAX StudioでRLSを確認する

RLSのルール式で、USREPRINCIPALNAME関数をCUSTOMDATA関数に書き換える。その上で、DAX Studioの接続設定で以下とする：

  • Effective User Name: 何も入力しない
  • Roles: RLSのロール
  • Additional Options: CUSTOMDATA="yamaoka@touzai.co.jp"

CALCULATE関数のブール型フィルター式はFILTER関数のシンタックス シュガー

CALCULATE(
    SUM( Sales[数量] ),
    Products[区分名] = "飲料"
)

equals to

CALCULATE(
    SUM( Sales[数量] ),
    FILTER(
        ALL( Products[区分名] ),
        Products[区分名] = "飲料"
    )
)

参考

次のステップ

15
10
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
15
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?