SnowflakeでMFAが無効なユーザー一覧を出してみた #SnowflakeDB

※本エントリは、Snowflakeをもっと使いこなそう！ Advent Calendar 2022の１８日目の記事となります。

さがらです。

SnowflakeでMFAが無効なユーザー一覧を出してみたので、その内容をまとめてみます。

SnowflakeにおけるMFA

まずMFAについてですが、Multi-Factor Authenticationの略称で、ユーザーがログインする際にIDとパスワードだけでなく、別の要素(例：ワンタイムパスワード、指紋認証、etc)も必要とすることでそのユーザーのセキュリティをより強固にすることが出来ます。

SnowflakeでもMFAに対応しており、特にACCOUNTADMINのロールを持つユーザーについては公式からもMFAの適用を強く推奨されています。

MFAの登録手順については、下記の弊社ブログや公式Docをご確認ください。

そんなセキュリティ上大事なMFAの機能ですが、「どうやってMFAを有効化していないユーザー一覧を出力すればいいのか」気になる方もいると思います。

本記事ではこの「MFAを有効化していないユーザー一覧を出力」する方法について、2つ記します。

方法その１

まず1つ目は、ACCOUNT_USAGEスキーマのUSERSビューを使う方法です。

このUSERSビューにはEXT_AUTHN_DUOというカラムがあり、このカラムでは「対象のレコードのユーザーがMFAを有効化している場合はtrue、MFAを有効化していない場合にはfalse」という値を保持します。

そのため、下記のようにwhere EXT_AUTHN_DUO = 'false'と絞り込んだクエリを発行することで、MFAを有効化していないユーザー一覧を出力可能です。

select 
    name,
    email,
    ext_authn_duo
from 
    snowflake.account_usage.users
where 
    EXT_AUTHN_DUO = 'false';

方法その２

先程記した方法その１ですが、ACCOUNT_USAGEスキーマのUSERSビューは更新までに最大2時間かかる可能性があります。そのため、直近2時間以内に作成・情報変更したユーザーについては正しい情報が出力出来ない可能性があります。

そこで、少しだけ手間ですがshow usersコマンドとRESULT_SCAN関数を用いて、最新のユーザー情報からMFAを有効化していないユーザー一覧を出力してみます。

行うこととしては、以下のようなクエリを発行すればOKです。show usersコマンドの後にRESULT_SCAN関数を使うことで、show usersコマンドで出力された最新のユーザー情報に対して絞り込みを行うことが出来ます。

show usersコマンドで出力されるデータにもEXT_AUTHN_DUOというカラムがあるため、方法その1と同じ様に値がfalseの場合のみに絞り込むことで、最新のユーザー情報からMFAを有効化していないユーザー一覧を出力可能です。

show users;
select 
    "name",
    "email",
    "ext_authn_duo"
from 
    table(result_scan(last_query_id()))
where 
    "ext_authn_duo" = 'false';

最後に

SnowflakeでMFAが無効なユーザー一覧を出す方法について2つまとめてみました。

定期的なユーザー管理の際に使えると思いますので、ぜひご活用ください！