16Access & ABAC

GOVERNED TAG    pii = email | phone | address        account level, values fixed
      |
      |  SET TAG ON COLUMN dwh_prod.gold.dim_customer.email pii = email
      v
COLUMN TAG      one column, one governed value
      |
      |  MATCH COLUMNS has_tag('pii') AS pii_col
      v
POLICY          ON SCHEMA dwh_prod.gold   TO ... EXCEPT ...   evaluated per query
      |
      |  COLUMN MASK dwh_prod.gold.mask_pii
      v
UDF             mask_pii(value STRING) RETURNS STRING

CREATE GOVERNED TAG pii
  DESCRIPTION 'Kind of personal data this column carries'
  VALUES ('email', 'phone', 'address');

CREATE GOVERNED TAG row_filter
  DESCRIPTION 'Row-level access scheme this table is subject to'
  VALUES ('territory');

CREATE GOVERNED TAG row_scope
  DESCRIPTION 'Column a row-filter policy scopes on'
  VALUES ('country');

SET TAG ON COLUMN dwh_prod.gold.dim_customer.email   pii = email;
SET TAG ON COLUMN dwh_prod.gold.dim_customer.phone   pii = phone;
SET TAG ON COLUMN dwh_prod.gold.dim_customer.street  pii = address;

-- the row-filter pair: which table is filtered, and on which column
SET TAG ON TABLE  dwh_prod.gold.dim_customer          row_filter = territory;
SET TAG ON COLUMN dwh_prod.gold.dim_customer.country  row_scope  = country;

-- and the thing you will actually run in a review
SELECT table_name, column_name, tag_name, tag_value
FROM   dwh_prod.information_schema.column_tags
WHERE  schema_name = 'gold'
ORDER  BY table_name, column_name;

CREATE OR REPLACE FUNCTION dwh_prod.gold.mask_pii(value STRING)
RETURNS STRING
COMMENT 'Redact personal data. NULL stays NULL so counts do not move.'
RETURN CASE WHEN value IS NULL THEN NULL ELSE '***' END;

CREATE OR REPLACE POLICY mask_customer_pii
ON SCHEMA dwh_prod.gold
COMMENT 'Redact every column tagged pii for all but the named readers'
COLUMN MASK dwh_prod.gold.mask_pii
TO `account users`
EXCEPT `dwh_pii_readers`, `dwh_prod_pipelines`
FOR TABLES
MATCH COLUMNS has_tag('pii') AS pii_col
ON COLUMN pii_col;

SELECT customer_id, customer_name, email, city, country
FROM   dwh_prod.gold.dim_customer
WHERE  _tech_is_current;

-- customer_id | customer_name    | email | city      | country
-- C-100482    | Nordbau Handel   | ***   | Hannover  | DE
-- C-100483    | Alpin Technik    | ***   | Innsbruck | AT

SHOW EFFECTIVE POLICIES ON TABLE dwh_prod.gold.dim_customer;
-- policy_name         | policy_type  | function                    | principals

CREATE OR REPLACE FUNCTION dwh_prod.gold.in_sales_territory(country STRING)
RETURNS BOOLEAN
COMMENT 'True if the caller belongs to the territory group for this country'
RETURN is_account_group_member(concat('dwh_territory_', lower(country)));

CREATE OR REPLACE POLICY territory_rows
ON SCHEMA dwh_prod.gold
COMMENT 'Sales roles see only the countries their territory group covers'
ROW FILTER dwh_prod.gold.in_sales_territory
TO `dwh_sales`
EXCEPT `dwh_prod_pipelines`
FOR TABLES
WHEN has_tag_value('row_filter', 'territory')
MATCH COLUMNS has_tag_value('row_scope', 'country') AS country_col
USING COLUMNS (country_col);

