Zenn
❄️

Snowflakeアカウントのセキュリティ状況が確認できるダッシュボードサンプル

2023/06/28に公開
Security
Snowflake
可視化
ロール
監査
tech

Snowflakeでセキュリティ機能に特化したSecurity Field CTO Officeのロールを担当しているMasayaです

今回は私が所属しているSecurity Field CTO Officeチームが公開したSnowflakeが提供しているAccountUsageを使用してセキュリティダッシュボードを作成する”QuickStart”ならびにブログを日本語化し、日本のユーザ様にも検索しやすいようにまとめました

ブログ：Announcing Snowflake Security Dashboards
https://www.snowflake.com/blog/snowflake-security-dashboards/

Security Dashboard QuickStart
https://quickstarts.snowflake.com/guide/security_dashboards_for_snowflake/#0

QuickStartとは？

QuickStart（クイックスタート）とは、QuickStartページに公開されているコードをコピペするだけでSnowflakeの新しい機能の学習を比較的容易に行うことができるサンプルコード集のことです。

セキュリティダッシュボードだけではなくいろいろなコードが公開されていますので、ぜひチェックしてみてください
https://quickstarts.snowflake.com/

QuickStartの中身については随時変更される可能性があるため、こちらの本文とQuickStartに差異があった場合はQuickStart側を正としてください。こちらのブログはオリジナルの英訳という位置付けになります。

なぜSnowflakeセキュリティダッシュボードが必要になったのか

お客様から「Snowflakeを使用してセキュリティやガバナンスに関する監査することができますか？」とよく聞かれますが、答えは「Yes」です

Snowflakeを使用し始めるとすぐに組み込みの監査ログ機能（AccountUsageやInformation_Schema）が機能し始め、セキュリティ監視だけでなく企業コンプライアンスに対して充足できるようSnowflakeの各種ログの提供が開始されます

AccountUsage
https://docs.snowflake.com/ja/sql-reference/account-usage

Information_Schema
https://docs.snowflake.com/ja/sql-reference/info-schema

しかし、ログは提供されるものの、そのログをどのように活用すればよいのかの指標やSQLなどはお客様にて記述してもらう必要がありました

そこで監査ログ情報を、より適切に整理、分析、視覚化するために使用できる一連のサンプルクエリを提供し、組み込みの可視化機能であるSnowsightと組み合わせて、一定の気づきやチェックすべきポイントなどをガイドするQuickStartの作成に着手し、リリースしました

例えば下の図は、「どのようなログイン失敗イベントが、誰によって、なぜ失敗したのか」を表しているものです。
ログインの失敗イベントの詳細を把握することで、パスワードスプレー攻撃、クレデンシャル・スタッフィングなどのサイバー攻撃に対応するのに役立つと考えています

また「最も危険なユーザ」というタイトルの下図は、どのユーザーが最も多い権限が付与されているかを表示しています

あまりにも多い権限を持つユーザに対しては、MFAが有効になっているか、どのようなクエリを実行しているのか、そもそもその権限は必要なのか？などのさらなる分析を行う必要があると考えられます

現在のQuickStartには、このような「タイル」が12個ほど用意されており、監査すべき項目サンプルとして、Snowflakeアカウント内部の監査を行うにあたってのヒントが掲載されてます。

もしこのQuickStartにはないものの、監査すべき項目が別にあると思われた場合、簡単に任意で新しいタイルやダッシュボードを追加することができますし、不要だと思うものを削除することもできます

QuickStart終了後にはぜひカスタマイズして利用していただくことをお勧めします

注意すべき点として、このダッシュボードは、Snowflakeアカウント内部の操作のみが対象になっていますが、Snowflakeをセキュリティ用途で利用するということに関して言うと、必ずしもSnowflakeはSnowflakeアカウント自体のセキュリティ監査にのみSnowflakeを利用できるということでもありません。

別のお客様ではSnowflakeをセキュリティログの分析基盤として利用する「サイバーセキュリティワークロード」というユースケースという形でSnowflakeを利用されており、Snowflakeを含む企業システムの大規模なセキュリティとガバナンスの分析を行うことも可能です

