Snowflake のアカウント保護に関するセキュリティ ポリシーについてまとめてみる

--アカウントレベルでネットワークポリシーを有効化
ALTER ACCOUNT SET NETWORK_POLICY = my_policy;

--ユーザーレベルでネットワークポリシーを有効化
ALTER USER joe SET NETWORK_POLICY = admin_policy;

--ポリシー管理用データベースを作成
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE DATABASE policies_db;

--ポリシーごとのスキーマを作成
----ネットワークルール
CREATE OR REPLACE SCHEMA policies_db.network_rules;
----認証ポリシー
CREATE OR REPLACE SCHEMA policies_db.authentication_policies;
----パスワードポリシー
CREATE OR REPLACE SCHEMA policies_db.password_policies;
----セッションポリシー
CREATE OR REPLACE SCHEMA policies_db.session_policies;

--各ポリシー作成ロールを作成
USE ROLE USERADMIN;
----ネットワークルール
CREATE OR REPLACE ROLE network_rule_admin;
----認証ポリシー
CREATE OR REPLACE ROLE authentication_policy_admin;
----パスワードポリシー
CREATE OR REPLACE ROLE password_policy_admin;
----セッションポリシー
CREATE OR REPLACE ROLE session_policy_admin;

--ロール階層を設定
USE ROLE SECURITYADMIN;
GRANT ROLE network_rule_admin TO ROLE SECURITYADMIN;
GRANT ROLE authentication_policy_admin TO ROLE SECURITYADMIN;
GRANT ROLE password_policy_admin TO ROLE SECURITYADMIN;
GRANT ROLE session_policy_admin TO ROLE SECURITYADMIN;

--権限付与
----ネットワークルール
GRANT USAGE ON DATABASE policies_db TO ROLE network_rule_admin;
GRANT USAGE ON SCHEMA policies_db.network_rules TO ROLE network_rule_admin;
GRANT CREATE NETWORK RULE ON SCHEMA policies_db.network_rules TO ROLE network_rule_admin;

SHOW GRANTS TO ROLE network_rule_admin;

----認証ポリシー
GRANT USAGE ON DATABASE policies_db TO ROLE authentication_policy_admin;
GRANT USAGE ON SCHEMA policies_db.authentication_policies TO ROLE authentication_policy_admin;
GRANT CREATE AUTHENTICATION POLICY ON SCHEMA policies_db.authentication_policies TO ROLE authentication_policy_admin;

SHOW GRANTS TO ROLE authentication_policy_admin;

----パスワードポリシー
GRANT USAGE ON DATABASE policies_db TO ROLE password_policy_admin;
GRANT USAGE ON SCHEMA policies_db.password_policies TO ROLE password_policy_admin;
GRANT CREATE PASSWORD POLICY ON SCHEMA policies_db.password_policies TO ROLE password_policy_admin;

SHOW GRANTS TO ROLE password_policy_admin;

----セッションポリシー
GRANT USAGE ON DATABASE policies_db TO ROLE session_policy_admin;
GRANT USAGE ON SCHEMA policies_db.session_policies TO ROLE session_policy_admin;
GRANT CREATE SESSION POLICY ON SCHEMA policies_db.session_policies TO ROLE session_policy_admin;

SHOW GRANTS TO ROLE session_policy_admin;

USE ROLE network_rule_admin;
USE SCHEMA policies_db.network_rules;

--特定の IP アドレスから Snowflake サービストラフィックを許可またはブロックするために使用されるネットワークルールを作成
CREATE NETWORK RULE myip_access
  TYPE = IPV4
  VALUE_LIST = ('xxx.xxx.xxx.xxx/32')
  MODE = INGRESS
  COMMENT ='myip';

CREATE NETWORK RULE tableau_access
  TYPE = IPV4
  VALUE_LIST = ('141.163.208.0/23')
  MODE = INGRESS
  COMMENT ='Tableau Cloud';

 --確認
 SHOW NETWORK RULES;

