Schema
You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with schemas:
- Manage the lifecycle of new and existing schemas
- Manage grants of schemas
- Manage tags applied to schemas
Usage
We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. Learn more about SOLE for Data Products which is currently available as a private preview.
- Schema in Current Configuration
- Schema in SOLE for Data Products
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>
- schema:
name: <schema-name>
<configuration-key>: <value>
database: rel(database.<database-name>)
grants:
<privilege>:
- rel(role.<role-name>)
- rel(role.<role-name>)
shares:
<privilege>:
- rel(share.<share-name>)
- rel(share.<share-name>)
with_tags:
<tag-name>:
value: <tag-value>
schema: <source-schema>
Supported parameters
The engine supports the parameters listed below.
Configuration Key | Required/Optional | Data Types and Values | Description |
---|---|---|---|
comment | Optional | String | Specifies a comment for the schema |
data_retention_days | Optional | Integer: For standard edition 0 or 1 , and for enterprise edition 0 to 90 (permanent databases) and 0 or 1 (transient databases) | Number of days for which Time Travel actions can be performed on the schema |
deleted | Optional | Boolean: True enables deletion prevention, False does nothing | Specifies what objects are allowed to be deleted |
is_managed | Optional | Boolean | Managed access schemas centralize privilege management with the schema owner |
is_transient | Optional | Boolean | Transient schemas do not have a fail-safe period, so they do not incur additional storage costs once they leave Time Travel. For more information about schema recreation behavior, see Schema storing masking policies or tags. |
grants | Optional | Map: See Supported Schema Grants to Roles | List of Privileges and Roles to which privileges are granted on the current schema |
manage_mode | Optional | String: all (default), none , grants | Configures what properties to manage for the Schema. See Changing Manage Mode before changing the value. |
table_grants | Optional | Map | List of Privileges and Roles to which privileges are granted for all tables in the current schema |
with_tags | Optional | Object: See here for a definition of with_tags | List of tag references to be applied to the current schema |
with_tags
parameter
You can specify a list of tags to apply to the schema.
Multiple tags are listed in the with_tags
parameter in an object format with the following parameters:
Parameter | Required/Optional | Data Type and Values | Description |
---|---|---|---|
value | Required | String | Value to assign to tag |
schema | Optional | String | Schema of the tag identifier. If omitted defaults to the name of this schema object. |
database | Optional | String | Database of the tag identifier. If omitted defaults to the name of the database this schema is in. |
Example
- Schema in Current Configuration
- Schema in SOLE for Data Products
databases:
DB1:
schema:
SCHEMA1:
with_tags:
DEPARTMENT:
value: Sales
CONFIDENTIALITY:
value: Sensitive
schema: TAG_LIBRARY
- schema:
name: SCHEMA1
database: rel(database.DB1)
with_tags:
DEPARTMENT:
value: Sales
CONFIDENTIALITY:
value: Sensitive
schema: TAG_LIBRARY
Supported schema grants to roles
Following are the privileges you can grant to roles 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 privilege you can grant to shares 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 override grants defined in the parent's section. That means that schema_grants
defined in the database would be overridden by grants
defined in the schema.
If no overriding grants are defined, then grants from parents are inherited for all schemas. If schema_grants
is defined in the database, then all schema in the database would inherit the grants defined.
Schema storing masking policies or tags
If a schema is used to store masking policies or tags, a recreation of the schema will lead to an error. Read more about this behavior here.
The recreation can occur due to the following factors:
Switching the transient nature of the schema
Snowflake doesn't allow altering the schema to change the transient nature. Due to this if the value is_transient
is changed, it leads to recreation.
Database recreated
If a database is recreated, the schemas present in the database are also recreated. To read more about database recreation, see database storing masking policies or tags.
Examples
Schemas with configured parameters
- Schema in Current Configuration
- Schema in SOLE for Data Products
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
- schema:
name: SALES
database: rel(database.SALES_RECORD)
comment: "Test Schema"
data_retention_days: 0
is_managed: false
is_transient: false
grants:
MODIFY:
- rel(role.HR_ROLE)
- ACCOUNTADMIN
MONITOR:
- rel(role.DEV_ROLE)
- ACCOUNTADMIN
shares:
USAGE:
- rel(share.DEV_SHARE)
table_grants:
UPDATE:
- rel(role.HR_ROLE)
- ACCOUNTADMIN