Table: alicloud_ram_role

A RAM role is a virtual RAM identity that you can create in your Alibaba Cloud account. A RAM role does not have a specific logon password or AccessKey pair. A RAM user can be used only after the RAM user is assumed by a trusted entity.

Examples

List the policies attached to the roles

select
  name,
  policies ->> 'PolicyName' as policy_name,
  policies ->> 'PolicyType' as policy_type,
  policies ->> 'DefaultVersion' as policy_default_version,
  policies ->> 'AttachDate' as policy_attachment_date
from
  alicloud_ram_role,
  jsonb_array_elements(attached_policy) as policies
order by
  name;

Find all roles having Administrator access

select
  name,
  policies ->> 'PolicyName' as policy_name
from
  alicloud_ram_role,
  jsonb_array_elements(attached_policy) as policies
where
  policies ->> 'PolicyName' = 'AdministratorAccess';

Find all roles grant cross-account access in the Trust Policy

select
  name,
  principal,
  split_part(principal, ':', 4) as foreign_account
from
  alicloud_ram_role,
  jsonb_array_elements(assume_role_policy_document -> 'Statement') as stmt,
  jsonb_array_elements_text(stmt -> 'Principal' -> 'RAM') as principal
where
  split_part(principal, ':', 4) <> account_id;

Query examples

.inspect alicloud_ram_role

Resource Access Management roles who can login via the console or access keys.

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	The Alicloud Account ID in which the resource is located.
akas	jsonb	Array of globally unique identifier strings (also known as) for the resource.
arn	text	The Alibaba Cloud Resource Name (ARN) of the RAM role.
assume_role_policy_document	jsonb	The content of the policy that specifies one or more entities entrusted to assume the RAM role.
assume_role_policy_document_std	jsonb	The standard content of the policy that specifies one or more entities entrusted to assume the RAM role.
attached_policy	jsonb	A list of policies attached to a RAM role.
create_date	timestamp with time zone	The time when the RAM role was created.
description	text	The description of the RAM role.
max_session_duration	bigint	The maximum session duration of the RAM role.
name	text	The name of the RAM role.
region	text	The Alicloud region in which the resource is located.
role_id	text	The ID of the RAM role.
title	text	Title of the resource.
update_date	timestamp with time zone	The time when the RAM role was modified.

turbot/alicloud