結論
FUTUREには設定の優先順位がある。
例えば、データベースレベルで指定してるFUTUREがあってもスキーマレベルでFUTUREの設定があると、そちらだけが使われる。
再現するためのSQL
データベースレベルで指定してるFUTUREがスキーマレベルで設定されているFUTUREがあると無視されるという振る舞いを再現するSQLです。
データベースを作成し、grant select on future tables in database を付与する。これにより将来作成されるテーブルへのselect権限が自働で付与されるようにします。
その上で2つのスキーマを作成し、片方には grant select on future tables in schema を設定し、もう片方には何も設定していないスキーマを作成します。
最終的に、2つのスキーマにそれぞれtableを作成しselectをしてます。
/*
ロール作成と権限設定
*/
create role grant_read;
create role grant_read_another;
grant role grant_read to role accountadmin;
grant role grant_read_another to role accountadmin;
/*
データベース作成と権限設定
*/
create database grant_sandbox;
use database grant_sandbox;
-- 作成したデータベースをUsageできるようにする
grant usage on database grant_sandbox to role grant_read;
grant usage on database grant_sandbox to role grant_read_another;
-- 将来grant_sanboxデータベースで作られるtablesへのselect権限をgrant_readロールに付与する
grant select on future tables in database grant_sandbox to role grant_read;
grant select on future tables in database grant_sandbox to role grant_read_another;
/*
スキーマ作成と権限設定
*/
-- スキーマレベルのFuture設定なしスキーマの作成
create schema grant_future_off;
grant usage on schema grant_future_off to role grant_read;
grant usage on schema grant_future_off to role grant_read_another;
-- スキーマレベルのFuture設定ありのスキーマ作成
create schema grant_future_on;
grant usage on schema grant_future_on to role grant_read;
grant usage on schema grant_future_on to role grant_read_another;
-- 将来grant_future_onスキーマで作られるtablesへのselect権限をgrant_readロールに付与する
grant select on future tables in schema grant_future_on to role grant_read;
/*
GRANTの確認
*/
show future grants in schema grant_sandbox.grant_future_off;
/*
show future grants in schema grant_sandbox.grant_future_off;
+------------+-----------+----------+------+----------+--------------+--------------+
| created_on | privilege | grant_on | name | grant_to | grantee_name | grant_option |
|------------+-----------+----------+------+----------+--------------+--------------|
+------------+-----------+----------+------+----------+--------------+--------------+
0 Row(s) produced. Time Elapsed: 0.120s
*/
show future grants in schema grant_sandbox.grant_future_on;
/*
show future grants in schema grant_sandbox.grant_future_on;
+-------------------------------+-----------+----------+---------------------------------------+----------+--------------+--------------+
| created_on | privilege | grant_on | name | grant_to | grantee_name | grant_option |
|-------------------------------+-----------+----------+---------------------------------------+----------+--------------+--------------|
| 2024-02-14 05:25:04.518 +0000 | SELECT | TABLE | GRANT_SANDBOX.GRANT_FUTURE_ON.<TABLE> | ROLE | GRANT_READ | false |
+-------------------------------+-----------+----------+---------------------------------------+----------+--------------+--------------+
1 Row(s) produced. Time Elapsed: 0.123s
*/
show future grants in database grant_sandbox;
/*
show future grants in database grant_sandbox;
+-------------------------------+-----------+----------+-----------------------+----------+--------------------+--------------+
| created_on | privilege | grant_on | name | grant_to | grantee_name | grant_option |
|-------------------------------+-----------+----------+-----------------------+----------+--------------------+--------------|
| 2024-02-14 05:24:53.741 +0000 | SELECT | TABLE | GRANT_SANDBOX.<TABLE> | ROLE | GRANT_READ | false |
| 2024-02-14 05:24:54.089 +0000 | SELECT | TABLE | GRANT_SANDBOX.<TABLE> | ROLE | GRANT_READ_ANOTHER | false |
+-------------------------------+-----------+----------+-----------------------+----------+--------------------+--------------+
2 Row(s) produced. Time Elapsed: 0.095s
*/
/*
それぞれのスキーマにテーブル作成
*/
create or replace table grant_future_on.sample_table (content variant);
create or replace table grant_future_off.sample_table (content variant);
/*
動作チェック
期待してる挙動はそれぞれにSELECTできること
*/
-- 成功するケース
use role grant_read;
select * from grant_future_on.sample_table;
select * from grant_future_off.sample_table;
-- 失敗するケース
use role grant_read_another;
-- データベースレベルで設定されているFUTUREのおかげでselectできる。
select * from grant_future_off.sample_table;
-- スキーマレベルで設定されているFUTUREが優先されるので、grant_reader_anotherはselect権限を持っていない。
-- Object 'GRANT_SANDBOX.GRANT_FUTURE_ON.SAMPLE_TABLE' does not exist or not authorized.
select * from grant_future_on.sample_table;
/*
後始末
*/
use role accountadmin;
drop database grant_sandbox;
drop role grant_read;
drop role grant_read_another;
設定の優先度は、スキーマ > データベースになっている
当たり前と言えば当たり前ですが、データベースレベルで設定しているFUTURE設定があったとしても、スキーマで別のFUTURE設定があると、そちらだけが優先されます。
私は思い込みで、データベースの設定が伝播するもんだと思っていたのですが、思い込みは要注意です😂
色々調べていたら、公式ドキュメントにも丁寧に言及されていました。
この挙動と遭遇したシチュエーション
私の扱っているデータ基盤では、データベースそれぞれにアクセスロールが存在しています。
例えば、PRODデータベースがあるなら、PROD_ADMINとPROD_READERのようなアクセスロールを作成するようにしています。
この2つは、データベースレベルで、複数のオブジェクトタイプに対して、FUTURE権限を付与しています。
この様な運用下で、特定のスキーマだけを参照したいというニーズが出てきました。
このスキーマを SPECIFIC としましょう。このスキーマへアクセスできるアクセスロールを作成します。ここではSPECIFIC_READER を作ったとします。
このロールには、スキーマレベルで grant select on future tables in schema SPECIFIC to role SPECIFIC_READER とやりたくなるわけです。
これで起きることとしては、新しく作られたテーブルへのselect権限は、SPECIFIC_READER にだけ付与されて、上位のロールである PROD_READER や PROD_ADMIN には付与されないことです。
私はてっきりデータベースレベルで設定されているものが、基本的に伝播するものだと思っていたのですが、そうでなかったという話でした。
どう対処したか？
grant role grant_read_another to role PROD_READER;
扱っているデータのポリシーにもよると思いますが、私のケースでは、PROD_READERやPROD_ADMIN などの上位のアクセスロールからは全てのスキーマが見えてる必要があったので、特定スキーマのアクセスロールへ grant roleして対処しました。
Discussion