Skip to content

Snowflake Privileges

Create a technical user with role

CLOE Airflow needs a user and a role for the user. Important, at this point the database should already exist in DEFAULT_NAMESPACE and the role should be able to access it.

USE ROLE SECURITYADMIN;
CREATE ROLE MY_CLOE_ROLE;
GRANT USAGE ON DATABASE MY_STAGE_DATABASE TO ROLE MY_CLOE_ROLE;
GRANT USAGE ON WAREHOUSE MY_WAREHOUSE TO ROLE MY_CLOE_ROLE;
CREATE OR REPLACE USER USER mycloeuser
    PASSWORD = 'MY_SUPER_SAVE_PASSWORD'
    DEFAULT_ROLE = "MY_CLOE_ROLE"
    DEFAULT_WAREHOUSE = 'WH_XS'
    DEFAULT_NAMESPACE = 'MY_STAGE_DATABASE'
    MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE MY_CLOE_ROLE TO USER mycloeuser;

Assign permissions to Airflow role

Finally, we need to assign all the necessary permissions to the Airflow role:

GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE MY_STAGE_DATABASE TO ROLE MY_CLOE_ROLE;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA MY_STAGE_DATABASE.MY_STAGE_SCHEMA TO ROLE MY_CLOE_ROLE;
GRANT ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA MY_STAGE_DATABASE.cloe_dwh TO ROLE MY_CLOE_ROLE;