Table: azuread_service_principal_app_role_assigned_to - Query Application Role Assignments granted for Azure Active Directory Service Principal 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_service_principal_app_role_assigned_to table provides insights application roles assigned for service principals 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 granted app permissions, aiding in the management and security of your organization's resources.

Examples

Basic info

Explore which Application Role Assignments are granted for an Azure Active Directory service principal. This can be useful for understanding which principles can access the application represented by the service principal.

select
  resource_id,
  resource_display_name,
  app_role_id,
  principal_id,
  principal_type,
  principal_display_name,
  created_date_time,
  deleted_date_time
from
  azuread_service_principal_app_role_assigned_to
where
  service_principal_id = '<service_principal_id>';

select
  resource_id,
  resource_display_name,
  app_role_id,
  principal_id,
  principal_type,
  principal_display_name,
  created_date_time,
  deleted_date_time
from
  azuread_service_principal_app_role_assigned_to
where
  service_principal_id = '<service_principal_id>';

List all application role assignments granted for service principals

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

select
  azuread_service_principal_app_role_assigned_to.service_principal_id,
  azuread_service_principal_app_role_assigned_to.resource_id,
  azuread_service_principal_app_role_assigned_to.resource_display_name,
  azuread_service_principal_app_role_assigned_to.app_role_id,
  azuread_service_principal_app_role_assigned_to.created_date_time,
  azuread_service_principal_app_role_assigned_to.deleted_date_time
from
  azuread_service_principal
  join azuread_service_principal_app_role_assigned_to on azuread_service_principal_app_role_assigned_to.service_principal_id = azuread_service_principal.id;

select
  azuread_service_principal_app_role_assigned_to.service_principal_id,
  azuread_service_principal_app_role_assigned_to.resource_id,
  azuread_service_principal_app_role_assigned_to.resource_display_name,
  azuread_service_principal_app_role_assigned_to.app_role_id,
  azuread_service_principal_app_role_assigned_to.created_date_time,
  azuread_service_principal_app_role_assigned_to.deleted_date_time
from
  azuread_service_principal
  join azuread_service_principal_app_role_assigned_to on azuread_service_principal_app_role_assigned_to.service_principal_id = azuread_service_principal.id;

Schema for azuread_service_principal_app_role_assigned_to

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.
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.
service_principal_id	text	=	The identifier (id) of the service principal.
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_service_principal_app_role_assigned_to