この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。
弊社プロダクトのデータ分析基盤「カスタマーストーリー アナリティクス」(Customer Story Analytics:CSA)のサービスである「CSA Job Management Console」(CSA JMC)では、Snowflakeに対して「テーブルの作成」や「ジョブ」による「データ連携」および「SQL」を実行することが可能です。
この「テーブルの作成」や「ジョブ」による「データ連携」および「SQL」を実行する際に、Snowflakeへの接続に利用する「ロール」を明示的に指定できるようになりました。
これにより、Snowflakeへの接続ユーザが利用できる「ロール」であれば、任意の「ロール」に切り替えてクエリを実行することが可能となります。イメージとしては、以下のようなイメージとなります。
例えば、特定の「ロール」でないとアクセスできないデータベースやテーブルに対して、接続設定の「ロール」に限定せずに任意の「ロール」に切り替えてクエリを実行することができます。
当エントリでは、実際に特定の「ロール」でのみ実行できるSQLクエリを実行するジョブを作成して試してみたいと思います。
CSA JMCの挙動確認バージョン
当エントリの内容は以下のCSA JMCバージョンで挙動を確認しています。
- CSA JMC v5.3.0
SQLクエリの準備
まずは、JMCで実行するSQLクエリを準備します。
今回は以下のようにROLE_DEMOデータベースのREGIONテーブルをSELECTするだけのクエリとします。このROLE_DEMOデータベース、および、REGIONテーブルはSYSADMINロールがオーナーとなっており、他のロールには権限を付与していません。
従って、今回JMCで設定している接続情報のロールDEVELOPERではアクセスできないテーブルとなっています。
SELECT R_NAME FROM ROLE_DEMO.PUBLIC.REGION;実際に、それぞれのロールでクエリをsnowsqlで実行して確認します。
DEVELOPERロールでの実行
foo_bar#XSMALL_WH@(no database).(no schema)>USE ROLE DEVELOPER;
╒══════════════════════════════════╕
│ status │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.140s
foo_bar#XSMALL_WH@(no database).(no schema)>USE WAREHOUSE XSMALL_WH;
╒══════════════════════════════════╕
│ status │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.101s
foo_bar#XSMALL_WH@(no database).(no schema)>SELECT R_NAME FROM ROLE_DEMO.PUBLIC.REGION;
002003 (02000): SQL compilation error:
Database 'ROLE_DEMO' does not exist or not authorized.権限が無いので、Database 'ROLE_DEMO' does not exist or not authorized.というエラーになりました。
SYSADMINロールでの実行
foo_bar#XSMALL_WH@(no database).(no schema)>USE ROLE SYSADMIN;
╒══════════════════════════════════╕
│ status │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.129s
foo_bar#XSMALL_WH@(no database).(no schema)>USE WAREHOUSE XSMALL_WH;
╒══════════════════════════════════╕
│ status │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.136s
foo_bar#XSMALL_WH@(no database).(no schema)>SELECT R_NAME FROM ROLE_DEMO.PUBLIC.REGION;
╒═════════════╕
│ R_NAME │
╞═════════════╡
│ AFRICA │
├─────────────┤
│ AMERICA │
├─────────────┤
│ ASIA │
├─────────────┤
│ EUROPE │
├─────────────┤
│ MIDDLE EAST │
╘═════════════╛
5 Row(s) produced. Time Elapsed: 0.160sこちらは問題なくSELECTできていますね。このSQLクエリをcan_select_with_sysadmin_role.sqlとして保存し、JMCに登録しておきます。
ジョブでロールを指定して実行する
次に、ロールにSYSADMINを指定した「ジョブ」を作成し、実際にジョブを実行してみます。
「ジョブ」での具体的なロール指定の箇所はこちらです。
初期画面では接続設定で指定しているロールが設定されているので、ここをプルダウンからSYSADMINへ変更します。
あとは通常通り、「構成要素」として「SQL」のcan_select_with_sysadmin_role.sqlを指定して保存します。
保存したらジョブを実行して、成功しているのを確認します。
無事成功していたので、今度はSnowflake側のクエリ履歴画面から該当クエリIDを特定します。
クエリIDが特定できたら、以下のクエリで本当にSYSADMINロールクエリが実行されたか確認してみます。
QUERY_HISTORYビューで確認
USE ROLE ACCOUNTADMIN;
USE DATABASE SNOWFLAKE;
USE SCHEMA ACCOUNT_USAGE;
SELECT
QUERY_TEXT,
ROLE_NAME,
WAREHOUSE_NAME
FROM
QUERY_HISTORY
WHERE
QUERY_ID = '0198f1a8-00a4-25c2-0000-0949001a823a'
;╒═════════════════════════════════════════════╤═══════════╤════════════════╕
│ QUERY_TEXT │ ROLE_NAME │ WAREHOUSE_NAME │
╞═════════════════════════════════════════════╪═══════════╪════════════════╡
│ SELECT R_NAME FROM ROLE_DEMO.PUBLIC.REGION; │ SYSADMIN │ XSMALL_WH │
╘═════════════════════════════════════════════╧═══════════╧════════════════╛
1 Row(s) produced. Time Elapsed: 0.870s想定どおり、ロールはSYSADMINでクエリが実行されていました！
まとめ
以上、CSA JMCのv5.3.0におけるロールの指定についてのご紹介でした。ジョブ単位でロールが指定できるようになりましたので、指定のロールでジョブを実行したい場合などにご利用いただければと思います。
どなたかのお役に立てば幸いです。それでは！
35
