Telemetry
Security and audit SQL recipe

Analyze Access-Policy Denials

Rank denied actions by policy, resource class, actor role, and affected identities.

Beginnerauthorization_eventsReviewed 2026-07-28Tested with Apache DataFusion 45.2.0

Reviewed by the Telemetry product team on . SQL compatibility, event contract, synthetic output, and operational caveats. Editorial ownership

Question answered

Which access policies deny the most actors and actions?

Denied actions can indicate healthy enforcement, a broken rollout, or suspicious behavior. Preserving policy version and bounded actor context makes those cases distinguishable.

Event contract

Fields the query expects

FieldTypeWhy it exists
timestamp_utcTimestampAuthorization decision time.
policy_nameUtf8Stable policy name and version.
actionUtf8Controlled attempted action.
resource_typeUtf8Resource class without contents.
actor_roleUtf8Bounded actor role.
actor_idUtf8Restricted privacy-safe actor identifier.
outcomeUtf8allowed or denied.
DataFusion SQL

Copy the query

sql
SELECT
  policy_name,
  action,
  resource_type,
  actor_role,
  COUNT(*) AS denied_actions,
  COUNT(DISTINCT actor_id) AS affected_actors
FROM authorization_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
  AND outcome = 'denied'
GROUP BY policy_name, action, resource_type, actor_role
ORDER BY affected_actors DESC, denied_actions DESC;

This read-only query is planned and executed against an empty typed table with Apache DataFusion 45.2.0. The deterministic sample output is synthetic and reviewed separately; validate field types, thresholds, and business definitions against your own data. Read the testing methodology.

Query result

Actors affected by policy denials

The workspace export policy affects the broadest actor set.

policy_nameactionresource_typeactor_roledenied_actionsaffected_actors
workspace-export-v3exportworkspacemember21486
billing-admin-v2updatesubscriptionadmin4831

Synthetic example output. Run the query against your own event schema and thresholds before using it for operational decisions.

Actors affected by policy denials: static chart of synthetic affected_actors values from the Analyze Access-Policy Denials example result
Indexable SVG of the deterministic example output. Download it for an article, runbook, or design review with attribution.

Reproduce the example

Download the public fixture

The JSON bundle includes the typed event contract, illustrative input rows, exact SQL, expected output, review notes, and engine version. The CSV contains the displayed result.

JSON fixtureExpected output CSVStatic SVG chart

How the SQL works

  1. 1Policy name and version connect the result to a change owner.
  2. 2Affected actors estimates rollout blast radius.
  3. 3Resource class supports review without exposing resource contents.

Edge cases to decide

  • Expected denials and suspicious attempts need different response rules.
  • Restrict drill-down actor access and audit it.
  • Policy renames need a durable version mapping.

Recommended dashboard

  • Bars: affected_actors by policy
  • Trend: denied_actions by release
  • Table: reviewed denial groups

Alert guidance

Alert when a new policy version causes a broad denial increase or a sensitive action is repeatedly denied.

Read alert setup

Put the recipe to work

Related instrumentation and guides

Security audit analyticsPrivileged-action auditIncident response with SQL

Continue the analysis

Analyze Authentication Failure Rate

Measure authentication failures by method and reason while keeping traffic volume and affected identities visible.

Open recipe

Audit Privileged Actions with SQL

Summarize sensitive administrative actions, denied attempts, and review-required outcomes without collecting raw secrets or resource contents.

Open recipe

Detect Suspicious Authentication Bursts

Find short authentication windows with repeated failures across many identities or coarse network sources.

Open recipe

Run it on real events

Create a table, adapt the fields, and save the result

Start free, send structured events, and use the query result as a chart, shared dashboard widget, or alert input.

Get an API key