こんにちは！ちゅらデータでCTOしてる菱沼です！
最近Snowflakeの仕事がどんどん増えてきて楽しいですね！

今日は初心者向けのコンテンツです！
Snowflakeのアカウントを作って学習や構築をする際に設定しておいたほうがいいユーザ権限周りの設定をお伝えしようと思います。

また、Snowflakeのトライアルアカウントを作って触り始めるときに、予め読んでおくとつまづきが減ると思います。

Snowflakeの権限について学ぼう

Snowflakeではテーブルやデータベースなど、アクセス権限を設定できるオブジェクトを セキュリティ保護可能なオブジェクト（Securable Objects）と呼びます。

下記にセキュリティ保護可能なオブジェクトの例を記載します。

・User
・Role
・Werehouse
・Database
　　・Schema
　　　　・Table
　　　　・ExternalTable
　　　　・View
　　　　・…etc

お気づきになりましたか？
そうなんです！
Snowflakeではほぼすべてのものが、セキュリティ保護可能なオブジェクトなんですね。

そのため、たとえばうっかりACCOUNTADMINでテーブルやデータベースを作って、いざ、一般ユーザに渡すと…。

「見えません！」
「使えません！」

のようになってしまうんですね。

今日はそうならないために、Snowflakeでのユーザ権限についても学んでいきましょう。

Snowflakeのアクセス制御について

Snowflakeのアクセス制御は2つのアプローチが組み合わされたものになっています。

