What makes a concept binding

-- The role matrix from chapter 3, checked into the repo as VALUES lists — one
-- per grant LEVEL, because the concept grants at three levels and each level
-- has its own information_schema view.
--
-- Symmetric EXCEPT: one direction finds grants nobody declared, the other
-- finds declarations nobody granted. One direction alone catches half.
--
-- Run this as a principal that can see every grant in the catalog.
-- information_schema is filtered to the objects the caller has privileges on,
-- so a narrowly-granted service principal reads an empty actual set, both
-- EXCEPTs collapse, and the check goes green having compared nothing.
--
-- inherited_from is the column that decides whether this check is usable at
-- all. table_privileges EXPANDS a schema- or catalog-level grant into one row
-- per table, tagged with the ancestor it came from. Chapter 3 grants gold to
-- grp_dwh_analyst as a whole, so comparing every row against a per-object list
-- reports every table in gold as undeclared on the first run — and a check that
-- is red on day one is muted by week two. The choice here is explicit: table
-- rows are filtered to DIRECT grants, and the inherited ones are declared at
-- the level they were actually issued. Databricks documents the column as
-- nullable and reports NONE for a direct grant; coalesce covers both.
--
-- Filtering to direct grants is only safe because the levels above are checked
-- too. Drop either of the other two blocks and a SELECT granted on the whole
-- catalog to grp_all_staff is inherited everywhere, direct nowhere, and
-- invisible to all six EXCEPTs.
--
-- Scoped to gold. silver and bronze get the same blocks with their own lists.
WITH concept_catalog(grantee, securable, privilege_type) AS (
  VALUES
    ('grp_dwh_bi_reader', 'dwh_prod', 'USE CATALOG'),
    ('grp_dwh_analyst',   'dwh_prod', 'USE CATALOG'),
    ('grp_dwh_engineer',  'dwh_prod', 'USE CATALOG')
),
actual_catalog AS (
  SELECT grantee, catalog_name AS securable, privilege_type
  FROM dwh_prod.information_schema.catalog_privileges
  WHERE coalesce(inherited_from, 'NONE') = 'NONE'
    AND grantee <> 'grp_dwh_owner'      -- ownership grants are implicit
),
-- Granted on the schema: this is what "gold as a whole" means, spelled out.
-- USE SCHEMA belongs here too, or the first run reports three real grants as
-- undeclared and the team learns to ignore the output.
concept_schema(grantee, securable, privilege_type) AS (
  VALUES
    ('grp_dwh_bi_reader', 'gold', 'USE SCHEMA'),
    ('grp_dwh_analyst',   'gold', 'USE SCHEMA'),
    ('grp_dwh_analyst',   'gold', 'SELECT'),
    ('grp_dwh_engineer',  'gold', 'USE SCHEMA'),
    ('grp_dwh_engineer',  'gold', 'SELECT'),
    ('grp_dwh_engineer',  'gold', 'MODIFY')
),
actual_schema AS (
  SELECT grantee, schema_name AS securable, privilege_type
  FROM dwh_prod.information_schema.schema_privileges
  WHERE schema_name = 'gold'
    AND coalesce(inherited_from, 'NONE') = 'NONE'
    AND grantee <> 'grp_dwh_owner'
),
-- Granted per object: the declared exceptions, and nothing else.
-- grp_dwh_bi_reader is one — it may reach two published objects, never the
-- schema, so it appears here rather than in the block above.
concept_table(grantee, securable, privilege_type) AS (
  VALUES
    ('grp_dwh_bi_reader', 'mv_sales',          'SELECT'),
    ('grp_dwh_bi_reader', 'agg_revenue_month', 'SELECT')
),
actual_table AS (
  SELECT grantee, table_name AS securable, privilege_type
  FROM dwh_prod.information_schema.table_privileges
  WHERE table_schema = 'gold'
    AND coalesce(inherited_from, 'NONE') = 'NONE'
    AND grantee <> 'grp_dwh_owner'
)
SELECT 'undeclared' AS problem, 'catalog' AS grant_level, *
FROM (SELECT * FROM actual_catalog  EXCEPT SELECT * FROM concept_catalog)
UNION ALL
SELECT 'missing'    AS problem, 'catalog' AS grant_level, *
FROM (SELECT * FROM concept_catalog EXCEPT SELECT * FROM actual_catalog)
UNION ALL
SELECT 'undeclared' AS problem, 'schema'  AS grant_level, *
FROM (SELECT * FROM actual_schema   EXCEPT SELECT * FROM concept_schema)
UNION ALL
SELECT 'missing'    AS problem, 'schema'  AS grant_level, *
FROM (SELECT * FROM concept_schema  EXCEPT SELECT * FROM actual_schema)
UNION ALL
SELECT 'undeclared' AS problem, 'table'   AS grant_level, *
FROM (SELECT * FROM actual_table    EXCEPT SELECT * FROM concept_table)
UNION ALL
SELECT 'missing'    AS problem, 'table'   AS grant_level, *
FROM (SELECT * FROM concept_table   EXCEPT SELECT * FROM actual_table)

---
title:         Berechtigungskonzept - dwh_prod
owner:         A. Weber (Data Owner, Vertrieb)   # a named person, never a group
authors:       lead engineer + platform
last_reviewed: 2026-07-14
review_cycle:  90d
status:        active                      # draft | active | superseded
proof:         tests/access/grants_match_concept.sql
applies_to:    dwh_prod.gold, dwh_prod.silver
---

import re
from datetime import date, timedelta
from pathlib import Path

import pytest
import yaml

CONCEPTS = sorted(Path("docs/concepts").glob("*.md"))


def test_concepts_are_found() -> None:
    """An empty parametrize list reports as skipped, not failed. Without this
    line the whole check passes vacuously the day the path or the CWD moves."""
    assert CONCEPTS, "docs/concepts/ is empty - nothing was checked"


@pytest.mark.parametrize("path", CONCEPTS, ids=lambda p: p.name)
def test_header_is_current(path: Path) -> None:
    front = re.match(r"^---\n(.*?)\n---", path.read_text(encoding="utf-8"), re.S)
    assert front, f"{path.name}: no header block"
    head = yaml.safe_load(front.group(1))

    if head["status"] != "active":
        return

    cycle = timedelta(days=int(head["review_cycle"].rstrip("d")))
    assert head["last_reviewed"] + cycle >= date.today(), (
        f"{path.name}: last reviewed {head['last_reviewed']}, cycle {head['review_cycle']}"
    )
    assert Path(head["proof"]).exists(), f"{path.name}: proof {head['proof']} is missing"