turbot/databricks
steampipe plugin install databricks

Table: databricks_iam_service_principal - Query Databricks IAM Service Principals using SQL

A Databricks IAM Service Principal is an identity that you can create in your Azure AD and assign roles to, allowing it to manage Azure resources. Service Principals are the Azure AD version of a service account, providing access to Azure resources within your organization. They can be used to automate tasks and manage Azure resources at scale.

Table Usage Guide

The databricks_iam_service_principal table provides insights into IAM Service Principals within Azure Databricks. As a DevOps engineer, explore Service Principal-specific details through this table, including roles, permissions, and associated metadata. Utilize it to uncover information about Service Principals, such as those with specific permissions, the roles assigned to them, and the resources they have access to.

Examples

Basic info

Explore which service principals are currently active in your Databricks environment. This can help in managing access and security within your applications.

select
id,
display_name,
active,
application_id,
account_id
from
databricks_iam_service_principal;
select
id,
display_name,
active,
application_id,
account_id
from
databricks_iam_service_principal;

List all inactive service principals

Discover the segments that consist of inactive service principals, allowing you to identify potential areas for optimization or decommissioning. This is particularly useful in managing resources and maintaining security within your Databricks IAM.

select
id,
display_name,
active,
application_id,
account_id
from
databricks_iam_service_principal
where
not active;
select
id,
display_name,
active,
application_id,
account_id
from
databricks_iam_service_principal
where
active = 0;

List assigned roles for each service principal

Explore the roles assigned to each service principal to understand their permissions and responsibilities within the Databricks IAM system. This is useful for auditing purposes and ensuring correct access levels are maintained.

select
u.id,
u.display_name,
r ->> 'value' as role,
r ->> 'type' as type,
u.account_id
from
databricks_iam_service_principal u,
jsonb_array_elements(roles) as r;
select
u.id,
u.display_name,
json_extract(r.value, '$.value') as role,
json_extract(r.value, '$.type') as type,
u.account_id
from
databricks_iam_service_principal u,
json_each(u.roles) as r;

List groups each service principal belongs to

Explore the affiliations of each service principal within a given system to understand their respective group memberships. This is useful for assessing access controls and managing permissions effectively.

select
u.id,
u.display_name,
r ->> 'display' as group_display_name,
r ->> 'value' as role,
r ->> 'type' as type,
u.account_id
from
databricks_iam_service_principal u,
jsonb_array_elements(groups) as r;
select
u.id,
u.display_name,
json_extract(r.value, '$.display') as group_display_name,
json_extract(r.value, '$.value') as role,
json_extract(r.value, '$.type') as type,
u.account_id
from
databricks_iam_service_principal u,
json_each(u.groups) as r;

Get service principal with a specific name

Explore which service principal corresponds to a specific user email. This is useful in identifying and managing user access and permissions in a Databricks environment.

select
id,
display_name,
active,
account_id
from
databricks_iam_service_principal
where
display_name = 'user@turbot.com';
select
id,
display_name,
active,
account_id
from
databricks_iam_service_principal
where
display_name = 'user@turbot.com';

List service principal entitlements

Explore which service principals have specific entitlements in your Databricks IAM setup. This can help you manage access and permissions effectively across different account holders.

select
id,
display_name,
r ->> 'value' as entitlement,
u.account_id
from
databricks_iam_service_principal u,
jsonb_array_elements(entitlements) as r;
select
u.id,
u.display_name,
json_extract(r.value, '$.value') as entitlement,
u.account_id
from
databricks_iam_service_principal u,
json_each(u.entitlements) as r;

Find the account with the most service principals

Explore which account utilizes the most service principals, aiding in the identification of potential areas of high resource usage or security concerns. This can be beneficial for optimizing resource allocation and enhancing security measures.

select
account_id,
count(*) as service_principal_count
from
databricks_iam_service_principal
group by
account_id
order by
service_principal_count desc
limit
1;
select
account_id,
count(*) as service_principal_count
from
databricks_iam_service_principal
group by
account_id
order by
service_principal_count desc
limit
1;

Schema for databricks_iam_service_principal

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The Databricks Account ID in which the resource is located.
activebooleanWhether the service principal is active.
application_idtextUUID relating to the service principal.
display_nametext=, !=String that represents a concatenation of given and family names.
entitlementsjsonbAll the entitlements associated with the service principal.
external_idtextExternal id of the service principal.
groupsjsonbAll the groups associated with the service principal.
idtext=Databricks service principal ID.
rolesjsonbAll the roles associated with the service principal.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
titletextThe title of the resource.

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

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

steampipe_export_databricks --config '<your_config>' databricks_iam_service_principal