LoginSignup
8
7
個人開発エンジニア応援 - 個人開発の成果や知見を共有しよう！-
@changkuhu(chang)

Snowflakeの権限付与で実行するSQLをある程度書き出してみた

Posted at

概要

Snowflake上にカスタムロールを作成して各種権限を与えたい時、公式ドキュメントの記載を見て書き方を解読するのが難しかったりするので、実行して上手くったものをまとめてみた。全部書くと膨大なので、ある程度の書き出しという点はご了承いただきたい。

  • 公式の権限一覧についてはこちら。
  • 権限付与の操作(GRANT)についてはこちら。
  • 所有権の操作(GRANT OWNERSHIP)についてはこちら。

ウェアハウス

やりたいこと 付与する権限 権限付与できるロール 権限を付与するクエリ
ウェアハウスの作成権限を与えたい CREATE WAREHOUSE ACCOUNTADMIN GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE MYROLE;
ウェアハウスの所有権を移譲したい OWNERSHIP ウェアハウスの所有者 GRANT OWNERSHIP ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
ウェアハウスの使用権を与えたい USAGE ウェアハウスの所有者 GRANT USAGE ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
ウェアハウスの起動・一時停止ができる権限を与えたい OPERATE ウェアハウスの所有者 GRANT OPERATE ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
ウェアハウスの設定を変更できる権限を与えたい MODIFY ウェアハウスの所有者 GRANT MODIFY ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
ウェアハウスの全権限権限を与えたい ALL(OWNERSHIP以外の権限)(※1) ウェアハウスの所有者 GRANT ALL ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
アカウント内に存在する全てのウェアハウスの管理権限を与えたい MANAGE WAREHOUSES(※2) ACCOUNTADMIN GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE OTHERROLE;

(※1) ALLという権限がつくのではなく、このワンコマンドで4つの権限(USAGE,OPERATE,MODIFY,MONITOR)をつけるというもの。
(※2) アカウント内のすべてのウェアハウスに対するMODIFY,MONITOR,OPERATEに相当する「MANAGE WAREHOUSES」という権限を与えるとのこと。USAGE権限は含まれないので個々のウェアハウスは使用(USE WAREHOUSE〜)できない。

データベース

以下に出てくる、MYROLEやOTHERROLEはカスタムロール名を指す。

USE ROLE USERADMIN;
CREATE ROLE MYROLE;
CREATE ROLE OTHERROLE;
CREATE ROLE GUESTROLE;
やりたいこと 付与する権限 権限付与できるロール 権限を付与するクエリ
データベースを作成する権限を与えたい CREATE DATABASE ROLE ACCOUNTADMIN GRANT CREATE DATABASE ON ACCOUNT TO ROLE MYROLE;
データベースの所有権を移譲したい OWNERSHIP データベースの所有者(※1) GRANT OWNERSHIP ON DATABASE MY_DB TO ROLE OTHERROLE;
データベースの使用権を与えたい USAGE データベースの所有者 GRANT USAGE ON DATABASE MY_DB TO ROLE OTHERROLE;
データベース内にスキーマを作成する権限を与えたい CREATE SCHEMA データベースの所有者 GRANT CREATE SCHEMA ON DATABASE MY_DB TO ROLE OTHERROLE;
データベースの全権限権限を与えたい ALL(OWNERSHIP以外の権限) データベースの所有者 GRANT ALL ON DATABASE MY_DB TO ROLE OTHERROLE;
データベース内に存在する全てのスキーマの使用権を与えたい USAGE データベースの所有者 GRANT USAGE ON ALL SCHEMAS IN DATABASE MY_DB TO ROLE OTHERROLE;
データベース内に将来作成されるスキーマの使用権を与えたい USAGE SECURITYADMIN GRANT USAGE ON FUTURE SCHEMAS IN DATABASE MY_DB TO ROLE OTHERROLE;
データベースロールの作成権限を与えたい CREATE DATABASE ROLE データベースの所有者 GRANT CREATE DATABASE ROLE ON DATABASE MY_DB TO ROLE OTHERROLE;

(※1) 試してみたところ、ACCOUNTADMIN、SECURITYADMINでも可能。

スキーマ

前提としてデータベースに対して使用権がないとスキーマに手出しができない点に注意。

GRANT USAGE ON DATABASE MY_DB TO ROLE OTHERROLE;
やりたいこと 付与する権限 権限付与できるロール 権限を付与するクエリ
スキーマの作成権限を与えたい CREATE SCHEMA データベースの所有者 GRANT CREATE SCHEMA ON DATABASE MY_DB TO ROLE OTHERROLE;
スキーマの所有権を移譲したい OWNERSHIP スキーマの所有者(※1) GRANT OWNERSHIP ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;(※2)
スキーマの使用権を与えたい USAGE スキーマの所有者 GRANT USAGE ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
スキーマ内でテーブルを作成する権限を与えたい CREATE TABLE(※3) スキーマの所有者 GRANT CREATE TABLE ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
スキーマの全権限権限を与えたい ALL(OWNERSHIP以外の権限) スキーマの所有者 GRANT ALL ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
スキーマ内に存在する全てのテーブルにSELECT権を与えたい SELECT データベースの所有者 GRANT SELECT ON ALL TABLES IN SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
スキーマ内に将来作成されるテーブルにSELECT権を与えたい(※4) SELECT SECURITYADMIN GRANT SELECT ON FUTURE TABLES IN SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;

(※1) データベース所有者であっても、他の誰かが作ったスキーマを勝手にいじれないようになっている。所有権は強い。
ただ、データベース所有者はデータベース自体の削除(DROP)はできてしまうので、その点は注意。
(※2) OWNERSHIPを与えようとしているスキーマに対してUSAGE権限を持っていると「SQL実行エラー：ロール「OTHERROLE」に対するセキュリティ保護可能な「MY_DB.MY_SCHEMA」の権限「USAGE」の依存付与が存在します。最初に取り消す必要があります。 複数の依存付与が存在する場合があります：「SHOW GRANTS」コマンドを使用してそれらを表示します。オブジェクトの所有権を転送している間にすべての依存付与を取り消すには、便利なコマンド「 <target_role> への <target_objects> の所有権の付与。現在の付与を取り消す」を使用します。」といったエラーが出る。あらかじめREVOKE USAGE ON SCHEMA MY_DB.MY_SCHEMA FROM ROLE OTHERROLE;で権限を外しておくこと。
(※3) CREATE VIEW等、他のオブジェクトも同様。
(※4) 「将来作成される」というのがポイント。例えば、ある特定のテーブルにのみSELECTを許可を与えていた場合、そのテーブルが削除・再作成されると権限が消えてしまう模様。そんな時のために将来作成されるテーブルに対する権限が必要。ただし「将来作成されるテーブル全て」が権限付与の対象になるので注意。特定テーブルにのみ継続的に権限を与えたい場合は、スキーマ単位で区切るのが良さそうか。

データベースロール

新しい機能。詳細はこちら。
要はデータベースに対して直接アクセス権を定義・設定できるようなもので、これまでのようにアクセスロールとして各種データベースへの権限を作成していたものが不要になり、見かけ上のロール数が減る。役割(ファンクショナルロール)に対してデータベースロールの使用権を付与するような使い方になるイメージ。

前提となるデータベースロールは作成しておくこと。

USE ROLE MYROLE;
CREATE DATABASE ROLE MYDATABASEROLE;
やりたいこと 付与する権限 権限付与できるロール 権限を付与するクエリ
スキーマの使用権を与えたい USAGE データベースの所有者 GRANT USAGE ON SCHEMA MY_DB.MY_SCHEMA TO DATABASE ROLE MY_DB.MYDATABASEROLE;
テーブルのSELECT権限を与えたい SELECT データベースロールの所有者 GRANT SELECT ON TABLE MY_DB.MY_SCHEMA.MY_TABLE TO DATABASE ROLE MYDATABASEROLE;
スキーマ内に存在する全てのテーブルにSELECT権限を与えたい SELECT データベースロールの所有者 GRANT SELECT ON ALL TABLES IN SCHEMA MY_DB.MY_SCHEMA TO DATABASE ROLE MY_DB.MYDATABASEROLE;
スキーマ内に将来作成されるテーブルにSELECT権を与えたい SELECT SECURITYADMIN GRANT SELECT ON FUTURE TABLES IN SCHEMA MY_DB.MY_SCHEMA TO DATABASE ROLE MY_DB.MYDATABASEROLE;

