Who can see what: access control basics

Description

Until this lesson, no table in this guide — orders_s04, dim_product, dim_store — had a column identifying a real person. This lesson introduces customers, Kiosko's delivery app's contact directory: six fixed customers, each with a customer_id, a customer_phone, and a customer_email. With that new table comes a question none of the six earlier modules' data quality tools needed to answer: once the data is already inside, correct or not, who can see it? This lesson builds ACCESS_POLICY — a dictionary that says, by business role, which columns each person can see — and apply_access_policy(), the function that really applies it, over two different Kiosko tables.

Connection to the module. Lessons 2 through 4 closed this module's first half: what to do when a check fails. This lesson opens the second half — data governance — with the first of its three pieces: who can see which column. Lessons 6 and 7 complete governance with deterministic PII masking and a minimal catalog.

An analogy: the passport, and who needs to see each page

Think of a physical passport. Not everyone who reviews it needs to see exactly the same information. An immigration officer needs to see the photo, the full name, and the passport number, to confirm the person in front of them is who they say they are. A duty-free shop cashier, who only needs to confirm you bought something outside your country of residence, doesn't need the complete passport number — the name and nationality are enough. And someone simply sitting next to you in the airport waiting room has no legitimate reason at all to see a single page of your passport. The document is exactly the same in all three cases — what changes is who's looking at it, and for what legitimate purpose.

ACCESS_POLICY is, precisely, that same idea applied to Kiosko's tables: the customers table is always the same, but what a data analyst, someone from finance, and someone from customer support need to see from it — and have a legitimate reason to see — isn't the same. This lesson builds that difference, column by column, role by role.

The material: the customers table, new in this guide

customers doesn't extend the shared orders/fact_orders schema you already know from this ecosystem's eight earlier guides — it's a completely new table, the contact directory Kiosko's delivery app always had implicitly, and this guide is the first to need explicitly. Six customers, three columns, fixed data:

# setup_customers.py
import duckdb

con = duckdb.connect("kiosko.duckdb")
con.execute("""
    CREATE OR REPLACE TABLE customers (
        customer_id VARCHAR, customer_phone VARCHAR, customer_email VARCHAR
    )
""")
con.execute("""
    INSERT INTO customers VALUES
        ('C001', '+57-300-555-0101', 'ana.torres@example.com'),
        ('C002', '+51-999-555-0102', 'luis.rojas@example.com'),
        ('C003', '+56-9-5550-0103',  'maria.fuentes@example.com'),
        ('C004', '+52-55-5550-0104', 'jorge.medina@example.com'),
        ('C005', '+57-300-555-0105', 'carla.suarez@example.com'),
        ('C006', '+51-999-555-0106', 'sofia.vargas@example.com')
""")
print(con.sql("SELECT * FROM customers"))

Notice the emails' domain: example.com — the domain reserved exactly for this purpose by internet's technical standard (RFC 2606), never resolved to any real server. No data in this table identifies any real person at all; it's example data, fixed, with the same determinism discipline every earlier table in this guide has already demanded. customer_id doesn't connect to any orders_s04 column in this guide — this guide doesn't extend that schema, as the DESIGN already clarified —; customers is, deliberately, an independent table, Kiosko's complete contact directory, not a specific order's detail table.

Worked example: ACCESS_POLICY and apply_access_policy()

# access_control.py -- module 7, lesson 5
import duckdb
import polars as pl

pl.Config.set_fmt_str_lengths(60)

ACCESS_POLICY: dict[str, list[str]] = {
    "analyst": [
        "order_id", "store_id", "product_id", "quantity", "unit_price", "order_ts",
        "customer_id", "customer_email",
    ],
    "finance": [
        "order_id", "store_id", "product_id", "quantity", "unit_price", "order_ts",
        "customer_id", "customer_email", "customer_phone",
    ],
    "support": [
        "order_id", "customer_id", "customer_email", "customer_phone",
    ],
}


def apply_access_policy(df: pl.DataFrame, role: str, policy: dict[str, list[str]] = ACCESS_POLICY) -> pl.DataFrame:
    """Filters df down to only the columns ACCESS_POLICY lets `role` see.

    policy[role] columns that don't exist in df get ignored -- the same policy
    can apply to different tables (orders_s04, customers) with no change at all.
    """
    allowed = [c for c in policy[role] if c in df.columns]
    return df.select(allowed)


