Snowflake privileges

Question

I have created a user and role like user : ali role : analyst

i have granted this role and user to accountadmin and systemsecurity roles.

And i granted usage permission on database Admin and custom schemas of database Admin to role analyst.

Also granted select permission on table "Admin.schema.table1" to role analyst.

But when i tried to execute the below query "Admin.information_schema.procedures" under role "analyst" showing empty table.

when i execute "Admin.information_schema.procedures" with role "accountadmin" it is showing results.

why i am getting empty resultset with analyst custom-role ?

Answer 1

Since Procedure is a Schema object, apart from USAGE privilege on database Admin, you need to grant access on both the Schema "SCHEMA" as well as any target Procedure.

You can do this by running the following statements, as accountadmin:

GRANT USAGE ON SCHEMA ADMIN.SCHEMA TO ROLE ANALYST;
GRANT USAGE ON PROCEDURE ADMIN.SCHEMA.<PROCEDURE_NAME> TO ROLE ANALYST;

Now that you do have access to the target procedure(s), you can query it in information_schema.procedures View.

Answer 2

Given the Snowflake documentation on information_schema.procedures VIEW

The view only displays objects for which the current role for the session has been granted access privileges. The view does not honor the MANAGE GRANTS privilege and consequently may show less information compared to a SHOW command when both are executed by a user who holds the MANAGE GRANTS privilege.

So because your analyst role doesn't have privilege on any procedures in the schema it shows an empty result. When you query the view with the accountadmin role which got privilege on everything in your account then you can see the procedures this role has privilege on.

You need to grant USAGE privilege on the procedure and of course USAGE on schema containing the procedure.

GRANT USAGE ON SCHEMA my_db.admin TO ROLE analyst;
GRANT USAGE ON PROCEDURE my_db.admin.my_proc TO ROLE analyst;

and for procedures not existing yet

USE ROLE SECURITYADMIN;
GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA my_db.admin TO ROLE analyst;