Snowflake Native Scoped Permissions Mode

The SQL following snippet provides is an example of how to create a Snowflake role with scoped permissions for the Satori integration. The snippet assumes that the SATORI_ROLE was created according to the instructions in the Integration Settings tab of the Satori console.

-- Create a role for the Satori integration
SET role_name = 'SATORI_ORCHESTRATION_ROLE_SCOPED';
CREATE OR REPLACE ROLE IDENTIFIER($role_name);

-- Grant global permissions to create roles and manage policies
GRANT CREATE ROLE ON ACCOUNT TO ROLE IDENTIFIER($role_name);
GRANT CREATE DATABASE ON ACCOUNT TO ROLE IDENTIFIER($role_name);
GRANT MANAGE GRANTS ON ACCOUNT TO ROLE IDENTIFIER($role_name);
GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE IDENTIFIER($role_name);
GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE IDENTIFIER($role_name);
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE IDENTIFIER($role_name);

-- Allow Satori to read all data from all existing databases. When new databases are
-- created permissions on them need to be granted to the Satori role. If only specific
-- databases should be accessible to Satori, the below permissions need to be granted
-- only on those databases instead of all databases in the system.
DECLARE
    dbs_cursor CURSOR FOR SELECT DATABASE_NAME FROM SNOWFLAKE.INFORMATION_SCHEMA.DATABASES WHERE TYPE = 'STANDARD';
BEGIN
    OPEN dbs_cursor;
    FOR db IN dbs_cursor DO
        LET db_name varchar := db.DATABASE_NAME;
        -- Grant USAGE on the database
        EXECUTE IMMEDIATE
          'GRANT USAGE ON DATABASE "' || db_name || '" TO ROLE "' || $role_name || '"';

-- Grant USAGE on all current schemas
        EXECUTE IMMEDIATE
          'GRANT USAGE ON ALL SCHEMAS IN DATABASE "' || db_name || '" TO ROLE "' || $role_name || '"';

-- Grant SELECT on all current tables
        EXECUTE IMMEDIATE
          'GRANT SELECT ON ALL TABLES IN DATABASE "' || db_name || '" TO ROLE "' || $role_name || '"';

-- Grant SELECT on all current views
        EXECUTE IMMEDIATE
          'GRANT SELECT ON ALL VIEWS IN DATABASE "' || db_name || '" TO ROLE "' || $role_name || '"';

-- Grant USAGE on future schemas
        EXECUTE IMMEDIATE
          'GRANT USAGE ON FUTURE SCHEMAS IN DATABASE "' || db_name || '" TO ROLE "' || $role_name || '"';

-- Grant SELECT on future tables
        EXECUTE IMMEDIATE
          'GRANT SELECT ON FUTURE TABLES IN DATABASE "' || db_name || '" TO ROLE "' || $role_name || '"';

-- Grant SELECT on future views
        EXECUTE IMMEDIATE
          'GRANT SELECT ON FUTURE VIEWS IN DATABASE "' || db_name || '" TO ROLE "' || $role_name || '"';
  END FOR;
END;

-- Grant the new scoped-permissions role to the main Satori role
GRANT ROLE IDENTIFIER($role_name) TO ROLE SATORI_ROLE;