Query: iam_user_kms_separation_of_duty_enforced

with users_with_roles as (
  select
    distinct split_part(member_entity, ':', 2) as user_name,
    project,
    _ctx,
    p ->> 'role' as assigned_role
  from
    gcp_iam_policy,
    jsonb_array_elements(bindings) as p,
    jsonb_array_elements_text(p -> 'members') as member_entity
  where
    split_part(member_entity, ':', 1) = 'user'
),
kms_admin_users as(
  select
    user_name,
    project
  from
    users_with_roles
  where
    assigned_role = 'roles/cloudkms.admin'
)
select
  distinct user_name as resource,
  case
    when user_name in (
      select
        user_name
      from
        kms_admin_users
    ) then 'alarm'
    else 'ok'
  end as status,
  case
    when user_name in (
      select
        user_name
      from
        kms_admin_users
    ) then user_name || ' assigned with KMS Admin role.'
    else user_name || ' not assigned KMS Admin role.'
  end as reason,
  project as project
from
  users_with_roles;