2025年1月20日にデフォルトで有効化されるバンドルにおいてセカンダリロールが全ユーザーに対して有効化されます

モダンデータスタック(MDS)

#Snowflake

さがら

2025.01.17

さがらです。
日本時間2025年1月17日の朝に、「Notice: Changing the default value of DEFAULT_SECONDARY_ROLES to ALL」というメールがSnowflakeから届いていました。
内容としては、2025年1月20日にリリース予定の9.2で、「セカンダリロールを全ユーザー有効化するバンドル2024_08」がデフォルトで有効化されてしまうという内容になります。2025年2月にはこのバンドル2024_08が全アカウントで一般提供となり無効化できなくなります。
一方で、セカンダリロールが有効化されると、意図せぬ組み合わせでのテーブルJOINなどされてしまうリスクもあるため、有効化されていない方が多いと思います。
そのため、このセカンダリロールの有効に関する対策について同じメール内で対策が言及されていました。この対策の内容を参考にした対策を、本記事でやってみたのでまとめてみます。
https://community.snowflake.com/s/article/default-secondary-roles-all-overview-and-additional-explanations?mkt_tok=MjUyLVJGTy0yMjcAAAGYD-TqeUe0ohBFkoVx6x1D75FjEAsPZ49UNuuVlkeSvdffV_Y4DLK2aNMxdO4tnaCLqoiwzdKvVt5-BBNsgcC3hLBLbIHVWihm8TMK0ynYiBpRjl7MSw#f1
 想定シチュエーション「2025年1月20日以降、対象のバンドル2024_08が有効化されて、全ユーザーのセカンダリロールが有効化されてしまったがどう対処しよう」というシチュエーションを想定してやってみます。
バンドル2024_08が有効化されると、各ユーザーのdefault_secondary_roles列を見ると["ALL"]となってしまいます。これは、バンドル2024_08の有効化時にdefault_secondary_roles列の値がnullであったユーザーのみ["ALL"]となる仕様となっています。
show users;
SELECT "name", "default_secondary_roles"
FROM TABLE(result_scan(last_query_id()));
 対策ここから下記の記事に記載の内容を参考に、対策をしてみます。
https://community.snowflake.com/s/article/default-secondary-roles-all-overview-and-additional-explanations?mkt_tok=MjUyLVJGTy0yMjcAAAGYD-TqeUe0ohBFkoVx6x1D75FjEAsPZ49UNuuVlkeSvdffV_Y4DLK2aNMxdO4tnaCLqoiwzdKvVt5-BBNsgcC3hLBLbIHVWihm8TMK0ynYiBpRjl7MSw#f1
注意点として、この記事に記載のストアドプロシージャだと、default_secondary_rolesの値がnullの場合のみ[]に書き換えてくれます。つまり、バンドル2024_08が有効化されたあとはdefault_secondary_rolesの値が["ALL"]になってしまっているため、このストアドプロシージャは実行しても何の意味がありません。
また、このストアドプロシージャをACCOUNTADMINで実行しようとすると、デフォルトのSNOWFLAKEというユーザーに対して変更が出来ずエラーになってしまいます。
そのため、以下の修正を行ったストアドプロシージャを以下に記します。注意点としては、元々セカンダリロールを有効化して運用していた場合は、その方も[]に変更してしまいます。　この点だけご注意ください。
default_secondary_rolesの値が["ALL"]の場合には[]に変更する
ユーザー名がSNOWFLAKEの場合はスキップする
CREATE OR REPLACE PROCEDURE update_default_secondary_roles_for_all()
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
let updated_users = [];
let users = snowflake.execute({sqlText: "SHOW USERS"});
while (users.next()) {
    let username = users.getColumnValue("name");
    let dsr = users.getColumnValue("default_secondary_roles");

    // SNOWFLAKEユーザーの場合はスキップ
    if (username === 'SNOWFLAKE') {
        continue;
    }

    // default_secondary_rolesが["ALL"]の場合のみ処理を実行
    if (dsr === '["ALL"]') {
        snowflake.execute({
            sqlText: "alter user identifier(?) set default_secondary_roles=()",
            binds: ["\"" + username + "\""],
        });
        updated_users.push(username);
    }
}
return updated_users;
$$;
この定義後、以下のクエリでストアドプロシージャを実行します。
USE ROLE ACCOUNTADMIN; 
CALL update_default_secondary_roles_for_all();
実行後、以下のクエリで確認してみると、default_secondary_rolesの値が[]になっています。これでバンドル2024_08有効後も、セカンダリロールが無効化されました！
show users;
SELECT "name", "default_secondary_roles"
FROM TABLE(result_scan(last_query_id()));