「サイバーセキュリティワークロード」のユースケースについての説明はまた別途掲載していきたいと思います

1.セキュリティダッシュボードQuickStartについて

本QuickStartでカバーしている領域

ダッシュボードは以下をカバーしています

  • 認証パターン
    • 失敗したログイン試行をユーザーと理由別に整理し、使用中の認証タイプをアカウント全体で可視化
  • ロール
    • 現在のRBACの評価、AccountAdminの使用状況の監視、ユーザが最も使用されていないロールを発見
  • ユーザー
    • 最も危険なユーザー、まだ削除されていない無効なユーザー、パスワードが古いユーザー
  • その他セキュリティ設定
    • ネットワークポリシー、セキュリティ統合、レプリケーション、フェールバック、Snowflakeアカウントのセキュリティベストプラクティスから逸脱していないか？

あるお客様でこのQuickStartを元にダッシュボードを作成していただいたところ、ログイン失敗回数が大量に発生しているユーザを発見することができたという例もありますので、ぜひ試してみてください。

このQuickStartで学習できること

  • SQLを使用してSnowflakeのセキュリティ監査と構成メタデータを探索する方法
  • Security Field CTOチームが考える、Snowflakeアカウントのセキュリティ状況を理解するための項目

必要な項目

Snowflakeアカウントのみ

2.前準備

まず、いくつかの初期設定を行います。ダッシュボードのみで使用するロールを作成し、そのロールに権限を付与、ユーザーにそのロールを付与します。

use role accountadmin; 
create role SENTRY;
grant imported privileges on database snowflake to role SENTRY;
create or replace warehouse SENTRY with warehouse_size='SMALL';
grant usage on warehouse SENTRY to role SENTRY;
grant role SENTRY to user <YOURUSER>;

これで、適切なリソースにアクセスできるSENTRYというロールができました
また、”SENTRY”というロールまたは対象リソースにアクセスさせたい任意のロールに同じくアクセス権を与えることで、これらの情報にアクセスできるようになります。

3.可視化タイルの作成

タイル1: ログインが失敗したユーザ一覧とその理由

Snowflake UIを開き、左メニューの "ダッシュボード"を選択します

先ほど作成したSENTRYロールをロールに設定します

画面右上の青い「+ダッシュボード」ボタンをクリックして新しいダッシュボードを作成

"Security Dashboard Quickstart"と入力します

ダッシュボードにウェアハウスが選択されていない場合は、ウェアハウスを先ほどロールに付与したSENTRYに設定します

では、一つ目のタイルを作成していきます
空白のダッシュボードの中央にある青い「＋新しいタイル」をクリックします。

編集エリアの左上にあるデータベースにSNOWFLAKE.ACCOUNT_USAGEに設定されているのを確認してください、もしデータベースの指定がない場合は、ドロップダウンでSNOWFLAKE.ACCOUNT_USAGEに設定してください

下記SQLを貼り付けます

select
    user_name,
    error_message,
    count(*) num_of_failures
from
    login_history
where
    is_success = 'NO'
group by
    user_name,
    error_message
order by
    num_of_failures desc;

SQLを実行し、編集エリアと結果エリアの間の「チャート」をクリックすると、下記のような図が表示されます

タイルの名前が日付になっているため、チャート・エリアの左上隅の日付の文字をクリックすることで、タイルの題名を変更することができます。
タイルのタイトルを「認証：失敗 - ユーザ別とその理由」とします

右ペインのチャート型を「バー」に変更、さらにX軸の左にあるドロップダウン・メニューを選択し、列をUSER_NAMEに変更します

USER_NAMEの下にある "+列を追加"をクリックし、"ERROR_MESSAGE "列を選択します
ERROR_MESSAGE列は、USER_NAMEの上に "シリーズ"として表示されます

続けて右側の「外観」の見出しの下にある「グループ化」の積み上げグラフのアイコンをクリックすると、チャートが変更されます。

