Set Up ALTR to Work with Snowflake

This document will guide you through setting up ALTR to work with the Standard and Enterprise Editions of Snowflake.

Before connecting Snowflake databases to ALTR, you must first create ALTR’s service account by permissioning a Snowflake user and role for ALTR to programmatically use. This guide walks you through configuring ALTR’s service account.

📘

Snowflake Partner Connect Users

If you created your ALTR account using Snowflake Partner Connect, follow these separate instructions: Set Up ALTR To Work with Snowflake - Snowflake Partner Connect

To create the service account, you will need a Snowflake user that can assume the role ACCOUNTADMIN. Reach out to your Snowflake administrator to set this up.

Accounts Created via ALTR.com

Express Configuration (Recommended)

These instructions will create a Snowflake User named ALTR_SERVICE_USER, a Snowflake Role named ALTR_SERVICE_ROLE, and a Snowflake Warehouse named ALTR_SERVICE_WH.

  1. Log into Snowflake as a user with the ACCOUNTADMIN role.

  2. In a new Worksheet under the “context” settings, set the following:

    a. Role to ACCOUNTADMIN. If you cannot select ACCOUNTADMIN, ask your Snowflake DBA to grant your user permission to assume it to proceed.
    b. Warehouse to any warehouse you are permissioned to use.
    c. Database to any database where your user can create a procedure.
    d. Schema to any schema where your user can create a procedure.

  3. Copy the CREATE USER statement below into your Snowflake Worksheet. Replace <password> with a secure password.

CREATE USER "ALTR_SERVICE_USER" WITH
  PASSWORD = '<password>'
  MUST_CHANGE_PASSWORD = FALSE;

📘

Note:

Be sure to remember this username and password: you will need them later when connecting Snowflake databases in the ALTR console.

  1. ALTR provides a Snowflake procedure named SETUP_ALTR_SERVICE_ACCOUNT for service account configuration. To add this procedure to your Snowflake database, copy and paste the below CREATE OR REPLACE PROCEDURE statement into the Worksheet and press the “Run” button.
