[Python] How to Transfer Snowflake Schema Ownership Without Revoking Fivetran Role Privileges?

I'm trying to design a solution to provide access to certain users to confidential data in snowflake from xero (an accounting tool) using a Fivetran connector.

I have a python script that basically controls and creates accesses (dbs, functional roles, access roles, etc), the roles are granted to be owned by a DataOps role.

Due to our process we have 2 functional roles CONFIDENTIAL_ROLE (where it will be used to add or remove users that will be allowed to access the schemas) and FIVETRAN_UAT (the functional role for Fivetran ingestion of data)

The issue comes in Snowflake. Since this is created under a DataOps role and it's not suppose for the users of the DataOps role have access to the data, we need to transfer ownership of the role, and for this I am doing this step:

GRANT OWNERSHIP ON ROLE CONFIDENTIAL_DATA TO ROLE ACCOUNTADMIN REVOKE CURRENT GRANTS

And from here comes the need of help, initially the 2nd line code that I was running was

GRANT OWNERSHIP ON SCHEMA BD_UAT.DEMO_CLIENT_SCHEMA TO ROLE CONFIDENTIAL_ROLE REVOKE CURRENT GRANTS

Running this 2nd line of code, results in the end goal of allowing the CONFIDENTIAL_ROLE role to access the data and blocking the DataOps role from access the data, however this will brake the Fivetran connector because it will revoke the privileges of the FIVETRAN_UAT role.

Also tried instead of running the 2nd line of code, write the bellow lines:


GRANT OWNERSHIP ON SCHEMA BD_UAT.DEMO_CLIENT_SCHEMA TO ROLE CONFIDENTIAL_ROLE

REVOKE OWNERSHIP ON SCHEMA BD_UAT.DEMO_CLIENT_SCHEMA FROM ROLE DataOps



But will lack in error or insufficient permissions

I am having trouble transferring the ownership, need assist on to tackle this.

Continue reading...