Is there a way in which I can grant only read access of a procedure in snowflake?

user_11455017 0
Viewed 84

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

snowflake-cloud-data-platform
1 Answers

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.

user_127563810
Related