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.

Danger

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

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 "<DATABASE>";

instead of

GRANT USAGE ON FUTURE TABLES IN SCHEMA "<SCHEMA>";

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, follow the steps in Create a Service User.

Note

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

Run ALTR’s Stored Procedure with Custom Service User Objects

To run ALTR’s stored procedure with custom service user objects, follow the steps in Create a Service User.

(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:

1. Update in the following command based on how you created your ALTR 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;

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

2. 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:

• , and to your respective values
• SCHEDULE if you want the task to run at a time other than daily at midnight UTC
• 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.

Danger

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.

If you need to use a different service user name or role name (to stay in compliance with your naming convention for example), follow the steps in Create a Service User.