Query: iam_user_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'
),
account_admin_users as(
  select
    user_name,
    project
  from
    users_with_roles
  where
    assigned_role = 'roles/iam.serviceAccountAdmin'
),
account_users as(
  select
    user_name,
    project
  from
    users_with_roles
  where
    assigned_role = 'roles/iam.serviceAccountUser'
)
select
  distinct user_name as resource,
  case
    when user_name in (
      select
        user_name
      from
        account_users
    )
    and user_name in (
      select
        user_name
      from
        account_admin_users
    ) then 'alarm'
    else 'ok'
  end as status,
  case
    when user_name in (
      select
        user_name
      from
        account_users
    )
    and user_name in (
      select
        user_name
      from
        account_admin_users
    ) then user_name || ' assigned with both Service Account Admin and Service Account User roles.'
    else user_name || ' not assigned with both Service Account Admin and Service Account User roles.'
  end as reason,
  project as project
from
  users_with_roles;