Lack of permissions to create stored procedures (snowflake)

Question

Currently migrating to snowflake from another relational database. Below are the SQL commands that we used in attempt to grant the right permissions. When attempting to create the stored procedure in the DEVELOPER_ARCHIVE database and LANDING_ZONE schema using the DEVELOPER_ROLE, we get an error that we don't have the permissions.

CREATE ROLE IF NOT EXISTS DEVELOPER_ROLE;
CREATE ROLE IF NOT EXISTS  DEVELOPER_CRUD_ROLE;
CREATE ROLE IF NOT EXISTS DEVELOPER_READONLY_ROLE;
GRANT ROLE DEVELOPER_READONLY_ROLE TO role DEVELOPER_CRUD_ROLE;
GRANT USAGE ON DATABASE DEVELOPER_ARCHIVE to DEVEVELOPER_CRUD_ROLE;

We have granted the following permissions prior to granting the USAGE GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA DEVELOPER_ARCHIVE.LANDING_ZONE to role DEVELOPER_CRUD_ROLE; Granting USAGE on DEVELOPER_ARCHIVE doesn't cover the entire tree? Do we have to specifically name LANDING_ZONE? — edennis

bstora bstora · Accepted Answer · 2020-06-18T16:37:28

The role will also need usage on the LANDING_ZONE schema and an additional create procedure grant. Here is a link to the grants documentation (schemaPrivileges);

https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html

Lack of permissions to create stored procedures (snowflake)

1 Answers