steampipe plugin install aws

Table: aws_iam_access_advisor - Query AWS IAM Access Advisor using SQL

The AWS IAM Access Advisor is a tool that helps you view and understand the permissions granted to your IAM entities (users, groups of users, or roles). It provides you with service last accessed information to help you refine your IAM policies. This service can be queried using SQL to fetch detailed insights.

Table Usage Guide

The aws_iam_access_advisor table in Steampipe provides you with information about the service last accessed data for IAM entities like users, groups, and roles within AWS Identity and Access Management (IAM). This table allows you, as a DevOps engineer, to query details about the services that IAM entities can access, the actions they can perform, and when they last accessed the services. You can utilize this table to gather insights on access patterns, such as identifying unused permissions or verifying least privilege policies. The schema outlines the various attributes of the IAM entity, including the entity ARN, last accessed time, and the services accessible.

Important Notes

  • You must specify a single principal_arn in a where or join clause in order to use this table.

  • The service last accessed data includes all your attempts to access an AWS API, not just the successful ones. This includes all attempts that you have made using the AWS Management Console, the AWS API through any of the SDKs, or any of the command line tools. An unexpected entry in the service last accessed data does not mean that your account has been compromised, because the request might have been denied. Refer to your CloudTrail logs as the authoritative source for information about all API calls and whether they were successful or denied access.

  • Service last accessed data does not use other policy types when determining whether a resource could access a service. These other policy types include resource-based policies, access control lists, AWS Organizations policies, IAM permissions boundaries, and AWS STS assume role policies. It only applies permissions policy logic. For more about the evaluation of policy types, see Evaluating Policies in the IAM User Guide.

Examples

Show the most recently used services for a user, role, group, or policy

Discover the segments that have recently interacted with a specific user, role, group, or policy. This helps in understanding the usage pattern and can aid in optimizing resource allocation.

select
principal_arn,
service_name,
last_authenticated,
age(last_authenticated :: date)
from
aws_iam_access_advisor
where
principal_arn = 'arn:aws:iam::123456789123:user/john'
and last_authenticated is not null
order by
age asc;
select
principal_arn,
service_name,
last_authenticated,
julianday('now') - julianday(last_authenticated) as age
from
aws_iam_access_advisor
where
principal_arn = 'arn:aws:iam::123456789123:user/john'
and last_authenticated is not null
order by
age asc;

Show unused services granted a user, role, group, or policy (unused in the last year)

This example helps to identify unused services that have been granted to a user, role, group, or policy, specifically those that have not been used in the past year. This is useful for maintaining security and efficiency by ensuring that unnecessary access permissions are revoked.

select
principal_arn,
service_name
from
aws_iam_access_advisor
where
principal_arn = 'arn:aws:iam::123456789123:role/turbot/admin'
and last_authenticated is null
order by
service_name
select
principal_arn,
service_name
from
aws_iam_access_advisor
where
principal_arn = 'arn:aws:iam::123456789123:role/turbot/admin'
and last_authenticated is null
order by
service_name

Show the last time a policy was used to access services, and the user, role, or group that used it

Determine the last time a specific policy was used to access services in AWS, along with the user, role, or group that used it. This can help in assessing the activity and access patterns related to your AWS resources, aiding in their management and security.

select
principal_arn,
service_name,
last_authenticated,
age(last_authenticated :: date),
last_authenticated_entity,
last_authenticated_region
from
aws_iam_access_advisor
where
principal_arn = 'arn:aws:iam::aws:policy/AdministratorAccess'
and last_authenticated is not null
order by
age asc;
select
principal_arn,
service_name,
last_authenticated,
julianday('now') - julianday(last_authenticated),
last_authenticated_entity,
last_authenticated_region
from
aws_iam_access_advisor
where
principal_arn = 'arn:aws:iam::aws:policy/AdministratorAccess'
and last_authenticated is not null
order by
julianday('now') - julianday(last_authenticated) asc;

Show unused services granted to a role, including the policy that grants access and the actions granted

Determine the areas in which services have been granted to a role but remain unused, including the specific policy that provides access and the actions permitted. This is particularly useful for identifying potential security risks and optimizing resource usage by revoking unnecessary permissions.