def main() -> None:
    con = duckdb.connect("kiosko.duckdb")
    orders_df = con.sql("SELECT * FROM orders_s04").pl()
    customers_df = con.sql("SELECT * FROM customers").pl()

    print("=== ACCESS_POLICY applied to orders_s04 (first 3 rows per role) ===")
    for role in ["analyst", "finance", "support"]:
        view = apply_access_policy(orders_df, role)
        print(f"\n--- role={role} | columns: {view.columns} ---")
        print(view.head(3))

    print("\n\n=== ACCESS_POLICY applied to customers (whole table per role) ===")
    for role in ["analyst", "finance", "support"]:
        view = apply_access_policy(customers_df, role)
        print(f"\n--- role={role} | columns: {view.columns} ---")
        print(view)


if __name__ == "__main__":
    main()

What to expect (verified by actually running python3 access_control.py, with kiosko.duckdb containing orders_s04 and customers, polars==1.43.2):

=== ACCESS_POLICY applied to orders_s04 (first 3 rows per role) ===

--- role=analyst | columns: ['order_id', 'store_id', 'product_id', 'quantity', 'unit_price', 'order_ts'] ---
shape: (3, 6)
┌──────────┬──────────┬────────────┬──────────┬────────────┬─────────────────────┐
│ order_id ┆ store_id ┆ product_id ┆ quantity ┆ unit_price ┆ order_ts            │
│ ---      ┆ ---      ┆ ---        ┆ ---      ┆ ---        ┆ ---                 │
│ str      ┆ str      ┆ str        ┆ i64      ┆ f64        ┆ datetime[μs]        │
╞══════════╪══════════╪════════════╪══════════╪════════════╪═════════════════════╡
│ ORD-9501 ┆ S04      ┆ P001       ┆ 3        ┆ 0.55       ┆ 2026-08-14 08:05:00 │
│ ORD-9502 ┆ S04      ┆ P002       ┆ 2        ┆ 1.2        ┆ 2026-08-14 08:12:00 │
│ ORD-9503 ┆ S04      ┆ P003       ┆ 1        ┆ null       ┆ 2026-08-14 08:19:00 │
└──────────┴──────────┴────────────┴──────────┴────────────┴─────────────────────┘

--- role=finance | columns: ['order_id', 'store_id', 'product_id', 'quantity', 'unit_price', 'order_ts'] ---
shape: (3, 6)
┌──────────┬──────────┬────────────┬──────────┬────────────┬─────────────────────┐
│ order_id ┆ store_id ┆ product_id ┆ quantity ┆ unit_price ┆ order_ts            │
│ ---      ┆ ---      ┆ ---        ┆ ---      ┆ ---        ┆ ---                 │
│ str      ┆ str      ┆ str        ┆ i64      ┆ f64        ┆ datetime[μs]        │
╞══════════╪══════════╪════════════╪══════════╪════════════╪═════════════════════╡
│ ORD-9501 ┆ S04      ┆ P001       ┆ 3        ┆ 0.55       ┆ 2026-08-14 08:05:00 │
│ ORD-9502 ┆ S04      ┆ P002       ┆ 2        ┆ 1.2        ┆ 2026-08-14 08:12:00 │
│ ORD-9503 ┆ S04      ┆ P003       ┆ 1        ┆ null       ┆ 2026-08-14 08:19:00 │
└──────────┴──────────┴────────────┴──────────┴────────────┴─────────────────────┘

--- role=support | columns: ['order_id'] ---
shape: (3, 1)
┌──────────┐
│ order_id │
│ ---      │
│ str      │
╞══════════╡
│ ORD-9501 │
│ ORD-9502 │
│ ORD-9503 │
└──────────┘


=== ACCESS_POLICY applied to customers (whole table per role) ===

--- role=analyst | columns: ['customer_id', 'customer_email'] ---
shape: (6, 2)
┌─────────────┬───────────────────────────┐
│ customer_id ┆ customer_email            │
│ ---         ┆ ---                       │
│ str         ┆ str                       │
╞═════════════╪═══════════════════════════╡
│ C001        ┆ ana.torres@example.com    │
│ C002        ┆ luis.rojas@example.com    │
│ C003        ┆ maria.fuentes@example.com │
│ C004        ┆ jorge.medina@example.com  │
│ C005        ┆ carla.suarez@example.com  │
│ C006        ┆ sofia.vargas@example.com  │
└─────────────┴───────────────────────────┘

