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 |
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. |
ALTR Support | ||
User | Partner Connect | PC_ALTR_USER |
ALTR Support | ALTR_SERVICE_USER | |
Role | Partner Connect | PC_ALTR_ROLE |
ALTR Support | ALTR_SERVICE_ROLE | |
Warehouse | Partner Connect | PC_ALTR_WH |
ALTR Support | ALTR_SERVICE_WH |
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
If these user and role names are changed or if a user or role with different names are used, the stored procedure will not work.
If your organization has a specific naming convention and you need to use a service user name and role that are different from PC_ALTR_USER and PC_ALTR_ROLE, we have very specific instructions. Learn more.
Run ALTR's Stored Procedure
To run ALTR's stored procedure:
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 run the procedure, set the IS_PARTNER_CONNECT parameter as follows, which depends on the name of your service user and role:
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), 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('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; $$;
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);
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.
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
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 is has the appropriate Snowflake privileges. Refer to Required Snowflake Objects for more information.
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.
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 custom service user 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.
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.