Set Up ALTR To Work With Snowflake - Accounts created via Snowflake Partner Connect

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.

Upon connecting to ALTR via Snowflake Partner Connect, Snowflake automatically created a user, role, database, and warehouse for ALTR to use: PC_ALTR_USER, PC_ALTR_ROLE, PC_ALTR_DB, and PC_ALTR_WH, respectively. ALTR will automatically use these objects as the ALTR Service Account. This guide walks you through updating ALTR’s service account permissions to control and govern data in Snowflake.

Express Configuration (Recommended)

These instructions will configure the PC_ALTR_USER and PC_ALTR_ROLE to work with ALTR.

  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 PC_ALTR_DB.
    d. Schema to PUBLIC.

  3. 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(TRUE);

📘

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.

  1. Refresh the ALTR console.

Custom Configuration

These instructions will walk you through appropriately configuring the PC_ALTR_USER and PC_ALTR_ROLE to work with ALTR.

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

📘

Note:

Do not log in as PC_ALTR_USER here, but rather as your Snowflake user created by your Snowflake DBA.

  1. In a new Snowflake 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 PC_ALTR_DB.
    d. Schema to PUBLIC.

  2. Select a warehouse that ALTR can use to execute its queries to affect data control and governance.

GRANT USAGE ON WAREHOUSE "<EnterWarehouseToUse>" TO ROLE "PC_ALTR_ROLE";
ALTER USER "PC_ALTR_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 "PC_ALTR_ROLE";
GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE "PC_ALTR_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 "PC_ALTR_ROLE";
GRANT MONITOR ON WAREHOUSE "<WarehouseName2>" TO ROLE "PC_ALTR_ROLE";
GRANT MONITOR ON WAREHOUSE "<WarehouseName3>" TO ROLE "PC_ALTR_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 "PC_ALTR_ROLE";
GRANT CREATE SCHEMA            ON DATABASE "<DatabaseName1>" TO ROLE "PC_ALTR_ROLE";
GRANT USAGE  ON FUTURE SCHEMAS IN DATABASE "<DatabaseName1>" TO ROLE "PC_ALTR_ROLE";
GRANT USAGE  ON ALL SCHEMAS    IN DATABASE "<DatabaseName1>" TO ROLE "PC_ALTR_ROLE";
GRANT SELECT ON FUTURE TABLES  IN DATABASE "<DatabaseName1>" TO ROLE "PC_ALTR_ROLE";
GRANT SELECT ON ALL TABLES     IN DATABASE "<DatabaseName1>" TO ROLE "PC_ALTR_ROLE";
  1. Refresh the ALTR console.

Updated 21 days ago

Set Up ALTR To Work With Snowflake - Accounts created via Snowflake Partner Connect


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.