Query: ad_authorization_policy_user_consent_disallowed

with distinct_tenant as (
  select
    distinct tenant_id,
    display_name,
    subscription_id,
    _ctx
  from
    azure_tenant
)
select
  id as resource,
  case when exists (
      select 1
      from jsonb_array_elements_text(
            (default_user_role_permissions -> 'permissionGrantPoliciesAssigned')::jsonb
          ) as pol(val)
      where val like 'ManagePermissionGrantsForSelf.%'
    ) then 'alarm'
    else 'ok'
  end as status,
  case when exists (
      select 1
      from jsonb_array_elements_text(
            (default_user_role_permissions -> 'permissionGrantPoliciesAssigned')::jsonb
          ) as pol(val)
      where val like 'ManagePermissionGrantsForSelf.%'
    ) then t.display_name || ' user consent for applications allowed.'
    else t.display_name || ' user consent for applications disallowed.'
  end as reason,
  t.tenant_id
  
from
  distinct_tenant as t,
  azuread_authorization_policy