How to pass parameters when calling an sql script from a stage in snowflake

Question

I am wondering what is the best way to pass variables as input parameters when running a SQL script from a stage in snowflake.

I already have a procedure that grants privileges to a user

  CREATE OR REPLACE PROCEDURE CHANGE_GRANTS_TO_ROLE("VAR_DB" VARCHAR(16777216), "VAR_SCHEMA" VARCHAR(16777216), "VAR_ROLE" VARCHAR(16777216), "VAR_OLD_ROLE" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
EXECUTE AS CALLER
AS '
    BEGIN
        grant usage on database IDENTIFIER(:VAR_DB) to role IDENTIFIER(:VAR_ROLE);

        GRANT USAGE,monitor,create table,create view,create materialized view, create file format,create sequence,create function,create procedure ,create stage,create task
        ON SCHEMA IDENTIFIER(:VAR_SCHEMA)to role IDENTIFIER(:VAR_ROLE) copy current grants; 

        grant ownership on all tables in schema  IDENTIFIER(:VAR_SCHEMA) to role IDENTIFIER(:VAR_ROLE) copy current grants;
        grant ownership on all views in schema  IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        grant ownership on all materialized views in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        grant ownership on all sequences in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        grant ownership on all procedures in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        grant ownership on all functions in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        grant ownership on all stages in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        grant ownership on all file formats in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;

        grant ownership on IDENTIFIER(:VAR_SCHEMA) to role sysadmin copy current grants;
        return ''Procedure Completed'';
    end;
    ';

I was wondering if it is possible to save this as an SQL file and pass parameters to it for the roles and schema names when executing it using the EXECUTE IMMEDIATE command, or similar?

I am looking to do something like this:

DECLARE
  VAR_SCHEMA_NAME = utildb.marketing
  VAR_ROLE = MARKETING_ROLE
BEGIN
  EXECUTE IMMEDIATE FROM @SQL_FILES/CHANGE_GRANTS_TO_ROLES.sql USING (VAR_SCHEMA_NAME, VAR_ROLE));
  RETURN 'SUCCESS'
END;

I have tried running it as a procedure, but unsure how to pass parameters to the script when running it as a sql file from a stage.

Answer 1

As per my understanding snowflake don't support this natively, But we need to craft our solution in order to support the way you wanted.

Basically we need some common ground to let sql file read the parameters runtime. We can use temp table to achieve this. See the following way.

Let me know if this works in your case.

calling script

   BEGIN
        create or replace table config (Parameter varchar,value varchar);
        insert overwrite into config values 
        ('VAR_DB','try_roles')
        ,('VAR_SCHEMA','public')
        ,('VAR_ROLE','role1');
      EXECUTE IMMEDIATE FROM @SQL_FILES/CHANGE_GRANTS_TO_ROLES.sql;
      RETURN 'SUCCESS';
    END;

SQL file script

BEGIN
let VAR_DB varchar := (select top 1 value from config where parameter = 'VAR_DB');
let VAR_SCHEMA varchar := (select top 1 value from config where parameter = 'VAR_SCHEMA');
let VAR_ROLE varchar := (select top 1 value from config where parameter = 'VAR_ROLE');
grant usage on database IDENTIFIER(:VAR_DB) to role IDENTIFIER(:VAR_ROLE);

        -- GRANT USAGE,monitor,create table,create view,create materialized view, create file format,create sequence,create function,create procedure ,create stage,create task
        -- ON SCHEMA IDENTIFIER(:VAR_SCHEMA)to role IDENTIFIER(:VAR_ROLE) copy current grants; 

        -- grant ownership on all tables in schema  IDENTIFIER(:VAR_SCHEMA) to role IDENTIFIER(:VAR_ROLE) copy current grants;
        -- grant ownership on all views in schema  IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        -- grant ownership on all materialized views in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        -- grant ownership on all sequences in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        -- grant ownership on all procedures in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        -- grant ownership on all functions in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        -- grant ownership on all stages in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;
        -- grant ownership on all file formats in schema   IDENTIFIER(:VAR_SCHEMA)  to role IDENTIFIER(:VAR_ROLE) copy current grants;

        -- grant ownership on IDENTIFIER(:VAR_SCHEMA) to role sysadmin copy current grants;
        return 'Grants Executed';
        END;