Snowflake stored procedure permission question

Question

I am struggling to grant execute permission on a simple stored procedure

I create the following procedure using the ROLE SYSADMIN

CREATE OR REPLACE PROCEDURE PUBLIC.SOME_PROC()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS 
$$
  return "Hello";
$$
;

I test it by calling it

CALL PUBLIC.SOME_PROC();

I grant execution as below

GRANT USAGE ON PROCEDURE PUBLIC.SOME_PROC() TO ROLE UAT_OPERATIONS;

I switch role

USE ROLE UAT_OPERATIONS;

I try to call it again

CALL PUBLIC.SOME_PROC();

And I get the following error

SQL Error [2141] [42601]: SQL compilation error: Unknown user-defined function PUBLIC.SOME_PROC

What am I missing???

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2020-10-30T20:57:39

To fix the same error, I had to make sure to also grant privileges USAGE ON DATABASE and USAGE ON SCHEMA to the role — otherwise you'll get the Unknown error when working within that role.

GRANT USAGE ON DATABASE db TO ROLE UAT_OPERATIONS;
GRANT USAGE ON SCHEMA db.public TO ROLE UAT_OPERATIONS;
GRANT USAGE ON PROCEDURE db.PUBLIC.SOME_PROC() TO ROLE UAT_OPERATIONS;

Snowflake stored procedure permission question

1 Answers