Query: identity_iam_administrators_no_update_tenancy_administrators_group_permission

with policies_to_update_tenancy as (
  select
    lower(s) as statement
  from
    oci_identity_policy,
    jsonb_array_elements_text(statements) as s
  where
    lower(s) like '%' || 'to use users in tenancy' || '%'
    or lower(s) like '%' || 'to use groups in tenancy' || '%'
),
policies_to_update_tenancy_without_condition as (
  select
    count(*) as num
  from
    policies_to_update_tenancy
  where
    not statement like '%' || 'where target.group.name != ''administrators'''
)
select
  id as resource,
  case
    when num > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when num > 0 then title || ' IAM administrators can update tenancy administrators group.'
    else title || ' IAM administrators cannot update tenancy administrators group.'
  end as reason,
  tenant_name as tenant
from
  oci_identity_tenancy,
  policies_to_update_tenancy_without_condition;