snowflake-cloud-data-platform

Privileges needed to execute Notebook in Snowflake

I am trying to execute Snowflake Notebooks from a tasks. However, the user (the role "PROD_ALL_RW_AR") that is the owner of the task (and hence is used to run the task) does not have the right to access the notebooks, which were created and are owned by ACCOUNTADMIN.

The documentation on Notebooks says that USAGE or OWNERSHIP of the Notebook are required to execute a Notebook.

I have tried the following to grant usage:

GRANT USAGE ON ALL NOTEBOOKS TO ROLE PROD_ALL_RW_AR; -- Unsupported feature 'ALL'.

GRANT USAGE ON NOTEBOOK BI_ELT.INTEGRATIONS.PRE_ADRECORD_PATCH TO ROLE PROD_ALL_RW_AR; --SQL compilation error: Notebook does not support this feature.

I also tried to change ownership:

GRANT OWNERSHIP ON ALL NOTEBOOKS IN DATABASE BI_ELT TO ROLE PROD_ALL_RW_AR; -- Unsupported feature 'GRANT on all objects of type NOTEBOOK'.

GRANT OWNERSHIP ON NOTEBOOK BI_ELT.INTEGRATIONS.PRE_ADRECORD_PATCH TO ROLE PROD_ALL_RW_AR;  -- SQL compilation error: Notebook does not support this feature.

None of this works.

The role PROD_ALL_RW_AR does have usage rights on the database.

Noit sure what to do here. Any hints are greatly appreciated!

Solution

    1. Using ACCOUNTADMIN to create and own objects is not recommended. More at: Avoid using the ACCOUNTADMIN role to create objects

    2. Notebooks can be exported and reimported/recreated using a desired role. For instance: PROD_ALL_RW_AR can be owner of both tasks and notebooks.

    3. GRANT OWNERSHIP should be possible according to docs. Access control requirements:

    Access control requirements

    USAGE or OWNERSHIP

    Notebook

    OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

    Though it seems it is not supported(?):

    SELECT CURRENT_VERSION();
-- 8.47.1

GRANT OWNERSHIP ON NOTEBOOK <DB>.<schema>.<notebook> TO ROLE <role>;
-- Notebook does not support this feature.

    A workaround (if usage/ownership cannot be granted directly on notebook level for some reason) is to wrap notebook execution with stored procedure defined as EXECUTE AS OWNER. Pseudocode below(not tested!):

    -- Role_X is owner of Notebook_A

-- Role_X is owner of stored procedure
CREATE PROCEDURE db.schema.notebook_A_launcher()
...
EXECUTE AS OWNER
AS
$$
   EXECUTE NOTEBOOK db.schema.notebook_a();
$$;

-- Grant usage of SP to role that owns task
GRANT USAGE ON PROCEDURE db.schema.notebook_A_launcher() TO ROLE Role_Y;

-- Role_Y is owner of task
CREATE OR ALTER TASK db.schema.task_name
WAREHOUSE = my_warehouse
SCHEDULE = '60 minute'
AS
$$
  CALL db.schema.notebook_A_launcher();
$$