はじめに

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

今回の記事は前回に引き続きAmazon Redshift（以下 Redshift）の権限管理についてです。
前回の記事はここから
ここでは前回の記事でご説明した、RBACの動きを実際にRedshiftで検証してみたので、その内容を共有します。

RedshiftのRBAC実装についてのアナウンス

AWSの公式ページからは以下の様に発表されています。

引用元：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に付与している。

★検証手順

ロールA、Bがそれぞれのスキーマしか参照できない事を確認する
ロールAをロールCへ継承し、ロールCからスキーマAが参照できるかを確認する
ロールBをロールCへ継承し、ロールCからスキーマA,Bが参照できるかを確認する

実施した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

SET SESSION AUTHORIZATION 'user_a'; #①
SELECT text FROM schema_a.table_1;　#②
SELECT text FROM schema_b.table_2;　#③

★それぞれの実行結果

①の結果

②の結果

③の結果

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

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

ロールAからロールCへ権限を継承させます。

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

継承前に各スキーマのテーブルに対して参照しようとすると・・・

★実施したSQL

SET SESSION AUTHORIZATION 'user_c'; #①
SELECT text FROM schema_a.table_1;　#②
SELECT text FROM schema_b.table_2;　#③

★実行結果

①の結果

②の結果

③の結果

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

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

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

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

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

継承後に各スキーマのテーブルに対して参照しようとすると・・・

実行したSQLは先ほどと同じなので割愛させていただきます。

★実行結果
①の結果

②の結果

③の結果

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

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

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

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

検証3　ロールBをロールCへ継承し、ロールCからスキーマA,Bが参照できるかを確認する

★ロールBからロールCへ継承させるSQL

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

こちらも先ほど同様で実施した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」を月額利用料にてご提供しております。
ご興味がある方は是非下記のリンクをご覧ください：

Amazon RedshiftでのRBACについて②