SSC-EWI-TS0080

Changing the execution context at runtime is not supported in Snowflake

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 [e-mail]