Snowflake / How get the original caller when a stored procedure is executed as OWNER

Question

I have created a stored procedure with the behavior execute as OWNER (I need the owner’s rights).

I am looking to get the original role of the user that called it, is there any way to do that? Unfortunately, as expected the select current_role() statement returns the owner's role ...

If a workaround exists, I would appreciate if you could describe it to me.

Following Mike's advice:there the code I done to test Harsh's solution:

Failed again ^^ There's my code. May be you ll see my mistake.

create or replace procedure do_noting_proc()
returns string
language javascript
execute as OWNER
AS
$$
 var retour="Resultat";
 try{
   rs0=snowflake.createStatement({sqlText:"SELECT $QUERY_TAG;"}).execute();
   cR=rs0.getColumnValue(1);
   return "QRValue= "+callerRole;        
 }catch(err){
    return "do_noting_proc KO: "+ err.message+";";
 }
$$;

create or replace procedure test_proc()
returns string
language javascript
execute as CALLER
AS
$$
var rString="Result -->";
try{
  rs0=snowflake.createStatement({sqlText:"SELECT CURRENT_ROLE();"}).execute();
  rs0.next();
  cr="testQT"+rs0.getColumnValue(1);
  snowflake.createStatement({sqlText:"alter session set QUERY_TAG="+cr+";"}).execute();
  rs=snowflake.createStatement({sqlText:"call  PUBLIC.do_noting_proc();"}).execute();
  rs.next();
  rString+=rs.getColumnValue(1);
}catch(err){
    return "test_proc: "+ err.message+";";
}
return "test_proc : "+rString;
$$;

Thencall share_view_or_table(); ==>test_proc : ** Result -->do_noting_proc KO: Use of session variable '$QUERY_TAG' is not allowed in owners rights stored procedure;** (procedure created by sysadmin and Executed as sysadmin)

Also if I run 'code' select $QUERY_TAG;

I get the error: SQL compilation error: error line 1 at position 7 Session variable '$QUERY_TAG' does not exist

whereas

SHOW PARAMETERs LIKE 'QUERY_TAG' IN SESSION ;

works fine and print TESTQTSYSADMIN

@Harsh many thanks for your time

I missed your comment (sorry Mike). Yes that's it, from within the stored procedure — SJE

Unknown Unknown · Accepted Answer · 2020-05-31T16:37:34

When using EXECUTE AS OWNER the rules do not permit reading the caller's session details, including their identity or role:

Owner’s rights stored procedures adhere to the following rules within a session:

[…]

Cannot access most caller-specific information. For example:

Cannot view, set, or unset the caller’s session variables.

Can read only specific session parameters, and cannot set or unset any of the caller’s session parameters.

Cannot query INFORMATION_SCHEMA table functions, such as AUTOMATIC_CLUSTERING_HISTORY, that return results based on the current user.

While inconvenient, you can use logic within the stored procedure to enforce that the role name of the caller should be passed as an argument:

CREATE OR REPLACE PROCEDURE PROC(ROLE_NAME STRING)
[…]
var VALID_ROLES_ARRAY = ["FOO", "BAR"];
var IS_VALID_CALLER_ROLENAME = VALID_ROLES_ARRAY.includes(ROLE_NAME);
[…];

CALL PROC(CURRENT_ROLE());

Snowflake / How get the original caller when a stored procedure is executed as OWNER

1 Answers