Skip to content

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
  • 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;
  • cannot be applied to a tag

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);