その下に "伸縮"ラベルが表示されれば、OKです（そのままにしておきます）

右ペインの「ラベルX軸」のチェックボックスをクリックし、入力ボックスに"ユーザ"と入力し、次に「ラベルY軸」のチェックボックスをクリックし、入力ボックスに"ログイン失敗"と入力します

次に、タイル編集ウィジェットの左上隅にある青い「 - Security Dashboard QuickStartに戻る」リンクをクリックして、ダッシュボード一覧に戻ります

以上で1つ目のタイルがダッシュボードに追加されました

タイル2: 認証方式一覧

次にログインが成功したユーザがどのような認証方式でログインしているのかを可視化するタイルを作成していきます。

ログイン失敗の追跡と同様にユーザが正しい認証方法を使用しているかどうか確認することが重要な要素です。
このタイルは、どのような認証方式が最も使用されているかを可視化・分析することができます

ダッシュボード上の左上の「＋」をクリック

「ワークシートから新しいタイル」を選択します

下記SQLを貼り付け、実行します

select
	first_authentication_factor || ' ' ||nvl(second_authentication_factor, '') as authentication_method,
	count(*)
from
	login_history
where
	is_success = 'YES'
	and user_name != 'WORKSHEETS_APP_USER'
group by
	authentication_method
order by
	count(*) desc;

右ペインで

チャート型：バー
データ：Count(*)：用途：バー
データ：authentication_method：用途：シリーズ
データ：authentication_method：用途：Y軸
外観：オリエンテーション；横棒グラフ
外観：グループ化：積み上げ棒グラフ
外観：伸縮：そのまま
ラベルX軸：イベントカウント
ラベルY軸：認証方式
に指定し、最後にタイルのタイトルを「認証方式一覧」に指定します

タイル3: パスワードを使用しているサービスアカウント

このタイルは、認証方法としてキーペアのみを使用すると想定される「サービスアカウント」のユーザーがパスワードを使用していないかを確認することができるタイルです

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

SELECT
	l.user_name,
	first_authentication_factor,
	second_authentication_factor,
	count(*) as Num_of_events
FROM
	snowflake.account_usage.login_history as l
JOIN 
	snowflake.account_usage.users u 
on
	l.user_name = u.name and l.user_name ilike '%svc' and has_rsa_public_key = 'true'
WHERE
	is_success = 'YES'
	AND first_authentication_factor != 'RSA_KEYPAIR'
GROUP BY
	l.user_name, first_authentication_factor, 
	second_authentication_factor
ORDER BY 
	count(*) desc;

クエリを実行したら、今回はチャートは作成せずに表のままにしておき、タイルのタイトルを「パスワードを使用しているサービスアカウント」とします

タイル4: AccountAdminが付与されたユーザ

どのようなシステム上でも特権アクセスユーザを監視することが必要のため、SnowflakeアカウントのAccountAdminが付与するコマンドが発行されていないか確認することができます

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

select
    user_name || ' granted the ' || role_name || ' role on ' || end_time as Description, query_text as Statement
from
    query_history
where
    execution_status = 'SUCCESS'
    and query_type = 'GRANT'
    and query_text ilike '%grant%accountadmin%to%'
order by
    end_time desc;

クエリを実行したら、今回はチャートは作成せずに表のままにしておき、タイルのタイトルを「AccountAdminが付与されたユーザ」とします

タイル5: MFAが設定されていないAccountAdmin

ここでは、AccountAdminを持っているが、MFA（多要素認証）の追加保護機能を使用していないユーザを表示しています

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

select 
    u.name, 
    timediff(days, last_success_login, current_timestamp()) || ' 日前' last_login ,
    timediff(days, password_last_set_time,current_timestamp(6)) || '  日前' password_age
from 
    users u
join 
    grants_to_users g on grantee_name = name and role = 'ACCOUNTADMIN' and g.deleted_on is null
where 
    ext_authn_duo = false and u.deleted_on is null and has_password = true
order by 
    last_success_login desc;

クエリを実行したら、今回はチャートは作成せずに表のままにしておき、タイルのタイトルを「MFAが設定されていないAccountAdmin」とします

タイル4,5はAccountAdminに絞ってタイルを作成しましたが、AccountAdminに加えて、SYSADMINやSECURITYADMINのようなロールを含めることも検討した方がよい場合がありますので、必要に応じてSQLを変更して利用してください

タイル6: パスワードが古いユーザ一覧

セキュリティの健全性を確保するため、ユーザを適切に管理することも重要な項目となります。

「パスワードが古いユーザ一覧」タイルでは、Snowflakeのusersビューにアクセスし、ユーザーのパスワードがどれくらい前に作成されたものなのかをチェックすることができます。

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

select 
	name, 
	datediff('day', password_last_set_time, current_timestamp()) || ' days ago' as password_last_changed 
from
	users 
where 
	deleted_on is null 
	and password_last_set_time is not null
order by 
	password_last_set_time;

クエリを実行したら、今回はチャートは作成せずに表のままにしておき、タイルのタイトルを「パスワードが古いユーザ一覧」とします

タイル7: 長期間ログインしていないユーザ一覧

「長期間ログインしていないユーザ一覧」タイルを作成します。このタイルでは、各ユーザの最終ログインを確認することができます

あまりにも長い間ログインしていないユーザがあった場合は利用の必要性を再度検討して、ユーザを無効化しておいた方がセキュリティ的には安全です。

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

select 
	name, 
	datediff("day", nvl(last_success_login, created_on), current_timestamp()) || ' days ago' Last_Login 
from 
	users 
where 
	deleted_on is null
order by 
	datediff("day", nvl(last_success_login, created_on),
	current_timestamp()) desc;

クエリを実行したら、今回はチャートは作成せずに表のままにしておき、タイルのタイトルを「長期間ログインしていないユーザ一覧」とします

タイル8: SCIMトークン一覧

SCIM（System for Cross-domain Identity Management）は、RESTful APIを使用して、クラウドアプリケーションのユーザーIDとグループ（Snowflakeで言うロール）の管理を自動化し、運用負荷を軽減することができるプロトコルで、SnowflakeもSCIM2.0に対応しています。

ユーザやロールのプロビジョニングを実行することができるため、その変更を行うSCIMプロバイダ間で使用されるSCIMトークンは非常に重要なものであり、SCIMトークンの確認作業はセキュリティから見ても非常に重要となります。

この「SCIMトークン一覧」タイルでは、対象のSnowflakeアカウントで生成されたSCIMトークンの一覧を期限が短い順に表示します。

もし期限が短い場合は、期限が切れる前にプロビジョニングが停止しないようにSCIMトークンを再生成した方がよいかもしれません

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

select
    user_name as by_whom,
    datediff('day', start_time, current_timestamp()) || ' days ago' as created_on,
    ADD_MONTHS(start_time, 6) as expires_on,
    datediff(
        'day',
        current_timestamp(),
        ADD_MONTHS(end_time, 6)
    ) as expires_in_days
from
    query_history
where
    execution_status = 'SUCCESS'
    and query_text ilike 'select%SYSTEM$GENERATE_SCIM_ACCESS_TOKEN%'
    and query_text not ilike 'select%where%SYSTEM$GENERATE_SCIM_ACCESS_TOKEN%'
order by
    expires_in_days;

クエリを実行したら、今回はチャートは作成せずに表のままにしておき、タイルのタイトルを「SCIMトークン一覧」とします

タイル9: 最も危険なユーザ

セキュリティの考え方の一つとして、最小権限（Least Privileged Access）という考え方があります。
これは必要最小限度の権限のみを必要なユーザに付与して、意図しない過失、もしくは故意による情報漏洩・遺失事故を防止するためです

「最も危険なユーザ」タイルを作成していきます。英語タイトルが非常にキャッチーですが、多くの権限が付与されているユーザはセキュリティ面から見ると脅威に他ならないため、このタイトルを流用しています

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

