Categories:: Table, View, & Sequence DDL

ALTER VIEW¶

Modifies the properties for an existing view. Currently the only supported operations are:

Renaming a view.
Converting to (or reverting from) a secure view.
Adding, overwriting, removing a comment for a view.

Note that you cannot use this command to change the definition for a view. To change the view definition, you must drop the view and then recreate it.

See also:: CREATE VIEW , DROP VIEW , SHOW VIEWS , DESCRIBE VIEW

In this Topic:

Syntax
Parameters
Usage Notes
Examples

Syntax¶

ALTER VIEW [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER VIEW [ IF EXISTS ] <name> SET COMMENT = '<string_literal>'

ALTER VIEW [ IF EXISTS ] <name> UNSET COMMENT

ALTER VIEW <name> SET SECURE

ALTER VIEW <name> UNSET SECURE

ALTER VIEW [ IF EXISTS ] <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER VIEW [ IF EXISTS ] <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

ALTER VIEW [ IF EXISTS ] <name>
  ADD ROW ACCESS POLICY <policy_name> ON (<col_name> [ , ... ])

ALTER VIEW [ IF EXISTS ] <name>
  DROP ROW ACCESS POLICY <policy_name>

ALTER VIEW [ IF EXISTS ] <name>
  ADD ROW ACCESS POLICY <policy_name> ON (<col_name> [ , ... ])
  , DROP ROW ACCESS POLICY <policy_name>

ALTER VIEW [ IF EXISTS ] <name> DROP ALL ROW ACCESS POLICIES

ALTER VIEW <name> { ALTER | MODIFY } [ COLUMN ] <col_name> SET MASKING POLICY <policy_name> [ USING ( <col_name> , cond_col_1 , ... ) ]
                                                                                            [ FORCE ]

ALTER VIEW <name> { ALTER | MODIFY } [ COLUMN ] <col_name> UNSET MASKING POLICY

ALTER VIEW <name> { ALTER | MODIFY } [ COLUMN ] <col_name> SET TAG <tag_name> = '<string_literal>' [ , <tag_name> = '<string_literal>' ... ]

ALTER VIEW <name> { ALTER | MODIFY } COLUMN <col_name> UNSET TAG <tag_name> [ , <tag_name> ... ]

Parameters¶

name

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

RENAME TO new_name

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

For more details, see Identifier Requirements.

You can move the object to a different database and/or schema while optionally renaming the object. To do so, specify a qualified new_name value that includes the new database and/or schema name in the form db_name.schema_name.object_name or schema_name.object_name, respectively.

Note

The destination database and/or schema must already exist. In addition, an object with the same name cannot already exist in the new location; otherwise, the statement returns an error.
Moving an object to a managed access schema is prohibited unless the object owner (i.e. the role that has the OWNERSHIP privilege on the object) also owns the target schema.

When an object is renamed, other objects that reference it must be updated with the new name.

SET ...

Specifies the property to set for the view:

SECURE: Specifies a view as secure.

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256. The maximum number of unique tags that can be set on an object is 20. For more information, see Tag Quotas for Objects/Columns.

A single ALTER statement can set or unset a maximum of 5 tags on an object.

COMMENT = 'string_literal'

Adds a comment or overwrites an existing comment for the view.

Note

You must set each view property individually.

UNSET ...

Specifies the property to unset for the view, which resets it to the default:

SECURE
TAG TAG tag_name = 'tag_value' [, TAG tag_name = 'tag_value' ... ]
COMMENT

When resetting a property, specify only the name; specifying a value for the property will return an error.

Note

You must reset each view property individually.

policy_name

Identifier for the row access policy; must be unique for your schema.

ADD ROW ACCESS POLICY <policy_name> ON (<col_name> [ , … ])

Adds a row access policy to a view.

At least one column name must be specified. Additional columns can be specified with a comma separating each column name.

DROP ROW ACCESS POLICY <policy_name>

Drops a row access policy from a view.

DROP ALL ROW ACCESS POLICIES

Drops all row access policy associations from a view.

This expression is helpful when a row access policy is dropped from a schema before dropping the policy from a view.

ALTER | MODIFY <col_name> SET MASKING POLICY <policy_name>

Applies a Column-level Security masking policy to a view column.

USING ( col_name , cond_col_1 ... )

Specifies the arguments to pass into the conditional masking policy SQL expression.

The first column in the list specifies the column for the policy conditions to mask or tokenize the data and must match the column to which the masking policy is set.

The additional columns specify the columns to evaluate to determine whether to mask or tokenize the data in each row of the query result when a query is made on the first column.

If the USING clause is omitted, Snowflake treats the conditional masking policy as a normal masking policy.

FORCE

Replaces a masking policy that is currently set on a column with a different masking policy in a single statement.

If a masking policy is not currently set on the column, specifying this keyword has no effect.

For details, see: Replace a Masking Policy on a Column.

ALTER | MODIFY <col_name> UNSET MASKING POLICY

Removes a Column-level Security masking policy from a view column.

Usage Notes¶

Moving a view to a managed access schema (using the ALTER VIEW … RENAME TO syntax) is prohibited unless the view owner (i.e. the role that has the OWNERSHIP privilege on the view) also owns the target schema.

For masking policies:
- The USING clause and the FORCE keyword are both optional; neither are required to set a masking policy on a column. The USING clause and the FORCE keyword can be used separately or together. For details, see:
  - Apply a Conditional Masking Policy on a Column
  - Replace a Masking Policy on a Column
- A single masking policy that uses conditional columns can be applied to multiple tables provided that the column structure of the table matches the columns specified in the policy.
- When modifying one or more table columns with a masking policy or the table itself with a row access policy, use the POLICY_CONTEXT function to simulate a query on the column(s) protected by a masking policy and the table protected by a row access policy.
A single masking policy that uses conditional columns can be applied to multiple views provided that the column structure of the view matches the columns specified in the policy.

For row access policies:
- Snowflake supports adding and dropping row access policies in a single SQL statement.
  
  For example, to replace a row access policy that is already set on a table with a different policy, drop the row access policy first and then add the new row access policy.
- For a given resource (i.e. table or view), to ADD or DROP a row access policy you must have either the APPLY ROW ACCESS POLICY privilege on the schema, or the OWNERSHIP privilege on the resource and the APPLY privilege on the row access policy resource.
- A table or view can only be protected by one row access policy at a time. Adding a policy fails if the policy body refers to a table or view column that is protected by a row access policy or the column protected by a masking policy.
  
  Similarly, adding a masking policy to a table column fails if the masking policy body refers to a table that is protected by a row access policy or another masking policy.
- Row access policies cannot be applied to system views or table functions.
- Similar to other DROP <object> operations, Snowflake returns an error if attempting to drop a row access policy from a resource that does not have a row access policy added to it.
- If an object has both a row access policy and one or more Column-level Security masking policies, the row access policy is evaluated first.
Regarding metadata:

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

Examples¶

Rename view view1 to view2:

ALTER VIEW view1 RENAME TO view2;

Convert a view to a secure view:

ALTER VIEW view1 SET SECURE;

Revert a secure view to a regular view:

ALTER VIEW view1 UNSET SECURE;

Apply a Column-level Security masking policy to a view column:

-- single column

ALTER VIEW user_info_v MODIFY COLUMN ssn_number SET MASKING POLICY ssn_mask_v;

-- multiple columns

ALTER VIEW user_info_v MODIFY
    COLUMN ssn_number SET MASKING POLICY ssn_mask_v
  , COLUMN dob SET MASKING POLICY dob_mask_v
;

Unset a Column-level Security masking policy from a view column:

-- single column

ALTER VIEW user_info_v modify column ssn_number unset masking policy;

-- multiple columns

ALTER VIEW user_info_v modify
    column ssn_number unset masking policy
  , column dob unset masking policy
;

The following example adds a row access policy on a view. After setting the policies, you can verify by checking the information schema.

alter view v1
  add row access policy rap_v1 on (empl_id);

The following example drops a row access policy from a view. Verify the policies were dropped by querying the information schema.

alter view v1
  drop row access policy rap_v1;

The following example shows how to combine adding and dropping row access policies in a single SQL statement for a view. Verify the results by checking the information schema.

alter view v1
  drop row access policy rap_v1_version_1,
  add row access policy rap_v1_version_2 on (empl_id);