Policies

Network Policies

• Can be set at Account, User or at Integration endpoint level (eg SCIM or OAuth)
• most specific (only single) policy wins. User > Account or Integration > Account
• consists of blocked_ip_list > allowed_ip_list CIDR IP ranges
• BP use user level policies for service accounts

Masking Policy

• masks plain-text data in a table or a view at query-time using masking policy
• account level privileges: {create|apply} masking policy;
• policy level privilege: apply on masking policy, allows table/view owners to un/set specific masking policy
• Owners of clones cannot unset a masking policy that was set on the original table
• Masking can be applied to external tables
• Dynamic Data Masking allows conditional (un)masking based on columns other than the one being masked
• query result cache disabled
• can use invoker_role() or is_granted_to_invoker_role(), which generally is the owner of the context
• for stored-procedures, it's owner if sp has owner right, otherwise it's current_role()
• for streams, it's the role that is querying the stream
• for View/UDF/Task it's the owner of the object
• can use invoker_share() to detect if it's being used within the context of a particular share
• note: if invoker_share() is used within a UDF, then it will return NULL because the context is UDF owner, not the share
• simulate results using execute using ... policy_context(...)

Tag based masking policies

• only masking policies, and not row-access policies can be assigned to a tag
• Multiple masking policies, one for each supported type, can be associated with a single tag (example)
• policies can dynamically inquire tag value for the current column using SYSTEM$GET_TAG_ON_CURRENT_COLUMN() (example)

Row Access Policy

• cannot assign to a tag, but can use a tag value dynamically using SYSTEM$GET_TAG_ON_CURRENT_TABLE()

Projection Policy

• Projection policy prevents protected columns from being part of the outermost select clause by returning NULL value instead
• set at a column level
• values can be indirectly inferred, e.g.
• can be used in where
• can be joined to a column in another table that is not protected
• cannot be applied to a tag

Example

CREATE OR REPLACE PROJECTION POLICY mypolicy AS () RETURNS PROJECTION_CONSTRAINT ->
  CASE WHEN CURRENT_ROLE() = 'ANALYST'
      THEN PROJECTION_CONSTRAINT(ALLOW => true)
      ELSE PROJECTION_CONSTRAINT(ALLOW => false)
  END;
ALTER TABLE T1 ALTER COLUMN C1 SET PROJECTION POLICY mypolicy;

Aggregation Policy

• enforces certain columns to be shown only aggregated values.
• applies to a table/view
• defined similar to a function that takes no argument and returns an aggregation_constraint
• aggregation_constraint constructor accepts one parameter min_group_size
• alternatively no_aggregation_constraint constructor is a no-op
• a table can optionally specify an entity key which in conjunction with aggregation policy ensures how many distinct key values must be present in a group v/s number of rows.
• a table can have more than one entity keys
• aggregation policies are deferred if a sub-query has a group by that matches entity key

Example:

CREATE AGGREGATION POLICY my_agg_policy AS () RETURNS AGGREGATION_CONSTRAINT ->
  CASE WHEN CURRENT_ROLE() = 'ADMIN'
      THEN NO_AGGREGATION_CONSTRAINT()
      ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5)
  END;
ALTER TABLE transactions SET AGGREGATION POLICY ap ENTITY KEY (user_id) ENTITY KEY (vendor_id);

Join Policy

• ensures queries specifying a particular table must join it with some other table
• can optionally specify allowed join columns

Example

CREATE JOIN POLICY jp1 AS () RETURNS JOIN_CONSTRAINT -> JOIN_CONSTRAINT(JOIN_REQUIRED => TRUE);
ALTER TABLE join_table SET JOIN POLICY jp1 ALLOWED JOIN KEYS (col1);