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

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

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
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