CREATE OR REPLACE PROCEDURE SETUP_ALTR_SERVICE_ACCOUNT(IS_PARTNER_CONNECT BOOLEAN)
RETURNS STRING
LANGUAGE JAVASCRIPT
VOLATILE
EXECUTE AS CALLER
AS $$
  // ********************************************
  // Stored Procedure Caller Report
  // ********************************************

  function StoredProcedureReport() {
    this.isSuccess = true;
    this.successMessages = [];
    this.failMessages = [];

    this.fail = function(message) {
      this.isSuccess = false;
      this.failMessages.push(message);
    }

    this.success = function(message) {
      this.successMessages.push(message);
    }

    this.callerReport = function() {
      let report = '';

      function appendMessages(prefix, messages) {
        const totalPrefix = '\n[' + prefix + ']: ';
        for (const message of messages) {
          report += totalPrefix + message;
        }
      }

      if (this.isSuccess) {
        report = 'SUCCEEDED!';
      } else {
        report = 'FAILED!';
        appendMessages('FAILURE', this.failMessages);
      }

      appendMessages('SUCCESS', this.successMessages);

      return report;
    }
  }

  const storedProcedureReport = new StoredProcedureReport();

  // ********************************************
  // Utilities
  // ********************************************

  function isNullOrEmpty(str) {
    return typeof('string') !== typeof(str) || str.length < 1;
  }

  // https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html
  function execQuery(queryString) {
    return snowflake.execute({sqlText:queryString});
  }

  function execStatement(queryString, handleError) {
    try {
      const resultSet = execQuery(queryString);
      storedProcedureReport.success(queryString);
      return true;
    } catch (error) {
      let reportFailure = true;
      if ('function' === typeof(handleError)) {
        try {
          reportFailure = handleError(queryString, error);
        } catch (error) {
          reportFailure = true;
        }
      }
      if (reportFailure) {
        storedProcedureReport.fail(queryString + ": " + error);
      }
    }
    return false;
  }

  // https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html
  function delimitIdentifier(identifier) {
    return '"' + identifier.replace(/\"/g, '""') + '"';
  }

  function getDelimitedComment(objectType) {
    return "'This " + objectType + " is used by ALTR to help simplify governance and control over data in Snowflake. Please do not modify without speaking with ALTR Support.'";
  }

  // ********************************************
  // Main functionality
  // ********************************************

  function permissionRole(delimitedRoleName) {
    // ********************************************
    // Grant account-level privileges on the role
    // ********************************************

    let targetRoleExists = true;
    execStatement('GRANT CREATE DATABASE ON ACCOUNT TO ROLE ' + delimitedRoleName, function(queryString, error) {
      const errorString = '' + error;
      if (errorString.includes('Role ') && errorString.includes(' does not exist or not authorized.')) {
        // Use this initial grant to check validity of the input role.
        targetRoleExists = false;

        let extraInfo = '';
        const upperCaseTargetRoleName = ALTR_SERVICE_ROLE_NAME.toUpperCase();
        if (ALTR_SERVICE_ROLE_NAME !== upperCaseTargetRoleName) {
          extraInfo = '. Perhaps you meant role ' + upperCaseTargetRoleName;
        }

        storedProcedureReport.fail('You called this stored procedure with a role that does not exist: ' + ALTR_SERVICE_ROLE_NAME + extraInfo + '.');

        return false;
      } else {
        // Some other error when executing the grant. Report the
        // error, but keep executing.
        return true;
      }
    });

    if (!targetRoleExists) return;

    execStatement('GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE ' + delimitedRoleName, function(queryString, error) {
      // This stored procedure is written to execute on all editions of Snowflake,
      // including those that do not support masking policies. So, if we happen
      // to be on the latter, swallow this error silently and move on.
      return !('' + error).includes('Unsupported feature');
    });

    execStatement('GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE ' + delimitedRoleName);

    // ********************************************
    // Grant privileges on warehouses to role
    // ********************************************

    const warehouseNames = [];

    let resultSet = execQuery('SHOW WAREHOUSES');
    while (resultSet.next()) {
      warehouseNames.push(resultSet.getColumnValueAsString('name'));
    }

    for (const warehouseName of warehouseNames) {
      execStatement('GRANT MONITOR ON WAREHOUSE ' + delimitIdentifier(warehouseName) + ' TO ROLE ' + delimitedRoleName);
    }

    // ********************************************
    // Grant database-level privileges to role
    // ********************************************

    const databaseNames = [];

    resultSet = execQuery('SHOW DATABASES');
    while (resultSet.next()) {
      const origin = resultSet.getColumnValueAsString('origin');
      if (isNullOrEmpty(origin)) {
        // Databases with a non-null origin are shared or otherwise
        // external databases and we do not want to apply governance
        // here, so we exclude them.
        databaseNames.push(resultSet.getColumnValueAsString('name'));
      }
    }

    for (const databaseName of databaseNames) {
      const delimitedDatabaseName = delimitIdentifier(databaseName);
      execStatement('GRANT USAGE ON DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName);
      execStatement('GRANT CREATE SCHEMA ON DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName);
      execStatement('GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName);
      execStatement('GRANT USAGE ON ALL SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName);
      execStatement('GRANT SELECT ON FUTURE TABLES IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName);
      execStatement('GRANT SELECT ON ALL TABLES IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName);
    }
  }

  function permissionUser(delimitedRoleName, delimitedUserName, delimitedWarehouseName) { 
    // Create the role for ALTR and assign the role to SYSADMIN as recommened by
    // Snowflake to help with troubleshooting and debugging if necessary.
    execStatement('CREATE ROLE IF NOT EXISTS ' + delimitedRoleName);
    execStatement('ALTER ROLE ' + delimitedRoleName + ' SET COMMENT = ' + getDelimitedComment('role'));
    execStatement('GRANT ROLE ' + delimitedRoleName + ' TO ROLE SYSADMIN');

    // Create the warehouse for ALTR and grant usage of it to the ALTR role.
    // Though XSMALL is the default for WAREHOUSE_SIZE and TRUE is the default
    // for AUTO_RESUME, we include them anyway because if the defaults of these
    // properties ever change, there will be serious functionality and cost concerns.
    execStatement('CREATE WAREHOUSE IF NOT EXISTS ' + delimitedWarehouseName
      + ' WITH WAREHOUSE_SIZE = XSMALL SCALING_POLICY = ECONOMY AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE');
    execStatement('ALTER WAREHOUSE ' + delimitedWarehouseName + ' SET COMMENT = ' + getDelimitedComment('warehouse'));
    execStatement('GRANT USAGE ON WAREHOUSE ' + delimitedWarehouseName + ' TO ROLE ' + delimitedRoleName);

    // Transfer ownership of the warehouse to customer SYSADMIN. They can then
    // assign resource monitors and other customer-specific configuration.
    // Though this procedure will necessarily run as ACCOUNTADMIN, Snowflake
    // recommeneds that database objects be owned by SYSADMIN.
    execStatement('GRANT OWNERSHIP ON WAREHOUSE ' + delimitedWarehouseName + ' TO ROLE SYSADMIN COPY CURRENT GRANTS');

    // Permission the ALTR role. Be sure to do this after you create the
    // ALTR warehouse so the ALTR role gets monitor on that warehouse.
    permissionRole(delimitedRoleName);

    // Finally, assign the role to the user (the latter must already be created)
    // and set appropriate defaults.
    execStatement('GRANT ROLE ' + delimitedRoleName + ' TO USER ' + delimitedUserName);
    execStatement('ALTER USER ' + delimitedUserName + ' SET DEFAULT_ROLE = ' + delimitedRoleName);
    execStatement('ALTER USER ' + delimitedUserName + ' SET DEFAULT_WAREHOUSE = ' + delimitedWarehouseName);
    execStatement('ALTER USER ' + delimitedUserName + ' SET COMMENT = ' + getDelimitedComment('user'));
  }

  // ********************************************
  // Main
  // ********************************************

  // This script must execute as ACCOUNTADMIN so that
  // we can grant the target role permissions on objects
  // that the target role may not even be able to see.
  try {
    execQuery("USE ROLE ACCOUNTADMIN");
  } catch (error) {
    storedProcedureReport.fail('You must call this stored procedure with a user and role that can assume role ACCOUNTADMIN: ' + error);
    return storedProcedureReport.callerReport();
  }

  if (IS_PARTNER_CONNECT) {
    permissionRole('"PC_ALTR_ROLE"');
  } else {
    permissionUser('"ALTR_SERVICE_ROLE"', '"ALTR_SERVICE_USER"', '"ALTR_SERVICE_WH"');
  }

  return storedProcedureReport.callerReport();
$$;
  1. Copy the below CALL statement into your Snowflake Worksheet and press the “Run” button to invoke the procedure. This may take multiple minutes to execute, depending on the complexity of your Snowflake configuration.
CALL SETUP_ALTR_SERVICE_ACCOUNT(FALSE);

📘

Note:

If there were any errors, the response will start with FAILED!. If this is the case, please consult your Snowflake DBA or contact ALTR Support.

Custom Configuration

These instructions will walk you through creating, and appropriately configuring, a Snowflake User named ALTR_SERVICE_USER and a Snowflake Role named ALTR_SERVICE_ROLE. These are required to properly connect ALTR to Snowflake.

  1. Log into Snowflake as a user with the ACCOUNTADMIN role.



  2. In a new Worksheet under the “context” settings, set the following:

    a. Role to ACCOUNTADMIN. If you cannot select ACCOUNTADMIN, ask your Snowflake DBA to grant your user permission to assume it to proceed.
    b. Warehouse to any warehouse you are permissioned to use.
    c. Database to any database where your user can create a procedure.
    d. Schema to any schema where your user can create a procedure.



  3. Create ALTR’s service role. This is the role under which ALTR’s service user will act to govern and control data in Snowflake. To aid in debugging and management, allow the SYSADMIN role to assume this role.

CREATE ROLE "ALTR_SERVICE_ROLE";
GRANT  ROLE "ALTR_SERVICE_ROLE" TO ROLE "SYSADMIN";
  1. Create the service user that will take on the previously created service role. Replace <password> with a secure password.
CREATE USER "ALTR_SERVICE_USER" WITH
    PASSWORD             = '<password>'
    MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE "ALTR_SERVICE_ROLE" TO USER "ALTR_SERVICE_USER";
ALTER USER "ALTR_SERVICE_USER" SET DEFAULT_ROLE = "ALTR_SERVICE_ROLE";

📘

Note:

Be sure to remember this username and password: you will need them later when connecting Snowflake databases in the ALTR console.

  1. Select a warehouse that ALTR can use to execute its queries to affect data control and governance.
GRANT USAGE ON WAREHOUSE "<EnterWarehouseToUse>" TO ROLE "ALTR_SERVICE_ROLE";
ALTER USER "ALTR_SERVICE_USER" SET DEFAULT_WAREHOUSE = "<EnterWarehouseToUse>";
  1. Grant ALTR’s service role CREATE INTEGRATION and APPLY MASKING POLICY privileges. ALTR affects data control and governance using external functions reached via a Snowflake API integration and via masking policies applied to columns with sensitive data.
GRANT CREATE INTEGRATION   ON ACCOUNT TO ROLE "ALTR_SERVICE_ROLE";
GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE "ALTR_SERVICE_ROLE";

📘

Note:

If you are using Snowflake Standard Edition, you will be unable to grant APPLY MASKING POLICY. See Using ALTR with Snowflake Standard for more information on how to set up Snowflake so ALTR can affect data control and governance on columns.

  1. Grant ALTR’s service role MONITOR on all warehouses. To affect data control and governance, ALTR needs to view queries executed on a warehouse to determine row count and execution status of governed data.
GRANT MONITOR ON WAREHOUSE "<WarehouseName1>" TO ROLE "ALTR_SERVICE_ROLE";
GRANT MONITOR ON WAREHOUSE "<WarehouseName2>" TO ROLE "ALTR_SERVICE_ROLE";
GRANT MONITOR ON WAREHOUSE "<WarehouseName3>" TO ROLE "ALTR_SERVICE_ROLE";
  1. Grant ALTR’s service role privileges on each database you’re connecting to ALTR. To govern columns, ALTR needs permissions on every object in the column ownership hierarchy: database, schema, and table. ALTR will automatically create a schema named ALTR_DSAAS in each database to hold masking policies, external functions, and UDFs necessary to affect ALTR data control and governance.
GRANT USAGE                   ON DATABASE "<DatabaseName1>" TO ROLE "ALTR_SERVICE_ROLE";
GRANT CREATE SCHEMA           ON DATABASE "<DatabaseName1>" TO ROLE "ALTR_SERVICE_ROLE";
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE "<DatabaseName1>" TO ROLE "ALTR_SERVICE_ROLE";
GRANT USAGE ON ALL SCHEMAS    IN DATABASE "<DatabaseName1>" TO ROLE "ALTR_SERVICE_ROLE";
GRANT SELECT ON FUTURE TABLES IN DATABASE "<DatabaseName1>" TO ROLE "ALTR_SERVICE_ROLE";
GRANT SELECT ON ALL TABLES    IN DATABASE "<DatabaseName1>" TO ROLE "ALTR_SERVICE_ROLE";

Updated a day ago

Set Up ALTR to Work with Snowflake


This document will guide you through setting up ALTR to work with the Standard and Enterprise Editions of Snowflake.

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.