turbot/alicloud_insights

Query: ram_user_manage_policies_sankey

Usage

powerpipe query alicloud_insights.query.ram_user_manage_policies_sankey

SQL

with args as (
select $1 as ram_user_arn
)
-- User
select
null as from_id,
name as id,
title,
0 as depth,
'alicloud_ram_user' as category
from
alicloud_ram_user
where
arn in (select ram_user_arn from args)
and account_id in (select split_part(ram_user_arn,':',4) from args)
-- Groups
union select
u.name as from_id,
g.name as id,
user_groups ->> 'GroupName' as title,
1 as depth,
'alicloud_ram_group' as category
from
alicloud_ram_user as u,
jsonb_array_elements(u.groups) as user_groups
inner join alicloud_ram_group g on g.name = user_groups ->> 'GroupName'
where
u.arn in (select ram_user_arn from args)
and u.account_id in (select split_part(ram_user_arn,':',4) from args)
-- Policies (attached to groups)
union select
g.name as from_id,
p.title as id,
p.title as title,
2 as depth,
'alicloud_ram_policy' as category
from
alicloud_ram_user as u,
alicloud_ram_policy as p,
jsonb_array_elements(u.groups) as user_groups
inner join alicloud_ram_group g on g.name = user_groups ->> 'GroupName',
jsonb_array_elements(g.attached_policy) as user_policy
where
user_policy ->> 'PolicyName' = p.title
and u.arn in (select ram_user_arn from args)
and u.account_id in (select split_part(ram_user_arn,':',4) from args)
-- Policies (attached to user)
union select
u.name as from_id,
p.title as id,
p.title as title,
2 as depth,
'alicloud_ram_policy' as category
from
alicloud_ram_user as u,
jsonb_array_elements(u.attached_policy) as pol_arn,
alicloud_ram_policy as p
where
pol_arn ->> 'PolicyName' = p.title
and u.arn in (select ram_user_arn from args)
and u.account_id in (select split_part(ram_user_arn,':',4) from args);

Dashboards

The query is used in the dashboards: