Auth0からpermission一覧を整形してまとめるまで

## 経緯
RBAC（Role Base Access Control: ロールベースアクセス制御）の設計に当たって、仕様を整備しようとした際の備忘録です。
特に設計の要諦については触れておらず、単にAuth0に登録している権限周りの設定情報をスプシに整理して、権限の良し悪しを俯瞰して見れるところまでをゴールにしています。

## ステップ
1. Auth0から権限設定の情報を取得
1. スプレッドシートにペースト
1. ペーストした情報を元に表に整形
1. スプレッドシート関数の作成

## 1. Auth0から権限設定の情報を取得
[Auth0 Management API](https://auth0.com/docs/authorization/rbac/roles/view-role-permissions#management-api)を使うことで簡単に設定情報を抽出することができます。
設定内容が多くなってくるとAuth0のGUIでポチポチ見ていくのは視認性が悪いので。

上記リンクにも記載されていますが、以下のコマンドで取得していきます。

```zsh
$ curl --request GET \
  --url 'https://YOUR_DOMAIN/api/v2/roles/ROLE_ID/permissions' \
  --header 'authorization: Bearer MGMT_API_ACCESS_TOKEN'
```

---
#### `YOUR_DOMAIN`

Auth0ログイン後の「Dashboard > Applications > APIs」にAuth0 Management APIのエンドポイントが記載されています。
【エンドポイント例】
`https://manage.auth0.com/dashboard/jp/{Tenant_Name}/apis`

この`{Tenant_Namem}`のところにご自身の組織のテナント名を含める必要があります。
![Image 2021-10-22 at 11.04.19 AM.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/31464/d795b666-7ae6-a25e-163e-6d2351530fbc.png)
画像中の「Auth0 Management API」のAPI Audienceをコピーしてください。

---
#### `ROLE_ID`

「Dashboard > User Management > Roles」にロールを登録していることを前提にしていますが、各ロールのリンクを踏むと、ロールIDを取得できます。
![Image 2021-10-22 at 11.10.41 AM.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/31464/1baa267c-fbff-a77f-5c8a-b4c48a244342.png)
![Image 2021-10-22 at 11.11.35 AM.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/31464/11332052-7306-6aeb-0e13-93b8d1f34a90.png)

---
#### `MGMT_API_ACCESS_TOKEN`

「Dashboard > Applications > APIs > Auth0 Mannagement API > API Explorer」まで行くと、GUI上でAPI用のトークンを発行できます。
`https://manage.auth0.com/dashboard/jp/{Tenant_Name}/apis/management/explorer`
![Image 2021-10-22 at 11.15.51 AM.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/31464/bc776d86-1560-d6ef-ff2e-674f88f840c5.png)

---
これでAPIを叩けるようになります。

※PU（パワーユーザー）権限の設定内容を取得する例

```zsh
$ curl --request GET \
  --url 'https://hoge-company/api/v2/roles/rol_yzhuTJiIn6dGgl2W/permissions' \
  --header 'authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCI6Ixxxxxxxxxxxxx' \
  | jq -r '["permission_name", "description", "resource_server_name", "resource_server_identifier"], \
 (.[] | [.permission_name, .description, .resource_server_name, .resource_server_identifier]) \
  | @tsv' > PU.tsv
```

```tsv:PU.tsv
permission_name	description	resource_server_name	resource_server_identifier
create:user	ユーザーの作成権限	HOGE Local	http://localhost
create:company	企業の作成権限	HOGE Local	http://localhost
...
```

## 2. スプレッドシートにペースト

Google Spreadsheetを開き、おもむろに先ほどのtsvファイルの内容をペーストします。
この時、後段のVLOOKUPのためにB列に「role」という列を挿入しています。
シート名は「Auth0」としました。
![ss 2021-10-22 11.40.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/31464/a7bd22f7-50d1-94d5-7142-9d7122ab41af.png)
※ダミーデータとして、パーキング管理システムを例に権限設定データを作成しました。

## 3. ペーストした情報を元に表に整形
完成したシート
![ss 2021-10-22 11.42.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/31464/bef5e8cd-1975-cf06-84dc-99ee9813b0b2.png)
このように
操作対象のリソース（resource）と、それに対応する権限（role）をマトリクス整理したシートを動的に表示するシートが完成しました。
ねらいとしては、このようにマトリクスで俯瞰して見ることで

> 「あっ、この権限でこのリソースいじれちゃマズイよね」

ってことも気づき易くなるのでは、と思ったからです。


#### 【権限まとめ表作成の手順】
1. Auth0の権限は「操作名:リソース名」という名前になっているので分割 （例. 「create:user」 => 「create」「user」）
2. まとめ表の縦列を動的に取得（例. user, company, ..)
3. まとめ表の横軸に権限名をベタ書き（例. PU, RO, SS）
4. 「Auth0」シートの内容を動的に取得するセルの関数（複雑！）を書いていく


---
#### 関数

_permission \ role_ | PU | RO | SS
-- | -- | -- | --
`=UNIQUE(Auth0!H2:H52)` | `=IFNA(JOIN(CHAR(10),IF(VLOOKUP("*"&$A2,INDIRECT("Auth0!"&ADDRESS(MAX(IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), MATCH(B$1,Auth0!$B:$B,0)),COLUMN(Auth0!$A:$A),1,TRUE)&":"&ADDRESS(MAX(IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), IF(C$1<>"",MATCH(C$1,Auth0!$B:$B,0)-1,MATCH("",Auth0!$B:$B,-1))),COLUMN(Auth0!$B:$B),1,TRUE)),2,FALSE)=B$1,QUERY(INDIRECT("Auth0!"&ADDRESS(MAX(IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), MATCH(B$1,Auth0!$B:$B,0)),COLUMN(Auth0!$A:$A),1,TRUE)&":"&ADDRESS(MAX(IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), IF(C$1<>"",MATCH(C$1,Auth0!$B:$B,0)-1,MATCH("",Auth0!$B:$B,-1))),COLUMN(Auth0!$B:$B),1,TRUE)),"SELECT A  WHERE A LIKE '%"&$A2&"' AND B = '"&B$1&"'"),"")),"")` | （同左）　| （同左）
 | （同上） | （同上）　| （同上）


#### 出力結果

_permission \ role_ | PU | RO | SS
-- | -- | -- | --
user | create:user<br>read:user | create:own-user<br>delete:own-other-user<br>read:own-user<br>update:own-user| create:self-user<br>read:own-user<br>update:self-user
company | create:company<br>delete:company<br>read:company<br>read:own-company<br>update:company | create:own-company<br>read:own-company<br>update:own-company| read:own-company

## 4. スプレッドシート関数の作成

以下の長ったらしい関数で何をしているのかを解説します。

```apex
=IFNA(JOIN(CHAR(10),IF(VLOOKUP("*"&$A2,INDIRECT("Auth0!"&ADDRESS(MAX(IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), MATCH(B$1,Auth0!$B:$B,0)),COLUMN(Auth0!$A:$A),1,TRUE)&":"&ADDRESS(MAX(IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), IF(C$1<>"",MATCH(C$1,Auth0!$B:$B,0)-1,MATCH("",Auth0!$B:$B,-1))),COLUMN(Auth0!$B:$B),1,TRUE)),2,FALSE)=B$1,QUERY(INDIRECT("Auth0!"&ADDRESS(MAX(IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), MATCH(B$1,Auth0!$B:$B,0)),COLUMN(Auth0!$A:$A),1,TRUE)&":"&ADDRESS(MAX(IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), IF(C$1<>"",MATCH(C$1,Auth0!$B:$B,0)-1,MATCH("",Auth0!$B:$B,-1))),COLUMN(Auth0!$B:$B),1,TRUE)),"SELECT A WHERE A LIKE '%"&$A2&"' AND B = '"&B$1&"'"),"")),"")
```
↓展開

