steampipe-plugin-aws: [Turbot / AWS] table 'aws_ssoadmin_account_assignment' doesn't work for IAM Identity Center (formerly known as AWS SSO)

Describe the bug A clear and concise description of what the bug is. According to the site (https://hub.steampipe.io/plugins/turbot/aws/tables/aws_ssoadmin_account_assignment), the examples could not work and will trigger errors:

"Error: operation error SSO Admin: ListAccountAssignments, https response error StatusCode: 400, RequestID: 6dbdc0da-dcdb-47d5-980c-e5eaf08ca47a, AccessDeniedExcept"
+--------------------+-------------------+----------------+--------------+
| permission_set_arn | target_account_id | principal_type | principal_id |
+--------------------+-------------------+----------------+--------------+
+--------------------+-------------------+----------------+--------------+

The error above will show multiple lines based on the number of aws accounts configured. Note that my IAM user assumed role has full administrative access and my steampipe dashboard works okey for all the AWS dashboard mods except tables pertaining to ‘aws_ssoadmin’

Some users together with me has raise this over at Steampipe slack channel as well.

Steampipe version (steampipe -v) Example: Steampipe v0.19.5

To reproduce Steps to reproduce the behavior (please include relevant code and/or commands).

steampipe query select permission_set_arn, target_account_id, identity_store_id, principal_type, principal_id, from aws_ssoadmin_account_assignment where permission_set_arn = ‘arn:aws:sso:::permissionSet/ssoins-0123456789abcdef/ps-0123456789abcdef’ and target_account_id = ‘012347678910’;

Expected behavior Display the following information in a command line table:

permission_set_arn,
target_account_id,
identity_store_id,
principal_type,
principal_id,

Additional context I could be wrong, AWS has some undocumented APIs for IAM Identity Center (formerly know as AWS SSO). Hence there is still no console dashboard. My objective will be to create an IAM Identity Center dashboard to display AWS accounts mapped to with which users and which PermissionSets.

Affected tables:

About this issue

Original URL
State: closed
Created a year ago
Comments: 21 (8 by maintainers)

Most upvoted comments

Hi @vkumbha @ParthaI and @rajlearner17,

Good Day!

Here is the code which i have used for less than 10 AWS accounts, but once it tested with more aws accounts which i tried ( >90 aws accounts) ,took 46 mins to run.

Hope anyone can optimize further of a greater usage for IAM Identity Center !

with orgs as (
    SELECT id FROM aws_master.aws_organizations_account
  )
  , 
  aws_ssoadmin_principal as (
  select
    i.arn as instance_arn,
    'GROUP' as "type",
    g.id,
    g.title
  from
    aws_master.aws_ssoadmin_instance i
    left join aws_identitystore_group g on i.identity_store_id = g.identity_store_id
  union
  select
    i.arn as instance_arn,
    'USER' as "type",
    u.id,
    u.title
  from
    aws_master.aws_ssoadmin_instance i
    left join aws_identitystore_user u on i.identity_store_id = u.identity_store_id
)

SELECT
  a.name as "AWS Account Name",
  a.id as "Account ID",
  u.title as "Username/GroupName",
  g.type as "Type",
  p.name as "PermissionSet",
  p.description as "PermissionSet Description"
FROM
  aws_master.aws_organizations_account a
  LEFT JOIN aws_master.aws_ssoadmin_account_assignment aa ON aa.target_account_id = a.id
  
  JOIN aws_ssoadmin_principal u ON u.id = aa.principal_id 
  LEFT JOIN aws_ssoadmin_principal g ON g.id = u.id
  
  JOIN aws_master.aws_ssoadmin_permission_set p ON p.arn = aa.permission_set_arn
WHERE
  permission_set_arn IN (SELECT p.arn FROM aws_master.aws_ssoadmin_permission_set)
  and target_account_id IN (SELECT a.id FROM orgs)
  ORDER BY
    a.name DESC;

Appreciate any help! Thanks so much

10 aws account -> took 10mins to load 90 aws account -> took 46 mins to load

ahkai86 on Jun 1, 2023

Hi @vkumbha , sure! Let me list the code here in a bit as there are some impediments when running more than 10 aws accounts. I am still doing various testing

ahkai86 on May 29, 2023

Hi @ParthaI ,

Got it solved, the connection name has to be lowercase. It is case sensitive =)

Able to ignore case sensitive connection name? The aggregator mapped it according to AWS Account name under AWS Organization.

ahkai86 on May 16, 2023