Skip to main content
Last updated on

CREATE POLICY

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 16.4 and above check marked yes Unity Catalog only

Creates a named policy on a securable. Policies can be row filters or column masks applied to catalogs, schemas, or tables. The policy name is scoped to the securable the policy is defined on.

To run this statement, you must have the MANAGE privilege on the target securable or be its owner.

Syntax​

CREATE [ OR REPLACE ] POLICY policy_name
ON { CATALOG catalog_name | SCHEMA schema_name | TABLE table_name }
[ COMMENT description ]
{ row_filter_body | column_mask_body }

row_filter_body
  ROW FILTER function_name
  TO principal [, ...]
  [ EXCEPT principal [, ...] ]
  FOR TABLES
  [ WHEN condition ]
  [ MATCH COLUMNS condition [ [ AS ] alias ] [, ...] ]
  [ USING COLUMNS ( function_arg [, ...] ) ]

column_mask_body
  COLUMN MASK function_name
  TO principal [, ...]
  [ EXCEPT principal [, ...] ]
  FOR TABLES
  [ WHEN condition ]
  [ MATCH COLUMNS condition [ [ AS ] alias ] [, ...] ]
  ON COLUMN alias
  [ USING COLUMNS ( function_arg [, ...] ) ]

Parameters​

  • policy_name

    Name of the policy. The name is scoped to the securable the policy is defined on. If a policy with the same name already exists and OR REPLACE is not specified, Databricks raises POLICY_ALREADY_EXISTS.

  • catalog_name

    The name of the catalog on which the policy is defined. If the securable type is not supported for policies, Databricks raises POLICY_ON_SECURABLE_TYPE_NOT_SUPPORTED.

  • schema_name

    The name of the schema on which the policy is defined.

  • table_name

    The name of the table on which the policy is defined.

  • description

    An optional string comment for the policy.

  • function_name

    The name of the UDF used for the row filter or column mask.

  • principal

    A user, group, or service principal name. Multiple principals can be listed after TO. Principals listed after EXCEPT are excluded from the policy.

  • condition

    For WHEN: a boolean expression that matches securables (for example, table-level tag conditions). For MATCH COLUMNS: a boolean expression that matches columns (for example, hasTag('tag_name')). Conditions are evaluated by the control plane on securable metadata. The only user-facing functions supported in conditions are hasTag() and hasTagValue(). If the condition is invalid, Databricks raises UC_INVALID_POLICY_CONDITION.

  • alias

    In MATCH COLUMNS, an optional identifier for the matched column. The alias can be referenced in USING COLUMNS (row filter) or in ON COLUMN and USING COLUMNS (column mask).

  • function_arg

    In USING COLUMNS, each argument is either a constant expression or an alias from MATCH COLUMNS. The arguments are passed to the policy function in order. If the options do not match the policy type, Databricks raises UC_POLICY_TYPE_OPTIONS_MISMATCH.

Examples​

The following example creates a column mask policy:

SQL
> CREATE FUNCTION ssn_to_last_nr (ssn STRING, nr INT) RETURNS STRING
    RETURN right(ssn, nr);

> CREATE POLICY ssn_mask
    ON CATALOG employees
    COLUMN MASK ssn_to_last_nr
    TO 'All Users' EXCEPT 'HR admins'
    FOR TABLES
    MATCH COLUMNS hasTag('ssn') AS ssn
    ON COLUMN ssn
    USING COLUMNS (4);

The following example creates a row filter policy:

SQL
> CREATE FUNCTION non_eu_region (geo_region STRING) RETURNS BOOLEAN
    RETURN geo_region <> 'eu';

> CREATE POLICY hide_eu_customers
    ON SCHEMA prod.customers
    COMMENT 'Hide European customers from sensitive tables'
    ROW FILTER non_eu_region
    TO analysts
    FOR TABLES
    WHEN hasTagValue('sensitivity', 'high')
    MATCH COLUMNS hasTag('geo_region') AS region
    USING COLUMNS (region);