Skip to main content

ALTR's Snowflake Service User

A Service User is an identity within a Snowflake account that third party tools can use to log into the Snowflake account and execute SQL. ALTR uses a Snowflake Service User to create Snowflake Objects such as Dynamic Data Masking Policies, Row Access Policies, and API integrations necessary to perform data access governance and advanced data security.

ALTR's Service user must have access to a Snowflake Role with the appropriate privileges (grants) to enforce access governance and security. This is typically the default role for the Snowflake Service User but can be explicitly defined when connecting a Snowflake data source.

For ALTR accounts created from Snowflake Partner Connect, the PC_ALTR_USER is automatically assigned the default role PC_ALTR_ROLE.

Required Snowflake Service Role Privileges

Privilege Name

How ALTR's service user uses this privilege

CREATE DATABASE

This is used for creating a database, ALTR_DSAAS, to house the Snowflake objects required for access governance and security.

APPLY MASKING POLICY

ALTR Uses Snowflake’s Dynamic Data Masking Policies to create and enforce Column Access Policies. When a column is connected to ALTR, ALTR creates a Masking Policy for that column in Snowflake. Once that policy is created, ALTR is invoked by Snowflake to make a governance decision each time that column is queried.

CREATE INTEGRATION

ALTR communicates with Snowflake to make governance decisions through an API integration. This privilege enables ALTR’s Service User to create those API integrations for each connected database.

APPLY TAG

ALTR can invoke Snowflake’s Classification tool to generate SEMANTIC_CATEGORY and PRIVACY_CATEGORY tags, which can then be leveraged when defining Tag-Based Column Access Policies. The APPLY TAG privilege is required to assign these tags to columns during a Snowflake Classification.

APPLY ROW ACCESS POLICY

This privilege is required for ALTR to apply Row Access Policies to tables in Snowflake.

MONITOR ON WAREHOUSE

LTR monitors warehouse activity to identify when sensitive columns are access by users. This is particularly used for ALTR’s query logging, Thresholding, and Rate Limiting capabilities, as monitoring warehouse activity enables ALTR to identify exactly how many values of a column were accessed.

USAGE ON DATABASE

This enables ALTR to access databases in Snowflake to create the necessary objects to enforce governance policies

CREATE SCHEMA ON DATABASE

ALTR creates all it’s governance objects in a custom ALTR_DSAAS schema, ensuring that these policies are separated from data-holding schemas.

USAGE ON SCHEMA

This enables ALTR to create necessary schema-level objects to create and enforce governance policies.

SELECT ON TABLES

This enables ALTR to identify tables within Snowflake. ALTR does not automatically select or store any raw data (actual values) from client Snowflake Accounts. However, if you opt-in to Google DLP classification, then ALTR uses this privilege to randomly sample data from tables present within a Snowflake database. ALTR does not persist these samples.

CREATE TAG ON SCHEMA

ALTR can invoke Snowflake’s Classification tool to generate SEMANTIC_CATEGORY and PRIVACY_CATEGORY tags, which can then be leveraged when defining Tag-Based Column Access Policies. The CREATE TAG privilege is required to create these tags during a Snowflake Classification.

CREATE ROW ACCESS POLICY

This privilege is required for ALTR to create Row Access Policies in Snowflake.

GRANT IMPORTED PRIVILEGES

If you opt to allow ALTR to access your database’s query history to generate Data Usage Analytics for all queries, ALTR requires this privilege to access Snowflake’s Access History Views.

SELECT ON VIEWS

This enables ALTR to identify views in Snowflake.

SELECT ON MATERIALIZED VIEWS

This enables ALTR to identify materialized views within Snowflake.

ALTR provides a Snowflake stored procedure to simplify granting necessary privileges to the Snowflake Service User. This procedure may take some time, depending on the complexity of the Snowflake Account.

To create and execute the stored procedure:

  1. Log into Snowflake with a user that can access the ACCOUNTADMIN role.

  2. Create the following script to create ALTR's Snowflake Stored Procedure:

    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('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 SETUP_ALTR_SERVICE_ACCOUNT(BOOLEAN) SET COMMENT = '" + new Date().toUTCString() + "'");
      } else if (RUN_AS_ALTR) {
        throw new AltrServiceAccountException(report)
      }
      return report;
    $$;
  3. Execute the following command to run the stored procedure. This may take several minutes, especially for Snowflake accounts with many databases, schemas, and tables.

    CALL SETUP_ALTR_SERVICE_ACCOUNT(TRUE); 
    1. If your service user was created by Snowflake Partner Connect and uses the PC_ALTR_ROLE (this is typically true if PC_ALTR_DB exists in your account), run the command with the argument set to TRUE.

    2. If your service user was created manually and uses the ALTR_SERVICE_ROLE role, run the command with the argument set to FALSE.

    3. If your service user was created manually and/or uses a custom service role, reach out to ALTR Support for assistance updating your service role.

  4. (Optional) Run a Data Source Status Check for any Snowflake Data Source Connections that use this service user. The result of this check will indicate if there are any missing privileges for the service user.

Service User Creation

When an ALTR account is created by Snowflake Partner Connect, Snowflake Automatically generates 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 grants to function. Refer to Configuring Snowflake Service User Privileges with ALTR's Stored Procedure for more information.

Snowflake Service User Password Changes

If the password for a service user changes, ALTR will not be able 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.