Onboard a Snowflake App to SSPM

1. From SSPM, get the redirect URL that you will need when you create your OAuth 2.0 security integration in Snowflake. To get this information, you will begin the onboarding process in SSPM, but you will not complete the process.

   1. Log in to Strata Cloud Manager.
   2. Select ConfigurationSaaS SecurityPosture SecurityApplicationsAdd Application and click the Snowflake tile.
   3. On the Posture Security tab, Add New instance.
   4. Log in with Credentials.
      SSPM displays a page for onboarding a Snowflake instance. The Redirect URL field displays the redirect URL value.
   5. Copy the URL and paste it into a text file.
      Don't continue to the next step unless you have copied the redirect URL. You will need to specify this URL later when you're configuring your OAuth 2.0 security integration in Snowflake.
   6. Because you won't be completing the onboarding process until after you have gathered the necessary configuration information, Cancel the onboarding process and return to the Apps Onboarding page.
2. Identify the Snowflake administrator account that you will use to create your OAuth 2.0 security integration.

   Required Permissions: The account that you will use to create your OAuth 2.0 security integration must be assigned to the ACCOUNTADMIN role.

   To verify that the account is assigned to the necessary roles, complete the following steps:

   1. From the left navigation pane in Snowflake, select AdminUsers & Roles.
   2. Select the name of the user whose roles you want to verify.
   3. On the information page for the user, locate the Granted Roles section. Verify that the user is assigned to the ACCOUNTADMIN role. If the user isn’t assigned to this role, you can Grant Role.
