turbot/aws_compliance

Query: iam_user_unused_credentials_45

Usage

powerpipe query aws_compliance.query.iam_user_unused_credentials_45

SQL

select
user_arn as resource,
case
--root_account will have always password associated even though AWS credential report returns 'not_supported' for password_enabled
when user_name = '<root_account>' then 'info'
when password_enabled
and password_last_used is null
and password_last_changed < (current_date - interval '45' day) then 'alarm'
when password_enabled
and password_last_used < (current_date - interval '45' day) then 'alarm'
when access_key_1_active
and access_key_1_last_used_date is null
and access_key_1_last_rotated < (current_date - interval '45' day) then 'alarm'
when access_key_1_active
and access_key_1_last_used_date < (current_date - interval '45' day) then 'alarm'
when access_key_2_active
and access_key_2_last_used_date is null
and access_key_2_last_rotated < (current_date - interval '45' day) then 'alarm'
when access_key_2_active
and access_key_2_last_used_date < (current_date - interval '45' day) then 'alarm'
else 'ok'
end status,
user_name || case
when not password_enabled then ' password not enabled,'
when password_enabled
and password_last_used is null then ' password created ' || to_char(password_last_changed, 'DD-Mon-YYYY') || ' never used,'
else ' password used ' || to_char(password_last_used, 'DD-Mon-YYYY') || ','
end || case
when not access_key_1_active then ' key 1 not enabled,'
when access_key_1_active
and access_key_1_last_used_date is null then ' key 1 created ' || to_char(access_key_1_last_rotated, 'DD-Mon-YYYY') || ' never used,'
else ' key 1 used ' || to_char(access_key_1_last_used_date, 'DD-Mon-YYYY') || ','
end || case
when not access_key_2_active then ' key 2 not enabled.'
when access_key_2_active
and access_key_2_last_used_date is null then ' key 2 created ' || to_char(access_key_2_last_rotated, 'DD-Mon-YYYY') || ' never used.'
else ' key 2 used ' || to_char(access_key_2_last_used_date, 'DD-Mon-YYYY') || '.'
end as reason,
account_id
from
aws_iam_credential_report;

Controls

The query is being used by the following controls: