PERMISSION MODEL

Config path: /permision_model.yaml

What is permission model?

Permission model is a special concept introduced by SnowDDL to help managing permissions for databases and schemas. Permission model holds information about CREATE GRANTS and FUTURE GRANTS.

This page describes the practical configuration of permission models. You may consider to reading this guide first explaining the concept in greater detail.

Default model

DEFAULT model is always present and does not require configuration. It is automatically applied to databases and schemas without permission_model parameter being explicitly set.

DEFAULT model will most likely evolve in future versions of SnowDDL in response to Snowflake adding more object types and changing privileges.

default:
  ruleset: SCHEMA_OWNER

  owner_create_grants:
    - FILE_FORMAT
    - FUNCTION
    - PROCEDURE
    - TABLE
    - VIEW

  owner_future_grants:
    ALERT: [OWNERSHIP]
    DYNAMIC_TABLE: [OWNERSHIP]
    EVENT_TABLE: [OWNERSHIP]
    EXTERNAL_TABLE: [OWNERSHIP]
    FILE_FORMAT:  [OWNERSHIP]
    FUNCTION: [OWNERSHIP]
    MATERIALIZED_VIEW: [OWNERSHIP]
    PIPE: [OWNERSHIP]
    PROCEDURE: [OWNERSHIP]
    SEQUENCE: [OWNERSHIP]
    STAGE: [OWNERSHIP]
    STREAM: [OWNERSHIP]
    TABLE: [OWNERSHIP]
    TASK: [OWNERSHIP]
    VIEW: [OWNERSHIP]

  write_future_grants:
    STAGE: [READ, WRITE, USAGE]
    SEQUENCE: [USAGE]
    TABLE: [INSERT, UPDATE, DELETE, TRUNCATE]

  read_future_grants:
    DYNAMIC_TABLE: [SELECT]
    EXTERNAL_TABLE: [SELECT, REFERENCES]
    FILE_FORMAT: [USAGE]
    FUNCTION: [USAGE]
    MATERIALIZED_VIEW: [SELECT, REFERENCES]
    PROCEDURE: [USAGE]
    STAGE: [READ, USAGE]
    STREAM: [SELECT]
    TABLE: [SELECT, REFERENCES]
    VIEW: [SELECT, REFERENCES]
  

Schema

• {key} (ident) - permission model role name

• {value} (dict)

  • inherit_from (str) - inherit all settings from DEFAULT or from another permission model defined earlier in the same config file. This feature helps to reduce code repetition when you have a lot of similar models with minor differences.

  • ruleset (str) - specific rules how "create grants" and "future grants" should be applied, currently supported values are: SCHEMA_OWNER, DATABASE_OWNER

  • owner_create_grants (list) - object types which OWNER role can create

    • {items} (str) - object type

  • owner_future_grants (dict) - future grants for OWNER role

    • {key} (str) - object type

    • {value} (list)

      • {items} (str) - privilege name

  • write_future_grants (dict) - future grants for WRITE role

    • {key} (str) - object type

    • {value} (list)

      • {items} (str) - privilege name

  • read_future_grants (dict) - future grants for READ role

    • {key} (str) - object type

    • {value} (list)

      • {items} (str) - privilege name

Usage notes

1. If you define permission model with name default, it will completely override default permission model which exists in SnowDDL.

2. OWNERSHIP privilege can only be granted to OWNER role. If you do not grant this privilege for specific object type, objects of this type will be owned by SnowDDL admin role or user role of user who created this object. Normally it is highly advised to define OWNERSHIP privilege for all object types you are planning to use.

3. You may change create_grants and future_grants at any time, but changing ruleset may require some additional considerations. Documentation page about changing ruleset for existing databases will be added shortly.

Examples

Example which creates a slightly extended version of DEFAULT model with additional grants:

my_custom_model:
  inherit_from: default
  
  owner_create_grants:
    - STAGE
  
  read_future_grants:
    STAGE: [USAGE]

Example which creates a custom permission model for Fivetran with DATABASE_OWNER ruleset:

my_custom_fivetran_model
  ruleset: DATABASE_OWNER
  
  owner_create_grants:
    - STAGE
    - TABLE
    - VIEW
  
  owner_future_grants:
    STAGE: [OWNERSHIP]
    TABLE: [OWNERSHIP]
    VIEW: [OWNERSHIP]
  
  read_future_grants:
    STAGE: [READ]
    TABLE: [SELECT, REFERENCES]
    VIEW: [SELECT, REFERENCES]

Links

• Parser & JSON Schema (GitHub)