turbot/gcp_compliance

Query: iam_user_separation_of_duty_enforced

Usage

powerpipe query gcp_compliance.query.iam_user_separation_of_duty_enforced

Steampipe Tables

SQL

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;

Controls

The query is being used by the following controls: