When setting up system users in Snowflake for services or integrations (e.g., Salesforce, Mixpanel, ETL tools), you want a secure and scoped configuration. This guide walks you through how to create a user with a custom role, whitelist IPs, and enable key pair authentication. 1. Create a Role Start by creating a dedicated role for the user. CREATE ROLE IF NOT EXISTS my_service_role; CREATE ROLE IF NOT EXISTS my_service_role; Grant necessary privileges to the role. For example, access to a warehouse: GRANT USAGE ON WAREHOUSE compute_wh TO ROLE my_service_role;
-- Add SELECT, INSERT, etc., on required schemas/tables as needed GRANT USAGE ON WAREHOUSE compute_wh TO ROLE my_service_role;
-- Add SELECT, INSERT, etc., on required schemas/tables as needed 2. Create a Network Policy (Optional IP Whitelisting) Snowflake allows restricting access to specific IPs via network policies. CREATE OR REPLACE NETWORK POLICY my_service_policy
  ALLOWED_IP_LIST = ('123.45.67.89', '98.76.54.32')
  COMMENT = 'Policy for system user access'; CREATE OR REPLACE NETWORK POLICY my_service_policy
  ALLOWED_IP_LIST = ('123.45.67.89', '98.76.54.32')
  COMMENT = 'Policy for system user access'; 3. Create the User Link the user to the warehouse and the network policy. Replace my_service_user with your actual username. my_service_user CREATE USER IF NOT EXISTS my_service_user
  DEFAULT_WAREHOUSE = compute_wh
  NETWORK_POLICY = my_service_policy
  COMMENT = 'System user for system integration'; CREATE USER IF NOT EXISTS my_service_user
  DEFAULT_WAREHOUSE = compute_wh
  NETWORK_POLICY = my_service_policy
  COMMENT = 'System user for system integration'; Assign the role to the user: GRANT ROLE my_service_role TO USER my_service_user; GRANT ROLE my_service_role TO USER my_service_user; 4. Generate RSA Key Pair (For Key Pair Authentication) On your local machine or CI/CD environment: # Generate private key
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out my_service_private.p8
# Generate public key
openssl rsa -in my_service_private.p8 -pubout -out my_service_public.pub # Generate private key
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out my_service_private.p8
# Generate public key
openssl rsa -in my_service_private.p8 -pubout -out my_service_public.pub The .p8 file is your private key. Keep it secret. The .pub content goes into Snowflake. .p8 .pub 5. Add Public Key to User Copy the public key content (excluding headers) and set it: ALTER USER my_service_user
  SET rsa_public_key = 'MIIBIjANBgkq...IDAQAB'; ALTER USER my_service_user
  SET rsa_public_key = 'MIIBIjANBgkq...IDAQAB'; The user is now restricted to specific IPs, has scoped access via roles, and can authenticate via key pair auth. You can now use this user securely for automation or integrations.

This story contains new, firsthand information uncovered by the writer.

Public

Keep

The Safe Way to Create Snowflake System Users for Integrations

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Inline Governance in Snowflake: Tags, Masking Policies, and Roles That Scale

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Reasons to Get Your Cybersecurity Certification

10 Best Tactics For Your WooCommerce Store Security

The Noonification: If Youre a Facebook User, Thousands of Companies Are Watching You (1/19/2024)

121 Stories To Learn About Data Protection

Inline Governance in Snowflake: Tags, Masking Policies, and Roles That Scale

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Reasons to Get Your Cybersecurity Certification

10 Best Tactics For Your WooCommerce Store Security

The Noonification: If Youre a Facebook User, Thousands of Companies Are Watching You (1/19/2024)

121 Stories To Learn About Data Protection

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps