Create and Set Up an ALTR Account
There are two ways to create and set up an ALTR account:
Use Snowflake Partner Connect. (Recommended)
Contact ALTR Support to get an account created. This might be required if you are using ALTR through a partner or third-party vendor.
Note
In order to connect to ALTR, you must have
Enterprise or higher level of Snowflake
ACCOUNTADMIN role, which is needed to sign up for Snowflake Partner Connect and to grant access to ALTR. ALTR is not required to run as this role after setup.
Warning
ALTR must be able to communicate with Snowflake over the internet in order to apply and enforce data security policies. If your Snowflake account restricts IP traffic using network policies , you must create new network rules whitelisting ALTR's IP addresses before connecting an ALTR account. ALTR's IP addresses are:
44.203.133.160/28
3.145.219.176/28
35.89.45.128/28
ALTR participates in Snowflake Partner Connect, making it easy for Snowflake ACCOUNTADMIN users to create and set up an ALTR account.
Note
The process to Snowflake Partner Connect to create your ALTR account is fully automated; once you begin the process, you must finish in order to successfully create your ALTR account. If you feel like Snowflake Partner Connect isn’t the right option or if you need assistance during the onboarding process, contact ALTR Support.
To access Snowflake Partner Connect:
Log into Snowsight as an account that has access to the ACCOUNTADMIN role.
Change your role to ACCOUNTADMIN.
Select Navigation menu.
→ in theSearch for “ALTR” or select Security & Governance.
Select ALTR.
Click Connect.
ALTR sends you an email to create your ALTR account, set your ALTR password, and start onboarding. Your ALTR username defaults to your email address and your Two-Factor Authentication method defaults to “email.”
Note
If you have an existing ALTR account, this process will direct you to your account. If your email address is tied to two different Snowflake accounts and you use Snowflake Partner Connect for both emails, you will be brought to the same ALTR organization. Contact ALTR Supportt if you need a new ALTR organization created.
When you create an ALTR account through Snowflake Partner Connect, required Snowflake objects are created. Once your account is created, run the SETUP_ALTR_SERVICE_ACCOUNT stored procedure.
ALTR's Service user must have access to a Snowflake role with the appropriate privileges to enforce access governance and security. ALTR operates using the default role on the Snowflake Service user unless the default explicitly overridden 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.
When using Snowflake Partner Connect, there are two ways to grant ALTR the necessary privileges:
Express Configuration (recommended)—Give ALTR privileges to run as ACCOUNTADMIN and the ALTR onboarding wizard runs the required stored procedure. Once onboarding is complete, you can revoke the ACCOUNTADMIN access.
Manual Configuration—Manually run the required stored procedure since it must be run as an ACCOUNTADMIN role.
Express Configuration
With Express Configuration, grant ALTR’s service user the ACCOUNTADMIN role. ALTR uses this role to grant all of the necessary privileges to PC_ALTR_USER. Once onboarding is complete (i.e., you have successfully connected a database), you can safely revoke the ACCOUNTADMIN role from PC_ALTR_USER.
Manual Configuration
With Manual Configuration, manually execute access to PC_ALTR_ROLE required for ALTR to create and enforce governance policies across all of your databases in Snowflake. ALTR provides a stored procedure that automates these privileges, which you will need run. ALTR then verifies the correct privileges were granted. For a list of these privileges and what ALTR uses them for, refer to Service User Privilege Requirements.
If you didn’t go through Snowflake Partner Connect to create your ALTR account, such as if you are using ALTR through a partner, ALTR Support will gladly walk you through the onboarding process.
To have ALTR create your account, contact ALTR Support with the following information:
Company name and address
Name, email address and phone number of the initial ALTR administrator
Create a Service User
Once your ALTR account is created, use ALTR’s stored procedure to create your service user and grant privileges.
Note
If you need to create a custom service user, we have very specific instructions. Learn more.
To create a service user and grant privileges:
Create a database with a well-known name (our recommendation is PC_ALTR_DB) that will house ALTR’s stored procedure.
Create the service user with the name ALTR_SERVICE_USER. The stored procedure creates the role (ALTR_SERVICE_ROLE) and the warehouse (ALTR_SERVICE_WH) if they do not already exist.
Run the following SnowSQL to set the password to ALTR_SERVICE_USER.
CREATE USER IF NOT EXISTS ALTR_SERVICE_USER password = "<Password>";
Run the following SnowSQL 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 stored, 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);