【Snowflake】権限トラブル時のTips

■ はじめに

https://dk521123.hatenablog.com/entry/2022/08/02/090439
https://dk521123.hatenablog.com/entry/2022/08/18/120935

で、Snowflakeの権限周りのトラブルについて扱ったが、
今後も同じようなトラブルがありそうで、
その際に原因を調べるために必要なSQL文を調べてみた。

なお、付与の仕方は、以下の関連記事を参照のこと

Snowflake ~ GRANT OWNERSHIP ~
https://dk521123.hatenablog.com/entry/2022/02/25/094250

目次

【1】ロールに付与された権限を確認する
【2】所有者を確認する
【3】所有者を変更する
【4】内容を表示する

【1】ロールに付与された権限を確認する

* 「SHOW GRANTS TO ROLE <Role Name>;」で確認可能。

SHOW GRANTS TO ROLE demo_admin;
# 項目名 説明
1 created_on 作成日時 2022-08-18 12:12:11.444-0700
2 privilege 付与した権限の種類 OWNERSHIP, USAGE, SELECT etc
3 granted_on 権限付与対象オブジェクトタイプ DATABASE, ROLE, FILE_FORMAT etc
4 name 権限付与対象オブジェクト名 DabaseならDB名
5 granted_to 権限付与した種類 ROLE
6 grantee_name 権限付与したロール名/ユーザ名
7 grant_option 権限は操作特権付きかどうか true/false
8 granted_by 権限を付与の操作を行ったロール名 SYSADMIN

1)使用上の注意

* ロール名に「.」などが付いている場合(例「user.admin」)は
 「”(ダブルクォーテーション)」で囲む必要がある
 =>「' (シングルクォーテーション)」や「` (バッククォート)」ではダメ!

-- 「”」で囲んだ
SHOW GRANTS TO ROLE "user.admin";

参考文献

https://dev.classmethod.jp/articles/how-to-check-the-pliviledges-granted-to-a-role/

【2】所有者を確認する

* 「SHOW <Object Typeの複数形 (e.g. TABLES)> (LIKE '%<キーワード>%');」で確認可能。

https://docs.snowflake.com/ja/sql-reference/sql/show.html

* 以下の関連記事も参照

https://dk521123.hatenablog.com/entry/2022/02/24/231532

例1:テーブル

-- テーブル一覧が出力される。その中の「owner」で確認可能
SHOW TABLES;

-- 絞り込みたい場合は、LIKEが使える。
SHOW TABLES LIKE '%DEMO%';

例2:ファイルフォーマット

-- ファイルフォーマット一覧が出力される。その中の「owner」で確認可能
SHOW FILE FORMATS;

例3:グラント

show grants;

-- sales データベースで付与されたすべての権限をリスト
show grants on database sales;

+---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------+
| created_on                      | privilege | granted_on | name       | granted_to | grantee_name | grant_option | granted_by   |
|---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------|
| Thu, 07 Jul 2016 05:22:29 -0700 | OWNERSHIP | DATABASE   | REALESTATE | ROLE       | ACCOUNTADMIN | true         | ACCOUNTADMIN |
| Thu, 07 Jul 2016 12:14:12 -0700 | USAGE     | DATABASE   | REALESTATE | ROLE       | PUBLIC       | false        | ACCOUNTADMIN |
+---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------+

https://docs.snowflake.com/ja/sql-reference/sql/show-grants.html

例4:ロール

show roles;

---------------------------------+---------------+------------+------------+--------------+-------------------+------------------+---------------+---------------+--------------------------+
           created_on            |     name      | is_default | is_current | is_inherited | assigned_to_users | granted_to_roles | granted_roles |     owner     |         comment          |
---------------------------------+---------------+------------+------------+--------------+-------------------+------------------+---------------+---------------+--------------------------+
 Fri, 05 Dec 2014 16:25:06 -0800 | ACCOUNTADMIN  | Y          | Y          | N            | 1                 | 0                | 2             |               |                          |
 Mon, 15 Dec 2014 17:58:33 -0800 | ANALYST       | N          | N          | N            | 0                 | 6                | 0             | SECURITYADMIN | Data analyst             |
 Fri, 05 Dec 2014 16:25:06 -0800 | PUBLIC        | N          | N          | Y            | 0                 | 0                | 0             |               |                          |
 Fri, 05 Dec 2014 16:25:06 -0800 | SECURITYADMIN | N          | N          | Y            | 0                 | 1                | 0             |               |                          |
 Fri, 05 Dec 2014 16:25:06 -0800 | SYSADMIN      | N          | N          | Y            | 5                 | 1                | 2             |               |                          |
---------------------------------+---------------+------------+------------+--------------+-------------------+------------------+---------------+---------------+--------------------------+

https://docs.snowflake.com/ja/sql-reference/sql/show-roles.html

【3】所有者を変更する

* GRANT OWNERSHIP を使う
 => 詳細は、以下の関連記事を参照のこと

Snowflake ~ GRANT OWNERSHIP ~
https://dk521123.hatenablog.com/entry/2022/02/25/094250

-- test_db.public.test_table テーブルの所有権をadminロールに変更。
-- その際、現在のアウトバウンド権限すべての破棄し adminロールに付与
grant ownership on table "test_db"."public"."test_table"
  to role admin revoke current grants;

-- test_db.public.test_table2 テーブルの所有権をadminロールに変更。
-- その際、現在のアウトバウンド権限すべてのコピーとともに adminロールに付与
grant ownership on table "test_db"."public"."test_table2"
 to role admin copy current grants;

【4】内容を表示する

* DESC <Object> <ObjectName> を使う
 => 詳細は、以下の関連記事を参照のこと

https://dk521123.hatenablog.com/entry/2022/02/24/231532

例1:ユーザ

-- ユーザ「admin」に関しての情報を表示
DESCRIBE USER "admin";

例2:統合

-- 統合「sample_integ」に関しての情報を表示
DESC INTEGRATION sample_integ;

関連記事

Snowflake ~ 基礎知識編 ~
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ~ 入門編 / Hello world
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ~ GRANT OWNERSHIP ~
https://dk521123.hatenablog.com/entry/2022/02/25/094250
Snowflake ~ SHOW / DESCRIBE ~
https://dk521123.hatenablog.com/entry/2022/02/24/231532
エラー「Insufficient privileges to operate」時の対応
https://dk521123.hatenablog.com/entry/2022/08/02/090439
エラー「Table does not exist or not authorized」時の対応
https://dk521123.hatenablog.com/entry/2022/08/18/120935