Categories:
Database, Schema, & Share DDL

ALTER SCHEMA

Modifies the properties for an existing schema, including renaming the schema or swapping it with another schema, and changing the Time Travel data retention period (if you are using Snowflake Enterprise Edition or higher).

See also:
CREATE SCHEMA , SHOW SCHEMAS

Syntax

ALTER SCHEMA [ IF EXISTS ] <name> RENAME TO <new_schema_name>

ALTER SCHEMA [ IF EXISTS ] <name> SWAP WITH <target_schema_name>

ALTER SCHEMA [ IF EXISTS ] <name> SET { [ DATA_RETENTION_TIME_IN_DAYS = <num> ] [ COMMENT = '<string_literal>' ] }

ALTER SCHEMA [ IF EXISTS ] <name> UNSET { DATA_RETENTION_TIME_IN_DAYS | COMMENT  } [ , ... ]

ALTER SCHEMA [ IF EXISTS ] <name> { ENABLE | DISABLE } MANAGED ACCESS

Parameters

name
Specifies the identifier for the schema to alter. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.
RENAME TO new_schema_name

Specifies the new identifier for the schema; must be unique for the database.

For more details, see Identifier Syntax.

SWAP WITH target_schema_name
Swaps all objects (tables, views, etc.) and metadata, including identifiers, between the two specified schemas. Also swaps all access control privileges granted on the schemas and objects they contain. SWAP WITH essentially performs a rename of both schemas as a single operation.
SET ...

Specifies one (or more) properties to set for the schema (separated by blank spaces, commas, or new lines):

DATA_RETENTION_TIME_IN_DAYS = num

Specifies the number of days for which Time Travel actions (CLONE and UNDROP) can be performed on the schema, as well as specifying the default Time Travel retention time for all tables created in the schema.

The value you can specify depends on the Snowflake Edition you are using:

  • Standard Edition: 0 or 1
  • Enterprise Edition (or higher): 0 to 90
COMMENT = 'string_literal'
Adds a comment or overwrites an existing comment for the schema.
UNSET ...

Specifies one (or more) properties and/or parameters to unset for the database, which resets them to the defaults:

  • DATA_RETENTION_TIME_IN_DAYS
  • COMMENT

You can reset multiple properties/parameters with a single ALTER statement; however, each property/parameter must be separated by a comma. When resetting a property/parameter, specify only the name; specifying a value for the property will return an error.

ENABLE | DISABLE MANAGED ACCESS

Enable managed access for a schema, or disable to convert a managed access schema to a regular schema. Managed access schemas centralize privilege management with the schema owner.

In regular schemas, the owner of an object (i.e. the role that has the OWNERSHIP privilege on the object) can grant further privileges on their objects to other roles. In managed access schemas, the schema owner manages all privilege grants, including future grants, on objects in the schema. Object owners retain the OWNERSHIP privileges on the objects; however, only the schema owner can manage privilege grants on the objects.

Usage Notes

  • To rename a schema, the role used to perform the operation must have the CREATE SCHEMA privilege on the database for the schema and OWNERSHIP privileges on the schema.

  • To swap two schemas, the role used to perform the operation must have OWNERSHIP privileges on both schemas.

  • To convert a regular schema to a managed access schema:

    • The schema owner must also have the global MANAGE GRANTS privilege.
    • All open future grants must be revoked using REVOKE <privileges> … FROM ROLE with the FUTURE keyword.

    After a regular schema is converted to a managed access schema, all privileges previously granted on individual objects are retained; however, the object owners cannot grant further privileges on those objects.

  • To convert a managed access schema to a regular schema, the schema owner must also have the global MANAGE GRANTS privilege only if the current schema has future privilege grants defined.

Examples

Rename schema schema1 to schema2:

ALTER SCHEMA IF EXISTS schema1 RENAME TO schema2;

Convert a regular schema to a managed access schema:

ALTER SCHEMA schema2 ENABLE MANAGED ACCESS;