--ネットワークポリシーを作成
USE ROLE SECURITYADMIN;
----ユーザーアクセス用
CREATE NETWORK POLICY person_user_net_policy
    ALLOWED_NETWORK_RULE_LIST = ('myip_access')
 ;

----外部ツールからのアクセス用
CREATE NETWORK POLICY service_net_policy
    ALLOWED_NETWORK_RULE_LIST = ('tableau_access')
 ;
--確認
SHOW NETWORK POLICIES;

--アカウントでネットワークポリシーを有効化
ALTER ACCOUNT SET NETWORK_POLICY = person_user_net_policy;

SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;

ALTER ACCOUNT UNSET NETWORK_POLICY;

ALTER USER user1 SET NETWORk_POLICY = service_net_policy;

SELECT GET_PRESIGNED_URL(@my_int_stg, 'sample_data.csv');

--ENFORCE_NETWORK_RULES_FOR_INTERNAL_STAGES パラメータを有効化
USE ROLE ACCOUNTADMIN;
ALTER ACCOUNT SET ENFORCE_NETWORK_RULES_FOR_INTERNAL_STAGES = true;
SHOW PARAMETERS LIKE 'ENFORCE_NETWORK_RULES_FOR_INTERNAL_STAGES' IN ACCOUNT;

--ユーザータイプを確認
SHOW USERS;
SELECT "name","type" FROM TABLE(RESULT_SCAN(-1));

--ユーザータイプを変更
ALTER USER <ユーザー名> SET TYPE = [PERSON | SERVICE | LEGACY_SERVICE | NULL];

USE ROLE authentication_policy_admin;
USE SCHEMA policies_db.authentication_policies;

--アカウント全体に適用する認証ポリシー
CREATE AUTHENTICATION POLICY require_mfa_authentication_policy
  AUTHENTICATION_METHODS = ('ALL') --デフォルト
  MFA_AUTHENTICATION_METHODS = ('PASSWORD') --デフォルト
  MFA_ENROLLMENT = REQUIRED  --デフォルト
  CLIENT_TYPES = ('ALL')  --デフォルト
;

--SnowSQLからの接続を想定した認証ポリシー
CREATE AUTHENTICATION POLICY snowsql_authentication_policy
  AUTHENTICATION_METHODS = ('KEYPAIR') 
  CLIENT_TYPES = ('SNOWSQL') 
;

--アカウントで認証ポリシーを有効化
USE ROLE SECURITYADMIN;
ALTER ACCOUNT SET AUTHENTICATION POLICY require_mfa_authentication_policy;

--アクティブな認証ポリシーを一覧
----POLICY_STATUS カラムでステータスが見れる
USE ROLE ACCOUNTADMIN;
SELECT *
FROM TABLE(
    policies_db.INFORMATION_SCHEMA.POLICY_REFERENCES(
    POLICY_NAME => 'policies_db.authentication_policies.require_mfa_authentication_policy'
  ));

USE ROLE SECURITYADMIN;
ALTER USER cli_user SET AUTHENTICATION POLICY policies_db.authentication_policies.snowsql_authentication_policy;

>snowsql -a <アカウント> -u cli_user
Password:
250001 (08001): Failed to connect to DB: <アカウント>.snowflakecomputing.com:443. Authentication attempt rejected by the current authentication policy.

>snowsql -a <アカウント> -u cli_user --private-key-path rsa_key.p8
Private Key Passphrase:
* SnowSQL * v1.3.2
Type SQL statements or !help
cli_user#(no warehouse)@(no database).(no schema)>

USE ROLE password_policy_admin;
USE SCHEMA policies_db.password_policies;

