- Categories:
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:
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 ;