...等。

データベースロールをロールで使用するには、ロールをロールに付与する時と同じ要領で操作すると良い。

USE ROLE MYROLE;
GRANT DATABASE ROLE MY_DB.MYDATABASEROLE TO ROLE GUESTROLE;

データベースロールが割り当てられたロールを使用して、その権限を行使できる。

USE ROLE GUESTROLE;
SELECT * FROM MY_DB.MY_SCHEMA.MY_TABLE;

なお、SHOW GRANTS TO ROLE で割り当てられているデータベースロールを見ることができる。

SHOW GRANTS TO ROLE GUESTROLE;

使用感はこれまでのファンクショナルロール+アクセスロールと同じような感じ。
アクセスロールを定義するとロール選択時にロールが大量に出てくるので、その煩わしさからは解放されそう。

テーブルの各種権限

前提として、データベース、スキーマのUSAGEが許可されている必要がある。

GRANT USAGE ON DATABASE MY_DB TO ROLE OTHERROLE;
GRANT USAGE ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
やりたいこと 付与する権限 権限付与できるロール 権限を付与するクエリ
テーブルのSELECT権限を与えたい SELECT テーブルの所有者 GRANT SELECT ON TABLE MY_DB.MY_SCHEMA.MY_TABLE TO ROLE MYROLE;
テーブルのINSERT権限を与えたい INSERT テーブルの所有者 GRANT INSERT ON TABLE MY_DB.MY_SCHEMA.MY_TABLE TO ROLE MYROLE;

同じ要領で、UPDATE、DELETE、TRUNCATEも権限付与ができる。
また、「ALL」でOWNERSHIP権限以外の全てが付与できるのも同様。

タスクの各種権限

やりたいこと 付与する権限 権限付与できるロール 権限を付与するクエリ
タスクの実行権限を与えたい EXECUTE TASK ACCOUNTADMIN GRANT EXECUTE TASK ON ACCOUNT TO ROLE MYROLE;
サーバレスタスクの実行権限を与えたい EXECUTE MANAGED TASK ACCOUNTADMIN GRANT EXECUTE MANAGED TASK ON ACCOUNT TO ROLE MYROLE;
タスク情報の参照権限を与えたい MONITOR タスクの所有者 GRANT MONITOR ON TASK MY_TASK TO ROLE OTHERROLE;

「ALL」でOWNERSHIP権限以外の全てが付与できるのも同様。

その他ACCOUNTADMIN権限を使う必要があるもの

使用状況と請求履歴のモニター

特にSQL等は実行できなくてもいいけど、Snowsightにログインして、利用状況を見たいときに必要な権限。

やりたいこと 付与する権限 権限付与できるロール 権限を付与するクエリ
- WebUI(Snowsight)でSnowflakeの使用状況と請求情報を表示する権限を与えたい
- Information Schemaで対応するテーブル関数をクエリできる権限を与えたい(※)		 MONITOR USAGE ACCOUNTADMIN GRANT MONITOR USAGE ON ACCOUNT TO ROLE MYROLE;

(※)2つのことができるようになる。分離はできない模様。

Snowflakeデータベース

Snowflakeデータベースは、Snowflakeアカウントを作成すると必ずついてくるデータベース。利用状況等のログ関係が格納されるもので、読み取り専用の領域。

運用管理のためにSnowflakeのログを見るだけの権限を与えたい時にどうぞ。

やりたいこと 付与する権限 権限付与できるロール 権限を付与するクエリ
SNOWFLAKEデータベースにある全てのACCOUNT USAGEビューをクエリできる権限を与えたい IMPORTED PRIVILEGES ACCOUNTADMIN GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE MYROLE;

シェアされたテーブルと同じ扱いということがわかる。

