steampipe plugin install aws

Table: aws_iam_user - Query AWS IAM User using SQL

The AWS Identity and Access Management (IAM) User is a resource that represents an individual or application that interacts with AWS. It contains the name, credentials, and permissions to access AWS resources. IAM Users enable the security best practice of granting least privilege, which means granting only the permissions required to perform a task.

Table Usage Guide

The aws_iam_user table in Steampipe provides you with information about IAM users within AWS Identity and Access Management (IAM). This table allows you, as a DevOps engineer, to query user-specific details, including user ID, name, path, and creation date. You can utilize this table to gather insights on user permissions, access keys, and associated metadata. The schema outlines the various attributes of the IAM user, including the user ARN, creation date, attached policies, and associated tags for you.

Examples

Basic IAM user info

Discover the segments that provide details about users in your AWS IAM, including when they were created and when they last used their password. This can be useful for auditing user activity and maintaining security compliance.

select
name,
user_id,
path,
create_date,
password_last_used
from
aws_iam_user;
select
name,
user_id,
path,
create_date,
password_last_used
from
aws_iam_user;

Groups details to which the IAM user belongs

Determine the affiliations of individual IAM users by identifying the groups they are a part of, providing insights into user access and permissions management within your AWS environment.

select
name as user_name,
iam_group ->> 'GroupName' as group_name,
iam_group ->> 'GroupId' as group_id,
iam_group ->> 'CreateDate' as create_date
from
aws_iam_user
cross join jsonb_array_elements(groups) as iam_group;
select
name as user_name,
json_extract(iam_group, '$.GroupName') as group_name,
json_extract(iam_group, '$.GroupId') as group_id,
json_extract(iam_group, '$.CreateDate') as create_date
from
aws_iam_user,
json_each(groups) as iam_group;

List all the users having Administrator access

This query helps identify users who have been granted administrator access in an AWS environment. It's useful for auditing user permissions and ensuring only authorized individuals have such high-level access.

select
name as user_name,
split_part(attachments, '/', 2) as attached_policies
from
aws_iam_user
cross join jsonb_array_elements_text(attached_policy_arns) as attachments
where
split_part(attachments, '/', 2) = 'AdministratorAccess';
select
name as user_name,
json_extract(attachments, '$[1]') as attached_policies
from
aws_iam_user
cross join json_each(attached_policy_arns) as attachments
where
json_extract(attachments, '$[1]') = 'AdministratorAccess';

List all the users for whom MFA is not enabled

Discover the users who have not enabled multi-factor authentication, allowing you to identify potential security risks and ensure all accounts are adequately protected.

select
name,
user_id,
mfa_enabled
from
aws_iam_user
where
not mfa_enabled;
select
name,
user_id,
mfa_enabled
from
aws_iam_user
where
mfa_enabled = 0;

List the policies attached to each IAM user

Determine the areas in which specific access controls are applied by identifying the policies attached to each user in your AWS IAM service. This can help ensure appropriate security measures are in place and assist in auditing user access rights.

select
name as user_name,
split_part(attachments, '/', 2) as attached_policies
from
aws_iam_user
cross join jsonb_array_elements_text(attached_policy_arns) as attachments;
select
name as user_name,
json_extract(attachments, '$[1]') as attached_policies
from
aws_iam_user,
json_each(attached_policy_arns) as attachments;

Find users that have inline policies

Identify instances where AWS IAM users have inline policies attached to their accounts. This is useful for security audits, as inline policies can grant or deny permissions to AWS services and resources.

select
name as user_name,
inline_policies
from
aws_iam_user
where
inline_policies is not null;
select
name as user_name,
inline_policies
from
aws_iam_user
where
inline_policies is not null;

Schema for aws_iam_user

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntext=The Amazon Resource Name (ARN) that identifies the user.
attached_policy_arnsjsonbA list of managed policies attached to the user.
create_datetimestamp with time zoneThe date and time, when the user was created.
groupsjsonbA list of groups attached to the user.
inline_policiesjsonbA list of policy documents that are embedded as inline policies for the user.
inline_policies_stdjsonbInline policies in canonical form for the user.
login_profilejsonbContains the user name and password create date for a user.
mfa_devicesjsonbA list of MFA devices attached to the user.
mfa_enabledbooleanThe MFA status of the user.
nametext=The friendly name identifying the user.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
password_last_usedtimestamp with time zoneThe date and time, when the user's password was last used to sign in to an AWS website.
pathtext=The path to the user.
permissions_boundary_arntextThe ARN of the policy used to set the permissions boundary for the user.
permissions_boundary_typetextThe permissions boundary usage type that indicates what type of IAM resource is used as the permissions boundary for an entity. This data type can only have a value of Policy.
regiontextThe AWS Region in which the resource is located.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags that are attached to the user.
titletextTitle of the resource.
user_idtextThe stable and unique string identifying 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_user