ユーザーが権限を持つデータベース一覧をRecursive CTEsを使用して取得する

はじめに

データエンジニア部の城内です。
Recursive CTEs（Recursive Common Table Expressioins）を使用したクエリで、ユーザーが権限を持つデータベース一覧を取得する方法を説明していきます。

この記事を読んでわかること

・Recursive CTEsの概要
・ユーザーが権限を持つデータベース一覧を取得する方法

Recursive CTEsを使用する理由

ユーザーが権限を持つデータベース一覧を取得するには、以下の手順を踏む必要があります。

手順1.ユーザーが持つロールを取得する
手順2.1で取得したロールに付与した（下の階層の）ロールを（無くなるまで）繰り返し取得する
手順3.1、2で取得した各ロールに付与した権限からデータベースの権限を取得する

手順2は、もしユーザーに対して全てのロールを明示的に付与している場合には必要ありません。ただしそうではない場合に、手順2を1回で行うには、階層データに対して再帰的に処理できるRecursive CTEsが適しています。
その他の手順2に対する方法としては、Procedureを使用する、手動で何回もクエリを実行する等が考えられます。

Recursive CTEsとは

まずCTEとは、WITH句で定義したサブクエリのことです。モジュール化することで、クエリを見やすくする等のために使用します。

with granted_role (name, grantee_name) as (
    select
        name, 
        grantee_name
    from 
        snowflake.account_usage.grants_to_roles
)

select name, grantee_name from granted_role;



+---------------+---------------+                                               
| NAME          | GRANTEE_NAME  |
|---------------+---------------|
| SECURITYADMIN | ACCOUNTADMIN  |
| USERADMIN     | SECURITYADMIN |
| SYSADMIN      | ACCOUNTADMIN  |
+---------------+---------------+

granted_role CTEはROLEの階層構造を取得するCTEで、nameがGrantするRole、grantee_roleがGrantされるRoleです。

次にRecursive CTEsとは、SELECT句AとSELECT句BをUNION ALLし、UNION ALLした結果はSELECT句Aとなり再度SELECT句BとUNION ALLする、これをSELECT句Bが空になるまで続けるサブクエリのことです。

with granted_role (name, grantee_name) as (
    select 
        name, 
        grantee_name
    from 
        snowflake.account_usage.grants_to_roles
    where 
        granted_on = 'ROLE'
    and 
        privilege = 'USAGE'
),
recursive_granted_role (name, grantee_name) as (
    select 
        name, 
        grantee_name
    from 
        granted_role
    union all
    select 
        granted_role.name as name, 
        recursive_granted_role.grantee_name as grantee_name
    from
        recursive_granted_role
    join
        granted_role
    on
        recursive_granted_role.name = granted_role.grantee_name
)

select name, grantee_name from recursive_granted_role;

+---------------+---------------+                                               
| NAME          | GRANTEE_NAME  |
|---------------+---------------|
| SECURITYADMIN | ACCOUNTADMIN  |
| USERADMIN     | ACCOUNTADMIN  |
| USERADMIN     | SECURITYADMIN |
| SYSADMIN      | ACCOUNTADMIN  |
+---------------+---------------+

Recurrent CTEsであるrecursive_granted_roleは、UNION ALLの後ろの句が無くなるまで、granted_roleのgrantee_roleに（直接的、間接的にかかわらず）GRANTしているRoleを探すので、Roleの全ての階層構造を取得できます。

必要なビュー

今回使用するビューになります。（account_usageスキーマ内のビューは、多少の遅延が起きるので、注意が必要です。）

snowflake.account_usage.grants_to_roles：ロールに付与している権限の情報
snowflake.account_usage.grants_to_users：ユーザーに付与しているロールの情報

ユーザーが権限を持つデータベース一覧を取得するクエリ

以下がRecursive CTEsを使用したクエリになります。

-- grants_to_roles ビューから、直接のGrantによってできたRoleの階層構造を取得するCTE
with granted_role as (
    select
        name,
        grantee_name
    from
        snowflake.account_usage.grants_to_roles
    where
        granted_on = 'ROLE'
    and
        privilege = 'USAGE'
),
-- granted_role cteから、直接的、間接的なGrantにかかわらない、Roleの階層構造を取得するRecursive CTEs
recursive_granted_role (name, grantee_name) as
    (
    select
        name,
        grantee_name
    from granted_role
    union all
    select
        granted_role.name as name,
        recursive_granted_role.grantee_name as grantee_name
    from
        recursive_granted_role
    join
        granted_role
    on
        recursive_granted_role.name = granted_role.grantee_name
),
-- RoleとRoleにGrantしたDatabaseを取得するCTE
role_database as (
  select
      name as database_name,
      grantee_name as role_name
  from
      snowflake.account_usage.grants_to_roles
  where
      granted_on = 'DATABASE'
  and
      deleted_on is null
),
-- RoleとRoleをGrantしたUserを取得するCTE
user_role as (
    select
        grantee_name as user_name,
        role
    from
        snowflake.account_usage.grants_to_users as grants_to_users
    where
        grants_to_users.deleted_on is null
)

-- ユーザーが権限を持つデータベース一覧を取得するクエリ
-- 複数Roleに同じGrantがされていることも考慮してGroupbyしてMaxを取る
select
    max(user_role.user_name) as user_name,
    max(role_database.database_name) as database_name
from
(
    -- recursive_granted_roleはGrantによるRoleの階層構造のみで、Role自身を含んでいないので、親Roleと子Roleが同じなデータをUNION ALLする
    select
        name,
        grantee_name
    from
        recursive_granted_role
    union all
    select
        name,
        name as grantee_name
    from
        snowflake.account_usage.roles
    where
        deleted_on is null
) as self_recursive_granted_role
-- 子RoleにGrantしたDatabase情報を取得するInner join
inner join
    role_database
on
    self_recursive_granted_role.name = role_database.role_name
-- UserにGrantしたRole情報を取得するInner join
inner join
    user_role as user_role
on
    user_role.role = self_recursive_granted_role.grantee_name
group by
    user_role.user_name,
    self_recursive_granted_role.grantee_name,
    role_database.database_name
order by
    user_role.user_name,
    role_database.database_name
;

おわりに

今回はRecursive CTEsと、ユーザーが権限を持つデータベース一覧を取得するクエリについて説明しました。クエリを変更すれば、テーブル一覧にすること、特定の権限に絞ること等も可能です。
Snowflakeについてお困りごとがあればDATUM STUDIOまでお問合せください。