with role_hier as (
    --Extract all Roles
    select
        grantee_name,
        name
    from
        grants_to_roles
    where
        granted_on = 'ROLE'
        and privilege = 'USAGE'
        and deleted_on is null
    union all
        --Adding in dummy records for "root" roles
    select
        'root',
        r.name
    from
        roles r
    where
        deleted_on is null
        and not exists (
            select
                1
            from
                grants_to_roles gtr
            where
                gtr.granted_on = 'ROLE'
                and gtr.privilege = 'USAGE'
                and gtr.name = r.name
                and deleted_on is null
        )
) --CONNECT BY to create the polyarchy and SYS_CONNECT_BY_PATH to flatten it
,
role_path_pre as(
    select
        name,
        level,
        sys_connect_by_path(name, ' -> ') as path
    from
        role_hier connect by grantee_name = prior name start with grantee_name = 'root'
    order by
        path
) --Removing leading delimiter separately since there is some issue with how it interacted with sys_connect_by_path
,
role_path as (
    select
        name,
        level,
        substr(path, len(' -> ')) as path
    from
        role_path_pre
) --Joining in privileges from GRANT_TO_ROLES
,
role_path_privs as (
    select
        path,
        rp.name as role_name,
        privs.privilege,
        granted_on,
        privs.name as priv_name,
        'Role ' || path || ' has ' || privilege || ' on ' || granted_on || ' ' || privs.name as Description
    from
        role_path rp
        left join grants_to_roles privs on rp.name = privs.grantee_name
        and privs.granted_on != 'ROLE'
        and deleted_on is null
    order by
        path
) --Aggregate total number of priv's per role, including hierarchy
,
role_path_privs_agg as (
    select
        trim(split(path, ' -> ') [0]) role,
        count(*) num_of_privs
    from
        role_path_privs
    group by
        trim(split(path, ' -> ') [0])
    order by
        count(*) desc
) --Most Dangerous Man - final query
select
    grantee_name as user,
    count(a.role) num_of_roles,
    sum(num_of_privs) num_of_privs
from
    grants_to_users u
    join role_path_privs_agg a on a.role = u.role
where
    u.deleted_on is null
group by
    user
order by
    num_of_privs desc;

クエリを実行したら、右ペインで

チャート型：バー
データ：num_of_privs：用途：バー
データ：user：用途：シリーズ
データ：user：用途：Y軸
外観：オリエンテーション；横棒グラフ
外観：グループ化：積み上げ棒グラフ
に指定し、最後にタイルのタイトルを「最も危険なユーザ」に指定します

タイル10: 最も権限を持っているロール

「最も権限を持っているロール」タイルを作っていきます。
これは「最も危険なユーザ」タイルと同じような位置付けですが、このタイルはロールに着目しています。
あまりにも多い権限を持っているロールを見つけた場合、ロールの権限を見直したり、そのロールを利用しているユーザの動向を調査した方がよいかもしれません

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

--Role Hierarchy
with role_hier as (
    --Extract all Roles
    select
        grantee_name,
        name
    from
        grants_to_roles
    where
        granted_on = 'ROLE'
        and privilege = 'USAGE'
        and deleted_on is null
    union all
        --Adding in dummy records for "root" roles
    select
        'root',
        r.name
    from
        roles r
    where
        deleted_on is null
        and not exists (
            select
                1
            from
                grants_to_roles gtr
            where
                gtr.granted_on = 'ROLE'
                and gtr.privilege = 'USAGE'
                and gtr.name = r.name
                and deleted_on is null
        )
) --CONNECT BY to create the polyarchy and SYS_CONNECT_BY_PATH to flatten it
,
role_path_pre as(
    select
        name,
        level,
        sys_connect_by_path(name, ' -> ') as path
    from
        role_hier connect by grantee_name = prior name start with grantee_name = 'root'
    order by
        path
) --Removing leading delimiter separately since there is some issue with how it interacted with sys_connect_by_path
,
role_path as (
    select
        name,
        level,
        substr(path, len(' -> ')) as path
    from
        role_path_pre
) --Joining in privileges from GRANT_TO_ROLES
,
role_path_privs as (
    select
        path,
        rp.name as role_name,
        privs.privilege,
        granted_on,
        privs.name as priv_name,
        'Role ' || path || ' has ' || privilege || ' on ' || granted_on || ' ' || privs.name as Description
    from
        role_path rp
        left join grants_to_roles privs on rp.name = privs.grantee_name
        and privs.granted_on != 'ROLE'
        and deleted_on is null
    order by
        path
) --Aggregate total number of priv's per role, including hierarchy
,
role_path_privs_agg as (
    select
        trim(split(path, ' -> ') [0]) role,
        count(*) num_of_privs
    from
        role_path_privs
    group by
        trim(split(path, ' -> ') [0])
    order by
        count(*) desc
) 
select * from role_path_privs_agg order by num_of_privs desc

