単一要素による認証からの移行に関するベストプラクティス¶

WITH users AS (
  SELECT DISTINCT
       user_id
      , name
      , login_name
      , type
      , email
  FROM
      SNOWFLAKE.ACCOUNT_USAGE.USERS
  WHERE
      DELETED_ON IS NULL)
SELECT
    u.user_id
    , a.event_timestamp
    , a.user_name
    , u.type
    , a.reported_client_type
    , a.first_authentication_factor
    , a.second_authentication_factor
  FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY AS a
    JOIN USERS u ON a.user_name = u.name
;

ALTER USER svc_user1 SET TYPE = SERVICE;
ALTER USER user1@example.COM SET TYPE = PERSON;
-- LEGACY APPLICATION ONLY

CREATE ACCOUNT <name> [ ADMIN_USER_TYPE = PERSON | SERVICE | LEGACY_SERVICE | NULL ];

CREATE AUTHENTICATION POLICY PROGRAMMATIC_ACCESS_USER_AUTH
  CLIENT_TYPES = ('DRIVERS', 'SNOWSQL')
  AUTHENTICATION_METHODS = ('OAUTH')
  SECURITY_INTEGRATIONS = ('<OAUTH SECURITY INTEGRATIONS>');

ALTER USER <SERVICE_USER> SET AUTHENTICATION POLICY PROGRAMMATIC_ACCESS_USER_AUTH;

CREATE AUTHENTICATION POLICY HUMAN_ACCESS_ACCOUNT_ENFORCE_MFA
  AUTHENTICATION_METHODS = ('SAML', 'PASSWORD')
  SECURITY_INTEGRATIONS = ('<SAML SECURITY INTEGRATIONS>')
  MFA_AUTHENTICATION_METHODS = ('PASSWORD'); -- enforce Snowflake MFA for native passwords only
  MFA_ENROLLMENT = 'REQUIRED';

CREATE AUTHENTICATION POLICY ACCOUNTADMIN_BREAKGLASS_MFA
  AUTHENTICATION_METHODS = ('PASSWORD')
  MFA_AUTHENTICATION_METHODS = ('PASSWORD') -- enforce Snowflake MFA for native passwords only
  MFA_ENROLLMENT = 'REQUIRED';

CREATE PASSWORD POLICY password_policy_account
  PASSWORD_MIN_LENGTH = 32
  -- PASSWORD_MAX_LENGTH = <integer>
  PASSWORD_MIN_UPPER_CASE_CHARS = 6
  PASSWORD_MIN_LOWER_CASE_CHARS = 6
  PASSWORD_MIN_NUMERIC_CHARS = 4
  PASSWORD_MIN_SPECIAL_CHARS = 8
  PASSWORD_MIN_AGE_DAYS = 10
  PASSWORD_MAX_AGE_DAYS = 30
  PASSWORD_MAX_RETRIES = 3
  PASSWORD_LOCKOUT_TIME_MINS = 30
  PASSWORD_HISTORY = 24
  COMMENT = '<string_literal>';

CREATE SESSION POLICY session_policy_account
  SESSION_IDLE_TIMEOUT_MINS = 240 -- Snowflake clients and programmatic clients
  SESSION_UI_IDLE_TIMEOUT_MINS = 20 -- For the Snowflake web interface
  COMMENT = '<string_literal>';

-- ACCESS FROM PUBLIC IP ADDRESSES
CREATE NETWORK RULE PROGRAMMATIC_ACCESS_USER_NET_RULE_PUBLIC
  TYPE = IPV4
  VALUE_LIST = ( 'PUBLIC IP1' , 'XX.XX.XX.XX/24' [ , ... ] )
  MODE =  INGRESS
;
-- ACCESS FROM PRIVATE NETWORK
CREATE NETWORK RULE PROGRAMMATIC_ACCESS_USER_NET_RULE_PL
  TYPE = AWSVPCEID
  VALUE_LIST = ( 'VPCE-123ABC3420C1931' , 'VPCE-123ABC3420C1932' )
  MODE =  INGRESS
;
-- RESTRICT ACCESS TO INTERNAL STAGE USING VPCE ID
CREATE NETWORK RULE PROGRAMMATIC_ACCESS_USER_NET_RULE_INTERNAL_STAGE
  TYPE = AWSVPCEID
  VALUE_LIST = ( 'VPCE-123ABC3420C1933' )
  MODE =  INTERNAL_STAGE
;
CREATE NETWORK POLICY PROGRAMMATIC_ACCESS_USER_NET_POLICY
  ALLOWED_NETWORK_RULE_LIST =
    (
     'PROGRAMMATIC_ACCESS_USER_NET_RULE_PUBLIC',
     'PROGRAMMATIC_ACCESS_USER_NET_RULE_PL',
     'PROGRAMMATIC_ACCESS_USER_NET_RULE_INTERNAL_STAGE'
    )
  --BLOCKED_NETWORK_RULE_LIST = ( 'OPTIONAL BLOCKED LIST OF IPS' )
;

-- ACCESS FROM PUBLIC IP ADDRESSES
CREATE NETWORK RULE HUMAN_ACCESS_ACCOUNT_NET_RULE_PUBLIC
  TYPE = IPV4
  VALUE_LIST = ( 'PUBLIC IP1' , 'XX.XX.XX.XX/24' [ , ... ] )
  MODE =  INGRESS
;
-- ACCESS FROM PRIVATE NETWORK
CREATE NETWORK RULE HUMAN_ACCESS_ACCOUNT_NET_RULE_PL
  TYPE = AWSVPCEID
  VALUE_LIST = ( 'VPCE-123ABC3420C1934' , 'VPCE-123ABC3420C1936' )
  MODE =  INGRESS
;
-- RESTRICT ACCESS TO INTERNAL STAGE USING VPCE ID
CREATE NETWORK RULE HUMAN_ACCESS_ACCOUNT_NET_RULE_INTERNAL_STAGE
  TYPE = AWSVPCEID
  VALUE_LIST = ( 'VPCE-123ABC3420C1937')
  MODE =  INTERNAL_STAGE
;
CREATE NETWORK POLICY ACCOUNT_LEVEL_NET_POLICY
  ALLOWED_NETWORK_RULE_LIST =
   (
    'HUMAN_ACCESS_ACCOUNT_NET_RULE_PUBLIC',
    'HUMAN_ACCESS_ACCOUNT_NET_RULE_PL',
    'HUMAN_ACCESS_ACCOUNT_NET_RULE_INTERNAL_STAGE'
   )
   --BLOCKED_NETWORK_RULE_LIST = ( 'OPTIONAL BLOCKED LIST OF IPS' )
;

-- FOR EVERY SERVICE PROGRAMMATIC ACCESS USER
ALTER USER SERVICE_USER_1 SET
TYPE = SERVICE
NETWORK_POLICY = PROGRAMMATIC_ACCESS_USER_NET_POLICY
AUTHENTICATION POLICY = PROGRAMMATIC_ACCESS_USER_AUTH;

ALTER ACCOUNT SET
SESSION POLICY = SESSION_POLICY_ACCOUNT;
PASSWORD POLICY = PASSWORD_POLICY_ACCOUNT;

SELECT *
  FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY(TIME_RANGE_START =>
    DATEADD('HOURS',-1,CURRENT_TIMESTAMP()),CURRENT_TIMESTAMP()))
  ORDER BY EVENT_TIMESTAMP;

ALTER ACCOUNT SET
AUTHENTICATION POLICY = HUMAN_ACCESS_ACCOUNT_ENFORCE_MFA;

ALTER USER SUPER_PROTECTED_ACCOUNTADMIN_1
AUTHENTICATION POLICY = ACCOUNTADMIN_DOUBLE_MFA;

ALTER USER BREAKGLASS_ACCOUNTADMIN_1
AUTHENTICATION POLICY = ACCOUNTADMIN_BREAKGLASS_MFA;

ALTER ACCOUNT SET
NETWORK_POLICY = ACCOUNT_LEVEL_NET_POLICY;

SELECT
  F.VALUE:ENTITY_ID::VARCHAR AS ENTITY_ID,
  F.VALUE:ENTITY_NAME::VARCHAR AS ENTITY_NAME,
  F.VALUE:ENTITY_OBJECT_TYPE::VARCHAR AS ENTITY_OBJECT_TYPE,
  F.VALUE:ENTITY_DETAIL AS ENTITY_DETAIL
FROM
  SNOWFLAKE.TRUST_CENTER.FINDINGS,
  LATERAL FLATTEN(INPUT => AT_RISK_ENTITIES) AS F;