Query: ad_authorization_policy_user_consent_verified_publishers_selected_permissions

with distinct_tenant as (
  select
    distinct tenant_id, subscription_id, _ctx
  from
    azure_tenant
)
select
  p.id as resource,
  case
    when (p.default_user_role_permissions -> 'permissionGrantPoliciesAssigned')::jsonb @> '["ManagePermissionGrantsForSelf.microsoft-user-default-low"]'::jsonb then 'ok'
    else 'alarm'
  end as status,
  case
    when (p.default_user_role_permissions -> 'permissionGrantPoliciesAssigned')::jsonb @> '["ManagePermissionGrantsForSelf.microsoft-user-default-low"]'::jsonb then p.display_name || ' user consent limited to verified publishers for selected permissions.'
    else p.display_name || ' user consent policy not set to verified publishers (LOW).'
  end as reason,
  t.tenant_id
  
from
  distinct_tenant t
  join azuread_authorization_policy p on p.tenant_id = t.tenant_id;