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.
You must specify a single
joinclause 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.
Show the most recently used services for a user, role, group, or policy
selectprincipal_arn,service_name,last_authenticated,age(last_authenticated :: date)fromaws_iam_access_advisorwhereprincipal_arn = 'arn:aws:iam::123456789123:user/john'and last_authenticated is not nullorder byage asc;
Show unused services granted a user, role, group, or policy (unused in the last year)
selectprincipal_arn,service_namefromaws_iam_access_advisorwhereprincipal_arn = 'arn:aws:iam::123456789123:role/turbot/admin'and last_authenticated is nullorder byservice_name
Show the last time a policy was used to access services, and the user, role, or group that used it
selectprincipal_arn,service_name,last_authenticated,age(last_authenticated :: date),last_authenticated_entity,last_authenticated_regionfromaws_iam_access_advisorwhereprincipal_arn = 'arn:aws:iam::aws:policy/AdministratorAccess'and last_authenticated is not nullorder byage asc;
Show unused services granted to a role, including the policy that grants access and the actions granted
selectadv.service_name,action as action_granted,attached as granted_in,adv.service_namespacefromaws_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 actionwhereprincipal_arn = 'arn:aws:iam::123456789123:role/turbot/admin'and r.arn = adv.principal_arnand last_authenticated is nulland attached = p.arnand stmt ->> 'Effect' = 'Allow'and action like adv.service_namespace || ':%'order byadv.service_name;
Show action-level last accessed info (currently, only supported for S3)
selectprincipal_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_timefromaws_iam_access_advisor,jsonb_array_elements(tracked_actions_last_accessed) as awhereprincipal_arn = 'arn:aws:iam::123456789123:user/jane'and last_authenticated is not nulland service_namespace = 's3'order byage asc;
For all users in the account, find unused services
selectprincipal_arn,service_namefromaws_iam_user as u,aws_iam_access_advisor as advwhereadv.principal_arn = u.arnand last_authenticated is null;
AWS IAM Access Advisor
|_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.|