Snowflakeには機能がどんどん増えていくので、権限周りもどんどん増えるはず。
どんどん追記していこうかな。

おまけ

上記を試せるSQLワークシート。

SQLワークシート 
-- 準備
USE ROLE USERADMIN;
CREATE OR REPLACE ROLE MYROLE;
CREATE OR REPLACE ROLE OTHERROLE;
CREATE OR REPLACE ROLE GUESTROLE;
GRANT ROLE MYROLE TO USER ユーザー名;
GRANT ROLE OTHERROLE TO USER ユーザー名;
GRANT ROLE GUESTROLE TO USER ユーザー名;
GRANT ROLE MYROLE TO ROLE SYSADMIN;
GRANT ROLE OTHERROLE TO ROLE SYSADMIN;
GRANT ROLE GUESTROLE TO ROLE SYSADMIN;


-- ウェアハウス
USE ROLE ACCOUNTADMIN;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE MYROLE;

-- GRANT OWNERSHIP ON WAREHOUSE
USE ROLE MYROLE;
CREATE OR REPLACE WAREHOUSE MY_WH WAREHOUSE_SIZE='X-SMALL' INITIALLY_SUSPENDED=TRUE;
SHOW GRANTS TO ROLE MYROLE;
USE WAREHOUSE MY_WH;
GRANT OWNERSHIP ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
SHOW GRANTS TO ROLE MYROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE WAREHOUSE MY_WH;
GRANT OWNERSHIP ON WAREHOUSE MY_WH TO ROLE MYROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE ROLE MYROLE;
SHOW GRANTS TO ROLE MYROLE;

-- GRANT USAGE ON WAREHOUSE
USE ROLE MYROLE;
GRANT USAGE ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE WAREHOUSE MY_WH;

-- GRANT OPERATE ON WAREHOUSE
USE ROLE MYROLE;
GRANT OPERATE ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
ALTER WAREHOUSE MY_WH RESUME;
ALTER WAREHOUSE MY_WH SUSPEND;

-- GRANT MODIFY ON WAREHOUSE
USE ROLE MYROLE;
GRANT MODIFY ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
SHOW WAREHOUSES;
ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE='MEDIUM';
SHOW WAREHOUSES;
USE ROLE MYROLE;
ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE='X-SMALL';
SHOW WAREHOUSES;

-- GRANT ALL ON WAREHOUSE
-- -> 指定したウェアハウスに対するMODIFY,MONITOR,OPERATE,USAGE
USE ROLE MYROLE;
REVOKE USAGE, OPERATE, MODIFY ON WAREHOUSE MY_WH FROM ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE ROLE MYROLE;
GRANT ALL ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
ALTER WAREHOUSE MY_WH RESUME;
ALTER WAREHOUSE MY_WH SUSPEND;
ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE='MEDIUM';
ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE='X-SMALL';
DESC WAREHOUSE MY_WH;
USE WAREHOUSE MY_WH;
USE ROLE MYROLE;
REVOKE ALL ON WAREHOUSE MY_WH FROM ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;

-- GRANT MANAGE WAREHOUSES ON ACCOUNT
-- -> アカウント内のすべてのウェアハウスに対するMODIFY,MONITOR,OPERATE 
USE ROLE ACCOUNTADMIN;
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;

ALTER WAREHOUSE MY_WH RESUME;
ALTER WAREHOUSE MY_WH SUSPEND;
ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE='MEDIUM';
ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE='X-SMALL';
DESC WAREHOUSE MY_WH;
-- USAGE権限は含まないのでウェアハウスは使用できない
USE WAREHOUSE MY_WH; -- エラー

USE ROLE ACCOUNTADMIN;
REVOKE MANAGE WAREHOUSES ON ACCOUNT FROM ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;

-- データベース
USE ROLE ACCOUNTADMIN;
GRANT CREATE DATABASE ON ACCOUNT TO ROLE MYROLE;
SHOW GRANTS TO ROLE MYROLE;

