CREATE PRIVACY POLICY¶
Creates a new privacy policy or replaces an existing privacy policy.
- See also:
ALTER PRIVACY POLICY , DESCRIBE PRIVACY POLICY , DROP PRIVACY POLICY , SHOW PRIVACY POLICIES
Syntax¶
CREATE [ OR REPLACE ] PRIVACY POLICY [ IF NOT EXISTS ] <name>
AS ( ) RETURNS PRIVACY_BUDGET -> <body>
[ COMMENT = '<string_literal>' ]
Required parameters¶
name
String that specifies the identifier (that is, name) for the privacy policy; must be unique for the schema in which the privacy policy is created.
In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example,
"My object"
). Identifiers enclosed in double quotes are also case-sensitive.For more information, see Identifier requirements.
body
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 thePRIVACY_BUDGET
function is:PRIVACY_BUDGET(BUDGET_NAME=> '<expression>')
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())
.
Optional parameters¶
COMMENT = 'string_literal'
Specifies a comment for the privacy policy.
Default: No value
Access control requirements¶
A role used to execute this SQL command must have the following privileges at a minimum:
Privilege |
Object |
Notes |
---|---|---|
CREATE PRIVACY POLICY |
Schema |
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¶
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.
CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.
Examples¶
Create a privacy policy that always returns a budget named analysts
:
CREATE PRIVACY POLICY my_priv_policy AS ( ) RETURNS PRIVACY_BUDGET -> PRIVACY_BUDGET(BUDGET_NAME=> 'analysts');
Create a privacy policy that will give admin
unrestricted access to the privacy-protected table while associating all other users with
the privacy budget analysts
:
CREATE PRIVACY POLICY my_priv_policy AS () RETURNS PRIVACY_BUDGET -> CASE WHEN CURRENT_USER() = 'ADMIN' THEN NO_PRIVACY_POLICY() ELSE PRIVACY_BUDGET(BUDGET_NAME => 'analysts') END;