Immuta-Snowflake integration bootstrap SQL script examples and brief explanation

Immuta bootstrap SQL script exmples:

• Enhanced Onboarding script example:

0B

enhanced_onboarding_bootstrap.sql

• Manual integration example:

4KB

manual_integration_bootstrap_script.sql

• Integration cleanup script example - it drops the Immuta database, drops the SYSTEM role, the SYSTEM_ACCOUNT user.

2KB

integration_cleanup_script.sql

The Immuta bootstrap SQL script establishes the framework in Snowflake for the integration. It includes the creation of an Immuta database in Snowflake, a dbname_SYSTEM role, and a dbname_SYSTEM_ACCOUNT (for automated integration) to manage the integration moving forward. This Immuta database in Snowflake will house all elements required for the integration, including policy objects created by Immuta.

The table below shows an example of the bootstrap script that creates the framework in Snowflake for the integration. The left column provides a brief explanation of what some of the SQL statements do and the ROLE permission required to execute them in Snowflake.

Permission required / What?	Native Integration SQLs
SYSADMIN immuta database - stores all Immuta managed objects immuta_procedures and immuta_functions schemas - for Immuta functions and Immuta procedures that manages the integration	CREATE DATABASE "BC_IMMUTA"; CREATE SCHEMA IF NOT EXISTS "BC_IMMUTA".immuta_procedures; CREATE SCHEMA IF NOT EXISTS "BC_IMMUTA".immuta_functions;
USERADMIN Naming convention: <DB>_SYSTEM role and <DB>_SYSTEM_ACCOUNT user <DB>_SYSTEM role - use for managing Immuta database. <DB>_SYSTEM_ACCOUNT user - for accessing and managing Immuta database. The impersonation role if this feature is selected/enabled is for user impersonation Grant ownership of roles and uses to be managed FULLY by the <DB>_SYSTEM role. Set <DB>_SYSTEM role for <DB>_SYSTEM_ACCOUNT user.	CREATE ROLE IF NOT EXISTS "BC_IMMUTA_SYSTEM"; CREATE USER IF NOT EXISTS "BC_IMMUTA_SYSTEM_ACCOUNT" default_role="BC_IMMUTA_SYSTEM" login_name="BC_IMMUTA_SYSTEM_ACCOUNT" default_namespace="BC_IMMUTA" password="HEKEuhEoQHPqkD3Xzoj72U0JWP0tqA5OJuqcBkNzplw="; CREATE ROLE "BC_IMMUTA_UI_IMPERSONATION"; GRANT OWNERSHIP ON ROLE "BC_IMMUTA_UI_IMPERSONATION" TO ROLE "BC_IMMUTA_SYSTEM"; GRANT ROLE "BC_IMMUTA_SYSTEM" TO USER "BC_IMMUTA_SYSTEM_ACCOUNT"; ALTER USER "BC_IMMUTA_SYSTEM_ACCOUNT" SET default_role="BC_IMMUTA_SYSTEM";
SECURITYADMIN Granting <DB>_SYSTEM and <DB>_SYSTEM_ACCOUNT ownership, grant option, and future grant to objects in the Immuta database. Grants usage of Snowflake warehouse to the <DB>_SYSTEM role.	GRANT ALL PRIVILEGES ON DATABASE "BC_IMMUTA" TO ROLE "BC_IMMUTA_SYSTEM" WITH GRANT OPTION; GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE "BC_IMMUTA" TO ROLE "BC_IMMUTA_SYSTEM" WITH GRANT OPTION; GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA "BC_IMMUTA".immuta_procedures TO ROLE "BC_IMMUTA_SYSTEM" WITH GRANT OPTION; GRANT USAGE ON WAREHOUSE "DEMO_WAREHOUSE" TO ROLE "BC_IMMUTA_SYSTEM";
SECURITYADMIN This privilege is for the native query audit feature; grants it to Immuta <DB>_SYSTEM role.	GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE "BC_IMMUTA_SYSTEM";
ACCOUNTADMIN This privilege is for Snowflake object tag ingestion feature; grants to Immuta <DB>_SYSTEM role. Vary if data-catalog integration is setup prior to integration setup; may vary depending on different Immuta versions.	GRANT APPLY TAG ON ACCOUNT TO "BC_IMMUTA_SYSTEM";
SECURITYADMIN Grants usage of the Immuta database, Immuta functions (including future grants), and Immuta procedures to Snowflake's default PUBLIC role (which can be redefined to another role as needed) without exposing any sensitive information. These grants are intended for use cases involving project-native-workspaces or when Immuta. May vary in later versions of Immuta	GRANT USAGE ON DATABASE "BC_IMMUTA" TO ROLE PUBLIC; GRANT USAGE ON SCHEMA "BC_IMMUTA".immuta_procedures TO ROLE PUBLIC; GRANT USAGE ON SCHEMA "BC_IMMUTA".immuta_functions TO ROLE PUBLIC; GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "BC_IMMUTA".immuta_functions TO ROLE PUBLIC;
ACCOUNTADMIN Required for the governance control feature(for both row-access and column-masking objects) Require Snowflake to be on Enterprise+ edition.	GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE "BC_IMMUTA_SYSTEM"; GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE "BC_IMMUTA_SYSTEM";
SYSADMIN The IMMUTA_POLICIES schema will house all row-access and masking policy objects created natively in Snowflake; the policy objects are applied to tables/views via ADD/SET.	CREATE SCHEMA IF NOT EXISTS "BC_IMMUTA".immuta_policies;
SECURITYADMIN Grant full ownership of IMMUTA_POLICIES schema to the <DB>_SYSTEM role. Using the <DB>_SYSTEM role can review Immuta created policy objects inside the schema, e.g., SHOW ROW ACCESS POLICIES;	GRANT OWNERSHIP ON SCHEMA "BC_IMMUTA".immuta_policies TO ROLE "BC_IMMUTA_SYSTEM" COPY CURRENT GRANTS;
SECURITYADMIN Grant full ownership of these schemas to the Immuta <DB>_SYSTEM role with the COPY CURRENT GRANTS	GRANT OWNERSHIP ON SCHEMA "BC_IMMUTA".immuta_procedures TO ROLE "BC_IMMUTA_SYSTEM" COPY CURRENT GRANTS; GRANT OWNERSHIP ON SCHEMA "BC_IMMUTA".immuta_functions TO ROLE "BC_IMMUTA_SYSTEM" COPY CURRENT GRANTS; GRANT OWNERSHIP ON SCHEMA "BC_IMMUTA".public TO ROLE "BC_IMMUTA_SYSTEM" COPY CURRENT GRANTS;