A role-assignment export does not show effective access, whether the identity is still valid, which sensitive objects are reachable, how the access was used, or who can justify it. A reliable review reconstructs the full path from identity through groups and roles to resources, then gives the correct owner enough evidence to make a decision.
AI can summarize complex entitlement paths, detect anomalies, explain recent usage, and propose a narrower alternative. It must not decide authorization or execute revocation on its own. Policy, owner decisions, separation of duties, approved exceptions, and verified changes remain authoritative.
Do reviewers approve warehouse access because the evidence is incomplete? Datrick can assess one data domain or privileged-role family and design a supervised recertification workflow around existing identity and warehouse controls.
Build a decision-ready review record
| Evidence layer | Capture | Risk if missing |
|---|---|---|
| Identity | Person or workload, employer, team, manager, owner, status, vendor end date, authentication, and last sign-in. | Orphaned, former, shared, or misclassified identity remains active. |
| Effective access | Direct grants, groups, nested roles, inherited privileges, ownership, future grants, and administrative capabilities. | Reviewer sees only one path and misses equivalent or higher access. |
| Resource risk | Database, schema, table, view, model, classification, policy tag, customer, geography, and business criticality. | Low-risk and highly sensitive entitlements receive the same review treatment. |
| Usage | Reads, writes, DDL, grants, query purpose, source application, frequency, recency, and known coverage gaps. | Unused access is retained blindly or legitimate seasonal access is revoked. |
| Purpose and ownership | Request, ticket, job function, project, data product, owner, expiry, exception, and conflicting duties. | No accountable person can justify continued access. |
| Decision and execution | Retain, reduce, revoke, investigate, reason, reviewer, due date, change, result, retry, and verification. | Review completion does not produce a reliable access change or audit trail. |
Warehouse and identity platforms expose different parts of this evidence. Snowflake Account Usage records role grants to users and their grant and revoke history. BigQuery audit logs record administrative and data-access activity, including table reads and writes, while IAM controls effective resource permissions. Microsoft Entra access reviews route attestations to users or decision makers and can apply approved changes after review completion.
Usage is evidence, not an authorization decision
Recent use can support a business-purpose claim, but it does not prove least privilege. A user may query one table through a role that exposes an entire domain. Inactivity can indicate stale access, but it can also represent break-glass duties, disaster recovery, quarter-end work, a new assignment, or a service account waiting for an event.
Normalize usage windows by access type and business calendar. Show the reviewer what was used, what remained unused, what narrower role could support observed work, and what audit coverage is missing. Never treat absence of logs as proof of absence of use.
Build a controlled recertification workflow
| Component | Responsibility | Production control |
|---|---|---|
| Entitlement graph | Resolves identities, groups, roles, ownership, grants, inheritance, resources, and effective privileges. | Snapshot time, source freshness, cycle detection, unsupported grant types, and reconciliation. |
| Risk and usage collector | Joins classification, criticality, query and admin activity, identity state, purpose, and exceptions. | Least-privilege collection, log coverage, sensitive SQL handling, and bounded history. |
| AI review analyst | Summarizes access paths, anomalies, usage, missing evidence, conflicts, and least-privilege alternatives. | Citations, confidence, no authorization authority, and no unrestricted query access. |
| Review router | Selects resource owner, manager, security reviewer, or service-account owner and manages reminders and escalation. | No self-approval for privileged access, delegation policy, due dates, and fallback owner. |
| Decision ledger | Records retain, reduce, revoke, investigate, justification, exception, expiry, and reviewer. | Immutable history, required reason, policy version, and expired-exception handling. |
| Change executor | Creates approved grant changes, handles dependencies, and verifies effective access after execution. | Dry run, separation of duties, idempotency, rollback, change window, and failure queue. |
| Coverage monitor | Reconciles reviewed scope, undecided access, applied changes, equivalent paths, and re-grants. | Completeness threshold, no silent closure, recurring review, and owner dashboard. |
Separate review decisions from access changes
A reviewer decision is an instruction, not proof that access changed. Revocation can fail, a nested group can preserve equivalent access, an owner privilege can supersede the revoked role, or automation can target a stale snapshot. Recalculate effective access after every change and keep the review open until the intended state is verified.
High-impact removals need a change window, dependency check, rollback, and notification. Service accounts need an accountable owner and workload evidence. Privileged, break-glass, external, dormant, and cross-region access should use dedicated policies rather than a generic review rule.
Evaluate coverage, decision quality, and verified enforcement
- Inventory: identity, grant, role-path, resource, classification, and owner coverage; stale-source and unresolved-path rate.
- Review: completion time, rubber-stamp rate, reviewer disagreement, missing evidence, escalations, and decision reversals.
- Recommendation: inappropriate retain or revoke suggestions, least-privilege alternative accuracy, unsupported claims, and risk prioritization.
- Execution: applied-change success, equivalent access remaining, rollback, outage, re-grant, and time to verified state.
- Control: self-approval, unauthorized execution, expired exception, audit evidence completeness, and sensitive-log exposure.
Pilot one domain or privileged-role family
- Select a bounded scope with meaningful sensitive data, known owners, and measurable review pain.
- Inventory identities, groups, roles, grants, inheritance, resources, classification, usage, owners, purposes, and exceptions.
- Define review policy, evidence windows, reviewer assignment, decision options, due dates, escalation, and execution controls.
- Replay a prior review and compare scope, evidence, decisions, changes, and verified outcomes.
- Run the new evidence pack in parallel with the current review; measure missing paths and reviewer behavior.
- Enable supervised decision routing, then approved change execution with post-change effective-access verification.
- Expand only after inventory coverage, owner quality, decision consistency, execution, rollback, and audit evidence meet thresholds.
A pilot can often reach supervised review in three to six weeks. Nested groups, cross-platform identities, incomplete audit logs, weak resource ownership, and manually managed exceptions are usually the main schedule risks.
Frequently asked questions
What is data warehouse access recertification automation?
Data warehouse access recertification automation inventories direct and inherited grants, maps them to people, service accounts, roles, resources, sensitive data, actual usage, ownership, and business purpose, then routes evidence to accountable reviewers and records retain, reduce, revoke, or investigate decisions.
Can AI decide who should keep data warehouse access?
AI can summarize evidence, identify anomalies, suggest questions, and prioritize reviews. It should not grant, retain, or revoke access autonomously. Authorization decisions require approved policy, accountable resource owners, separation of duties, and verified execution.
Does unused access always mean access should be revoked?
No. Break-glass roles, seasonal processes, disaster recovery, month-end tasks, service accounts, and newly granted access can be legitimately unused. Usage is evidence, not the decision. Reviewers need purpose, criticality, identity state, resource sensitivity, and exception policy.
What evidence should a warehouse access review include?
Include identity and employment or vendor state, direct and inherited roles, effective privileges, resource and data classification, recent reads and writes, administrative actions, grant age and source, owner, business purpose, conflicting duties, prior decisions, exceptions, and the proposed least-privilege alternative.
How long does an access recertification automation pilot take?
A pilot for one warehouse domain or privileged-role family can often reach supervised review in three to six weeks when identity, grant, role hierarchy, usage, classification, ownership, and prior review data are available. Nested groups, cross-platform roles, weak ownership, and incomplete audit logs can extend the schedule.
Official implementation references
- Snowflake grants to users history
- BigQuery audit logs
- BigQuery IAM and least privilege
- Microsoft Entra access reviews
- Microsoft Entra access review workflow
Start with one domain where access evidence is expensive to assemble. Datrick can assess entitlement paths, usage, classification, ownership, reviewer workflow, change controls, and verification before proposing a pilot.