turbot/gcp_compliance

Query: iam_user_kms_separation_of_duty_enforced

Usage

powerpipe query gcp_compliance.query.iam_user_kms_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'
),
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;

Controls

The query is being used by the following controls: