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
- Configuration Key:
- 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
or1
- Enterprise Edition:
0
to90
for permanent databases0
or1
for transient databases
- Standard Edition:
- Configuration Key:
- WITH MANAGED ACCESS: Managed access schemas centralize privilege management with the schema owner
- Configuration Key:
is_managed
- Data Type: Boolean
- Configuration Key:
- 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
- Configuration Key:
- MANAGE_MODE: Configures what properties to manage for the Schema.
- Configuration key:
manage_mode
- Data Type: String
- Possible Values:
none
grants
all
(Default)
- Configuration key:
- GRANTS: List of Privileges and Roles to which privileges are granted to on the current Schema.
- Configuration key:
grants
- Data Type: Map
- Configuration key:
- 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
- Configuration key:
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