ALTER PRIVACY POLICY¶

Modifies the properties of an existing privacy policy.

See also:: CREATE PRIVACY POLICY , DESCRIBE PRIVACY POLICY , DROP PRIVACY POLICY , SHOW PRIVACY POLICIES

Syntax¶

ALTER PRIVACY POLICY [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER PRIVACY POLICY [ IF EXISTS ] <name> SET BODY -> <expression>

ALTER PRIVACY POLICY <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER PRIVACY POLICY <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

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

ALTER PRIVACY POLICY [ IF EXISTS ] <name> UNSET COMMENT

Copy

Parameters¶

name

Specifies the identifier for the privacy policy 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.

For more information, see Identifier requirements.

RENAME TO new_name

Specifies the new identifier for the privacy policy; must be unique for your schema. The new identifier cannot be used if the identifier is already in place for a different privacy policy.

For more information, 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 (that is, the role that has the OWNERSHIP privilege on the object) also owns the target schema.

SET ...

Specifies one (or more) properties to set for the privacy policy:

BODY -> expression

Specifies a new body for the policy.

The SQL expression of the body calls two internal functions to control the return value of the policy: NO_PRIVACY_POLICY and PRIVACY_BUDGET. When a query is executed against a table that has been assigned the policy, Snowflake evaluates the conditions of the body to call the appropriate function and return a value. This return value determines which privacy budget, if any, is associated with the query against the privacy-protected table.

The expression can use context functions such as CURRENT_ROLE or INVOKER_ROLE to associate a user or group of users with a privacy budget.

If you use a CASE block in the body’s expression, it must include an ELSE statement that calls either NO_PRIVACY_POLICY or PRIVACY_BUDGET. Every user must either be associated with a privacy budget or have unrestricted access to the privacy-protected table. If a user should not have any access to a privacy-protected table or view, revoke SELECT privileges rather than trying to define this in the privacy policy.

NO_PRIVACY_POLICY

Use the body’s expression to call the NO_PRIVACY_POLICY function when you want a query to have unrestricted access to the table or view to which the privacy policy is assigned.

PRIVACY_BUDGET

Use the body’s expression to call the PRIVACY_BUDGET function when you want to return a privacy budget from the policy. The expression can contain conditions that allow the policy to return different privacy budgets for different queries based on factors like the user who is executing the query. The signature of the PRIVACY_BUDGET function is:

PRIVACY_BUDGET(BUDGET_NAME=> '<expression>')
Copy
Where expression resolves to the name of a privacy budget. Snowflake creates the privacy budget automatically when its name is specified in the body of the privacy policy.

In cross-account collaboration, privacy budgets are automatically namespaced by the account identifier of the consumer account, which prevents two different consumer accounts from sharing the same privacy budget even if the name of the privacy budget is the same. Using the CURRENT_ACCOUNT function to concatenate the name of the account with the name of the privacy budget can help distinguish between privacy budgets. For example, you could call the function as follows: PRIVACY_BUDGET(BUDGET_NAME => 'external_budget.' || CURRENT_ACCOUNT()).

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.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

COMMENT = 'string_literal'

Adds a comment or overwrites the existing comment for the privacy policy.

Default: No value

UNSET ...

Specifies one or more properties and/or parameters to unset, by resetting them to their defaults, for the privacy policy:

TAG tag_name [ , tag_name ... ]
COMMENT

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

Access control requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege	Object	Notes
OWNERSHIP	Privacy policy	OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

If you want to update an existing privacy policy and need to see the current definition of the policy, run the DESCRIBE PRIVACY POLICY command. You can also use the GET_DDL function to obtain the full definition of the privacy policy, including its body.
Moving a privacy policy to a managed access schema (using the ALTER PRIVACY POLICY … RENAME TO syntax) is prohibited unless the privacy policy owner (that is, the role that has the OWNERSHIP privilege on the privacy policy) also owns the target schema.
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¶

Modify the body of a privacy policy my_priv_policy so it always returns a budget named analysts:

ALTER PRIVACY POLICY my_priv_policy SET BODY ->
  PRIVACY_BUDGET(BUDGET_NAME => 'analysts');

Copy