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_idfrom databricks_iam_service_principal;
select id, display_name, active, application_id, account_idfrom 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_idfrom databricks_iam_service_principalwhere not active;
select id, display_name, active, application_id, account_idfrom databricks_iam_service_principalwhere 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_idfrom 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_idfrom 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_idfrom 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_idfrom 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_idfrom databricks_iam_service_principalwhere display_name = 'user@turbot.com';
select id, display_name, active, account_idfrom databricks_iam_service_principalwhere 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_idfrom 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_idfrom 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_countfrom databricks_iam_service_principalgroup by account_idorder by service_principal_count desclimit 1;
select account_id, count(*) as service_principal_countfrom databricks_iam_service_principalgroup by account_idorder by service_principal_count desclimit 1;
Schema for databricks_iam_service_principal
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
account_id | text | =, !=, ~~, ~~*, !~~, !~~* | The Databricks Account ID in which the resource is located. |
active | boolean | Whether the service principal is active. | |
application_id | text | UUID relating to the service principal. | |
display_name | text | =, != | String that represents a concatenation of given and family names. |
entitlements | jsonb | All the entitlements associated with the service principal. | |
external_id | text | External id of the service principal. | |
groups | jsonb | All the groups associated with the service principal. | |
id | text | = | Databricks service principal ID. |
roles | jsonb | All the roles associated with the service principal. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
title | text | The 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