Snowflake - Get the schema owner of the currently executing stored procedure

user_7788320 0
Viewed 447

Is there any way to get the owner schema for a stored procedure from within the executing procedure? For example, I have procedure declared as:

CREATE PROCEDURE UTIL.P_TEST()
EXECUTE AS CALLER

When I execute the procedure from within a different schema "NOT_UTIL":

USE NOT_UTIL;
CALL UTIL.P_TEST();

If I use the CURRENT_SCHEMA() function, I get the name of the schema for the current session "NOT_UTIL" (as-expected), What I need is to get the owner schema for the procedure: UTIL.

In TSQL, we can get the schema using this syntax:

OBJECT_SCHEMA_NAME(@@PROCID)

Is there any way to get this value from within a snowflake procedure? Please note that using the "EXECUTE AS OWNER" option is not a viable solution for this use-case.

snowflake-cloud-data-platform procedure
2 Answers

SET CATALOGUE =(SELECT PROCEDURE_CATALOG FROM SNOWFLAKE.INFORMATION_SCHEMA.PROCEDURES WHERE PROCEDURE_NAME LIKE '%NAME%');

user_150889640

CURRENT_SCHEMA() will return the value you are looking for, as long as the procedure has execute as owner (the default).

Otherwise with execute as caller it only knows about the caller's schema.

You didn't specify that you are not using the default execute as env in the question, so I'll assume you did, and that the desired result is exactly what you wanted.

Test with:

create schema temp2;
use schema temp.public;

CREATE OR REPLACE PROCEDURE temp.temp2.currschema()
RETURNS STRING
LANGUAGE JAVASCRIPT
-- execute as caller
execute as owner
AS
$$
var cmd = "select current_schemas()";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

call temp.temp2.currschema();

https://docs.snowflake.com/en/sql-reference/stored-procedures-rights.html

user_1324380
Related