-- GRANT OWNERSHIP ON DATABASE
USE ROLE MYROLE;
CREATE OR REPLACE DATABASE MY_DB;
SHOW GRANTS TO ROLE MYROLE;
GRANT OWNERSHIP ON DATABASE MY_DB TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
GRANT OWNERSHIP ON DATABASE MY_DB TO ROLE MYROLE;
USE ROLE MYROLE;
SHOW GRANTS TO ROLE MYROLE;

USE ROLE SECURITYADMIN;
SHOW GRANTS TO ROLE MYROLE;
GRANT OWNERSHIP ON DATABASE MY_DB TO ROLE OTHERROLE;
SHOW GRANTS TO ROLE MYROLE;
SHOW GRANTS TO ROLE OTHERROLE;
GRANT OWNERSHIP ON DATABASE MY_DB TO ROLE MYROLE;
SHOW GRANTS TO ROLE MYROLE;
SHOW GRANTS TO ROLE OTHERROLE;

-- GRANT USAGE ON DATABASE
USE ROLE MYROLE;
SHOW GRANTS TO ROLE MYROLE;
GRANT USAGE ON DATABASE MY_DB TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE DATABASE MY_DB;

-- GRANT CREATE SCHEMA ON DATABASE
USE ROLE MYROLE;
SHOW GRANTS TO ROLE MYROLE;
GRANT CREATE SCHEMA ON DATABASE MY_DB TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE DATABASE MY_DB;
CREATE OR REPLACE SCHEMA MY_SCHEMA;
DROP SCHEMA MY_SCHEMA; 

-- GRANT ALL ON DATABASE
USE ROLE MYROLE;
GRANT ALL ON DATABASE MY_DB TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
-- USE ROLE MYROLE;
-- REVOKE ALL ON DATABASE MY_DB FROM ROLE OTHERROLE;
-- USE ROLE OTHERROLE;
-- SHOW GRANTS TO ROLE OTHERROLE;

-- GRANT USAGE ON ALL SCHEMAS IN DATABASE
USE ROLE MYROLE;
USE DATABASE MY_DB;
CREATE SCHEMA MY_SCHEMA;
GRANT USAGE ON ALL SCHEMAS IN DATABASE MY_DB TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;

-- GRANT USAGE ON FUTURE SCHEMAS IN DATABASE
USE ROLE SECURITYADMIN;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE MY_DB TO ROLE OTHERROLE;
USE ROLE MYROLE;
USE DATABASE MY_DB;
CREATE OR REPLACE SCHEMA MY_SCHEMA_2;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA_2;
USE ROLE MYROLE;
DROP SCHEMA MY_DB.MY_SCHEMA_2;

-- スキーマ
USE ROLE MYROLE;
GRANT CREATE SCHEMA ON DATABASE MY_DB TO ROLE OTHERROLE;

-- GRANT OWNERSHIP ON SCHEMA
USE ROLE MYROLE;
CREATE OR REPLACE SCHEMA MY_DB.MY_SCHEMA;
SHOW GRANTS TO ROLE MYROLE;
REVOKE USAGE ON SCHEMA MY_DB.MY_SCHEMA FROM ROLE OTHERROLE;
GRANT OWNERSHIP ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
SHOW GRANTS TO ROLE MYROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE ROLE MYROLE;
-- DB所有者でもSCNEMAの所有権を持っていないと削除などできない
DROP SCHEMA MY_DB.MY_SCHEMA; -- エラー
USE ROLE OTHERROLE;
GRANT OWNERSHIP ON SCHEMA MY_DB.MY_SCHEMA TO ROLE MYROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE ROLE MYROLE;
SHOW GRANTS TO ROLE MYROLE;

-- GRANT USAGE ON SCHEMA
USE ROLE MYROLE;
GRANT USAGE ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;

-- GRANT CREATE TABLE ON SCHEMA
USE ROLE MYROLE;
GRANT CREATE TABLE ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;
CREATE OR REPLACE TABLE MY_DB.MY_SCHEMA.MY_TABLE (ID NUMBER, NAME VARCHAR);

USE ROLE MYROLE;
GRANT USAGE ON WAREHOUSE MY_WH TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
USE WAREHOUSE MY_WH;
INSERT INTO MY_DB.MY_SCHEMA.MY_TABLE VALUES(1, 'user_name');
SELECT * FROM MY_DB.MY_SCHEMA.MY_TABLE;
DROP TABLE MY_DB.MY_SCHEMA.MY_TABLE;

