steampipe plugin install aws

Table: aws_iam_credential_report - Query AWS IAM Credential Reports using SQL

The AWS IAM Credential Report is a document that provides details about how the AWS Identity and Access Management (IAM) users in your AWS account are accessing AWS services. It lists all your AWS account's users and the status of their various credentials, including passwords, access keys, MFA devices, and signing certificates. This report can help you audit and improve the security of your AWS account.

Table Usage Guide

The aws_iam_credential_report table in Steampipe provides you with information about IAM credential reports within AWS Identity and Access Management (IAM). This table allows you, as a DevOps engineer, to query user-specific details, including access keys, password status, and MFA device usage. You can utilize this table to gather insights on IAM users, such as inactive users, users with password-enabled login, access key usage, and more. The schema outlines the various attributes of the IAM credential report, including the user name, user creation time, access key details, and password last used date. For more information about the credential report, see Getting Credential Reports in the IAM User Guide.

Important Notes

  • You need a valid credential report to exist for this table. To generate one, please run the following AWS CLI command - aws iam generate-credential-report.

Examples

List users that have logged into the console in the past 90 days

Determine the users who have accessed the console within the past three months. This can be useful for monitoring user activity, identifying potential security risks, or auditing user access for compliance purposes.

select
user_name
from
aws_iam_credential_report
where
password_enabled
and password_last_used > (current_date - interval '90' day);
select
user_name
from
aws_iam_credential_report
where
password_enabled = 1
and password_last_used > date('now', '-90 day');

List users that have NOT logged into the console in the past 90 days

Identify users who may have abandoned their accounts or are no longer active by pinpointing those who haven't logged in for the past 90 days. This assists in maintaining secure and efficient user management by flagging potential inactive accounts for review or deletion.

select
user_name,
password_last_used,
age(password_last_used)
from
aws_iam_credential_report
where
password_enabled
and password_last_used <= (current_date - interval '90' day)
order by
password_last_used;
select
user_name,
password_last_used,
julianday('now') - julianday(password_last_used)
from
aws_iam_credential_report
where
password_enabled
and julianday('now') - julianday(password_last_used) >= 90
order by
password_last_used;

List users with console access that have never logged in to the console

Discover the segments of users who have been granted console access but have never utilized it. This can be useful in identifying unnecessary access privileges and enhancing security measures.

select
user_name
from
aws_iam_credential_report
where
password_status = 'never_used';
select
user_name
from
aws_iam_credential_report
where
password_status = 'never_used';

List access keys older than 90 days

Discover the segments that have access keys older than 90 days to assess potential security risks and ensure timely key rotation. This can help maintain secure access protocols and prevent unauthorized access.

select
user_name,
access_key_1_last_rotated,
age(access_key_1_last_rotated) as access_key_1_age,
access_key_2_last_rotated,
age(access_key_2_last_rotated) as access_key_2_age
from
aws_iam_credential_report
where
access_key_1_last_rotated <= (current_date - interval '90' day)
or access_key_2_last_rotated <= (current_date - interval '90' day)
order by
user_name;
select
user_name,
access_key_1_last_rotated,
julianday('now') - julianday(access_key_1_last_rotated) as access_key_1_age,
access_key_2_last_rotated,
julianday('now') - julianday(access_key_2_last_rotated) as access_key_2_age
from
aws_iam_credential_report
where
julianday('now') - julianday(access_key_1_last_rotated) >= 90
or julianday('now') - julianday(access_key_2_last_rotated) >= 90
order by
user_name;

List users that have a console password but do not have MFA enabled

Explore which users have an active console password but lack multi-factor authentication. This is useful for identifying potential security vulnerabilities within your AWS IAM user base.

select
user_name,
mfa_active,
password_enabled
from
aws_iam_credential_report
where
password_enabled
and not mfa_active;
select
user_name,
mfa_active,
password_enabled
from
aws_iam_credential_report
where
password_enabled = 1
and mfa_active = 0;

Check if root login has MFA enabled

Determine if the root account of your AWS IAM service has multifactor authentication (MFA) enabled. This is crucial for enhancing account security and preventing unauthorized access.

select
user_name,
mfa_active
from
aws_iam_credential_report
where
user_name = '<root_account>';
select
user_name,
mfa_active
from
aws_iam_credential_report
where
user_name = '<root_account>';

Control examples

Schema for aws_iam_credential_report

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
access_key_1_activebooleanDoes the user have an access key and is the access key's status Active.
access_key_1_last_rotatedtimestamp with time zoneThe date and time when the user's access key was created or last changed.
access_key_1_last_used_datetimestamp with time zoneThe date and time when the user's access key was most recently used to sign an AWS API request. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_1_last_used_regiontextThe AWS Region in which the access key was most recently used. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_1_last_used_servicetextThe AWS service that was most recently accessed with the access key. The value in this field uses the service's namespace—for example, s3 for Amazon S3 and ec2 for Amazon EC2. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_2_activebooleanDoes the user have a second access key and is the access key's status Active.
access_key_2_last_rotatedtimestamp with time zoneThe date and time when the user's second access key was created or last changed.
access_key_2_last_used_datetimestamp with time zoneThe date and time when the user's second access key was most recently used to sign an AWS API request. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_2_last_used_regiontextThe AWS Region in which the user's second access key was most recently used. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_2_last_used_servicetextThe AWS service that was most recently accessed with the user's second access key. The value in this field uses the service's namespace—for example, s3 for Amazon S3 and ec2 for Amazon EC2. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
cert_1_activebooleanDoes the user have an X.509 signing certificate and is that certificate's status Active.
cert_1_last_rotatedtimestamp with time zoneThe date and time when the user's signing certificate was created or last changed.
cert_2_activebooleanDoes the user have a second X.509 signing certificate and is that certificate's status Active.
cert_2_last_rotatedtimestamp with time zoneThe date and time when the user's second signing certificate was created or last changed.
generated_timetimestamp with time zoneThe date and time when the credential report was created, in ISO 8601 date-time format (http://www.iso.org/iso/iso8601).
mfa_activebooleanWhether a multi-factor authentication (MFA) device has been enabled for the user.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
password_enabledbooleanWhen the user has a password, this value is true. Otherwise it is false. The value for the AWS account root user is always false.
password_last_changedtimestamp with time zoneThe date and time when the user's password was last set.
password_last_usedtimestamp with time zoneThe date and time when the AWS account root user or IAM user's password was last used to sign in to an AWS website.
password_next_rotationtimestamp with time zoneWhen the account has a password policy that requires password rotation, this field contains the date and time.
password_statustextThe status of an user password. Password status can be one of used, never_used and not_set.
regiontextThe AWS Region in which the resource is located.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
user_arntextThe Amazon Resource Name (ARN) of the user.
user_creation_timetimestamp with time zoneThe date and time when the user was created.
user_nametextThe friendly name of the user.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_iam_credential_report