3. Create an OAuth 2.0 security integration.

   When you later onboard Snowflake to SSPM, you will supply SSPM with the application credentials (Client ID and Client Secret) of your OAuth 2.0 security integration. SSPM will redirect you to log in to Snowflake to allow the requested permissions.

   In this step, you will create an SQL worksheet to run a series of SQL statements that will create the OAuth 2.0 security integration and also the user account that you will use to log in to Snowflake to allow the requested permissions. The new user will be assigned to a new custom role that will limit the user's permissions to only the permissions that SSPM requires.

   1. Open a web browser to the Snowflake login page, and log in to the administrator account you identified earlier.
   2. From the Snowflake home page, select ProjectsWorksheets.
   3. On the Worksheets page, select + SQL Worksheet to create an SQL worksheet.
   4. In the new worksheet that you created, select the ACCOUNTADMIN role and a warehouse that the ACCOUNTADMIN role has privileges to use.
   5. Paste the following SQL statements into your worksheet, and replace the following variables in the worksheet with information specific to the OAuth 2.0 security integration that you're creating.
      1. The SQL statements first create a warehouse and a custom role for the user who will grant SSPM permissions during onboarding. In the SQL statements that we provided, replace all occurrences of <warehouse_name> and <custom_role_name> with names for the new warehouse and custom role.
      2. The SQL statements then create the user account that you will use during onboarding to log in to Snowflake to grant SSPM the permissions that it needs. Replace all occurrences of <username> with the name you want to give to this user. Also replace <password> and <email> with a user name and password for the new user. Make note of the user name and password.
         Don’t continue to the next step unless you have noted the user name and password. You will need this information to log in to Snowflake during the onboarding process.
      3. The SQL statements then grant the required permissions to the custom role and assign the user to the custom role. Double check that you have replaced all the variables in these sections.
      4. Finally, the SQL statements create the OAuth 2.0 security integration. Replace <integration_name> with a name for your OAuth 2.0 security integration. Replace <redirect_url_from_onboarding_page> with the redirect URL that you obtained from SSPM.

   // Create minimum size warehouse and custom role CREATE OR REPLACE WAREHOUSE <warehouse_name> WITH WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 30 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE; CREATE OR REPLACE ROLE <custom_role_name>; // Create a new user CREATE OR REPLACE USER <username> PASSWORD='<password>' EMAIL='<email>' DEFAULT_ROLE=<custom_role_name> DEFAULT_WAREHOUSE=<warehouse_name>; // Give required permissions to the role GRANT OPERATE ON WAREHOUSE <warehouse_name> TO ROLE <custom_role_name>; -- To resume warehouse GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE <custom_role_name>; -- To run SQL queries GRANT MONITOR ON ACCOUNT TO ROLE <custom_role_name>; -- Grants permission to approve OAuth integration GRANT APPLY PASSWORD POLICY ON ACCOUNT TO ROLE <custom_role_name>; -- To view password policy description GRANT APPLY AUTHENTICATION POLICY ON ACCOUNT TO ROLE <custom_role_name>; -- To view authentication policy description GRANT APPLY SESSION POLICY ON ACCOUNT TO ROLE <custom_role_name>; -- To view session policy description GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE <custom_role_name>; -- To get all users // Assign the new user to the role GRANT ROLE <custom_role_name> TO USER <username>; // Create an OAuth security integration CREATE OR REPLACE SECURITY INTEGRATION <integration_name> TYPE = OAUTH OAUTH_CLIENT = CUSTOM OAUTH_CLIENT_TYPE = 'CONFIDENTIAL' OAUTH_REDIRECT_URI = '<redirect_url_from_onboarding_page>' OAUTH_ISSUE_REFRESH_TOKENS = TRUE OAUTH_REFRESH_TOKEN_VALIDITY = 7776000 ENABLED = TRUE; // Get required onboarding details EXECUTE IMMEDIATE $$ DECLARE onboarding_details RESULTSET; target_integration STRING := '<integration_name>'; BEGIN EXECUTE IMMEDIATE 'DESC SECURITY INTEGRATION ' || :target_integration; onboarding_details := ( WITH extracted_secrets AS ( SELECT PARSE_JSON(SYSTEM$SHOW_OAUTH_CLIENT_SECRETS(:target_integration)) AS secret_json ) SELECT 'LOCATOR_URL' AS ATTRIBUTE, REGEXP_SUBSTR($3, 'https?://[^/]+') AS SETTING FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE $1 = 'OAUTH_ALLOWED_TOKEN_ENDPOINTS' UNION ALL SELECT secret_fields.key AS ATTRIBUTE, secret_fields.value::STRING AS SETTING FROM extracted_secrets, LATERAL FLATTEN(input => secret_json) secret_fields WHERE secret_fields.key IN ('OAUTH_CLIENT_ID', 'OAUTH_CLIENT_SECRET') ); RETURN TABLE(onboarding_details); END; $$;

4. Execute the SQL statements and copy the information that SSPM requires for onboarding.

   1. Click the run button in the upper-right corner of the worksheet page to execute the SQL statements.

      Snowflake will create the security integration, custom role, and user account. In the Results area of the worksheet page, Snowflake displays your account URL and the security integration's application credentials (Client ID and Client Secret).
   2. Copy the account URL (LOCATOR_URL), Client ID (OAUTH_CLIENT_ID),and Client Secret (OAUTH_CLIENT_SECRET) and paste them into a text file.

      Don't continue to the next step unless you have copied your account URL, the Client ID, and the Client Secret. You will provide this information to SSPM during the onboarding process.
5. Connect SSPM to your Snowflake instance.

   In SSPM, complete the following steps to enable SSPM to connect to your Snowflake instance.

   1. Log in to Strata Cloud Manager.
   2. Select ConfigurationSaaS SecurityPosture SecurityApplicationsAdd Application and click the Snowflake tile.
   3. On the Posture Security tab, Add New instance.
   4. Log in with Credentials.
   5. Enter your account URL and the application credentials (Client ID and Client Secret).
   6. Connect.

      SSPM redirects you to the Snowflake login page.
   7. Log in to the new user account that you assigned to the new custom role.

      Snowflake displays a consent form that details the access permissions that SSPM requires.
   8. Review the consent form and allow the requested permissions.