/
Snowflake Authorisation Best Practices: Secure Connection Guide

Snowflake Authorisation Best Practices: Secure Connection Guide

This document outlines the best practices for securely connecting Wondaris to your Snowflake data warehouse. Wondaris connects to Snowflake using user credentials stored in Google Cloud's Secret Manager within a customer-specific, isolated project. This guide emphasises data isolation and access control to ensure the security and integrity of your data.

Wondaris Connection Details:

When setting up the connection, you will need to provide the following information:

  • Connection Name: A descriptive name for the connection (required) - this is what is displayed in the Wondaris user interface to identify the connection.

  • Snowflake Account: Your Snowflake account identifier (e.g., your_account_identifier.snowflakecomputing.com) (required).

  • Snowflake Warehouse: The warehouse Wondaris will use for querying the view (required).

  • Snowflake Database User: The username of a Snowflake user with read access (required).

  • Snowflake User Role: (Optional) The role to be used during the connection.

  • Snowflake Region: (Optional) Specify if different from the default.

  • Snowflake Database Name: The name of the database containing your view.

  • Snowflake Password: The password for the specified Snowflake user.

Data Isolation

Data isolation is crucial for maintaining the confidentiality and integrity of your data. By implementing proper isolation techniques, you can minimise the risk of unauthorised access and ensure that Wondaris only accesses the data it needs.

Data Minimisation

The principle of data minimisation states that you should only collect and store the data that is absolutely necessary. Before exposing any data to Wondaris, carefully consider which data is required for your specific use case. Avoid granting access to entire tables or databases if only a subset of data is needed.

Views

The primary method for data isolation with Wondaris is through the use of secure views in Snowflake. Create views that specifically select only the necessary columns and rows from your underlying tables. This allows you to restrict the data that Wondaris can access without modifying your base tables.

In this data model, you will typically create a view on a single customer table and views on any number of event tables. Wondaris handles the joining of these tables (e.g., customer and event tables) using primary/foreign key relationships. It is important that both the customer and event tables have primary keys defined.

  • Example:

    • Customer Table: customer_table with columns like customer_id, name, email, etc. customer_id should be the primary key.

    • Event Tables: event_table_1 (e.g., page_views) with columns like event_id, customer_id, event_time, page_url, etc., and event_table_2 (e.g., purchases) with columns like purchase_id, customer_id, purchase_time, amount, product_id, etc. event_id and purchase_id should be the primary keys for their respective tables, and customer_id is the foreign key.

    You would create a customer view:

    CREATE VIEW customer_view AS SELECT customer_id, name, email FROM customer_table;

    And event views, selecting the relevant columns:

    CREATE VIEW page_views_view AS SELECT event_id, -- Primary Key customer_id, event_time, page_url FROM event_table_1; CREATE VIEW purchases_view AS SELECT purchase_id, -- Primary Key customer_id, purchase_time, amount, product_id FROM event_table_2;

Column-Level Security ADVANCED

For even finer-grained control, consider using Snowflake's column-level security features (e.g., masking policies). This allows you to mask or redact sensitive data within the view itself, further protecting it from unauthorised access.

Logical Data Segmentation

Organise your data in Snowflake in a way that supports logical segmentation. This can involve using separate databases, schemas, or tables to store different types of data. By segmenting your data, you can more easily control access and ensure that Wondaris only has access to the relevant segments.

  • Separate Databases/Schemas: Consider creating a dedicated schema or database in Snowflake specifically for the data that will be accessed by Wondaris. This provides a clear separation of data and simplifies access control.

  • Regular Audits: Regularly review the data that is exposed to Wondaris to ensure that it is still necessary and that the access controls are still appropriate. Data requirements may change over time, so it's important to update your views and permissions accordingly.

Write Access

If you are wanting to use Wondaris enrichments (except for RFM), Wondaris will need somewhere to write data to. Ideally you would create a specific dataset JUST for this purpose and provide write access to the service accounts for this purpose.

Data Access Permissions

Properly configuring data access permissions is essential for enforcing the principle of least privilege and ensuring that Wondaris can only perform the actions it is authorised to perform.

  • Read-Only User: For the initial setup, create a dedicated Snowflake user with read-only access to the view(s) you have created for Wondaris. This user should have the minimum necessary privileges to query the view and retrieve the required data.

    • Role-Based Access Control (RBAC): Snowflake's RBAC system allows you to define roles with specific privileges and then assign those roles to users. Create a dedicated role (e.g., WONDARIS_READ_ONLY) with the SELECT privilege on the view(s). Assign this role to the Snowflake user that Wondaris will use.

      • Example SQL:

        -- Create a role CREATE ROLE WONDARIS_READ_ONLY; -- Grant SELECT privilege on the view(s) GRANT SELECT ON VIEW your_database.your_schema.customer_view TO ROLE WONDARIS_READ_ONLY; GRANT SELECT ON VIEW your_database.your_schema.page_views_view TO ROLE WONDARIS_READ_ONLY; GRANT SELECT ON VIEW your_database.your_schema.purchases_view TO ROLE WONDARIS_READ_ONLY; -- Create a user CREATE USER wondaris_user PASSWORD = 'your_secure_password' DEFAULT_ROLE = WONDARIS_READ_ONLY; -- Grant the role to the user GRANT ROLE WONDARIS_READ_ONLY TO USER wondaris_user; -- Grant usage on the database and schema GRANT USAGE ON DATABASE your_database TO ROLE WONDARIS_READ_ONLY; GRANT USAGE ON SCHEMA your_database.your_schema TO ROLE WONDARIS_READ_ONLY;

  • Least Privilege: Always adhere to the principle of least privilege. Grant the Wondaris user only the minimum necessary permissions required for its intended function. Start with read-only access and only grant additional permissions if and when they are explicitly required.

  • Future Permissions (Write Access): If future use cases require Wondaris to write data back to Snowflake (e.g., for writing ML predictions), you will need to grant additional permissions. Follow these steps:

    • Dedicated Schema for Wondaris Output: Create a separate schema in Snowflake (e.g., wondaris_output) where Wondaris will write data. This isolates the data written by Wondaris from your other data.

    • Grant CREATE and WRITE Privileges: Grant the Wondaris user the following privileges on the wondaris_output schema:

      • CREATE TABLE: To allow Wondaris to create new tables in the schema.

      • INSERT: To allow Wondaris to insert data into tables in the schema.

      • Optionally, grant USAGE on the schema.

    • Specific Table Privileges: Instead of granting blanket privileges on the entire schema, you can grant privileges on specific tables as needed. This provides more granular control. If Wondaris will be writing to a specific table, grant the necessary privileges (e.g., INSERT, UPDATE) on that table only.

      • Example SQL for Write Access:

        -- Create a schema for Wondaris output CREATE SCHEMA your_database.wondaris_output; -- Grant privileges on the output schema GRANT CREATE TABLE ON SCHEMA your_database.wondaris_output TO ROLE WONDARIS_ROLE; GRANT INSERT ON SCHEMA your_database.wondaris_output TO ROLE WONDARIS_ROLE; GRANT USAGE ON SCHEMA your_database.wondaris_output TO ROLE WONDARIS_ROLE; -- Create a new role, or use an existing one CREATE ROLE WONDARIS_ROLE; GRANT ROLE WONDARIS_ROLE TO USER wondaris_user; -- If you want to grant access to a specific table GRANT INSERT ON TABLE your_database.wondaris_output.ml_predictions TO ROLE WONDARIS_ROLE;

  • Regular Security Reviews: Periodically review the permissions granted to the Wondaris user to ensure that they are still appropriate and that the principle of least privilege is being followed. As your use cases evolve, you may need to adjust the permissions accordingly.

 

Official Snowflake Documentation Links

These links are accurate at the time of writing this document and may be out of date - please ensure you are using the correct version of the documentation.