Amazon RedshiftでのRBACについて②

# はじめに
株式会社ジール所属の@hiroaki_yoshidaです。
普段はAWSやPythonを使った開発を行っています。
開発に携わるにあたり、現場で学んだ事をアウトプットしていこうと思います。

今回の記事は前回に引き続きAmazon Redshift（以下 Redshift）の権限管理についてです。
[前回の記事はここから](https://qiita.com/hiroaki_yoshida/private/d53393fa993404137cb7)
ここでは前回の記事でご説明した、RBACの動きを実際にRedshiftで検証してみたので、その内容を共有します。


***

# RedshiftのRBAC実装についてのアナウンス
AWSの公式ページからは以下の様に発表されています。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/9993756f-3970-cc5b-eae1-aaaac2885b69.png" width=50%>


引用元：https://aws.amazon.com/jp/about-aws/whats-new/2022/04/amazon-redshift-role-based-access-control/
***

# 検証
★前提条件

・ ロールA：スキーマAの参照権限を持っている。ユーザーAに付与している。
・ ロールB：スキーマBの参照権限を持っている。ユーザーBに付与している。
・ ロールC：権限を持っていない。ユーザーCに付与している。

★検証手順
1. ロールA、Bがそれぞれのスキーマしか参照できない事を確認する
2. ロールAをロールCへ継承し、ロールCからスキーマAが参照できるかを確認する
3. ロールBをロールCへ継承し、ロールCからスキーマA,Bが参照できるかを確認する



<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/a0bbea9d-bc04-c2d4-e3e4-b2e7017727a6.png" width=50%>

***
 __実施したSQL__

```SQL
--schemaの作成
CREATE SCHEMA schema_a;
CREATE SCHEMA schema_b;

--tableの作成
CREATE table schema_a.table_1 (text varchar);
CREATE table schema_b.table_2 (text varchar);

--tableに値を入れる
INSERT INTO schema_a.table_1 VALUES ('スキーマaのテーブル1です');
INSERT INTO schema_b.table_2 VALUES ('スキーマbのテーブル2です');

--各ロールへの権限付与
--ロールAへの権限付与
GRANT USAGE ON SCHEMA schema_a TO ROLE role_a;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_a TO ROLE role_a;

--ロールBへの権限付与
GRANT USAGE ON SCHEMA schema_b TO ROLE role_b;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_b TO ROLE role_b;
```


***
## 検証1　ロールA、Bがそれぞれのスキーマしか参照できない事を確認する
 __★実施したSQL__ 
```SQL
SET SESSION AUTHORIZATION 'user_a'; #①
SELECT text FROM schema_a.table_1;　#②
SELECT text FROM schema_b.table_2;　#③
```
 __★それぞれの実行結果__

①の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/af45772c-4722-824e-b6ef-1ad0feff1109.png" width=50%>
②の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/55798e97-e8cf-6ead-ea24-9460dbb7894f.png" width=50%>
③の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/79a8fb69-d983-e330-053c-e6a061daca6a.png" width=50%>

スキーマAのテーブルだけ見えていますね！
今回は記載しませんが、ロールBもスキーマBのテーブルだけ確認できました。

***

## 検証2　ロールAをロールCへ継承し、ロールCからスキーマAが参照できるかを確認する
ロールAからロールCへ権限を継承させます。

 __★ ポイント__ 
ロールCからはどのスキーマにも参照権限はなかったけど、ロールAを継承することでスキーマAのテーブルのみ参照できるようになる。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/7e8de113-0378-926f-df56-a519e2dffa87.png" width=60%>


***
### 継承前に各スキーマのテーブルに対して参照しようとすると・・・
 __★実施したSQL__ 
```SQL
SET SESSION AUTHORIZATION 'user_c'; #①
SELECT text FROM schema_a.table_1;　#②
SELECT text FROM schema_b.table_2;　#③
```
 __★実行結果__

①の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/8ca7968a-0a4c-b88a-ff7e-d9f67991d496.png" width=50%>
②の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/fb1dcc03-286e-953c-ae41-b334effb0ec1.png" width=50%>
③の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/1c9b8241-ea8a-ddd2-ccbe-2017512d3b56.png" width=50%>

当然ですが、現在ロールCにはなんの権限も付与されていないので、両テーブルとも参照できません。

***
では、ロールAからロールCへ継承してみましょう。

 __★ロールAからロールCへ継承させるSQL__

```SQL
--ロールベースでの上位ロールへの権限付与方法
GRANT ROLE role_a TO ROLE role_c;
```
<br/>

たったこれだけです。
一行で済んでしまいます。

***

### 継承後に各スキーマのテーブルに対して参照しようとすると・・・
実行したSQLは先ほどと同じなので割愛させていただきます。

 __★実行結果__
①の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/45d30032-f19a-59e7-3324-2072362aa1fa.png" width=50%>
②の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/a7cfdf6d-0fa2-646e-b364-116ab5799f70.png" width=50%>
③の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/ec5a43cb-bcd9-c770-8685-792064972a6e.png" width=50%>


スキーマAのテーブルだけ参照することができましたね。

***

## ちなみに、ユーザーグループではどのように権限付与するかというと・・・

```SQL
--ユーザーグループでのグループへの権限付与方法
GRANT USAGE ON SCHEMA schema_a TO GROUP group_c;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_a TO GROUP group_c;
```


<br/>

2行かつ、ユーザーグループAに付けた権限と同じ権限を付けないといけないんですね。
管理するスキーマが増えると、ミスが多くなりそうな気がします。


***
## 検証3　ロールBをロールCへ継承し、ロールCからスキーマA,Bが参照できるかを確認する
 __★ロールBからロールCへ継承させるSQL__ 
```SQL
--ロールベースでの上位ロールへの権限付与方法
GRANT ROLE role_b TO ROLE role_c;
```

こちらも先ほど同様で実施したSQLは同じなので割愛させていただきます。

 __★実行結果__
①の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/19a3c3ec-5599-cb15-aefd-3cb27a1ee01e.png" width=50%>
②の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/712edad8-eed8-b70b-4089-6aa38a355e73.png" width=50%>
③の結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2166184/bbc3e6c7-25ad-3ee7-705b-e7d8bdc2dae0.png" width=50%>

 無事両方とも確認できました！

***
# 今回検証で利用したSQLです
参考までに

```SQL
-----------
--検証準備--
-----------

--usergroupの作成
CREATE GROUP group_a;
CREATE GROUP group_b;
CREATE GROUP group_c;

--roleの作成
CREATE ROLE role_a;
CREATE ROLE role_b;
CREATE ROLE role_c;

--userの作成
CREATE USER user_a PASSWORD 'Abc1234567';
CREATE USER user_b PASSWORD 'Abc1234567';
CREATE USER user_c PASSWORD 'Abc1234567';

--schemaの作成
CREATE SCHEMA schema_a;
CREATE SCHEMA schema_b;

--tableの作成
CREATE table schema_a.table_1 (text varchar);
CREATE table schema_b.table_2 (text varchar);

--tableに値を入れる
INSERT INTO schema_a.table_1 VALUES ('スキーマaのテーブル1です');
INSERT INTO schema_b.table_2 VALUES ('スキーマbのテーブル2です');


--各ロールへの権限付与
--ロールAへの権限付与
GRANT USAGE ON SCHEMA schema_a TO ROLE role_a;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_a TO ROLE role_a;

--ロールBへの権限付与
GRANT USAGE ON SCHEMA schema_b TO ROLE role_b;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_b TO ROLE role_b;


--各ユーザーグループへの権限付与
--ユーザーグループAへの権限付与
GRANT USAGE ON SCHEMA schema_a TO GROUP group_a;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_a TO GROUP group_a;

--ユーザーグループBへの権限付与
GRANT USAGE ON SCHEMA schema_b TO GROUP group_b;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_b TO GROUP group_b;

----------------------------
--  検証(ユーザーグループ)  --
----------------------------


--userをusergroupへセット
ALTER GROUP group_a ADD USER user_a;
ALTER GROUP group_b ADD USER user_b;
ALTER GROUP group_c ADD USER user_c;

--検証①-1
--schema_a.table_1だけ見れる
SET SESSION AUTHORIZATION 'user_a';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

--検証①-2
--schema_b.table_2だけ見れる
SET SESSION AUTHORIZATION 'user_b';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

--検証③
--両方見れない
SET SESSION AUTHORIZATION 'user_c';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

--ユーザーグループでの上位グループへの権限付与方法
GRANT USAGE ON SCHEMA schema_a TO GROUP group_c;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_a TO GROUP group_c;

--検証④
--schema_a.table_1だけ見れる
SET SESSION AUTHORIZATION 'user_c';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

--ユーザーグループでの上位グループへの権限付与方法
GRANT USAGE ON SCHEMA schema_b TO GROUP group_c;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_b TO GROUP group_c;

--検証⑤
--両方見れる
SET SESSION AUTHORIZATION 'user_c';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

----------------------------
--   検証(ユーザーロール)   --
----------------------------

--userをroleへセット
GRANT ROLE role_a TO user_a;
GRANT ROLE role_b TO user_b;
GRANT ROLE role_c TO user_c;

--検証①-1
--schema_a.table_1だけ見れる
SET SESSION AUTHORIZATION 'user_a';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

--検証①-2
--schema_b.table_2だけ見れる
SET SESSION AUTHORIZATION 'user_b';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

--検証③
--両方見れない
SET SESSION AUTHORIZATION 'user_c';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

--ロールベースでの上位ロールへの権限付与方法
GRANT ROLE role_a TO ROLE role_c;

--検証④
--schema_a.table_1だけ見れる
SET SESSION AUTHORIZATION 'user_c';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

--ロールベースでの上位ロールへの権限付与方法
GRANT ROLE role_b TO ROLE role_c;

--検証⑤
--両方見れる
SET SESSION AUTHORIZATION 'user_c';
SELECT text FROM schema_a.table_1;
SELECT text FROM schema_b.table_2;

```


***
# まとめ
この記事では2回に渡りRedshiftのRBACについて説明しました。
なんとなくでもRBACを理解することはできましたでしょうか。

とても便利な機能ですが、Redshiftロールと検索しても記事が全然なかったため、今回投稿することにしました。
この記事をみて少しでもお役に立てたら幸いです。

>株式会社ジールでは、「ITリテラシーがない」「初期費用がかけられない」「親切・丁寧な支援がほしい」「ノーコード・ローコードがよい」「運用・保守の手間をかけられない」などのお客様の声を受けて、オールインワン型データ活用プラットフォーム「ZEUSCloud」を月額利用料にてご提供しております。
ご興味がある方は是非下記のリンクをご覧ください：

https://www.zdh.co.jp/products-services/cloud-data/zeuscloud/?utm_source=qiita&utm_medium=referral&utm_campaign=qiita_zeuscloud_content-area