• DAC(Discretionary Access Control、任意アクセス制御) … 各セキュリティ保護可能なオブジェクトは所有者を持っていて、所有者はそのオブジェクトのアクセス許可を設定できます
• RBAC(Role-based Access Control、ロールベースのアクセス制御）…アクセス権限がロールに割り当てられ、そのロールはユーザに割り当てられます。

そして、このDACとRBACによって登場する概念は次の4つです。

1. ユーザ … SnowflakeのWebUIにログインするのに使ったり、JDBC接続などの認証情報に使われます。
2. ロール … ロールはユーザに割り当てられます。1人のユーザは複数のロールを持てますし、1つのロールは複数のユーザに割り当てられます。
3. 権限 … セキュリティ保護可能なオブジェクトに対して、何ができるかを定義できます。テーブルへの参照や書き込みなど他のRDB製品にもあるようなものや、ステージオブジェクトの使用権限などあまり聞き慣れないものもあります。
4. セキュリティ保護可能なオブジェクト … 前述の通り

Snowflakeでは、DACとRBACをベースに上記の4つの概念を組み合わせて、ユーザ権限管理を行っています。

Snowflakeでユーザを作ろう

ここまで読んでくれた方は、きっともうユーザが作りたくてしょうがないはず。
ひとまずユーザを作りましょう。

1.まずはユーザやロールを作成するための「USERADMIN」ロールになります。

USE ROLE USERADMIN;

2.次にユーザを作成します。

CREATE USER OPERATOR
    PASSWORD = '12345678aiueo'
    DEFAULT_ROLE = 'PUBLIC'
    MUST_CHANGE_PASSWORD = TRUE;

上記の「CREATE USER」文で「OPERATOR」ユーザを作成しました。
また、「MUST_CHANGE_PASSWORD」をTRUEにすることで、「OPERATOR」が次回ログインするときにパスワードの変更が要求されます。
※参考：CREATE USER（https://docs.snowflake.com/ja/sql-reference/sql/create-user.html）

これらのユーザ作成の操作は「USERADMIN」ロールで実行するのが良いでしょう。「USERADMIN」ロールはSnowflakeの既定のロールの一つで、「ユーザおよびロール管理者」とされています。
「USERADMIN」ロールであれば、CREATE USERやCREATE ROLEのセキュリティ権限が付与されています。
他にも既定のロールはあり、下記のような関係になっています。

図：既定のロール
図中では、上に行けば行くほど偉いアカウントです。
なので操作になれないうちは、「ACCOUNTADMIN」ロールで各クエリを実行しても良いかもしれません。
※私も最初のうちはACCOUNTADMINでオブジェクトを作ってしまい、他のユーザで権限なくて操作できない！をやってしまいました。
ちなみに図には載せていませんが、すべてのユーザは既定のPUBLICロールを持っています。

Snowflakeで作ったユーザにロールを割り当てる

さて、では作った「OPERATOR」ユーザにシステム管理者権限を割り当ててみましょう。

GRANT ROLE SYSADMIN
    TO USER OPERATOR;

すると残念がなら、下記のようなエラーが表示されるはずです。
「実行されない付与：権限が不十分です。」
これは「USERADMIN」ロールには権限を付与する権限がないからですね。
ですので、実行するロールを「SECURITYADMIN」ロールに変更してから再度実行してましょう。

USE ROLE SECURITYADMIN;

GRANT ROLE SYSADMIN
    TO USER OPERATOR;

どうでしょう？今後は成功しましたか？
「SECURITYADMIN」ロールは先程も紹介しましたが、セキュリティ管理者のためのロールです。既存のユーザにロールを許可させるなどの権限の操作をする際に使います。
※参考：GRANT <権限>（https://docs.snowflake.com/ja/sql-reference/sql/grant-privilege.html）

ここまで読めば、基本的な操作と概念は理解できたかなと思います。

たとえば、「OPERATOR」でログインすると、「SYSADMIN」として、様々なオペレーションが可能なはずです。
また新たにユーザやロールが欲しくなったら、「SECURITYADMIN」や「USERADMIN」で作っていくことができるでしょう。

プロジェクトごとに権限を管理してみよう

ではこの後はすこし実践的に、プロジェクト用データベースを用意し、そこに管理者と一般ユーザ、それにBIツール用のユーザを用意してみましょう。

1.まずはプロジェクト用にロールを用意します。

USE ROLE SECURITYADMIN;
-- ここではプレフィックスにPJ名の「DS」をつける
CREATE ROLE DS_ADMIN_ROLE; -- PJ管理者ロール
CREATE ROLE DS_OPERATOR_ROLE; -- PJ一般ユーザロール
CREATE ROLE DS_BI_ROLE; -- PJのBIツール用のロール
CREATE ROLE DS_PUBLIC_ROLE; -- PJの公開ロール

2.次にそれぞれのロールを「SYSADMIN」からのツリー構造に権限を設定します。

GRANT ROLE DS_ADMIN_ROLE
    TO ROLE SYSADMIN;
GRANT ROLE DS_OPERATOR_ROLE
    TO ROLE DS_ADMIN_ROLE;
GRANT ROLE DS_BI_ROLE
    TO ROLE DS_ADMIN_ROLE;
GRANT ROLE DS_PUBLIC_ROLE
    TO ROLE DS_BI_ROLE;
GRANT ROLE DS_PUBLIC_ROLE
    TO ROLE DS_OPERATOR_ROLE;

図：DSプロジェクト用のロールの階層

3.プロジェクト用のウェアハウスを作りましょう。

CREATE WAREHOUSE DS_WH
    WITH WAREHOUSE_SIZE = 'XSMALL'
    WAREHOUSE_TYPE = 'STANDARD'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE;
GRANT USAGE
    ON WAREHOUSE DS_WH
    TO ROLE DS_PUBLIC_ROLE;
GRANT OWNERSHIP
    ON SCHEMA DS_DB.PUBLIC
    TO ROLE DS_ADMIN_ROLE;

4.データベース「DS_DB」を作って、「DS_ADMIN_ROLE」へ所有権を渡します。

USE ROLE SYSADMIN;
CREATE DATABASE DS_DB;

GRANT OWNERSHIP
    ON DATABASE DS_DB
    TO ROLE DS_ADMIN_ROLE;

USE ROLE DS_ADMIN_ROLE;

※補足：ロールの階層で自分より下のロールに権限が付与されれば、自分にも権限が伝播するため、上記のように「DS_PUBLIC_ROLE」に権限を付与すれば「DS_ADMIN_ROLE」や残り２つのロールにも「DS_WH」のUSAGE権限が付与されたことになります。

5.データベース「DS_DB」を下位のロールが使えるようにしていきましょう。

「DS_OPERATOR_ROLE」はPUBLICスキーマへの読み書きが、「DS_BI_ROLE」はPUBLICスキーマへの読み込みができるようにします。また、テーブルを作るたびに各ロールへ権限などを与えるのは手間なので「将来の許可」を使って、今後作られるスキーマ内のオブジェクトへの権限を設定します。

-- 「DS_PUBLIC_ROLE」にはDBとその中のPUBLICスキーマの使用権限を与える
GRANT USAGE
  ON DATABASE DS_DB
  TO ROLE DS_PUBLIC_ROLE;
GRANT USAGE
  ON SCHEMA DS_DB.PUBLIC
  TO ROLE DS_PUBLIC_ROLE;

-- この後に実行する「将来の許可(ON FUTURE)」を使うには「MANAGE GRANTS グローバル権限」をもったロールに変える。
USE ROLE SECURITYADMIN;

-- 「DS_OPERATOR_ROLE」には読み書き権限を与える
GRANT SELECT, INSERT, UPDATE, DELETE
  ON FUTURE TABLES IN SCHEMA DS_DB.PUBLIC
  TO ROLE DS_OPERATOR_ROLE;

-- 「DS_BI_ROLE」には参照権限のみを与える
GRANT SELECT
  ON FUTURE TABLES IN SCHEMA DS_DB.PUBLIC
  TO ROLE DS_BI_ROLE;

※補足１：オブジェクトに対する将来の付与の割り当て（https://docs.snowflake.com/ja/user-guide/security-access-control-configure.html#assigning-future-grants-on-objects）
※補足２：将来の付与を管理するために必要なセキュリティ権限（https://docs.snowflake.com/ja/user-guide/security-access-control-configure.html#security-privileges-required-to-manage-future-grants）

6.あとは各ユーザを作成して、ロールに割り当てます。

USE ROLE USERADMIN;

-- DS_ADMINユーザの作成
CREATE USER DS_ADMIN
    PASSWORD = 'password'
    DEFAULT_ROLE = 'DS_ADMIN_ROLE'
    MUST_CHANGE_PASSWORD = FALSE;
-- DS_OPERATORユーザの作成
CREATE USER DS_OPERATOR
    PASSWORD = 'password'
    DEFAULT_ROLE = 'DS_OPERATOR_ROLE'
    MUST_CHANGE_PASSWORD = FALSE;
-- DS_BIユーザの作成
CREATE USER DS_BI
    PASSWORD = 'password'
    DEFAULT_ROLE = 'DS_BI_ROLE'
    MUST_CHANGE_PASSWORD = FALSE;

USE ROLE SECURITYADMIN;

GRANT ROLE DS_ADMIN_ROLE
    TO USER DS_ADMIN;
GRANT ROLE DS_OPERATOR_ROLE
    TO USER DS_OPERATOR;
GRANT ROLE DS_BI_ROLE
    TO USER DS_BI;

さて、だいたいこれで完成です。
あとは各ユーザでログインして、パスワードを変更したり、デフォルトのウェアハウスを設定してもらうのがいいでしょう。
※注意：記事執筆時点ではデフォルトのウェアハウスはログインしているユーザ自身のみが設定できます。

おわりに

さて、いかがでしたでしょうか？
Snowflakeでのユーザ作成や権限設定など初歩的な部分を一通り体験できるように作ってみました。
慣れるまではこんなに作業があるのかよ！みたいな風に思うかもしれませんけれど、SQLのテンプレを作ったり、スクリプト化してしまえば良いと思います。

Snowflakeの導入をご検討のお客様は、ご相談からできますので、
「サービス/研修のお問い合わせ」からぜひお問い合わせください。

そして、Snowflakeが使いたいけど今の職場で使えなくて転職を考えてる方が居ましたらご連絡ください。
一緒にSnowflakeでお客様の課題を解決しましょう！

DATUM STUIDO採用ページ