Query: identity_only_administrators_group_with_manage_all_resources_permission_in_tenancy

with policies_with_manage_all_resource_per as (
  select
    lower(s) as statement
  from
    oci_identity_policy,
    jsonb_array_elements_text(statements) as s
  where
    lower(s) like '%' || 'to manage all-resources in tenancy'
),
policies_with_manage_all_resource_per_except_admin as (
  select
    count(*) as num_of_statements
  from
    policies_with_manage_all_resource_per
  where
    not statement ilike '%' || 'administrators' || '%'
)
select
  tenant_id as resource,
  case
    when num_of_statements > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when num_of_statements > 0 then title || ' permissions on all resources are given to the groups other than administrator group.'
    else title || ' permissions on all resources are given to the administrator group only.'
  end as reason,
  tenant_name as tenant
from
  oci_identity_tenancy,
  policies_with_manage_all_resource_per_except_admin;