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

Supported Parameters#

  • 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_garnts
    • Data Type: Map

Basic syntax#

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

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

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