クエリを実行したら、今回はチャートは作成せずに表のままにしておき、タイルのタイトルを「最も権限を持っているロール」とします

タイル11: 設定変更を実行したロール一覧

このタイルでは、Snowflake設定に行われたすべての変更について調べることができ、どのロールが最も設定変更を行なったのかを見ることができます

想定しないロールが多数の設定変更を行なっている場合は、その設定変更が妥当なものであるかも調査しておいた方がよいと思われます

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

SELECT
	query_text,
	user_name,
	role_name,
	end_time
FROM 
	snowflake.account_usage.query_history
WHERE 
	execution_status = 'SUCCESS'
	AND query_type NOT in ('SELECT')
	AND (query_text ILIKE '%create role%'
	OR query_text ILIKE '%manage grants%'
	OR query_text ILIKE '%create integration%'
	OR query_text ILIKE '%create share%'
	OR query_text ILIKE '%create account%'
	OR query_text ILIKE '%monitor usage%'
	OR query_text ILIKE '%ownership%'
	OR query_text ILIKE '%drop table%'
	OR query_text ILIKE '%drop database%'
	OR query_text ILIKE '%create stage%'
	OR query_text ILIKE '%drop stage%'
	OR query_text ILIKE '%alter stage%'
	)
ORDER BY 
	end_time desc;

クエリを実行したら、右ペインで

チャート型：バー
データ：QUERY_TEXT：用途：バー
データ：ROLE_NAME：用途：シリーズ
データ：ROLE_NAME：用途：X軸
外観：オリエンテーション；横棒グラフ
外観：グループ化：そのまま
に指定し、最後にタイルのタイトルを「設定変更を実行したロール一覧」とします

タイル12: ネットワークポリシー変更履歴

ネットワークポリシーは、どのIPアドレスがSnowflakeアカウントへ接続できるかを制御できるセキュリティ機能ですが、ネットワークポリシーの変更により不要なアクセスが許可されてしまう可能性もあります。
そのためネットワークポリシーの変更を定期的に監視した方がよいと思われます

タイル2と同じく、ダッシュボード上の左上の「＋」をクリックして、新しいタイルをワークシートから作成します

select 
	user_name || ' がネットワークポリシーを変更しました - ' || end_time || ' [' ||  query_text || ']' as Events
from
	query_history where execution_status = 'SUCCESS'
	and query_type in ('CREATE_NETWORK_POLICY', 'ALTER_NETWORK_POLICY', 'DROP_NETWORK_POLICY')
	or (query_text ilike '% set network_policy%' 
	or query_text ilike '% unset network_policy%')
	and query_type != 'SELECT' 
	and query_type != 'UNKNOWN'
order by 
	end_time desc;

クエリを実行したら、今回はチャートは作成せずに表のままにしておき、タイルのタイトルを「ネットワークポリシー変更履歴」とします

まとめ

ここで作成したセキュリティダッシュボードはあくまで一部であり、AccountUsageやInformation_Schemaで利用できるデータの一部しか利用していません

本番運用を行う上で確認すべき項目は企業のセキュリティポリシーによって異なりますが、必要に応じて追加変更を行なってください。

Snowflakeのセキュリティ機能一覧
https://docs.snowflake.com/ja/guides-overview-secure

Discussion