--- role=finance | columns: ['customer_id', 'customer_email', 'customer_phone'] ---
shape: (6, 3)
┌─────────────┬───────────────────────────┬──────────────────┐
│ customer_id ┆ customer_email            ┆ customer_phone   │
│ ---         ┆ ---                       ┆ ---              │
│ str         ┆ str                       ┆ str              │
╞═════════════╪═══════════════════════════╪══════════════════╡
│ C001        ┆ ana.torres@example.com    ┆ +57-300-555-0101 │
│ C002        ┆ luis.rojas@example.com    ┆ +51-999-555-0102 │
│ C003        ┆ maria.fuentes@example.com ┆ +56-9-5550-0103  │
│ C004        ┆ jorge.medina@example.com  ┆ +52-55-5550-0104 │
│ C005        ┆ carla.suarez@example.com  ┆ +57-300-555-0105 │
│ C006        ┆ sofia.vargas@example.com  ┆ +51-999-555-0106 │
└─────────────┴───────────────────────────┴──────────────────┘

--- role=support | columns: ['customer_id', 'customer_email', 'customer_phone'] ---
shape: (6, 3)
┌─────────────┬───────────────────────────┬──────────────────┐
│ customer_id ┆ customer_email            ┆ customer_phone   │
│ ---         ┆ ---                       ┆ ---              │
│ str         ┆ str                       ┆ str              │
╞═════════════╪═══════════════════════════╪══════════════════╡
│ C001        ┆ ana.torres@example.com    ┆ +57-300-555-0101 │
│ C002        ┆ luis.rojas@example.com    ┆ +51-999-555-0102 │
│ C003        ┆ maria.fuentes@example.com ┆ +56-9-5550-0103  │
│ C004        ┆ jorge.medina@example.com  ┆ +52-55-5550-0104 │
│ C005        ┆ carla.suarez@example.com  ┆ +57-300-555-0105 │
│ C006        ┆ sofia.vargas@example.com  ┆ +51-999-555-0106 │
└─────────────┴───────────────────────────┴──────────────────┘

Read this result carefully, because the same ACCESS_POLICY dictionary produces very different views depending on which table it applies to — the same policy, two completely different behaviors, with no change at all to apply_access_policy(). On orders_s04: analyst and finance see all six business columns complete (neither is PII in that section of ACCESS_POLICY); support only sees order_id — knowing which order a customer calling about refers to is enough, with no need for that sale's exact price or quantity. On customers: analyst only sees customer_id and customer_email — not even the phone, a purchase-behavior analyst has no reason to call anyone —; finance and support see all three columns, including customer_phone, because both roles do have a legitimate reason to contact the customer directly (billing, in one case; delivery support, in the other). Notice something important: in this example, no value is masked yet — customer_email and customer_phone appear in plain text for any role the policy lets see them. That's, precisely, what's missing, and what lesson 6 builds.

Diagram: one policy, two tables, three roles

flowchart TD
    P["ACCESS_POLICY\n(a single dictionary)"] --> O["apply_access_policy(orders_s04, role)"]
    P --> C["apply_access_policy(customers, role)"]

    O --> OA["analyst: 6 columns\n(all business)"]
    O --> OF["finance: 6 columns\n(all business)"]
    O --> OS["support: 1 column\n(order_id only)"]

    C --> CA["analyst: 2 columns\n(id + email)"]
    C --> CF["finance: 3 columns\n(id + email + phone)"]
    C --> CS["support: 3 columns\n(id + email + phone)"]

Going deeper: why ACCESS_POLICY silently filters absent columns, instead of failing

Notice one specific line of apply_access_policy(): [c for c in policy[role] if c in df.columns]. This line makes finance's policy, which mentions customer_phone, not fail when applied to orders_s04 — a table that never had that column; it simply ignores it. This is a deliberate design decision, and it's worth understanding why.

The alternative — making apply_access_policy() raise an error if any of the policy's columns doesn't exist in the table — seems, at first glance, "safer": it would force whoever writes ACCESS_POLICY to maintain a separate, exact list for every table. But that alternative breaks exactly the advantage this lesson's worked example demonstrated: a single policy, reusable over any table sharing some of its columns. ACCESS_POLICY["finance"] describes, underneath, a business concept — "everything finance needs, in any Kiosko table that has it" —, not a list tied to a specific table. If Kiosko added a seventh table tomorrow, with its own customer_email column, the same policy would already know how to filter it correctly, with no new entry needed from anyone. The cost of this flexibility is that a typo in ACCESS_POLICY — a column that should exist, but got misspelled — fails silently, with no warning at all, instead of raising an immediate exception. It's the same kind of trade-off between flexibility and early error detection you already saw in module 4, when pydantic, with Literal[...], decided when a value should fail loudly instead of getting accepted silently — here, this guide chooses the flexibility side, with this paragraph's explicit warning.

Common mistakes

