Migrate data quality permissions

Migrate from legacy Snowflake data quality (DQ) permissions to a new model that uses a least-privilege service role and reduces reliance on owner privileges. This model aligns with Snowflake access control for data metric functions (DMFs) and separates table ownership from DMF association.

Prerequisites

Before you begin, make sure you have:

An existing Snowflake data quality setup
The ACCOUNTADMIN role or equivalent administrative privileges in Snowflake
Access to the following roles in your Snowflake environment: dq_admin and atlan_dq_service_role

Set up new permission model

Follow these steps to move to the new permission model:

Switch to the dq_admin role:
```
USE ROLE dq_admin;
```

Create the stored procedure for DMF schedule management:

View procedure code

/**
* Updates the Data Metric Function (DMF) schedule for a specified Snowflake entity.
*
* This secure procedure validates inputs, verifies entity existence, and applies
* the appropriate DMF schedule configuration (MINUTES, CRON, ON_DATA_CHANGE, NOT_SCHEDULED).
* It enforces strict validation for:
* - Entity type (TABLE, VIEW, MATERIALIZED VIEW, EXTERNAL TABLE, ICEBERG TABLE)
* - Schedule type and required values (including CRON format and timezone validity)
* - Supported minute intervals for MINUTES-based schedules
*
* The procedure executes with OWNER privileges to ensure proper permissions and
* returns a structured JSON response indicating success or failure with details.
*
* @param {string} ENTITY_TYPE - Type of entity (TABLE, VIEW, MATERIALIZED VIEW, EXTERNAL TABLE, ICEBERG TABLE)
* @param {string} ENTITY_NAME - Fully qualified name of the entity (database.schema.name)
* @param {string} SCHEDULE_TYPE - Schedule type (MINUTES, CRON, ON_DATA_CHANGE, NOT_SCHEDULED)
* @param {string} [SCHEDULE_VALUE=null] - Schedule value based on type (minutes or CRON expression)
* @returns {string} - JSON string with:
*   - isSuccessful: Boolean indicating operation success
*   - message: Success or error details
*/

CREATE OR REPLACE SECURE PROCEDURE ATLAN_DQ.SHARED.MANAGE_DMF_SCHEDULE(
  ENTITY_TYPE STRING,
  ENTITY_NAME STRING,
  SCHEDULE_TYPE STRING,
  SCHEDULE_VALUE STRING DEFAULT NULL
)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
// -----------------------------------------------------Constants-----------------------------------------------------
const VALID_ENTITY_TYPES = new Set([
  "TABLE",
  "VIEW",
  "MATERIALIZED VIEW",
  "EXTERNAL TABLE",
  "ICEBERG TABLE",
]);
const VALID_SCHEDULE_TYPES = new Set([
  "MINUTES",
  "CRON",
  "ON_DATA_CHANGE",
  "NOT_SCHEDULED",
]);
const MINUTES_ALLOWED = new Set(["5", "15", "30", "60", "720", "1440"]);
const SCHEDULE_TYPES_THAT_REQUIRE_VALUE = new Set(["MINUTES", "CRON"]);

// -----------------------------------------------------UTILITY FUNCTIONS-----------------------------------------------------
/**
* Executes a SQL query with optional bind parameters
* Returns an object with execution status and results/error details
* @param {string} sqlText - SQL statement to execute
* @param {Array} [binds=[]] - Array of bind parameters for parameterized queries
* @returns {Object} Object containing:
*   - isErrored: boolean indicating if execution failed
*   - message: error details if failed, empty string if successful
*   - result: query result object if successful, null if failed
*/
function executeQuery(sqlText, binds = []) {
  try {
    if (!sqlText) throw new Error("SQL Text is required");
    const statement = snowflake.createStatement({ sqlText, binds });
    const result = statement.execute();
    return {
      isErrored: false,
      message: "",
      result,
    };
  } catch (err) {
    const errorMessage = `${err.code ?? ''} - ${err.message} - ${sqlText} with binds: ${binds.join(", ")}`;
    return {
      isErrored: true,
      message: errorMessage.trim(),
      result: null,
    };
  }
}

// -----------------------------------------------------VALIDATION Helpers-----------------------------------------------------

/**
* Validates if a timezone string is recognized by Snowflake
* Uses CONVERT_TIMEZONE to test validity without side effects
* @param {string} timezone - Timezone to validate (e.g., 'America/New_York', 'UTC')
* @returns {boolean} True if timezone is valid, false otherwise
*/
function isTimezoneValid(timezone) {
  const result = executeQuery(
    `SELECT CONVERT_TIMEZONE(?, CURRENT_TIMESTAMP())`,
    [timezone]
  );
  return !result.isErrored;
}

/**
* Validates if a string represents a number within specified bounds
* @param {string} value - String value to parse and validate
* @param {number} min - Minimum allowed value (inclusive)
* @param {number} max - Maximum allowed value (inclusive)
* @returns {boolean} True if value is a valid number within range
*/
function isNumberInRange(value, min, max) {
  const num = parseInt(value, 10);
  return !isNaN(num) && num >= min && num <= max;
}