--パスワードポリシーを作成
CREATE PASSWORD POLICY PASSWORD_POLICY_PROD_1
    PASSWORD_MIN_LENGTH = 14
    PASSWORD_MAX_LENGTH = 24
    PASSWORD_MIN_UPPER_CASE_CHARS = 2
    PASSWORD_MIN_LOWER_CASE_CHARS = 2
    PASSWORD_MIN_NUMERIC_CHARS = 2
    PASSWORD_MIN_SPECIAL_CHARS = 2
    PASSWORD_MIN_AGE_DAYS = 1
    PASSWORD_MAX_AGE_DAYS = 30
    PASSWORD_MAX_RETRIES = 3
    PASSWORD_LOCKOUT_TIME_MINS = 30
    PASSWORD_HISTORY = 5
    COMMENT = 'production account password policy';

--アカウントレベルでパスワードポリシーを適用 
USE ROLE SECURITYADMIN;
ALTER ACCOUNT SET PASSWORD POLICY PASSWORD_POLICY_PROD_1;

--アクティブなパスワードポリシーを一覧
USE ROLE ACCOUNTADMIN;
SELECT *
FROM TABLE(
  policies_db.INFORMATION_SCHEMA.POLICY_REFERENCES(
    POLICY_NAME => 'policies_db.password_policies.PASSWORD_POLICY_PROD_1'
  ));
--無効化
ALTER ACCOUNT UNSET PASSWORD POLICY;

USE ROLE SECURITYADMIN;
ALTER USER <ユーザー名> SET PASSWORD POLICY policies_db.password_policies.PASSWORD_POLICY_PROD_1;

USE ROLE USERADMIN;
CREATE USER test_user
    DEFAULT_ROLE = PUBLIC
    TYPE = PERSON
    PASSWORD='<パスワード>'
    DEFAULT_SECONDARY_ROLES = ( 'ALL' )
;
--SECURITYADMIN を付与
USE ROLE SECURITYADMIN;
GRANT ROLE SECURITYADMIN TO USER test_user;

USE ROLE session_policy_admin;
USE SCHEMA policies_db.session_policies;

--アカウント全体に適用する認証ポリシー
CREATE SESSION POLICY block_secondary_roles
ALLOWED_SECONDARY_ROLES = (); --空とすることでセカンダリロールを許可しない

--アカウントレベルでパスワードポリシーを適用 
USE ROLE SECURITYADMIN;
ALTER ACCOUNT SET SESSION POLICY policies_db.session_policies.block_secondary_roles;

--アクティブなパスワードポリシーを一覧
USE ROLE ACCOUNTADMIN;
SELECT *
FROM TABLE(
  policies_db.INFORMATION_SCHEMA.POLICY_REFERENCES(
    POLICY_NAME => 'policies_db.session_policies.block_secondary_roles'
  ));
--無効化
ALTER ACCOUNT UNSET SESSION POLICY;

USE ROLE SECURITYADMIN;
ALTER USER <ユーザー名> SET SESSION POLICY policies_db.session_policies.block_secondary_roles;

USE ROLE ACCOUNTADMIN;
USE SCHEMA policies_db.network_rules;

