Skip to main content

Schema

Configuration can be provided to Snowflake Object Lifecycle Engine for the following operation with schemas:

  • Manage Lifecycle of new and existing schemas
  • Manage Grants of schemas
  • Manage Tags applied to schemas

Supported Parameters​

The engine supports the parameters listed below.

  • COMMENT: Specifies a comment for the schema.
    • Configuration Key: comment
    • Data Type: String
  • DATA_RETENTION_DAYS: Number of days for which Time Travel actions can be performed on the schema
    • Configuration Key: data_retention_days
    • Data Type: Integer
    • Range:
      • Standard Edition: 0 or 1
      • Enterprise Edition:
        • 0 to 90 for permanent databases
        • 0 or 1 for transient databases
  • WITH MANAGED ACCESS: Managed access schemas centralize privilege management with the schema owner
    • Configuration Key: is_managed
    • Data Type: Boolean
  • TRANSIENT: Transient schemas do not have a Fail-safe period, so they do not incur additional storage costs once they leave Time Travel
    • Configuration Key: is_transient
    • Data Type: Boolean
  • MANAGE_MODE: Configures what properties to manage for the Schema.
    • Configuration key: manage_mode
    • Data Type: String
    • Possible Values:
      • none
      • grants
      • all(Default)
  • GRANTS: List of Privileges and Roles to which privileges are granted to on the current Schema.
    • Configuration key: grants
    • Data Type: Map
  • TABLE_GRANTS: List of Privileges and Roles to which privileges are granted to for all Tables in the current Schema.
    • Configuration key: table_grants
    • Data Type: Map
  • WITH_TAGS: List of Tag references to be applied to the current Schema.
    • Configuration key: with_tags
    • Data Type: Object. See here for definition of with_tags.

Basic syntax​

databases:
  <database-name>:
    schemas:
      <schema-name>:
        <configuration-key>: <value>
        grants:
          <privilege>:
            - <role-name>
            - <role-name>
        shares:
          <privilege>:
            - <share-name>
        with_tags:
          <tag-name>:
            value: <tag-value>
            schema: <source-schema>

with_tags Parameter​

User can specify a list of tags to apply to the schema.

Multiple tags be listed in the with_tags parameter in an object format, with the following parameters:

  • VALUE: Value to assign to Tag
    • REQUIRED
    • Configuration key: value
    • Data Type: String
  • SCHEMA: Schema of the Tag identifier. If omitted defaults to the name of this schema object.
    • Configuration key: schema
    • Data Type: String
  • DATABASE: Database of the Tag identifier. If omitted defaults to the name of the database this schema is in.
    • Configuration key: database
    • Data Type: String

Example​

databases:
  DB1:
    schema:
      SCHEMA1:
        with_tags:
          DEPARTMENT:
            value: Sales
          CONFIDENTIALITY:
            value: Sensitive
            schema: TAG_LIBRARY

Supported Schema Grants to Roles​

Following is the list of Privileges Grant to Roles that can be specified in the schema definition

  • ALL PRIVILEGES
  • MODIFY
  • MONITOR
  • USAGE
  • CREATE TABLE
  • CREATE EXTERNAL TABLE
  • CREATE VIEW
  • CREATE MATERIALIZED VIEW
  • CREATE MASKING POLICY
  • CREATE FILE FORMAT
  • CREATE STAGE
  • CREATE PIPE
  • CREATE STREAM
  • CREATE TASK
  • CREATE SEQUENCE
  • CREATE FUNCTION
  • CREATE PROCEDURE
  • OWNERSHIP
info

For supported Table Grants see Grants section in Table

Supported Schema Grants to Shares​

Following is the list of Privileges Grant to Shares that can be specified in the schema definition

  • USAGE

Inherited Grants​

Grants for a schema can be defined in one of three sections:

  • Schema Definition with parameter grants
  • Database Definition with parameter schema_grants

Local grants overrides grants defined in parent's section. That means that schema_grants defined in the database would be overridden by grants defined in schema.

If no overriding grants are defined, then grants from parents are inherited for all schema. If schema_grants is defined in the database, then all schema in the database would inherit the grants defined.

Examples​

Schemas with configured parameters

databases:
  SALES_RECORD:
    schemas:
      SALES:
        comment: "Test Schema"
        data_retention_days: 0
        is_managed: false
        is_transient: false
        grants:
          MODIFY:
            - HR_ROLE
            - ACCOUNTADMIN
          MONITOR:
            - DEV_ROLE
            - ACCOUNTADMIN
        shares:
          USAGE:
            - DEV_SHARE
        table_grants:
          UPDATE:
            - HR_ROLE
            - ACCOUNTADMIN
Last updated on