Required Snowflake Objects

Regardless of how your ALTR account is created, there are five required Snowflake objects. If using Partner Connect, these objects are created for you. If ALTR Support created your account, you have to create the user, the role, database and the stored procedure; the stored procedure creates the warehouse.

Warning

Do not change the SETUP_ALTR_SERVICE_ACCOUNT stored procedure. If you need to use a service user different from PC_ALTR_USER, we have very specific instructions on how to make your new service user work with the stored procedure in order for ALTR to properly work. Learn more.

Snowflake Object	Account Created By	Object Name
Database	Partner Connect	PC_ALTR_DB
Database	ALTR Support	Named by user, recommendation is PC_ALTR_DB
Stored Procedure	Partner Connect	SETUP_ALTR_SERVICE_ACCOUNT *Partner Connect puts this stored procedure in the PC_ALTR_DB database. If using ALTR Support, manually place this stored procedure in your database.
Stored Procedure	ALTR Support
User	Partner Connect	PC_ALTR_USER
User	ALTR Support	ALTR_SERVICE_USER
Role	Partner Connect	PC_ALTR_ROLE
Role	ALTR Support	ALTR_SERVICE_ROLE
Warehouse	Partner Connect	PC_ALTR_WH
Warehouse	ALTR Support	ALTR_SERVICE_WH

ALTR's Stored Procedure

To ensure that the ALTR service user has the privileges it needs for ALTR to work properly, run the SETUP_ALTR_SERVICE_ACCOUNT stored procedure after

your account is created (either through Snowflake Partner Connect or by ALTR Support)
any time you add a new database in Snowflake
any time you create a new warehouse in Snowflake

Note

ALTR recommends assigning a network policy to the service user to further secure the user and to prevent unauthorized access. Learn more.

There are two versions of the ALTR stored procedure, used for different scenarios:

When you use the default service user, role and warehouse
When you use a custom-named (i.e., your own) service user, role and/or warehouse. This is often used if you require these Snowflake objects adhere to a specific naming convention.

Snowflake Privilege Grants to Future Tables

The ALTR stored procedure grants privileges to your ALTR service role, which is required for proper operation of ALTR. Privileges are granted on current and future database, schemas and tables.

Snowflake allows future grants to be set at the database level and at the schema level; however, if a privilege is granted to a future object of the same type at both the database and the schema level, the schema-level grant takes precedence and the database-level grant to the same object type will be ignored.

For privileges on future objects, ALTR sets them at the database level instead of the schema level.

For example, the ALTR stored procedure uses

GRANT USAGE ON FUTURE TABLES IN DATABASE

instead of

GRANT USAGE ON FUTURE TABLES IN SCHEMA <myschema_name>

We do this so that the setup procedure does not need to be run again if new schemas or tables are added to a database that is already connected to ALTR.

Therefore, if your database operations require you to have any schema-level privileges granted to other roles on future tables (in an ALTR-connected database), you also need to explicitly grant usage and select on future tables in the schema to the ALTR service role.

Run ALTR's Stored Procedure with Default Service User Objects

If your service username, role, and warehouse are changed from the default objects (PC_ALTR_USER, PC_ALTR_ROLE, and/or PC_ALTR_WAREHOUSE, or ALTR_SERVICE_USER, ALTR_SERVICE_ROLE, and ALTR_SERVICE_WAREHOUSE), this stored procedure will not work. Instead, see the custom object procedure. Learn more.

To run ALTR's stored procedure with the default service objects:

Run the following script in the database to create the ALTR_SETUP_SERVICE_ACCOUNT stored procedure. You should take note of which database you create the procedure in.

Note

When you execute the procedure, set the IS_PARTNER_CONNECT parameter as follows, depending on if you are using the PC_ALTR_USER or ALTR_SERVICE_USER username:

