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

Syntax¶

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

ALTER VIEW [ IF EXISTS ] <name> SET { SECURE | COMMENT = '<string_literal>' }

ALTER VIEW [ IF EXISTS ] <name> UNSET { SECURE | COMMENT }

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

ALTER VIEW <name> ALTER | MODIFY <col_name> UNSET MASKING POLICY

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.

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.
COMMENT = 'string_literal': Adds a comment or overwrites an existing comment for the view.

Note

You must set each review property individually.

UNSET ...

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

SECURE
COMMENT

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

Note

You must reset each review property individually.

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

Applies a Column-level Security masking policy to a view 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.

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
;