turbot/alicloud
steampipe plugin install alicloud

Table: alicloud_ram_credential_report - Query Alicloud RAM Credential Reports using SQL

Alicloud RAM (Resource Access Management) is a service that helps you manage user identities and control their access to your resources. It allows you to create and manage multiple identities under your Alicloud account and grant permissions to these identities to access your Alicloud resources. The RAM Credential Report is a document that provides information about the credential security status of RAM users.

Table Usage Guide

The alicloud_ram_credential_report table provides insights into the credential security status of RAM users within Alicloud RAM. As a security administrator, explore user-specific details through this table, including password status, MFA device bindings, and access key usage. Utilize it to uncover information about users, such as those with high-risk passwords or inactive MFA devices, and to monitor the usage of access keys.

Examples

List users that have logged into the console in the past 90 days

Determine the areas in which users have been active in the past 90 days, focusing on those who have logged into the console. This can help in understanding user engagement and identifying patterns in user activity.

select
user_name,
user_last_logon
from
alicloud_ram_credential_report
where
password_exist
and password_active
and user_last_logon > (current_date - interval '90' day);
select
user_name,
user_last_logon
from
alicloud_ram_credential_report
where
password_exist = 1
and password_active = 1
and user_last_logon > date('now', '-90 day');

List users that have NOT logged into the console in the past 90 days

Determine the areas in which users have not been active for over 90 days, focusing on those with existing and active passwords. This is useful for identifying potentially dormant or unused accounts, helping to maintain security and efficiency within your system.

select
user_name,
user_last_logon,
age(user_last_logon)
from
alicloud_ram_credential_report
where
password_exist
and password_active
and user_last_logon <= (current_date - interval '90' day)
order by
user_last_logon;
select
user_name,
user_last_logon,
julianday('now') - julianday(user_last_logon) as age
from
alicloud_ram_credential_report
where
password_exist
and password_active
and date(user_last_logon) <= date(julianday('now'), '-90 day')
order by
user_last_logon;

List users with console access that have never logged in to the console

Determine the users who have console access but have never actually logged in. This can help identify unused accounts, enabling better management of user access and improving security by eliminating potential vulnerabilities.

select
user_name
from
alicloud_ram_credential_report
where
password_exist
and user_last_logon is null;
select
user_name
from
alicloud_ram_credential_report
where
password_exist = 1
and user_last_logon is null;

Find access keys older than 90 days

Identify instances where user access keys are older than 90 days to ensure secure and up-to-date access management. This is useful for maintaining security standards and preventing potential unauthorized access.

select
user_name,
access_key_1_last_rotated,
age(access_key_1_last_rotated) as access_key_1_age,
access_key_2_last_rotated,
age(access_key_2_last_rotated) as access_key_2_age
from
alicloud_ram_credential_report
where
access_key_1_last_rotated <= (current_date - interval '90' day)
or access_key_2_last_rotated <= (current_date - interval '90' day)
order by
user_name;
select
user_name,
access_key_1_last_rotated,
julianday('now') - julianday(access_key_1_last_rotated) as access_key_1_age,
access_key_2_last_rotated,
julianday('now') - julianday(access_key_2_last_rotated) as access_key_2_age
from
alicloud_ram_credential_report
where
julianday('now') - julianday(access_key_1_last_rotated) >= 90
or julianday('now') - julianday(access_key_2_last_rotated) >= 90
order by
user_name;

Find users that have a console password but do not have MFA enabled

Determine the areas in which users have an active console password but lack multi-factor authentication (MFA). This query is useful for identifying potential security risks within your Alicloud resource access management.

select
user_name,
mfa_active,
password_exist,
password_active
from
alicloud_ram_credential_report
where
password_exist
and password_active
and not mfa_active;
select
user_name,
mfa_active,
password_exist,
password_active
from
alicloud_ram_credential_report
where
password_exist = 1
and password_active = 1
and mfa_active = 0;

Check if root login has MFA enabled

Determine if multi-factor authentication (MFA) is activated for the root login, enhancing security by requiring an additional verification step during authentication.

select
user_name,
mfa_active
from
alicloud_ram_credential_report
where
user_name = '<root>';
select
user_name,
mfa_active
from
alicloud_ram_credential_report
where
user_name = '<root>';

Schema for alicloud_ram_credential_report

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
access_key_1_activebooleanIndicates whether the user access key is active, or not.
access_key_1_existbooleanIndicates whether the user have access key, or not.
access_key_1_last_rotatedtimestamp with time zoneSpecifies the time when the access key has been rotated.
access_key_1_last_usedtimestamp with time zoneSpecifies the time when the access key was most recently used to sign an Alicloud API request.
access_key_2_activebooleanIndicates whether the user access key is active, or not.
access_key_2_existbooleanIndicates whether the user have access key, or not.
access_key_2_last_rotatedtimestamp with time zoneSpecifies the time when the access key has been rotated.
access_key_2_last_usedtimestamp with time zoneSpecifies the time when the access key was most recently used to sign an Alicloud API request.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The Alicloud Account ID in which the resource is located.
additional_access_key_1_activebooleanIndicates whether the user access key is active, or not.
additional_access_key_1_existbooleanIndicates whether the user have access key, or not.
additional_access_key_1_last_rotatedtimestamp with time zoneSpecifies the time when the access key has been rotated.
additional_access_key_1_last_usedtimestamp with time zoneSpecifies the time when the access key was most recently used to sign an Alicloud API request.
additional_access_key_2_activebooleanIndicates whether the user access key is active, or not.
additional_access_key_2_existbooleanIndicates whether the user have access key, or not.
additional_access_key_2_last_rotatedtimestamp with time zoneSpecifies the time when the access key has been rotated.
additional_access_key_2_last_usedtimestamp with time zoneSpecifies the time when the access key was most recently used to sign an Alicloud API request.
additional_access_key_3_activebooleanIndicates whether the user access key is active, or not.
additional_access_key_3_existbooleanIndicates whether the user have access key, or not.
additional_access_key_3_last_rotatedtimestamp with time zoneSpecifies the time when the access key has been rotated.
additional_access_key_3_last_usedtimestamp with time zoneSpecifies the time when the access key was most recently used to sign an Alicloud API request.
generated_timetimestamp with time zoneSpecifies the time when the credential report has been generated.
mfa_activebooleanIndicates whether multi-factor authentication (MFA) device has been enabled for the user.
password_activebooleanIndicates whether the password is active, or not.
password_existbooleanIndicates whether the user have any password for logging in, or not.
password_last_changedtimestamp with time zoneSpecifies the time when the password has been updated.
password_next_rotationtimestamp with time zoneSpecifies the time when the password will be rotated.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
user_creation_timetimestamp with time zoneSpecifies the time when the user is created.
user_last_logontimestamp with time zoneSpecifies the time when the user last logged in to the console.
user_nametextThe email of the RAM user.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- alicloud

You can pass the configuration to the command with the --config argument:

steampipe_export_alicloud --config '<your_config>' alicloud_ram_credential_report