SSC-EWI-TS0080

Changing the execution context at runtime is not supported in Snowflake

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Severity

High

Description

Users in SQL Server can use the command EXECUTE AS to temporarily change the execution context, this modifies the execution privileges and affects the results of context-dependent functions like USER_NAME(). The REVERT command can be used to restore the context previous to the last EXECUTE AS.

Snowflake only supports the definition of an execution context in procedures, using either the CREATE PROCEDURE or ALTER PROCEDURE statements. Changing the context at runtime is not supported.

Code Example

Input Code:

IN -> SqlServer_01.sql

CREATE PROCEDURE proc1()
WITH EXECUTE AS OWNER
AS
BEGIN
	SELECT USER_NAME();
	EXECUTE AS CALLER;
	SELECT USER_NAME();
	REVERT;
	SELECT USER_NAME();
END

GO

Output Code:

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE proc1 ()
RETURNS ARRAY
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/05/2024" }}'
EXECUTE AS OWNER
AS
$$
	DECLARE
		ProcedureResultSet1 VARCHAR;
		ProcedureResultSet2 VARCHAR;
		ProcedureResultSet3 VARCHAR;
		return_arr ARRAY := array_construct();
	BEGIN
		ProcedureResultSet1 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet1) AS
			SELECT
				CURRENT_USER();
		return_arr := array_append(return_arr, :ProcedureResultSet1);
		!!!RESOLVE EWI!!! /*** SSC-EWI-TS0080 - CHANGING THE EXECUTION CONTEXT AT RUNTIME IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
	EXECUTE AS CALLER;
		ProcedureResultSet2 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet2) AS
			SELECT
				CURRENT_USER();
		return_arr := array_append(return_arr, :ProcedureResultSet2);
		!!!RESOLVE EWI!!! /*** SSC-EWI-TS0080 - CHANGING THE EXECUTION CONTEXT AT RUNTIME IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
	REVERT;
		ProcedureResultSet3 := 'RESULTSET_' || REPLACE(UPPER(UUID_STRING()), '-', '_');
		CREATE OR REPLACE TEMPORARY TABLE IDENTIFIER(:ProcedureResultSet3) AS
			SELECT
				CURRENT_USER();
		return_arr := array_append(return_arr, :ProcedureResultSet3);
		--** SSC-FDM-0020 - MULTIPLE RESULT SETS ARE RETURNED IN TEMPORARY TABLES **
		RETURN return_arr;
	END;
$$;

Recommendations

• Refactor the code so it works without having to switch the context.

• If you need more support, you can email us at [email protected]