Confusing ACCESS_POLICY with row-level access control. What happens: someone expects apply_access_policy() to also filter which rows each role can see — for example, that support only sees orders from the store it's assigned to —, not just which columns. Why it happens: "access control" is a broad term, and row-level security (filtering rows by role) is a real practice, just as common as column-level security (filtering columns). How to spot it: check what apply_access_policy() receives and returns — always the same row count as the input, never less. How to fix it: this lesson deliberately builds only column-level access control — the piece this guide's DESIGN and the market audit that originated it explicitly mark as the competitive gap to close. Row-level security (filtering rows, not columns) is a real, reasonable extension, but it falls outside this module's scope — this lesson's Exercise 3 invites you to explore it on your own.

Thinking a role not listed in ACCESS_POLICY gets access to everything, by default. What happens: someone calls apply_access_policy(df, "admin"), a role not in the dictionary, expecting it to return the whole table as "secure by default" behavior. Why it happens: in many systems, "I don't have a specific rule" gets interpreted as "no restriction." How to spot it: try calling the function with a role that doesn't exist — Python raises KeyError: 'admin', because policy[role] tries to access a key that isn't in the dictionary. How to fix it: this behavior is, actually, the correct one for a data governance system — the security principle the industry calls "deny by default": if a role has no explicit policy, the correct answer is to fail loudly (KeyError), not silently return unlimited access. A new role at Kiosko always needs an explicit ACCESS_POLICY entry before it can query any table governed by this function — never default access.

Exercises

Exercise 1 — Run access_control.py yourself, from scratch. In a new folder, with kiosko.duckdb containing orders_s04 and customers (this lesson), run python3 access_control.py. Confirm support sees only order_id from orders_s04, but all three complete columns of customers.

Exercise 2 — Add a fourth role, "marketing", with read-only access to customer_id and customer_email, but never to customer_phone or any orders_s04 column. Extend ACCESS_POLICY with this new entry, and confirm apply_access_policy(customers_df, "marketing") returns exactly two columns.

ACCESS_POLICY["marketing"] = ["customer_id", "customer_email"]

view = apply_access_policy(customers_df, "marketing")
print(f"columns: {view.columns}")
print(view.height, "rows")

columns: ['customer_id', 'customer_email']
6 rows

Exercise 3 — Design, in prose (with no code), how you'd extend ACCESS_POLICY to add row-level, not just column-level, control. Imagine Kiosko wants a "store_manager" role to only see orders_s04 rows belonging to its own store. In 3-4 sentences, describe what additional data structure you'd need, and why apply_access_policy(), as written in this lesson, can't solve that case with no changes.

Summary and next step

In this lesson you met customers, this guide's first table with data identifying a real person, and you built ACCESS_POLICY and apply_access_policy(), the first piece of Kiosko's data governance: column-level access control, really run over two different tables — orders_s04 and customers — with three business roles, each seeing exactly what it needs for its job, no more, no less.

Before moving on you should be able to: explain the difference between column-level (this lesson) and row-level access control (out of scope, discussed in Exercise 3); reproduce, by running the code yourself, this lesson's six different views (three roles, two tables); and justify why apply_access_policy() fails with KeyError for an unknown role, instead of returning unlimited access.

With this, Kiosko now controls which column each role sees. But notice a detail the lesson deliberately left unresolved: customer_email and customer_phone still appear in plain text for any role allowed to see them — ACCESS_POLICY decides whether a column is visible, never how it looks. Lesson 6 answers that pending question: mask_pii(), with a deterministic hash, so even a role with access to a sensitive column can work with it without seeing the real value.

Resources

• Polars — official documentation (select, DataFrame.columns — apply_access_policy()'s foundation). docs.pola.rs. In English.
• IETF — RFC 2606, "Reserved Top Level DNS Names" (the source of the example.com domain, reserved for documentation and examples, never resolved to a real server — the reason this lesson uses it in customers's emails). datatracker.ietf.org/doc/html/rfc2606. In English.
• AWS — "AWS Certified Data Engineer - Associate (DEA-C01)" exam guide (Domain 4, "Data Security and Governance," 18% of the exam). docs.aws.amazon.com/aws-certification. In English.
• Microsoft Learn — "Microsoft Certified: Fabric Data Engineer Associate (DP-700)" (the official exam description confirms one of the role's three central responsibilities is "Securing and managing an analytics solution" — the market evidence anchoring this module's column governance, alongside AWS DEA-C01). learn.microsoft.com/credentials/certifications/exams/dp-700. In English.
• This guide's DESIGN — the source of customers, ACCESS_POLICY, and the boundary with real infrastructure security. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.