Table: azuread_group_app_role_assignment - Query Application Role Assignments granted to Azure Active Directory Group using SQL

Azure Active Directory (Azure AD) is Microsoft's multi-tenant, cloud-based directory, and identity management service. It combines core directory services, application access management, and identity protection into a single solution. Azure AD also offers a rich, standards-based platform that enables developers to deliver access control to their applications, based on centralized policy and rules.

Table Usage Guide

The azuread_group_app_role_assignment table provides insights into application roles assigned to groups within Microsoft's Azure Active Directory. As an IT administrator, you can explore app role assignment-specific details through this table, including the application ID, display name, role, and more. Utilize it to uncover group app permissions, aiding in the management and security of your organization's resources.

Examples

Basic info

Explore which Application Role Assignments are granted to an Azure Active Directory group. This can be useful for understanding what applications are available to group members.

select
  resource_id,
  resource_display_name,
  app_role_id,
  created_date_time,
  deleted_date_time
from
  azuread_group_app_role_assignment
where
  group_id = '<group_id>';

select
  resource_id,
  resource_display_name,
  app_role_id,
  created_date_time,
  deleted_date_time
from
  azuread_group_app_role_assignment
where
  group_id = '<group_id>';

List all application role assignments granted to groups

Explore which groups in your Azure Active Directory have Application Role Assignments. This information is useful for auditing purposes and ensuring adherence to security protocols.

select
  azuread_group_app_role_assignment.group_id,
  azuread_group_app_role_assignment.resource_id,
  azuread_group_app_role_assignment.resource_display_name,
  azuread_group_app_role_assignment.app_role_id,
  azuread_group_app_role_assignment.created_date_time,
  azuread_group_app_role_assignment.deleted_date_time
from
  azuread_group
  join azuread_group_app_role_assignment on azuread_group_app_role_assignment.group_id = azuread_group.id;

select
  azuread_group_app_role_assignment.group_id,
  azuread_group_app_role_assignment.resource_id,
  azuread_group_app_role_assignment.resource_display_name,
  azuread_group_app_role_assignment.app_role_id,
  azuread_group_app_role_assignment.created_date_time,
  azuread_group_app_role_assignment.deleted_date_time
from
  azuread_group
  join azuread_group_app_role_assignment on azuread_group_app_role_assignment.group_id = azuread_group.id;

Schema for azuread_group_app_role_assignment

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
app_role_id	text		The identifier (id) for the app role which is assigned to the principal. This app role must be exposed in the appRoles property on the resource application's service principal (resourceId). If the resource application has not declared any app roles, a default app role ID of 00000000-0000-0000-0000-000000000000 can be specified to signal that the principal is assigned to the resource app without any specific app roles.
created_date_time	timestamp with time zone		The time when the app role assignment was created. The Timestamp type represents date and time information using ISO 8601 format and is always in UTC time. For example, midnight UTC on Jan 1, 2014 is 2014-01-01T00:00:00Z.
deleted_date_time	timestamp with time zone		The date and time when the app role assignment was deleted. Always null for an appRoleAssignment object that hasn't been deleted.
group_id	text	=	The identifier (id) of the group.
id	text	=	A unique identifier for the appRoleAssignment key.
principal_display_name	text	=	The display name of the user, group, or service principal that was granted the app role assignment.
principal_id	text		The unique identifier (id) for the user, security group, or service principal being granted the app role.
principal_type	text		The type of the assigned principal. This can either be User, Group, or ServicePrincipal.
resource_display_name	text		The display name of the resource app's service principal to which the assignment is made.
resource_id	text	=	The unique identifier (id) for the resource service principal for which the assignment is made.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.

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)" -- azuread

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

steampipe_export_azuread --config '<your_config>' azuread_group_app_role_assignment