Skip to main content

Database

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

  • Manage Lifecycle of new and existing Databases
  • Manage Lifecycle of cloned Databases
  • Manage Grants of Databases

Supported Parameters#

The engine supports the parameters listed below.

  • DATA_RETENTION_TIME_IN_DAYS: Specifies the number of days for which Time Travel actions (CLONE and UNDROP) can be performed on the database, range 0 to 90.
    • Configuration key: data_retention_time_in_days
    • Data Type: Integer
    • Range:
      • Standard Edition: 0 or 1
      • Enterprise Edition:
        • 0 to 90 for permanent databases
        • 0 or 1 for transient databases
  • FROM_DATABASE: Specify a database to create a clone from.
    • Configuration key: from_database
    • Data Type: String
  • FROM_SHARE: Specify a provider and a share in this map to create a database from a share.
    • Configuration key: from_share
    • Data Type: Object. See here for definition of from_share.
  • COMMENT: Specifies a comment for the database.
    • Configuration key: comment
    • Data Type: String
  • SCHEMAS: List of schemas to be managed for the database.
    • Configuration key: schemas
    • Data Type: List of Schema Definitions
  • NAMESPACING: Specify whether Prefix or Suffix or both are to be added to Database Name[Doesn't apply to Default Database].
    • Configuration key: namespacing
    • Data Type: String
    • Possible Values:
      • none
      • prefix
      • suffix
      • both(Default)
  • ENVIRONMENT: Specify the environment in which the Database is managed. Regex can be provided as well.
    • Configuration key: environment
    • Data Type: String
  • MANAGE_MODE: Configures what properties to manage for the Database.
    • 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 Database.
    • Configuration key: grants
    • Data Type: Map
  • SCHEMA_GRANTS: List of Privileges and Roles to which privileges are granted to for all Schemas in the current Database.
    • Configuration key: schema_grants
    • Data Type: Map
  • TABLE_GRANTS: List of Privileges and Roles to which privileges are granted to for all Tables in the current Database.
    • Configuration key: table_garnts
    • Data Type: Map
  • SHARES: List of Privileges and Shares to which privileges are granted to on the current Database.
    • Configuration key: shares
    • Data Type: Map

Basic syntax#

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

FROM_SHARE Parameter#

User can specify provider account and share name to create database from a share. It accepts following parameters:

  • PROVIDER: Name of the account providing the share.
    • REQUIRED
    • Configuration Key: provider
    • Data Type: String
  • SHARE: Name of the share from which to create the database.
    • REQUIRED
    • Configuration Key: share
    • Data Type: String

Example#

from_share:  provider: ab678XX  share: TEST_SHARE

If the Snowflake account is missing the SNOWFLAKE_SAMPLE_DATA database(Created by Snowflake), it can be created using the following configuration:

databases:    SNOWFLAKE_SAMPLE_DATA:      namespacing: none      from_share:        provider: SFC_SAMPLES        share: SAMPLE_DATA

Supported Database Grants to Roles#

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

  • ALL PRIVILEGES
  • MODIFY
  • MONITOR
  • USAGE
  • CREATE SCHEMA
  • IMPORTED PRIVILEGES
  • OWNERSHIP

For supported Schema Grants see Grants section in Schema

For supported Table Grants see Grants section in Table

Supported Database Grants to Shares#

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

  • USAGE
  • REFERENCE_USAGE

Examples#

databases:  SALES_RECORD:    comment: "product sales record"    data_retention_time_in_days: 0    from_database: "product_sales_record"    namespacing: suffix    environment: PROD    schemas:      SALES:        comment: "SALES"    grants:      MODIFY:        - HR_ROLE        - ACCOUNTADMIN      IMPORTED PRIVILEGES:        - DEV_ROLE        - ACCOUNTADMIN    shares:      USAGE:        - DEV_SHARE    schema_grants:      CREATE TABLE:        - ACCOUNTADMIN    table_grants:      UPDATE:        - HR_ROLE        - ACCOUNTADMIN
Last updated on