If you're using PC_ALTR_USER and PC_ALTR_ROLE (i.e., you’ve used Snowflake Partner Connect to create your ALTR account), set to TRUE.
If you're using ALTR_SERVICE_USER and ALTR_SERVICE_ROLE (i.e., you manually created your ALTR account with the recommended defaults), set to FALSE .

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 AltrServiceAccountException(message) {
    this.message = message;
  }

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

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

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

    this.unknownDB = function(message) {
      this.unknownDBList.push(message);
    }

    this.skipped = function(message) {
      this.skippedDBList.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);
      appendMessages('SKIPPED', this.skippedDBList);
      appendMessages('UKNOWN OBJECT FOUND', this.unknownDBList);

      return report;
    }
  }

  var RUN_AS_ALTR = false;
  const storedProcedureReport = new StoredProcedureReport();

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

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

  function tryGetColumnValueAsString(resultSet, columnName) {
    try {
      return resultSet.getColumnValueAsString(columnName);
    } catch (err) {
      return null;
    }
  }

  // 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.'";
  }

  function isUknownDB(queryString, error) {
    const errorString = '' + error;
    let idx = -1;
    idx = errorString.indexOf('Database');
    if (idx !== -1) {
      if (errorString.includes(' does not exist or not authorized.')) {
        storedProcedureReport.unknownDB(errorString.substring(idx));
        return false;
      }
    }
    return true;
  }

  // ********************************************
  // 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 + '.');
        if (RUN_AS_ALTR) {
          throw new AltrServiceAccountException(storedProcedureReport.callerReport());
        }
        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);
    execStatement('GRANT APPLY TAG ON ACCOUNT TO ROLE ' + delimitedRoleName);
    execStatement('GRANT APPLY ROW ACCESS POLICY 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'); // Do not use TERSE here; we need some of the columns it trims
    while (resultSet.next()) {
      const name = resultSet.getColumnValueAsString('name');
      // Exclude TRANSIENT databases. There are no TEMPORARY databases as of this writing,
      // but we might as well exclude them if they every implement them.
      const options = tryGetColumnValueAsString(resultSet, 'options');
      if (!isNullOrEmpty(options)) {
        const optionsUpper = options.toUpperCase();
        if (optionsUpper.includes('TRANSIENT') || optionsUpper.includes('TEMPORARY')) {
          storedProcedureReport.skipped('Database ' + name + ' because it has options: ' + options);
          continue;
        }
      }

      // Exclude any non-standard databases (so, shared and native app).
      const kind = tryGetColumnValueAsString(resultSet, 'kind');
      if (!isNullOrEmpty(kind) && !kind.toUpperCase().includes('STANDARD')) {
        storedProcedureReport.skipped('Database ' + name + ' because it is type: ' + kind);
        continue;
      }

      // Older versions of Snowflake did not provide us with the "kind"
      // column in SHOW DATABASES, so we had to check the "origin" of the
      // database. If there is an origin, it means the database "comes from
      // somewhere", and is, therefore, a SHARED database.
      const origin = tryGetColumnValueAsString(resultSet, 'origin');
      if (!isNullOrEmpty(origin)) {
        storedProcedureReport.skipped('Database ' + name + ' because it has origin: ' + origin);
        continue;
      }

      // Just a regular database, but exclude databases with empty or null names.
      if (!isNullOrEmpty(name)) {
        databaseNames.push(name);
      } else {
        storedProcedureReport.skipped('Database ' + name + ' because it has null or empty name');
      }
    }

    for (const databaseName of databaseNames) {
      const delimitedDatabaseName = delimitIdentifier(databaseName);
      let querySuccess = execStatement('GRANT USAGE ON DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      if (querySuccess === false)
        continue;
      execStatement('GRANT CREATE SCHEMA ON DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT USAGE ON ALL SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON FUTURE TABLES IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON ALL TABLES IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON FUTURE VIEWS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON ALL VIEWS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT CREATE TAG ON FUTURE SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT CREATE TAG ON ALL SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT CREATE ROW ACCESS POLICY ON FUTURE SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT CREATE ROW ACCESS POLICY ON ALL SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
    }

    execStatement('CREATE DATABASE IF NOT EXISTS ALTR_DSAAS_DB'); // ownership will be accountadmin
    execStatement('GRANT ALL ON FUTURE SCHEMAS IN DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);
    execStatement('GRANT ALL ON ALL SCHEMAS IN DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);
    execStatement('GRANT ALL ON FUTURE FUNCTIONS IN DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);
    execStatement('GRANT ALL ON ALL FUNCTIONS IN DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);
    execStatement('GRANT ALL ON DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);

    execStatement('GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE ' + delimitedRoleName, isUknownDB);
  }

  function permissionUser(delimitedRoleName, delimitedUserName, delimitedWarehouseName) {
    // Create the role for ALTR and assign the role to SYSADMIN as recommended 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_RESUME = TRUE INITIALLY_SUSPENDED = TRUE');
    execStatement('ALTER WAREHOUSE ' + delimitedWarehouseName + ' SET AUTO_SUSPEND = 30 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
  // ********************************************

  let paramsResultSet = execQuery("SHOW PARAMETERS LIKE '%QUERY_TAG%' IN SESSION");
  while (paramsResultSet.next()) {
    let queryTagStr = '';
    queryTagStr = paramsResultSet.getColumnValueAsString('value');
    if (!isNullOrEmpty(queryTagStr) && queryTagStr === "ALTR") {
      RUN_AS_ALTR = true;
      break;
    }
  }

  // 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);
    if (RUN_AS_ALTR) {
      throw new AltrServiceAccountException(storedProcedureReport.callerReport());
    }
    return storedProcedureReport.callerReport();
  }

  if (IS_PARTNER_CONNECT) {
    execStatement('ALTER WAREHOUSE PC_ALTR_WH SET AUTO_SUSPEND = 30');
    permissionRole('"PC_ALTR_ROLE"');
  } else {
    permissionUser('"ALTR_SERVICE_ROLE"', '"ALTR_SERVICE_USER"', '"ALTR_SERVICE_WH"');
  }

  report = storedProcedureReport.callerReport();
  if (storedProcedureReport.isSuccess) {
    execStatement("ALTER PROCEDURE IF EXISTS {{.Database}}.{{.Schema}}.SETUP_ALTR_SERVICE_ACCOUNT(BOOLEAN) SET COMMENT = '" + new Date().toUTCString() + "'");
  } else if (RUN_AS_ALTR) {
    throw new AltrServiceAccountException(report)
  }
  return report;
$$;

Execute the following command to run the stored procedure. The stored procedure may take several minutes to run, especially for Snowflake accounts with many databases, schemas and tables.

Note

If using PC_ALTR_USER, set the parameter to TRUE. If using ALTR_SERVICE_USER, set the parameter to FALSE.

CALL SETUP_ALTR_SERVICE_ACCOUNT(FALSE);

Run ALTR's Stored Procedure with Custom Service User Objects

To run ALTR's stored procedure with custom service user objects:

Create the following stored procedure which accepts the following parameters. Note the database and schema in which you create the procedure in case you need to execute it again in the future:

SERVICE_USER—The username for the service user. This service user must be created before executing the stored procedure. Defaults to ALTR_SERVICE_USER if not supplied. If you created your ALTR account from Snowflake Partner connect and would like to use the default Partner Connect user, set this to PC_ALTR_USER.
SERVICE_ROLE—The default role for the service user. This defaults to ALTR_SERVICE_ROLE if not set, and PC_ALTR_ROLE if SERVICE_USER is set to PC_ALTR_USER. The procedure will attempt to create this role if it does not already exist.
SERVICE_WAREHOUSE— The default warehouse of the service user. This defaults to ALTR_SERVICE_WH if not set, and PC_ALTR_WH if SERVICE_USER is set to PC_ALTR_USER. The procedure will attempt to create this warehouse if it does not already exist.
DATABASE_NAMES—An aray of databases to grant privileges to. If left blank or set to an empty array, this defaults to all databases in your Snowflake account. By specifying an array of specific databases, this parameter limits the stored procedure to only grant database, schema, and table/view level privileges within the specified databases. This is useful for reducing the amount of time the stored procedure takes to execute for troubleshooting.

CREATE OR REPLACE PROCEDURE SETUP_ALTR_SERVICE_ACCOUNT(
  SERVICE_USER STRING DEFAULT 'ALTR_SERVICE_USER',
  DATABASE_NAMES ARRAY DEFAULT [],
  SERVICE_ROLE STRING DEFAULT NULL,
  SERVICE_WAREHOUSE STRING DEFAULT NULL
) RETURNS STRING
LANGUAGE JAVASCRIPT
VOLATILE
EXECUTE AS CALLER
AS $$
  // ********************************************
  // Stored Procedure Caller Report
  // ********************************************
  
  function AltrServiceAccountException(message) {
    this.message = message;
  }

  function StoredProcedureReport() {
    this.currentRole = "";
    this.isSuccess = true;
    this.successMessages = [];
    this.failMessages = [];
    this.unknownDBList = [];
    this.skippedDBList = [];

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

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

    this.unknownDB = function(message) {
      this.unknownDBList.push(message);
    }

    this.skipped = function(message) {
      this.skippedDBList.push(message);
    }

    this.callerReport = function() {
      let report = 'ROLE EXECUTED AS: ' + this.currentRole + "\n\n";

      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);
      appendMessages('SKIPPED', this.skippedDBList);
      appendMessages('UNKNOWN OBJECT FOUND', this.unknownDBList);

      return report;
    }
  }

  var RUN_AS_ALTR = false;
  const storedProcedureReport = new StoredProcedureReport();

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

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

  function tryGetColumnValueAsString(resultSet, columnName) {
    try {
      return resultSet.getColumnValueAsString(columnName);
    } catch (err) {
      return null;
    }
  }

  // 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.'";
  }

  function isUknownDB(queryString, error) {
    const errorString = '' + error;
    let idx = -1;
    idx = errorString.indexOf('Database');
    if (idx !== -1) {
      if (errorString.includes(' does not exist or not authorized.')) {
        storedProcedureReport.unknownDB(errorString.substring(idx));
        return false;
      }
    }
    return true;
  }

  function isDatabaseInList(databaseName) {
    return DATABASE_NAMES.find(function(element) { return element === databaseName });
  }

  // ********************************************
  // 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 + '.');
        if (RUN_AS_ALTR) {
          throw new AltrServiceAccountException(storedProcedureReport.callerReport());
        }
        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);
    execStatement('GRANT APPLY TAG ON ACCOUNT TO ROLE ' + delimitedRoleName);
    execStatement('GRANT APPLY ROW ACCESS POLICY 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'); // Do not use TERSE here; we need some of the columns it trims
    while (resultSet.next()) {
      
      const name = resultSet.getColumnValueAsString('name');
      if (DATABASE_NAMES.length !== 0) {
          // We need to filter the result set down to the DATABASE_NAMES they have specified
          const database = DATABASE_NAMES.find(function(element) { return element === name });
          if (!database) {
              // the database provided in the array is not here (at least yet), on to the next.
              continue;
          }
      }
    
      // Exclude TRANSIENT databases. There are no TEMPORARY databases as of this writing,
      // but we might as well exclude them if they every implement them.
      const options = tryGetColumnValueAsString(resultSet, 'options');
      if (!isNullOrEmpty(options)) {
        const optionsUpper = options.toUpperCase();
        if (optionsUpper.includes('TRANSIENT') || optionsUpper.includes('TEMPORARY')) {
          storedProcedureReport.skipped('Database ' + name + ' because it has options: ' + options);
          continue;
        }
      }

      // Exclude any non-standard databases (so, shared and native app).
      const kind = tryGetColumnValueAsString(resultSet, 'kind');
      if (!isNullOrEmpty(kind) && !kind.toUpperCase().includes('STANDARD')) {
        storedProcedureReport.skipped('Database ' + name + ' because it is type: ' + kind);
        continue;
      }

      // Older versions of Snowflake did not provide us with the "kind"
      // column in SHOW DATABASES, so we had to check the "origin" of the
      // database. If there is an origin, it means the database "comes from
      // somewhere", and is, therefore, a SHARED database.
      const origin = tryGetColumnValueAsString(resultSet, 'origin');
      if (!isNullOrEmpty(origin)) {
        storedProcedureReport.skipped('Database ' + name + ' because it has origin: ' + origin);
        continue;
      }

      // Just a regular database, but exclude databases with empty or null names.
      if (!isNullOrEmpty(name)) {
        databaseNames.push(name);
      } else {
        storedProcedureReport.skipped('Database ' + name + ' because it has null or empty name');
      }
    }

    for (const databaseName of databaseNames) {
      const delimitedDatabaseName = delimitIdentifier(databaseName);
      let querySuccess = execStatement('GRANT USAGE ON DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      if (querySuccess === false)
        continue;
      execStatement('GRANT CREATE SCHEMA ON DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT USAGE ON ALL SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON FUTURE TABLES IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON ALL TABLES IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON FUTURE VIEWS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON ALL VIEWS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT CREATE TAG ON FUTURE SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT CREATE TAG ON ALL SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT CREATE ROW ACCESS POLICY ON FUTURE SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
      execStatement('GRANT CREATE ROW ACCESS POLICY ON ALL SCHEMAS IN DATABASE ' + delimitedDatabaseName + ' TO ROLE ' + delimitedRoleName, isUknownDB);
    }

    execStatement('GRANT ALL ON FUTURE SCHEMAS IN DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);
    execStatement('GRANT ALL ON ALL SCHEMAS IN DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);
    execStatement('GRANT ALL ON FUTURE FUNCTIONS IN DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);
    execStatement('GRANT ALL ON ALL FUNCTIONS IN DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);
    execStatement('GRANT ALL ON DATABASE ALTR_DSAAS_DB TO ROLE ' + delimitedRoleName, isUknownDB);
    execStatement('GRANT MANAGE GRANTS ON ACCOUNT TO ROLE ' + delimitedRoleName, isUknownDB);

    execStatement('GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE ' + delimitedRoleName, isUknownDB);
  }

  function permissionUser(delimitedRoleName, delimitedUserName, delimitedWarehouseName) {
    // Create the role for ALTR and assign the role to SYSADMIN as recommended 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_RESUME = TRUE INITIALLY_SUSPENDED = TRUE');
    execStatement('ALTER WAREHOUSE ' + delimitedWarehouseName + ' SET AUTO_SUSPEND = 30 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
  // ********************************************

  let paramsResultSet = execQuery("SHOW PARAMETERS LIKE '%QUERY_TAG%' IN SESSION");
  while (paramsResultSet.next()) {
    let queryTagStr = '';
    queryTagStr = paramsResultSet.getColumnValueAsString('value');
    if (!isNullOrEmpty(queryTagStr) && queryTagStr === "ALTR") {
      RUN_AS_ALTR = true;
      break;
    }
  }

  // get the current role that the user is before we switch to accountadmin
  let currentRoles = [];
  let resultSet = execQuery("SELECT CURRENT_ROLE() AS CURRENT_ROLE;");
  while (resultSet.next()) {
     currentRoles.push(tryGetColumnValueAsString(resultSet, "CURRENT_ROLE"));
  }
  storedProcedureReport.currentRole = currentRoles[0];

  // 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);
    if (RUN_AS_ALTR) {
      throw new AltrServiceAccountException(storedProcedureReport.callerReport());
    }
    return storedProcedureReport.callerReport();
  }

  // Create the database asap so that we can grant the role permissions on it.
  execStatement('CREATE DATABASE IF NOT EXISTS ALTR_DSAAS_DB'); // ownership of this database will be accountadmin

  if (SERVICE_USER === "PC_ALTR_USER") {
    // if this user is Partner Connect and SERVICE_ROLE is not defined, assume PC_ALTR_ROLE, else use what they gave.
    let pcRole = !SERVICE_ROLE ? '"PC_ALTR_ROLE"' : SERVICE_ROLE;
    let pcWarehouse = !SERVICE_WAREHOUSE ? '"PC_ALTR_WH"' : SERVICE_WAREHOUSE;
    execStatement('ALTER WAREHOUSE ' + pcWarehouse +' SET AUTO_SUSPEND = 30');
    permissionRole(pcRole);
  } else {
    let serviceUser = !SERVICE_USER ? '"ALTR_SERVICE_USER"' : SERVICE_USER;
    let serviceRole = !SERVICE_ROLE ? '"ALTR_SERVICE_ROLE"' : SERVICE_ROLE;
    let serviceWarehouse = !SERVICE_WAREHOUSE ? '"ALTR_SERVICE_WH"' : SERVICE_WAREHOUSE;
    permissionUser(serviceRole, serviceUser, serviceWarehouse);
  }

  report = storedProcedureReport.callerReport();
  if (storedProcedureReport.isSuccess) {
    execStatement("ALTER PROCEDURE IF EXISTS {{.Database}}.{{.Schema}}.SETUP_ALTR_SERVICE_ACCOUNT(STRING, ARRAY, STRING, STRING) SET COMMENT = '" + new Date().toUTCString() + "'");
  } else if (RUN_AS_ALTR) {
    throw new AltrServiceAccountException(report)
  }
  return report;
$$;

Execute the following command to run the stored procedure, setting the parameters as defined above. The stored procedure may take several minutes to run, especially for Snowflake accounts with many databases, schemas and tables. You can reduce this time be specifying a limited number of databases in the relevant argument.
```
CALL SETUP_ALTR_SERVICE_ACCOUNT(<USER_NAME>,<ROLE_NAME>,<WAREHOUSE_NAME>,<DATABASE_ARRAY>);
```

(Optional and Recommended) Restrict Service User Access to ALTR’s IPs

To have a more robust security strategy, restrict the ALTR service user’s access to our IPs:

Update <USER> in the following command based on how you created your ALTR account:

Use PC_ALTR_USER if using Snowflake Partner Connect
Use ALTR_SERVICE_USER if ALTR Support created your account

CREATE NETWORK RULE ALTR_PROD_INGRESS TYPE = IPV4 MODE = INGRESS VALUE_LIST = ('3.145.219.176/28','35.89.45.128/28', '44.203.133.160/28');

CREATE NETWORK POLICY ALTR_PROD_INGRESS ALLOWED_NETWORK_RULE_LIST = ('ALTR_PROD_INGRESS');

ALTER USER <USER> SET NETWORK_POLICY = ALTR_PROD_INGRESS;

Execute the command.

Note

ALTR service users are configured with strong random passwords. As a robust defense-in-depth strategy, configure a Snowflake Network Policy utilizing a Network Rule that restricts the ALTR service user account to our outbound network CIDR ranges.

Configure ALTR’s Stored Procedure to Run as a Task

Situations will occur where you have to manually run the SETUP_ALTR_SERVICE_ACCOUNT stored procedure, such as when you add another database. If you create a new database, run this stored procedure again in order for ALTR (specifically, PC_ALTR_USER and PC_ALTR_ROLE) to recognize and govern the new database.

Our recommendation is to run this stored procedure as a task that runs on a schedule, such as nightly.

Use the following command to set up this task to run on a schedule. Before running it, update the following:

<your wh>, <procedure db> and <procedure schema> to your respective values
SCHEDULE if you want the task to run at a time other than daily at midnight UTC
<is partner connect boolean> depending on the name of your service user:
- if PC_ALTR_ROLE, set to TRUE
- if ALTR_SERVICE_ROLE, set to FALSE

--PREREQUISITE: SETUP_ALTR_SERVICE_ACCOUNT must already exist as a stored procedure in your environment.

create database SASA_TASK_DB;
create schema SASA_TASK_SCHEMA;
use schema SASA_TASK_DB.SASA_TASK_SCHEMA;
--It is not required to make a new database and schema. However, if you use an exisiting database and or schema,
--make sure to replace all instance of SASA_TASK_DB and SASA_TASK_SCHEMA in this script with your desired values.

use role securityadmin;
create role sasataskrole;
create role taskadmin;
--to ensure your user has access to sasataskrole, or whichever role you use in its place, 
--go to admin > users and roles on the left hand sidebar, or run '''grant role SASATASKROLE to user <your user>;'''


USE ROLE accountadmin;
GRANT EXECUTE TASK, EXECUTE MANAGED TASK ON ACCOUNT TO ROLE taskadmin;

USE ROLE securityadmin;
GRANT ROLE taskadmin TO ROLE sasataskrole;

grant usage on database SASA_TASK_DB to role sasataskrole;
grant all on schema SASA_TASK_DB.SASA_TASK_SCHEMA to role sasataskrole;

--You can identify the database and schema where your SETUP_ALTR_SERVICE_ACCOUNT is stored by calling
-- '''show procedures;'''. There will be a row for SETUP_ALTR_SERVICE_ACCOUNT. Within this row, the database name will
-- be stored in the catalog_name column and the schema name will be stored in the schema name column.
-- replace all instances of <procedure db> and <procedure schema> with these values.
grant usage on database <procedure db> to role sasataskrole;
grant usage on schema <procedure db>.<procedure schema> to role sasataskrole;
grant usage on procedure <procedure db>.<procedure schema>.SETUP_ALTR_SERVICE_ACCOUNT(BOOLEAN) to role sasataskrole;

USE ROLE sasataskrole;
CREATE TASK SASA_TASK_DB.SASA_TASK_SCHEMA.SASA_TASK
  WAREHOUSE = <your wh> --replace with your desired warehouse
  SCHEDULE = 'USING CRON 0 0 * * * UTC'--currently setup to run daily at midnight UTC
  --see snowflake cron documentation for more information
  AS
    CALL <procedure db>.<procedure schema>.SETUP_ALTR_SERVICE_ACCOUNT(<is partner connect boolean>);

ALTER TASK SASA_TASK_DB.SASA_TASK_SCHEMA.SASA_TASK RESUME; --all tasks are 'suspended' immediately following creation.
--This command resumes the task so that it will run at the specified time.

EXECUTE TASK SASA_TASK_DB.SASA_TASK_SCHEMA.SASA_TASK; --test your task to make sure it was set up properly

ALTR's Service User and Role

When an ALTR account is created by Snowflake Partner Connect, Snowflake automatically creates the PC_ALTR_USER,PC_ALTR_ROLE, and PC_ALTR_WAREHOUSE objects and shares their information with ALTR. During onboarding, ALTR users privilege PC_ALTR_ROLE with the necessary grants for the service user to properly function.

If an ALTR account is not created by Snowflake Partner Connect or if for some reason a customer wishes not to use PC_ALTR_USER, the service user, role, and warehouse must be manually created in Snowflake. The service user must be specified when connecting Snowflake data sources to ALTR and, if the warehouse and role are not set as default on the service user, they must also be specified when connecting the Snowflake data source.

Warning

When creating a custom service user, make sure that it has the appropriate Snowflake privileges. Refer to Required Snowflake Objects for more information.

Update Snowflake Service User Password

If you change the password for your service user, ALTR will be unable to connect to Snowflake. Although existing data access governance and security policies will continue to function, ALTR will be unable to define new policies and may not be able to generate accurate query audit logs. Do not change the Service User's password in Snowflake without also updating the password in ALTR.

If your ALTR account leverages PC_ALTR_USER and that service user's password changes, you will be unable to connect new data sources using PC_ALTR_USER until you update that user's password in your organization's Snowflake Partner Connect Settings. This must be done in addition to updating the password for any existing Snowflake data source connections.

Create a Custom Service User

If you need to use a different service user name or role name (to stay in compliance with your naming convention for example), create a custom service user and grant privileges. We have a specific recommended procedure to accomplish this task. If you need help along the way, contact ALTR Support.

Note

Creating a custom service user is not the routine path to create a service user. Learn more to create the recommended service user.

Warning

Do not modify PC_ALTR_ROLE, ALTR_SERVICE_ROLE, ALTR_SERVICE_USER, PC_ALTR_USER, PC_ALTR_WH or ALTR_SERVICE_WH.

To create a service user with custom service user objects and grant privileges:

Don’t change the stored procedure.
Create a new service user with whatever name you’d like that will be the ALTR service user. Set the TYPE to LEGACY_SERVICE.
Create a new role for your new service user.
Grant your new role the role PC_ALTR_ROLE or ALTR_SERVICE_ROLE (as applicable) so all of the required privileges are extended to your new role.
Note
For the list of privileges required by the service role, refer to Service User Privilege Requirements.
Grant your new role to the new service user so privileges required for ALTR are extended to your new service user.

Note

Whenever you run the stored procedure, even if it’s just during set up, ensure the IS_PARTNER_CONNECT parameter is correctly set because the role name is different depending on how the account was created.

If you used Snowflake Partner connect, the role name is PC_ALTR_ROLE; set the parameter to TRUE.
If ALTR Support created your account, the role name is ALTR_SERVICE_ROLE; set the parameter to FALSE.

In this section:

Required Snowflake Objects

Warning

ALTR's Stored Procedure

Note

Snowflake Privilege Grants to Future Tables

Run ALTR's Stored Procedure with Default Service User Objects

Note

Note

Run ALTR's Stored Procedure with Custom Service User Objects

(Optional and Recommended) Restrict Service User Access to ALTR’s IPs

Note

Configure ALTR’s Stored Procedure to Run as a Task

ALTR's Service User and Role

Warning

Update Snowflake Service User Password

Create a Custom Service User

Note

Warning

Note

Note

Search results