/**
* Validates Snowflake CRON expression format and component values
* Expected format: minute hour dayOfMonth month dayOfWeek timezone
* Example: "0 9 * * MON America/New_York" (Every Monday at 9 AM EST)
* @param {string} cronExpression - CRON expression to validate
* @throws {Error} If any component of the CRON expression is invalid
*/
function validateCronExpression(cronExpression) {
  if (cronExpression.length > 100)
    throw new Error("Cron expression is too long");
  const cronFields = cronExpression.trim().split(/\s+/);
  if (cronFields.length !== 6)
    throw new Error("Invalid cron expression. Expected 6 fields");
  const [minute, hour, dayOfMonth, month, dayOfWeek, timezone] = cronFields;
  const isTimezoneValidResult = isTimezoneValid(timezone);
  if (!isTimezoneValidResult)
    throw new Error("Invalid timezone provided in the cron expression");
  const regexPatterns = {
    minute: /^(\*|\d+|\*\/\d+|\d+\-\d+|\d+(,\d+)*)$/,
    hour: /^(\*|\d+|\*\/\d+|\d+\-\d+|\d+(,\d+)*)$/,
    dayOfMonth: /^(\*|L|\d+|\*\/\d+|\d+\-\d+|\d+(,\d+)*)$/,
    month:
      /^(\*|\d+|JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC|\*\/\d+|\d+\-\d+|[A-Z]{3}\-[A-Z]{3}|\d+(,\d+)*|([A-Z]{3}(,[A-Z]{3})*))$/i,
    dayOfWeek:
      /^(\*|\d+|SUN|MON|TUE|WED|THU|FRI|SAT|\d+L|[A-Z]{3}L|\*\/\d+|\d+\-\d+|[A-Z]{3}\-[A-Z]{3}|\d+(,\d+)*|([A-Z]{3}(,[A-Z]{3})*))$/i,
  };
  if (minute.match(/^\d+$/))
    if (!isNumberInRange(minute, 0, 59))
      throw new Error("Invalid minute value");
  if (hour.match(/^\d+$/))
    if (!isNumberInRange(hour, 0, 23)) throw new Error("Invalid hour value");
  if (dayOfMonth.match(/^\d+$/))
    if (!isNumberInRange(dayOfMonth, 1, 31))
      throw new Error("Invalid day of month value");
  if (month.match(/^\d+$/))
    if (!isNumberInRange(month, 1, 12)) throw new Error("Invalid month value");
  if (dayOfWeek.match(/^\d+$/))
    if (!isNumberInRange(dayOfWeek, 0, 6))
      throw new Error("Invalid day of week value");
  if (
    !regexPatterns.minute.test(minute) ||
    !regexPatterns.hour.test(hour) ||
    !regexPatterns.dayOfMonth.test(dayOfMonth) ||
    !regexPatterns.month.test(month) ||
    !regexPatterns.dayOfWeek.test(dayOfWeek)
  )
    throw new Error("Invalid cron expression");
}

// -----------------------------------------------------VALIDATION Functions-----------------------------------------------------
/**
* Verifies that the specified entity exists and is accessible
* Uses SHOW COLUMNS to check entity accessibility without modifying data
* @param {string} entityName - Fully qualified entity name to verify
* @throws {Error} If entity doesn't exist or isn't accessible
*/
function verifyEntityExists(entityName) {
  const query = `SHOW COLUMNS IN IDENTIFIER(?)`;
  const result = executeQuery(query, [entityName]);
  if (result.isErrored) throw new Error(result.message);
}

/**
* Main validation orchestrator - validates all input parameters
* @param {string} entityType - Type of database entity
* @param {string} entityName - Fully qualified entity name
* @param {string} scheduleType - Type of schedule to set
* @param {string} scheduleValue - Schedule configuration value
* @throws {Error} If any validation fails
*/

function validateArguments(
  entityType,
  entityName,
  scheduleType,
  scheduleValue
) {
  if (!entityType) throw new Error("ENTITY_TYPE cannot be empty");
  if (!entityName) throw new Error("ENTITY_NAME cannot be empty");
  if (!scheduleType) throw new Error("SCHEDULE_TYPE cannot be empty");

  if (!VALID_ENTITY_TYPES.has(entityType))
    throw new Error(
      `Invalid ENTITY_TYPE: "${entityType}". Valid options are ${Array.from(
        VALID_ENTITY_TYPES
      ).join(", ")}`
    );

  const parts = entityName
    .split(".")
    .map((part) => part.trim())
    .filter(Boolean);
  if (parts.length !== 3)
    throw new Error(
      `Invalid fully qualified name: ${entityName}. Expected format: database.schema.name`
    );
  if (!VALID_SCHEDULE_TYPES.has(scheduleType))
    throw new Error(
      `Invalid schedule type: "${scheduleType}". Valid options are ${Array.from(
        VALID_SCHEDULE_TYPES
      ).join(", ")}`
    );
  if (SCHEDULE_TYPES_THAT_REQUIRE_VALUE.has(scheduleType) && !scheduleValue)
    throw new Error("SCHEDULE_VALUE is required");
  verifyEntityExists(entityName);
  if (scheduleType === "MINUTES" && !MINUTES_ALLOWED.has(scheduleValue))
    throw new Error(
      `Invalid SCHEDULE_VALUE for MINUTES. Valid options are ${Array.from(
        MINUTES_ALLOWED
      ).join(", ")}`
    );
  if (scheduleType === "CRON") validateCronExpression(scheduleValue);
}

