Table: aws_iam_access_advisor
Access Advisor returns details about when an IAM principal (user, group, role, or policy) was last used in an attempt to access AWS services. Recent activity usually appears within four hours. IAM reports activity for the last 365 days, or less if your Region began supporting this feature within the last year.
Important Notes:
You must specify a single
principal_arn
in awhere
orjoin
clause in order to use this table.The service last accessed data includes all attempts to access an AWS API, not just the successful ones. This includes all attempts that were 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
select principal_arn, service_name, last_authenticated, age(last_authenticated :: date)from aws_iam_access_advisorwhere principal_arn = 'arn:aws:iam::123456789123:user/john' and last_authenticated is not nullorder by age asc;
Show unused services granted a user, role, group, or policy (unused in the last year)
select principal_arn, service_namefrom aws_iam_access_advisorwhere principal_arn = 'arn:aws:iam::123456789123:role/turbot/admin' and last_authenticated is nullorder by service_name
Show the last time a policy was used to access services, and the user, role, or group that used it
select principal_arn, service_name, last_authenticated, age(last_authenticated :: date), last_authenticated_entity, last_authenticated_regionfrom aws_iam_access_advisorwhere principal_arn = 'arn:aws:iam::aws:policy/AdministratorAccess' and last_authenticated is not nullorder by age asc;
Show unused services granted to a role, including the policy that grants access and the actions granted
select adv.service_name, action as action_granted, attached as granted_in, adv.service_namespacefrom 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 actionwhere 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;
Show action-level last accessed info (currently, only supported for S3)
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_timefrom aws_iam_access_advisor, jsonb_array_elements(tracked_actions_last_accessed) as awhere 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
select principal_arn, service_namefrom aws_iam_user as u, aws_iam_access_advisor as advwhere adv.principal_arn = u.arn and last_authenticated is null;
Query examples
- iam_user_excessive_permissions_count
- iam_user_excessive_permissions_report
- iam_user_with_excessive_permissions_count
.inspect aws_iam_access_advisor
AWS IAM Access Advisor
Name | Type | Description |
---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. |
account_id | text | The AWS Account ID in which the resource is located. |
last_authenticated | timestamp with time zone | The date and time when an authenticated entity most recently attempted to access the service. AWS does not report unauthenticated requests. |
last_authenticated_entity | text | The ARN of the authenticated entity (user or role) that last attempted to access the service. AWS does not report unauthenticated requests. |
last_authenticated_region | text | The Region from which the authenticated entity (user or role) last attempted to access the service. AWS does not report unauthenticated requests. |
partition | text | The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov). |
principal_arn | text | 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. |
region | text | The AWS Region in which the resource is located. |
service_name | text | The name of the service in which access was attempted. |
service_namespace | text | The namespace of the service in which access was attempted. |
total_authenticated_entities | bigint | The total number of authenticated principals (root user, IAM users, or IAM roles) that have attempted to access the service. |
tracked_actions_last_accessed | jsonb | An 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. |