I have created a Procedure in snowflake
I want to give read-privileges of that stored procedure in snowflake to some other user/role.
GRANT USAGE ON PROCEDURE
get_column_scale(float)
TO ROLE other_role_name_here;
I believe the above command would also give the write access, which I don't want. Is there a way in which only read access can be granted
The only privilege to assign to a procedure is usage
, so if you want a role to be able to read the definition for the procedure but not run it, then you can use a stored procedure defined to execute with owner's rights:
create or replace procedure read_procedure(PROCEDURE_PATH string)
returns string
language javascript
execute as owner
as
$$
let rs = snowflake.execute({sqlText: `select get_ddl('procedure', ?) as DDL`, binds:[PROCEDURE_PATH]});
rs.next();
return rs.getColumnValue('DDL');
$$;
create or replace procedure FOO(S string)
returns string
language javascript
as
$$
return S;
$$;
grant usage on procedure READ_PROCEDURE(string) to role MY_ROLE;
call read_procedure('FOO(string)');
In this setup, make sure that MY_ROLE
has usage on procedure read_procedure(string)
but does not have usage on procedure foo(string)
. That way the role will be able to see the definition of foo(string)
without the privilege required to execute it (usage).
Note that since the read_procedure(string)
procedure runs with owner's rights, anyone calling it will be able to read any procedure that the owner's role can read.
Edit: You can also read the SP in parts in the INFORMATION_SCHEMA.PROCEDURES view. You can grant select on that view without granting USAGE on the procedure.