```apex
=IFNA( // #N/Aとなった場合に空文字""で置き換え
	JOIN( // 取得結果がリストの場合、セル内で改行文字と連結
		CHAR(10), // 改行文字
		IF( // もしVLOOKUPの結果=ロール名ならQUERYで文字列検索 OR 空文字""
			VLOOKUP( // A列のリソース名（例: user）で「Auth0」シートを検索し、該当するロール名（例: PU）を取得
				"*"&$A2, // ワイルドカード"*"と検索キーワードであるリソース名（例: user）を「&」で連携
				INDIRECT(　// 「Auth0」シートで現在のロール（例: PU）が記載されているセル範囲を動的に取得する（例: Auth0!$A$2:$B$15）
					"Auth0!"&ADDRESS( // 取得したいセル範囲の先頭セル番地（例: $A$2）を取得
						MAX( // 現在の権限（例: PU)が最初に現れる行番号を取得
							IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B), ""), // 後続のMATCH関数が該当なしの際に先頭行番号「2」を取得
							MATCH(B$1, Auth0!$B:$B, 0) // 現在のロール（例: PU）が最初に現れる行番号を取得
						),
						COLUMN(Auth0!$A:$A), // 取得したい先頭セル番地が存在する列番号「1」を取得
						1, // 行列指定が絶対参照（$あり）なら１、相対参照（$なし）なら0
						TRUE // A1表記ならTRUE,
R1C1表記ならFALSE
					)&":"&ADDRESS( // 取得したいセル範囲の最後尾セル番地（例: $B$15）を取得
						MAX( // 現在の権限（例: PU)が最後に現れる行番号を取得
							IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B), ""), // 後続のIF関数内のMATCH関数が該当なしの際に先頭行番号「2」を取得
							IF(C$1<>"", MATCH(C$1,Auth0!$B:$B,0)-1, MATCH("",Auth0!$B:$B,-1)) // 「RBACまとめ」シートの1行目の現在の権限がさらに右列にも存在しない場合は「Auth0」シートの最終行を取得
						),
						COLUMN(Auth0!$B:$B), // 取得したい最後尾セル番地が存在する列番号「2」を取得
						1,
						TRUE
					)
				),
				2,
				FALSE
			)=B$1,
			QUERY( // INDIRECTで取得した範囲に対して、SELECT文を発行
				INDIRECT( // 文字列検索範囲を取得する
					"Auth0!"&ADDRESS( // 取得したいセル範囲の先頭セル番地（例: $A$2）を取得
						MAX( // 現在のロール（例: PU)が最初に現れる行番号を取得
							IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), // 後続のMATCH関数が該当なしの際に先頭行番号「2」を取得
							MATCH(B$1, Auth0!$B:$B, 0) // 現在のロール（例: PU）が最初に現れる行番号を取得
						),
						COLUMN(Auth0!$A:$A), // 取得したい権限名が存在する列番号「1」を取得
						1,
						TRUE
					)&":"&ADDRESS( // 取得したいセル範囲の最後尾セル番地（例: $B$15）を取得
						MAX( // 現在のロール（例: PU)が最後に現れる行番号を取得
							IF(Auth0!$B:$B<>"", COLUMN(Auth0!$B:$B),""), // 後続のIF関数内のMATCH関数が該当なしの際に先頭行番号「2」を取得
							IF(C$1<>"", MATCH(C$1,Auth0!$B:$B,0)-1, MATCH("",Auth0!$B:$B,-1)) // 「RBACまとめ」シートの1行目の現在の権限がさらに右列にも存在しない場合は「Auth0」シートの最終行を取得
						),
						COLUMN(Auth0!$B:$B), // 取得したい最後尾セル番地が存在する列番号「2」を取得
						1,
						TRUE
					)
				),
				"SELECT A WHERE A LIKE '%"&$A2&"' AND B = '"&B$1&"'" // 「Auth0」シートのA列が検索したいリソース名（例: user）に該当し、かつ同シートB列が検索したいロール（例: PU）に該当する場合、その権限名（例: create:user）を取得する
			),
			""
		)
	)
	,""
)
```

---
### 完成ファイル

[RBACテスト.xlsx](https://github.com/hatatouille/rbac-spreadsheet-sample/blob/main/RBACテスト.xlsx)

ご査収ください。