-- GRANT ALL ON SCHEMA
USE ROLE MYROLE;
GRANT ALL ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
-- USE ROLE MYROLE;
-- REVOKE ALL ON SCHEMA MY_DB.MY_SCHEMA FROM ROLE OTHERROLE;
-- USE ROLE OTHERROLE;
-- SHOW GRANTS TO ROLE OTHERROLE;

-- GRANT SELECT ON ALL TABLES IN SCHEMA
USE ROLE MYROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;
CREATE OR REPLACE TABLE MY_DB.MY_SCHEMA.MY_TABLE (ID NUMBER, NAME VARCHAR);
USE WAREHOUSE MY_WH;
INSERT INTO MY_DB.MY_SCHEMA.MY_TABLE VALUES(1, 'user_name');
GRANT SELECT ON ALL TABLES IN SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;
SELECT * FROM MY_DB.MY_SCHEMA.MY_TABLE;
USE ROLE MYROLE;
DROP TABLE MY_DB.MY_SCHEMA.MY_TABLE;

-- GRANT SELECT ON FUTURE TABLES IN SCHEMA
USE ROLE SECURITYADMIN;
GRANT SELECT ON FUTURE TABLES IN SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE ROLE MYROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;
CREATE OR REPLACE TABLE MY_DB.MY_SCHEMA.MY_TABLE (ID NUMBER, NAME VARCHAR);
USE WAREHOUSE MY_WH;
INSERT INTO MY_DB.MY_SCHEMA.MY_TABLE VALUES(1, 'user_name');
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;
SELECT * FROM MY_DB.MY_SCHEMA.MY_TABLE;
USE ROLE MYROLE;
DROP TABLE MY_DB.MY_SCHEMA.MY_TABLE;

-- データベースロール
USE ROLE MYROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;
CREATE OR REPLACE TABLE MY_DB.MY_SCHEMA.MY_TABLE (ID NUMBER, NAME VARCHAR);
USE WAREHOUSE MY_WH;
INSERT INTO MY_DB.MY_SCHEMA.MY_TABLE VALUES(1, 'user_name');
GRANT SELECT ON ALL TABLES IN SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;

-- CREATE DATABASE ROLE
USE ROLE MYROLE;
GRANT CREATE DATABASE ROLE ON DATABASE MY_DB TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;

-- GRANT USAGE ON SCHEMA ... TO DATABASE ROLE
USE ROLE MYROLE;
USE DATABASE MY_DB;
CREATE OR REPLACE DATABASE ROLE MYDATABASEROLE;
SHOW DATABASE ROLES IN DATABASE MY_DB;
GRANT USAGE ON SCHEMA MY_DB.MY_SCHEMA TO DATABASE ROLE MY_DB.MYDATABASEROLE;

-- GRANT SELECT ON
USE ROLE MYROLE;
GRANT SELECT ON TABLE MY_DB.MY_SCHEMA.MY_TABLE TO DATABASE ROLE MYDATABASEROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA MY_DB.MY_SCHEMA TO DATABASE ROLE MY_DB.MYDATABASEROLE;
USE ROLE SECURITYADMIN;
GRANT SELECT ON FUTURE TABLES IN SCHEMA MY_DB.MY_SCHEMA TO DATABASE ROLE MY_DB.MYDATABASEROLE;

-- GRANT DATABASE ROLE
USE ROLE MYROLE;
GRANT DATABASE ROLE MY_DB.MYDATABASEROLE TO ROLE GUESTROLE;
USE ROLE GUESTROLE;
SELECT * FROM MY_DB.MY_SCHEMA.MY_TABLE;
SHOW GRANTS TO ROLE GUESTROLE;

USE ROLE MYROLE;
DROP TABLE MY_DB.MY_SCHEMA.MY_TABLE;

