How to grant access to PROCEDURE_DEFINITION in Snowflake from ADMIN role to another role?-CodePudding

I have created a stored procedure 'XYZ' in "ADMIN" schema by using the role "ADMIN_ROLE" and have granted a "USAGE" permission from "ADMIN" account to another role which is "ABC_ROLE".

Statement used:

GRANT USAGE 
ON ALL PROCEDURES IN SCHEMA "DATABASE_TEST"."ADMIN" 
TO ROLE "ABC_ROLE";

However I do not see the PROCEDURE_DEFINITION if I use ABC_ROLE.

SELECT PROCEDURE_DEFINITION  
FROM INFORMATION_SCHEMA.procedures 
WHERE procedure_name = 'XYZ'

returns NULL or DESCRIBE PROCEDURE XYZ() returns body as blank.

What additional permissions should I provide to grant access to body/procedure definition for "ABC_ROLE" from "ADMIN_ROLE"?

CodePudding user response：

If the stored procedure executes as caller, then roles with the usage permission can see its body.

Test:

create or replace procedure test_view(x string)
returns string 
language javascript
execute as caller
as $$
return "hi"
$$;

describe procedure test_view(string);

grant usage on procedure test_view(string) to role public;

use role public;

call test_view('');

describe procedure test_view(string);

But if the procedure executes as owner, then the owner can see its body.

Note that procedures privileges are only 'usage' and 'ownership':

https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html#stored-procedure-privileges