Snowflakeでのユーザアカウント棚卸しについて

本記事は Supershipグループ Advent Calendar 2022 の12月3日の記事になります。

いよいよ2022年も残すところわずかな年末となりました。年末といえば、皆様も大掃除をされるかと思います。そしてコンピュータシステムのお世話となっている我々としては、不要なデータなどのお片付けをしたいですね。 ということで、今回はSnowflakeでのユーザアカウント棚卸しについて、紹介いたします。

ユーザアカウント棚卸しの目的

ユーザアカウントの棚卸しといえば、そのシステムを利用していない人や、使うことがなくなった人のアカウントを削除するかと思います。

使っていないアカウントが出来てしまう理由として、このようなものが挙げられます。

• 離任や異動により、ユーザアカウントの人がそのコンピュータシステムを使わなくなった場合。
• 退職などで、ユーザアカウントを使えなくする必要がある場合。
• 業務の変化により、そのコンピュータシステムを使わなくなった場合。

本当ならば、離任・異動・退職や業務の変化のタイミングでアカウント削除などの業務が行われるのが理想です。しかし、アカウント削除は直ちに行われなくても業務が止まるといった気づきやすい不具合が少なかったり、アカウント削除の業務が漏れているなどして、アカウント削除が忘れられることがあります。

アカウント削除を行わずに無策に放置されると、不正に利用されたり、利用されないユーザに対するライセンス費用を支払うことになったりと、今すぐではないにしても損害を負ったり費用負担することなります。

どんなアカウントを洗い出すべきか。

ここまでの目的から、今回のアカウント棚卸しは下記のようなユーザを洗い出すこととします。

• 退職などをしたユーザの削除
• 離任したユーザの削除。
• 担当業務が移動したユーザの発見

退職などをしたユーザの削除

退職をしたユーザを調べる場合は、次のような状態の2つのデータを準備することとなるでしょう。

• 現在の棚卸し対象システムでのユーザ一覧
• 在職中のユーザ一覧

現在の棚卸し対象のシステムでのユーザ一覧は、各システムでの取得方法があるかと思います。純粋にそのまま実行すればよいかと思います。

在職中のユーザ一覧のお話。

在職中のユーザ一覧の取得は、各社での従業者一覧の持ち方によって苦労されるかと思います。本件を書き始めると、現在有効なユーザの状態の持ち方や条件が雇用や契約内容によって異なったりと、奥深いお話になるかと思います。今回はスキップさせていただきます。

Supershipの場合でしたら、Single Sign On の対象となるユーザ一覧や、Gogole Workspaceのユーザ一覧が、現在有効なユーザ一覧として使い勝手が良かったです。他の企業でも、例えば Active Directory, Azure AD, Google Workspace などが在職中のユーザ一覧として使われているケースを拝見しております。

取得する在職中のユーザ一覧は、ログインIDやメールアドレスなど、照合に使いやすい形式で取得してください。

Snowflake でのユーザアカウント一覧の取得方法

Snowflakeでしたら、Account Admin権限で SHOW USERS を実行すれば現在のユーザ一覧が取得できます。 Snowflake - SHOW USERS

あとはIDまたはE-mail アドレスと、在職中のユーザ一覧を照合する具合になるかと思います。

では照合を

私の場合ですが、地味ですが、このようなLinuxのコマンドの sort, uniq を使っております。

# 在職中のユーザ一覧 (active_users.txt) を sort|uniq して
# 重複する行をなくす。
sort active_users.txt | uniq > sortuniq_active_users.txt

# 同様に、照合対象のシステムのユーザ一覧も重複する行を無くす。
sort snowflake_users.txt | uniq > sortuniq_snowflake_users.txt

# 照合対象システムと、在職中のユーザで、両方に存在する行を取得する
sort sortuniq_active_users.txt sortuniq_snowflake_users.txt |uniq -d > snowflake_dupusers.txt

# 両方に存在する行と、照合対象システムのユーザ一覧で、片方にしか存在しない行を取得します。
# この場合の、片方にしか存在しない行は「照合対象システムのユーザ一覧」のみに存在する行となる。
sort snowflake_dupusers.txt sortuniq_snowflake_users.txt |uniq -u

例えば、現在有効なユーザ一覧の内容が下記のようになっています。

[e-mail]
[e-mail]
[e-mail]

照合対象システムから取得したユーザ一覧が、下記のようになっているとします。

[e-mail]
[e-mail]
[e-mail]

在職者一覧と照合対象システムのユーザ一覧で、重複する行を取得すると、このようになります。

[e-mail]
[e-mail]

上記の重複する行 と 照合対象システムのユーザ一覧 で片方にしか存在しない行を取得すると、下記のようになります。

[e-mail]

このようにして、アカウントを削除すべき人を調べ出せます。

業務と担当者棚卸し

Snowflakeをご利用される場合、利用目的となる業務 と 利用者の対応付けは、各社にて工夫して構成されているかと思います。 Supership の事例では、利用目的の業務ごとに Role を作成し、そのRoleに利用者(User)を許可(Grant)するように構成しています。

RoleにUserをGrantする具合で業務と権限の対応付けを行っているので、このような手順を踏みました。

• 先に退職などしたユーザを削除する
• Snowflake から RoleとUserの組み合わせを取り出す
• 取り出したRole:Userの組み合わせを照合する。

先に退職などしたユーザを削除したのには理由があります。 このあとにRole毎のユーザのリストを確認するのですが、確認の段階で退職などされた方はRoleのメンバーとして削除されるのは明らかです。確認の手間を減らすためにも、予め退職などされたユーザを削除します。

SnowflakeからRole:User の一覧を取り出すには、このような手順を踏みました。

-- まずは Role の一覧を取り出す
show roles;

-- Role ごとのUser一覧を取り出す。
show grants of role (Role名);

この結果をCSVに保存して、grented_to = 'USER' を取り出します。 下記の表のように Role とユーザの対応が取り出せるかと思います。

created_on	role	granted_to	grantee_name	granted_by
2022-01-01T00:00:00	ACCOUNTADMIN	USER	[e-mail]
2022-01-01T00:00:00	ACCOUNTADMIN	USER	[e-mail]
2022-01-01T00:00:00	SYSADMIN	USER	[e-mail]
2022-01-01T00:00:00	SYSADMIN	USER	[e-mail]
2022-01-02T00:00:00	GYOUMU_ROLE	USER	[e-mail]	USERADMIN
2022-01-02T00:00:00	GYOUMU_ROLE	USER	[e-mail]	USERADMIN
2022-01-02T00:00:00	GYOUMU_ROLE	USER	[e-mail]	USERADMIN

(本当はSHOWを繰り返し実行する部分や granted_to = 'USER をSELECT WHEREで実行したかったのですが、作業を早く終わらせるためにも今回はコード化するまで手が回りませんでした)

あとは各Roleごとにユーザの組み合わせが正しいかどうかを個別に確認していくと良いでしょう。

アカウント棚卸しまでやってみて気づいたこと。

システム運用でのアカウント棚卸しまでやってみて気づいたこと・思ったことなどがあります。

• ユーザアカウントを削除したら困ることも。
• システム開発の設計時の段階から棚卸し業務のことも考慮したい。
• システム導入での業務設計でも棚卸業務のことを考慮したい。

ユーザ削除したときに起きる問題

利用者が退職したからさっさとアカウント削除したらコマッタ、というお話もあります。この話だけでも色々なお話があるかと思いますが、ちょっと過去の経験だけでも。。

たとえば販売管理システムだと受注の案件担当者が退職しても、ユーザ削除という操作はすぐにでは出来ません。退職者が担当していた案件に次の担当者を割り当てる業務が必要です。仮に関連するレコードも一緒に削除するといった設定をすると、退職者の削除を行ったら担当案件の受注も消えてしまう、という笑えない話になります。削除する前に、退職者の担当していた受注などを洗い出して、新担当者を設定するなどの作業が必要でしょう。

他の事例では、Linuxなどの cron のような定期的にJobを実行するものがあり、退職した個人のアカウントを削除したらJobが実行されなくなり、データの更新が止まったこともあります。 Cronの設定を個人のまま残すのを止めるなどの工夫をされるとよいかと思います。

システム開発の設計時から棚卸業務を考慮したい

今回はSnowflakeでのユーザアカウント棚卸しを紹介いたしました。他のシステムでの棚卸では、例えばユーザ一覧を取り出す機能WebAPIを1回コールするごとに最大50人分までで、それ以上の件数を取得するには複数回のWebAPI Callが必要なことがありました。結局、我々ユーザ側でWebAPIをCallするRubyのプログラムを書いてユーザ一覧を一括取得できるようにしました。

安全なシステム利用を提供するには、アカウントの棚卸しも定期的に行っていきたいです。 ぜひともアカウント棚卸しに役立つ機能も充実していたけると幸いです。

システム導入でも棚卸し業務を考慮したい。

システム導入時だと、システムに実装される機能や処理、データの利活用部分には注目できますが、アカウント棚卸しのところまで目が行き届く機会は少ないです。 私の経験からすると、導入してしばらくしてからアカウント棚卸しの難しさに気づくことが多いです。
システム選定時や、システムでのグループ機能の使い方を作る場合には、棚卸し・確認の方法も含めて

お知らせ

Supershipではプロダクト開発やサービス開発に関わる人を絶賛募集しております。
ご興味がある方は以下リンクよりご確認ください。

Supership 採用サイト

では、みなさま、良いお年をお迎えください！