--アカウントにネットワークポリシーを適用
CREATE OR REPLACE PROCEDURE set_net_policy_on_account(net_policy_name VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
    LET sql string := 'ALTER ACCOUNT SET NETWORK_POLICY = '|| :net_policy_name;
    EXECUTE IMMEDIATE :sql;
EXCEPTION
  WHEN OTHER THEN
  RAISE;
END;
$$
;

--アカウントからネットワークポリシーを解除
CREATE OR REPLACE PROCEDURE unset_net_policy_on_account()
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
    ALTER ACCOUNT UNSET NETWORK_POLICY;
$$
;

--ユーザーにネットワークポリシーを適用
CREATE OR REPLACE PROCEDURE set_net_policy_on_user(net_policy_name VARCHAR, user_name VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
    LET sql string := 'ALTER USER ' || :user_name || ' SET NETWORK_POLICY = '|| :net_policy_name;
    EXECUTE IMMEDIATE :sql;
EXCEPTION
  WHEN OTHER THEN
  RAISE;
END;
$$
;

--ユーザーからネットワークポリシーを解除
CREATE OR REPLACE PROCEDURE unset_net_policy_on_user(user_name VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
    LET sql string := 'ALTER USER ' || :user_name || ' UNSET NETWORK_POLICY';
    EXECUTE IMMEDIATE :sql;
EXCEPTION
  WHEN OTHER THEN
  RAISE;
END;
$$
;

USE ROLE SECURITYADMIN;
GRANT USAGE ON ALL PROCEDURES IN SCHEMA policies_db.network_rules TO ROLE network_rule_admin;
SHOW GRANTS TO ROLE network_rule_admin;

USE ROLE network_rule_admin;
USE SCHEMA policies_db.network_rules;

--現在の状態を確認
>SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;
+----------------+-------+---------+-------+-----------------------------------------------+--------+
| key            | value | default | level | description                                   | type   |
|----------------+-------+---------+-------+-----------------------------------------------+--------|
| NETWORK_POLICY |       |         |       | Network policy assigned for the given target. | STRING |
+----------------+-------+---------+-------+-----------------------------------------------+--------+

--ストアドプロシージャでアカウントにポリシーを適用
>CALL set_net_policy_on_account('PERSON_USER_NET_POLICY');
>SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;
+----------------+------------------------+---------+---------+-----------------------------------------------+--------+
| key            | value                  | default | level   | description                                   | type   |
|----------------+------------------------+---------+---------+-----------------------------------------------+--------|
| NETWORK_POLICY | PERSON_USER_NET_POLICY |         | ACCOUNT | Network policy assigned for the given target. | STRING |
+----------------+------------------------+---------+---------+-----------------------------------------------+--------+

--ストアドプロシージャでアカウントのポリシーを解除
>CALL unset_net_policy_on_account();
>SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;
+----------------+-------+---------+-------+-----------------------------------------------+--------+
| key            | value | default | level | description                                   | type   |
|----------------+-------+---------+-------+-----------------------------------------------+--------|
| NETWORK_POLICY |       |         |       | Network policy assigned for the given target. | STRING |
+----------------+-------+---------+-------+-----------------------------------------------+--------+

USE ROLE network_rule_admin;
USE SCHEMA policies_db.network_rules;

--現在の状態を確認
USE ROLE USERADMIN;
>SHOW PARAMETERS LIKE 'NETWORK_POLICY' FOR USER test_user;
+----------------+-------+---------+-------+-----------------------------------------------+--------+
| key            | value | default | level | description                                   | type   |
|----------------+-------+---------+-------+-----------------------------------------------+--------|
| NETWORK_POLICY |       |         |       | Network policy assigned for the given target. | STRING |
+----------------+-------+---------+-------+-----------------------------------------------+--------+

--ストアドプロシージャでユーザーにポリシーを適用
USE ROLE network_rule_admin;
CALL set_net_policy_on_user('person_user_net_policy','test_user');

--現在の状態を確認
USE ROLE USERADMIN;
>SHOW PARAMETERS LIKE 'NETWORK_POLICY' FOR USER test_user;
+----------------+------------------------+---------+-------+-----------------------------------------------+--------+
| key            | value                  | default | level | description                                   | type   |
|----------------+------------------------+---------+-------+-----------------------------------------------+--------|
| NETWORK_POLICY | PERSON_USER_NET_POLICY |         | USER  | Network policy assigned for the given target. | STRING |
+----------------+------------------------+---------+-------+-----------------------------------------------+--------+

--ストアドプロシージャでユーザーのポリシーを解除
USE ROLE network_rule_admin;
CALL unset_net_policy_on_user('test_user');

--現在の状態を確認
USE ROLE USERADMIN;
>SHOW PARAMETERS LIKE 'NETWORK_POLICY' FOR USER test_user;
+----------------+-------+---------+-------+-----------------------------------------------+--------+
| key            | value | default | level | description                                   | type   |
|----------------+-------+---------+-------+-----------------------------------------------+--------|
| NETWORK_POLICY |       |         |       | Network policy assigned for the given target. | STRING |
+----------------+-------+---------+-------+-----------------------------------------------+--------+