-- テーブル
USE ROLE MYROLE;
REVOKE ALL ON SCHEMA MY_DB.MY_SCHEMA FROM ROLE OTHERROLE;
REVOKE SELECT ON ALL TABLES IN SCHEMA MY_DB.MY_SCHEMA FROM ROLE OTHERROLE;
USE ROLE SECURITYADMIN;
REVOKE SELECT ON FUTURE TABLES IN SCHEMA MY_DB.MY_SCHEMA FROM ROLE OTHERROLE;
USE ROLE MYROLE;
GRANT USAGE ON DATABASE MY_DB TO ROLE OTHERROLE;
GRANT USAGE ON SCHEMA MY_DB.MY_SCHEMA TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;

-- GRANT SELECT ON TABLE
USE ROLE MYROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;
CREATE OR REPLACE TABLE MY_DB.MY_SCHEMA.MY_TABLE (ID NUMBER, NAME VARCHAR);
USE WAREHOUSE MY_WH;
INSERT INTO MY_DB.MY_SCHEMA.MY_TABLE VALUES(1, 'user_name');
GRANT SELECT ON TABLE MY_DB.MY_SCHEMA.MY_TABLE TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
SELECT * from MY_DB.MY_SCHEMA.MY_TABLE;

-- GRANT INSERT ON TABLE
USE ROLE MYROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;
GRANT INSERT ON TABLE MY_DB.MY_SCHEMA.MY_TABLE TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW GRANTS TO ROLE OTHERROLE;
INSERT INTO MY_DB.MY_SCHEMA.MY_TABLE VALUES(2, 'user_name');
SELECT * from MY_DB.MY_SCHEMA.MY_TABLE;

-- タスク
USE ROLE MYROLE;
USE DATABASE MY_DB;
USE SCHEMA MY_SCHEMA;

CREATE TASK MY_TASK
  SCHEDULE = '5 MINUTE'
  WAREHOUSE = MY_WH
AS
SELECT CURRENT_TIMESTAMP;

-- GRANT EXECUTE TASK ON ACCOUNT
USE ROLE ACCOUNTADMIN;
GRANT EXECUTE TASK ON ACCOUNT TO ROLE MYROLE;
USE ROLE MYROLE;
EXECUTE TASK MY_DB.MY_SCHEMA.MY_TASK;
SELECT * FROM TABLE(MY_DB.INFORMATION_SCHEMA.TASK_HISTORY());

-- GRANT EXECUTE MANAGED TASK ON ACCOUNT
USE ROLE ACCOUNTADMIN;
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO ROLE MYROLE;
USE ROLE MYROLE;
CREATE TASK MY_MANAGED_TASK
  SCHEDULE = '5 MINUTE'
AS
SELECT CURRENT_TIMESTAMP;
EXECUTE TASK MY_DB.MY_SCHEMA.MY_MANAGED_TASK;
SELECT * FROM TABLE(MY_DB.INFORMATION_SCHEMA.TASK_HISTORY());

-- GRANT OPERATE TASK ON ACCOUNT
USE ROLE MYROLE;
GRANT MONITOR ON TASK MY_TASK TO ROLE OTHERROLE;
USE ROLE OTHERROLE;
SHOW TASKS;
DESC TASK MY_TASK;

USE ROLE MYROLE;
DROP TASK MY_MANAGED_TASK;
DROP TASK MY_TASK;

-- 使用状況と請求履歴のモニター
USE ROLE ACCOUNTADMIN;
GRANT MONITOR USAGE ON ACCOUNT TO ROLE MYROLE;

-- Snowflakeデータベース
USE ROLE ACCOUNTADMIN;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE MYROLE;

-- リソースの削除
USE ROLE ACCOUNTADMIN;
DROP ROLE GUESTROLE;
DROP ROLE OTHERROLE;
DROP TASK MY_TASK;
DROP TASK MY_MANAGED_TASK;
DROP TABLE MY_DB.MY_SCHEMA.MY_TABLE;
DROP SCHEMA MY_DB.MY_SCHEMA_2;
DROP SCHEMA MY_DB.MY_SCHEMA;
DROP DATABASE ROLE MYDATABASEROLE;
DROP DATABASE MY_DB;
DROP WAREHOUSE MY_WH;
8
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
7