Authentication, Roles, Groups, Service Identities, Separation of Duties, and Environment Isolation

Learning outcomes

1. The realistic problem: “the dashboard is private” is not an access model

AtlasMart has certified finance, marketing, and operations marts. A team proposes one analyst login for everyone, while the ETL service account can also export BI files and administer security. The dashboard itself requires sign-in, so the design initially looks protected. It is not. Authentication answers who or what is presenting credentials; authorization decides which actions that identity may perform on which resources. A service identity is a non-human principal used by software. A role or group bundles permissions; it should not erase the underlying identity needed for attribution.

The business decision is whether an analyst or pipeline component can access only the data/actions required for its purpose. The warehouse grain and metrics do not change. The failure mode is privilege amplification: one stolen/shared identity can cross raw, semantic, export, and administrative boundaries.

2. Identity → role/group → resource → action

Least privilege is not “few roles.” It is the effective privilege set after group membership, inherited grants, views, service identities, environments, and copies are combined.

3. Controlled failure: shared credentials destroy attribution

INITIAL FINDINGSshared credential: shared_analyst -> FAILmarketing_analyst SELECT prod.secure_customer_view -> ALLOWmarketing_analyst SELECT prod.raw_customer        -> ALLOW  <-- bypassservice svc_etl_prod INSERT prod.raw_customer     -> ALLOWservice svc_etl_prod ADMIN prod.security_catalog  -> DENYdev.dana SELECT_PII prod.dim_customer             -> DENY (environment isolation)AFTER REPAIRshared active credentials -> 0analytical raw-table bypass paths -> 0marketing_analyst SELECT prod.raw_customer -> DENYseparation-of-duties conflicts -> 0

shared_analyst is deliberately marked is_shared=1. It may seem convenient for a BI team, but the audit trail can no longer reliably distinguish who issued a query. The repair disables the shared identity and keeps individual identities bound to reusable roles.

rows=c.execute('SELECT identity,identity_type,environment FROM identity_registry WHERE is_shared=1 AND enabled=1').fetchall()assert [x['identity'] for x in rows] == ['shared_analyst']c.execute("UPDATE identity_registry SET enabled=0 WHERE identity='shared_analyst'")

4. Separation of duties constrains dangerous combinations

Separation of duties prevents one identity from unilaterally performing combinations whose joint power is risky. In the lab, pipeline_writer + security_admin and privacy_operator + security_admin are forbidden combinations. The ETL service can write governed ingestion targets, but it cannot administer security or publish BI exports. The privacy operator can execute a verified erasure case, but cannot grant itself broader access.

svc_etl_prod INSERT prod.raw_customer     ALLOWsvc_etl_prod ADMIN  prod.security_catalog DENYsvc_etl_prod INSERT prod.bi_export_masked DENYpriya.privacy ERASE_PII prod.dim_customer  ALLOWpriya.privacy ADMIN prod.security_catalog  DENYseparation-of-duties conflicts             0

5. Environment isolation is part of authorization

A correct role in the wrong environment is still the wrong access. dev.dana is bound to dev; asking for production PII is denied before any role grant is considered. Production should also use distinct databases/accounts/projects/keys where the chosen platform supports stronger isolation. The lab models the semantic rule; it does not claim one physical topology fits every engine.

dev.dana SELECT_PII prod.dim_customer -> DENYreason -> environment isolation

6. Why service identities need narrower blast radius than humans

Pipeline identities are long-lived operational actors and often execute unattended. They should have exactly the read/write actions required for the pipeline stage, not a human analyst's browsing privileges and not security administration. Credential storage/rotation is platform-specific, but the invariant is portable: never embed production secrets in SQL, notebooks, repository files, or lesson fixtures. The lab's HMAC key is supplied through an environment variable and is explicitly synthetic.

# Bash / Git Bashexport ATLASMART_TOKEN_KEY="synthetic-lab-key-2026"python ch22_lab.py# PowerShell$env:ATLASMART_TOKEN_KEY = "synthetic-lab-key-2026"python .\ch22_lab.py

7. What the policy evaluator proves—and does not prove

The executed SQLite fixture proves the decision logic, audit events, environment boundary, and absence of forbidden role combinations for this dataset. It does not prove that SQLite itself enforces enterprise IAM, that a cloud warehouse inherits identical semantics, or that a BI tool cannot cache/export data elsewhere. Native identity federation, role inheritance, row policies, service-account credentials, query delegation, and audit-log integrity are product-specific and must be verified in the selected platform.

8. Production judgment

Correctness: security changes must not silently redefine warehouse measures. Freshness/history: grants and group memberships need effective time/audit history where investigations require reconstruction. Retry: role/policy changes should be idempotent and observable. Data quality: denied access is not evidence that allowed data is correct. Security: test effective privileges, not intended diagrams. Performance/cost: policy evaluation can add work, but no universal overhead is asserted here. Compatibility: IAM syntax is engine/cloud-specific. Rollback: keep versioned grants and an emergency revoke path.

9. Verification checklist

1. Run the lab with a synthetic runtime key.
2. Confirm the initial shared identity is detected.
3. Confirm the ETL service can write raw but cannot administer security/export BI.
4. Confirm dev → prod PII is denied.
5. Repair the shared identity and raw bypass grant.
6. Re-run findings; shared credentials, bypass paths, and SoD conflicts must all be zero.

10. Bridge to Lesson 2

Identity and role boundaries answer who may approach a resource. Lesson 2 moves inside the resource: which rows and columns may be visible, what masking actually changes, how tokenization changes linkage, and where policy must be enforced so a direct physical table cannot bypass it.

Knowledge check

Authoritative references

• NIST CSRC — Least privilegeDefines restricting users/processes to the minimum authorizations and resources needed for assigned functions.
• NIST SP 800-207 — Zero Trust ArchitectureResource-centric authentication/authorization and granular least privilege; useful for identity and service-account boundaries.
• NIST SP 1800-35 — Implementing a Zero Trust ArchitectureImplementation-oriented identity/access examples; not a warehouse-specific prescription.
• PostgreSQL documentation — Row Security PoliciesConcrete engine-specific example of row-level policy enforcement; exact syntax/semantics are not universal.
• European Commission — Information for individuals under GDPRExplains data-subject rights including erasure and notes that erasure is not absolute.
• EUR-Lex — Regulation (EU) 2016/679Primary legal text for GDPR; technical examples in this course do not substitute for controller/legal interpretation.
• Python documentation — HMACUsed locally to demonstrate stable keyed pseudonymous tokens; tokenization is not equivalent to anonymization.
• SQLite documentation — CREATE VIEWLocal lab uses views and an application policy evaluator; SQLite is not presented as a native enterprise RLS engine.

11. Lab cleanup/reset

Delete atlasmart_ch22_lab (or your configured disposable lab directory) and rerun ch22_lab.py with a synthetic ATLASMART_TOKEN_KEY. The lab modifies no repository, cloud, or production resource.