Query: iam_user_consent_to_apps_accessing_data_on_their_behalf_disabled

with distinct_tenant as (
  select
    distinct tenant_id,
    subscription_id,
    _ctx
  from
    azure_tenant
),
authorization_policy_with_overly_permission as (
  select
    *
  from
    azuread_authorization_policy,
    jsonb_array_elements_text(
      default_user_role_permissions -> 'permissionGrantPoliciesAssigned'
    ) as a
  where
    a like '%microsoft-user-default-legacy'
)
select
  a.id as resource,
  case
    when a.tenant_id is null then 'ok'
    else 'alarm'
  end as status,
  case
    when a.tenant_id is null then a.display_name || ' user consent to apps accessing company data on their behalf disabled.'
    else a.display_name || ' user consent to apps accessing company data on their behalf enabled.'
  end as reason,
  t.tenant_id
from
  distinct_tenant as t,
  authorization_policy_with_overly_permission as a;