select
adv.service_name,
action as action_granted,
attached as granted_in,
adv.service_namespace
from
aws_iam_access_advisor as adv,
aws_iam_role as r,
jsonb_array_elements_text(r.attached_policy_arns) as attached,
aws_iam_policy as p,
jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Action') as action
where
principal_arn = 'arn:aws:iam::123456789123:role/turbot/admin'
and r.arn = adv.principal_arn
and last_authenticated is null
and attached = p.arn
and stmt ->> 'Effect' = 'Allow'
and action like adv.service_namespace || ':%'
order by
adv.service_name;
select
adv.service_name,
action.value as action_granted,
attached.value as granted_in,
adv.service_namespace
from
aws_iam_access_advisor as adv,
aws_iam_role as r,
json_each(r.attached_policy_arns) as attached,
aws_iam_policy as p,
json_each(p.policy_std, '$.Statement') as stmt,
json_each(stmt.value, '$.Action') as action
where
principal_arn = 'arn:aws:iam::123456789123:role/turbot/admin'
and r.arn = adv.principal_arn
and last_authenticated is null
and attached.value = p.arn
and json_extract(stmt.value, '$.Effect') = 'Allow'
and action.value like adv.service_namespace || ':%'
order by
adv.service_name;

Show action-level last accessed info (currently, only supported for S3)

This query allows you to gain insights into the most recent actions taken within the S3 service for a specific user. It helps in identifying key details such as the action name, the entity accessed, the region of access, and the time of access, which can be useful for auditing and security purposes.

select
principal_arn,
service_name,
last_authenticated,
age(last_authenticated :: date),
a ->> 'ActionName' as action_name,
a ->> 'LastAccessedEntity' as action_last_accessed_entity,
a ->> 'LastAccessedRegion' as action_last_accessed_region,
a ->> 'LastAccessedTime' as action_last_accessed_time
from
aws_iam_access_advisor,
jsonb_array_elements(tracked_actions_last_accessed) as a
where
principal_arn = 'arn:aws:iam::123456789123:user/jane'
and last_authenticated is not null
and service_namespace = 's3'
order by
age asc;
select
principal_arn,
service_name,
last_authenticated,
julianday('now') - julianday(last_authenticated) as age,
json_extract(a.value, '$.ActionName') as action_name,
json_extract(a.value, '$.LastAccessedEntity') as action_last_accessed_entity,
json_extract(a.value, '$.LastAccessedRegion') as action_last_accessed_region,
json_extract(a.value, '$.LastAccessedTime') as action_last_accessed_time
from
aws_iam_access_advisor,
json_each(tracked_actions_last_accessed) as a
where
principal_arn = 'arn:aws:iam::123456789123:user/jane'
and last_authenticated is not null
and service_namespace = 's3'
order by
age asc;

For all users in the account, find unused services

This query helps you identify the services associated with each user in your account that have not been used. This is particularly useful for optimizing resource allocation and ensuring security by minimizing unnecessary access permissions.

select
principal_arn,
service_name
from
aws_iam_user as u,
aws_iam_access_advisor as adv
where
adv.principal_arn = u.arn
and last_authenticated is null;
select
principal_arn,
service_name
from
aws_iam_user as u,
aws_iam_access_advisor as adv
where
adv.principal_arn = u.arn
and last_authenticated is null;

Schema for aws_iam_access_advisor

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
last_authenticatedtimestamp with time zoneThe date and time when an authenticated entity most recently attempted to access the service. AWS does not report unauthenticated requests.
last_authenticated_entitytextThe ARN of the authenticated entity (user or role) that last attempted to access the service. AWS does not report unauthenticated requests.
last_authenticated_regiontextThe Region from which the authenticated entity (user or role) last attempted to access the service. AWS does not report unauthenticated requests.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
principal_arntext=The ARN of the IAM resource (user, group, role, or managed policy) used to generate information about when the resource was last used in an attempt to access an AWS service.
regiontextThe AWS Region in which the resource is located.
service_nametextThe name of the service in which access was attempted.
service_namespacetextThe namespace of the service in which access was attempted.
total_authenticated_entitiesbigintThe total number of authenticated principals (root user, IAM users, or IAM roles) that have attempted to access the service.
tracked_actions_last_accessedjsonbAn array of objects that contains details about the most recent attempt to access a tracked action within the service. Currently, only S3 supports action level tracking.

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_access_advisor