こんにちは、株式会社 エーティーエルシステムズ 鍋島です。
今回はの投稿はPowerBI サービスとサーバーサイドの行レベルセキュリティを検証した記録です。
検証の目的
目的は次の３つの要件を満たすPowerBI レポートの作成することです。
- PowerBIサービスのレポートで、Azureの仮想ネットワーク(VNET)内のSQL Databaseデータを表示したい。
- SQL Databaseの認証は Azure AD認証としたい。
- PowerBI実行ユーザーによる行レベルのセキュリティ(RLS,Row Level Security)をAzure SQL Database側でやりたい。
検証してみたら、手順が長くなりましたので、記事にまとめてみました。
何かの参考になると幸いです。
今回はAzure ADユーザーによる行レベルセキュリティを実現するために 「仮想ネットワークデータゲートウェイ」というプレビュー機能を利用します。
PowerBI Premium Per User ライセンスが必要です。検証では60日間の試用版を利用しています。
本投稿の内容は、2022年1月時点の情報です。
全体の流れ
全体の流れは次のとおりです。
- Azure SQL Databaseで Azure Active Directory認証を有効にして、グループで認証できるようにする。
- データベースを準備して、行レベルのセキュリティを構成する
- Power Platform 管理センターで仮想ネットワークデータゲートウェイを追加する
- PowerBIサービスにプレミアムワークスペースを作成、レポートを作成して発行する
- PowerBIサービスでVNETデータゲートウェイのデータソースを作成して、マップする
- レポートから最終確認。行レベルのセキュリティで、ログインユーザーでフィルターされているのを確認する
参考情報
今回の投稿は以下の2つの記事を参考にしています。とても分かりやすい投稿で感謝しています。
#1. Azure SQL Databaseで Azure Active Directoryの設定
Azure Active Directoryで認証を許可する
SQL DatabaseでAzure ADによるに認証を許可するには、SQL Server リソースで Azure Active Directory認証をするように設定します。
Azureの分かりにくいところですが、SQL データベース側ではなくSQL Server側での設定になりますので、ご注意下さい。
Azure ADにグループを追加する。
AADにグループを追加して、データベースにアクセス許可したい検証用ユーザを追加します。
今回、作成したセキュリティグループ PowerBI-AAD-RLS-TEST に、検証用のユーザーとして 太郎 testtaro@foobarboo.onmicrosoft.com と 花子
testhanako@foobarboo.onmicrosoft.com を 追加してます。
2. 行レベルのセキュリティの構成
SSMSでデータベースに接続する際は、SQL ServerでAzure Active Directory 管理者 に設定したユーザーで接続します。
データベース test-db を事前に準備しています。
検証用テーブルとデータの登録
検証用のUsersテーブルを作成し、検証用のデータを突っ込みます。
CREATE TABLE [dbo].[Users](
[Id] [int] NOT NULL,
[UserName] [varchar](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
データを登録します。PowerBIで接続する予定のユーザー（ここでは、太郎と花子）を追加し、それ以外にも検証用にダミーデータを登録します。
INSERT [dbo].[Users] ([Id], [UserName]) VALUES (1, N'testtaro@foobarboo.onmicrosoft.com')
INSERT [dbo].[Users] ([Id], [UserName]) VALUES (2, N'testhanako@foobarboo.onmicrosoft.com')
INSERT [dbo].[Users] ([Id], [UserName]) VALUES (3, N'foo@example.com')
INSERT [dbo].[Users] ([Id], [UserName]) VALUES (4, N'bar@example.com')
INSERT [dbo].[Users] ([Id], [UserName]) VALUES (5, N'boo@example.com')
グループに選択権限の付与
AzureADのグループ PowerBI-AAD-RLS-TEST をデータベース test-db のユーザーとして追加し、さらにUsersテーブルに対しセレクト権限を付与します。
USE [test-db]
--AzureADのセキュリティグループをユーザーとして追加
CREATE USER [PowerBI-AAD-RLS-TEST] FROM EXTERNAL PROVIDER
-- 選択権限をセキュリティーグループに付与
GRANT SELECT ON Users TO [PowerBI-AAD-RLS-TEST];
セキュリティポリシーを設定
-- Securityという名前のスキーマを作成
CREATE SCHEMA Security;
-- Usersテーブル用の関数を作成
--
CREATE FUNCTION Security.fn_secpredicate_user(@Name AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
1 AS fn_secpredicate_user
WHERE
@Name = USER_NAME() OR USER_NAME() = 'dbo';
USER_NAME関数は、AzureActiveDirectory認証の場合、基本的にはAzureADのメールアドレスが返ってきます。ただし、sysadmin 固定サーバー ロールのメンバーの場合 USER_NAME関数は dbo という文字列を返すので、別途許可しています。
作成した関数を使ってポリシーを作成します。
CREATE SECURITY POLICY Security.Users_Filter
ADD FILTER PREDICATE Security.fn_secpredicate_user(UserName) ON dbo.Users
WITH (STATE = ON);
RLSの完成！SSMSから確認します。
Usersテーブルには4レコード追加していますが、ログインした自分自身の行しか出力されません。
3. 仮想ネットワークデータゲートウェイの追加
公式はMicrosoftのこちらのドキュメントを参照してください。
https://docs.microsoft.com/ja-jp/data-integration/vnet/use-data-gateways-sources-power-bi
Power BI Premium アカウントの準備
まず、Power BI Premium アカウントを用意します。
VNET データ ゲートウェイは Power BI プレミアム ワークスペースと Premium Per User (PPU) ライセンスが必要になります。
本記事ではPPUは60日間の試用版を使用しています。
仮想ネットワーク データ ゲートウェイはプレミアムのみの機能で、Power BI プレミアム ワークスペースと Premium Per User (PPU) でのみパブリック プレビューが可能となります。 ただし、VNet データ ゲートウェイが一般的に利用できるようになった時点で、ライセンス要件が変更される可能性があります。
VNET データゲートウェイを作成する
公式ドキュメントに沿って進めます。手順は、以下のドキュメントが良く書かれていますので、割愛します。
https://docs.microsoft.com/ja-jp/data-integration/vnet/create-data-gateways
手順１：サブスクリプションにサービスプロバイダの登録します。
手順 2: サブネットを Microsoft Power Platform に関連付けます。
手順 3: VNet データ ゲートウェイを作成します。
その前に準備としてPPUのあるユーザーに「ネットワーク共同作成者」のロールを割り当てます。
以下の操作はAzurePortalでサブスクリプションに対して行います。
Power Platform 管理センター にアクセスして、仮想ネットワーク データゲートウェイを新規作成します。
Power Platform 管理センターに戻って 仮想ネットワークデータゲートウェイを新規作成します。
仮想ネットワークデータゲートウェイが作成できました。
PowerBIサービス側から確認します。
4. PowerBIレポートを作成して発行
テーブルを挿入してUsersテーブルのIDとUserNameを表示します。レポートができたら、Premium ワークスペースに発行します。
5. VNETデータゲートウェイにデータソースの追加
発行が完了するとPowerBIサービスでデータセットの設定を行います。
データセットの設定を開いた直後は、「資格情報が正しくないため、データ ソースを更新できません。資格情報を更新して、もう一度お試しください。」と表示されています。これはPowerBIサービスからVNET内のSQLDatabaseに接続できないためです。
データゲートウェイ接続を展開し、アクションの横▼をクリックします。
[VNETに追加]をクリックして、VNETにデータソース「SQL」を追加します。
データソースの情報を入力します。その際「DirectQuery クエリに Azure AD 経由で SSO を使用する」にチェックを入れることでAzure ADのユーザーとしてSQL Databaseに接続するようになります。
作成したデータソース「SQL」をこのデータセットに含まれている データ ソースのマップ先にセットして、「適用」します。
適用後、資格情報のエラーがなくなります。
6. レポートから最終確認
最後に発行したレポートを確認すると、ログインしているアカウントのレコードだけが表示されていることを確認できます。
最後に
仮想ネットワークデータゲートウェイ という プレビュー機能と PowerBI Premium Per User という有償ライセンスを使うことで目的が達成できました。また、仮想ネットワークデータゲートウェイはオンプレミスデータゲートウェイと違い設定が楽な上に、Azure ADによるセキュリティの設定ができるようになります。価格が気になるところですが、GAされるのが楽しみですね。