GRANT USE CATALOG ON CATALOG dwh_prod            TO `dwh_analysts`;
GRANT USE SCHEMA  ON SCHEMA  dwh_prod.gold       TO `dwh_analysts`;
GRANT SELECT      ON SCHEMA  dwh_prod.gold       TO `dwh_analysts`;
GRANT EXECUTE     ON FUNCTION dwh_prod.gold.mask_pii TO `dwh_analysts`;

-- gold only. No grant on silver, no grant on bronze.
-- Territory scoping is membership of dwh_territory_*, not a grant.

-- 1. drive the inventory loop; run SHOW per row, concatenate client-side.
--    Materialized views and streaming tables carry policies too, so filtering to
--    MANAGED alone drops exactly the downstream copies the exemptions leak into.
SELECT table_catalog, table_schema, table_name, table_type
FROM   dwh_prod.information_schema.tables
WHERE  table_schema = 'gold'
  AND  table_type IN ('MANAGED', 'MATERIALIZED_VIEW', 'STREAMING_TABLE');

-- 2. candidate personal columns carrying no pii tag. Zero rows is a pass.
--    The list is the vocabulary, not dim_customer's column list: it fires when
--    any gold table grows an email or a street. customer_name is deliberately
--    absent -- in this B2B model it is the company name, not personal data.
SELECT c.table_name, c.column_name
FROM   dwh_prod.information_schema.columns c
LEFT   JOIN dwh_prod.information_schema.column_tags t
       ON  t.schema_name = c.table_schema
       AND t.table_name  = c.table_name
       AND t.column_name = c.column_name
       AND t.tag_name    = 'pii'
WHERE  c.table_schema = 'gold'
  AND  c.column_name IN ('email', 'phone', 'street')
  AND  t.tag_name IS NULL;

-- 3. every policy change in the last 30 days, with the editor
SELECT event_time,
       user_identity.email          AS actor,
       action_name,
       request_params['name']       AS policy_name
FROM   system.access.audit
WHERE  service_name = 'unityCatalog'
  AND  action_name IN ('createPolicy', 'deletePolicy')
  AND  event_date >= current_date() - INTERVAL 30 DAYS
ORDER  BY event_time DESC;

-- One row per metric, so the output is a posture SNAPSHOT you can trend.
-- These are the same questions the Hub's Data page consolidates; computing
-- them yourself is what makes the Hub an upgrade rather than a migration.

-- 1. classification coverage: candidate personal columns carrying a pii tag.
SELECT 'pii_coverage_pct' AS metric,
       ROUND(100.0 * count_if(t.tag_name IS NOT NULL) / nullif(count(*), 0), 1) AS value
FROM   dwh_prod.information_schema.columns c
LEFT   JOIN dwh_prod.information_schema.column_tags t
       ON  t.schema_name = c.table_schema
       AND t.table_name  = c.table_name
       AND t.column_name = c.column_name
       AND t.tag_name    = 'pii'
WHERE  c.table_schema = 'gold'
  AND  c.column_name IN ('email', 'phone', 'street')

UNION ALL
-- 2. ownership decay: production objects owned by a human, not a group.
SELECT 'tables_owned_by_individual',
       count(*)
FROM   dwh_prod.information_schema.tables
WHERE  table_schema IN ('silver', 'gold')
  AND  table_owner LIKE '%@%'          -- a group name has no @

UNION ALL
-- 3. discoverability: gold tables shipped with no COMMENT.
SELECT 'gold_tables_without_comment',
       count_if(comment IS NULL OR trim(comment) = '')
FROM   dwh_prod.information_schema.tables
WHERE  table_schema = 'gold'

UNION ALL
-- 4. staleness: gold tables not written in 48h. Freshness is governance too --
--    an abandoned table is a table nobody can vouch for.
SELECT 'gold_tables_stale_48h',
       count_if(last_altered < current_timestamp() - INTERVAL 48 HOURS)
FROM   dwh_prod.information_schema.tables
WHERE  table_schema = 'gold';