// -----------------------------------------------------MAIN FUNCTION-----------------------------------------------------

/**
* Main entry point - manages DMF schedule configuration
* Validates inputs, constructs appropriate SQL, and executes the schedule change
* @returns {string} JSON response with operation status and message
*/
function main() {
  const entityType = ENTITY_TYPE.trim();
  const entityName = ENTITY_NAME.trim();
  const scheduleType = SCHEDULE_TYPE.trim();
  const scheduleValue = SCHEDULE_VALUE?.trim() ?? null;

  validateArguments(entityType, entityName, scheduleType, scheduleValue);
  const SQL_TEMPLATES = {
    MINUTES: `ALTER ${entityType} IDENTIFIER(?) SET DATA_METRIC_SCHEDULE = '${scheduleValue} MINUTE'`,
    CRON: `ALTER ${entityType} IDENTIFIER(?) SET DATA_METRIC_SCHEDULE = 'USING CRON ${scheduleValue}'`,
    ON_DATA_CHANGE: `ALTER ${entityType} IDENTIFIER(?) SET DATA_METRIC_SCHEDULE = 'TRIGGER_ON_CHANGES'`,
    NOT_SCHEDULED: `ALTER ${entityType} IDENTIFIER(?) UNSET DATA_METRIC_SCHEDULE`,
  };
  const sqlText = SQL_TEMPLATES[scheduleType];
  const returnMessage = `Successfully updated schedule for ${entityType} ${entityName} to ${scheduleType} ${scheduleValue ?? ''}`;
  const result = executeQuery(sqlText, [entityName]);
  return JSON.stringify({
    isSuccessful: !result.isErrored,
    message: result.isErrored ? result.message : returnMessage.trim(),
  });
}
try {
  return main();
} catch (err) {
  return JSON.stringify({
    isSuccessful: false,
    message: err.message,
  });
}
$$;

Grant USAGE on the procedure to the service role:

GRANT USAGE ON PROCEDURE ATLAN_DQ.SHARED.MANAGE_DMF_SCHEDULE(
    STRING, STRING, STRING, STRING
) TO ROLE atlan_dq_service_role;

Switch to the ACCOUNTADMIN role:
```
USE ROLE ACCOUNTADMIN;
```

Grant privilege to execute data metric function to the service role:

GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE atlan_dq_service_role;

Grant read access to the service role to attach rules to tables. Replace <database-name> and <schema-name> with your object names.

Grant database and schema usage to enable Atlan to discover and reference objects:

GRANT USAGE ON DATABASE <database-name> TO ROLE atlan_dq_service_role;
GRANT USAGE ON ALL SCHEMAS IN DATABASE <database-name> TO ROLE atlan_dq_service_role;

Grant table and view read access to attach and execute rules on these objects:

GRANT SELECT ON ALL TABLES IN DATABASE <database-name> TO ROLE atlan_dq_service_role;
GRANT SELECT ON ALL VIEWS  IN DATABASE <database-name> TO ROLE atlan_dq_service_role;

Set up future grants to retain grants when objects are recreated and apply them to new objects automatically:

GRANT USAGE  ON FUTURE SCHEMAS  IN DATABASE <database-name> TO ROLE atlan_dq_service_role;
GRANT SELECT ON FUTURE TABLES   IN DATABASE <database-name> TO ROLE atlan_dq_service_role;
GRANT SELECT ON FUTURE VIEWS    IN DATABASE <database-name> TO ROLE atlan_dq_service_role;

If you define future grants at both database and schema levels, schema-level grants take precedence. For more information, see the Snowflake documentation about Considerations for future grants.

Schema-level future grants

If you use schema-level future grants, grant the following to the service role:

GRANT SELECT ON FUTURE TABLES IN SCHEMA <database-name>.<schema-name> TO ROLE atlan_dq_service_role;
GRANT SELECT ON FUTURE VIEWS  IN SCHEMA <database-name>.<schema-name> TO ROLE atlan_dq_service_role;

Submit a support request with the Atlan team to migrate your existing rules to the new permission model.

Need help

If you have questions or need assistance with the process, reach out to Atlan Support by submitting a support request.

Prerequisites​

Set up new permission model​

Need help​

See also​

Prerequisites

Set up new permission model

Need help

See also