Use a managed identity to run an update policy

Applies to: ✅ Azure Data Explorer

The update policy must be configured with a managed identity in the following scenarios:

• When the update policy query references tables in other databases
• When the update policy query references tables with an enabled row level security policy

An update policy configured with a managed identity is performed on behalf of the managed identity.

In this article, you learn how to configure a system-assigned or user-assigned managed identity and create an update policy using that identity.

Prerequisites

• A cluster and database Create a cluster and database.
• AllDatabasesAdmin permissions on the database.

Configure a managed identity

There are two types of managed identities:

• System-assigned: A system-assigned identity is connected to your cluster and is removed when the cluster is removed. Only one system-assigned identity is allowed per cluster.

• User-assigned: A user-assigned managed identity is a standalone Azure resource. Multiple user-assigned identities can be assigned to your cluster.

Select one of the following tabs to set up your preferred managed identity type.

User-assigned

1. Follow the steps to Add a user-assigned identity.

2. In the Azure portal, in the left menu of your managed identity resource, select Properties. Copy and save the Tenant Id and Principal ID for use in the following steps.

   

3. Run the following .alter-merge policy managed_identity command, replacing <objectId> with the managed identity Principal ID from the previous step. This command sets a managed identity policy on the cluster that allows the managed identity to be used with the update policy.

   .alter-merge cluster policy managed_identity ```[
       {
         "ObjectId": "<objectId>",
         "AllowedUsages": "AutomatedFlows"
       }
   ]```
   

   Note

   To set the policy on a specific database, use database <DatabaseName> instead of cluster.

4. Run the following command to grant the managed identity Database Viewer permissions over all databases referenced by the update policy query.

   .add database <DatabaseName> viewers ('aadapp=<objectId>;<tenantId>')
   

   Replace <DatabaseName> with the relevant database, <objectId> with the managed identity Principal ID from step 2, and <tenantId> with the Microsoft Entra ID Tenant Id from step 2.

System-assigned

1. Follow the steps to Add a system-assigned identity.

2. Copy and save the Object ID for use in a later step.

3. Run the following .alter-merge policy managed_identity command. This command sets a managed identity policy on the cluster that allows the managed identity to be used with the update policy.

   .alter-merge cluster policy managed_identity ```[
       {
         "ObjectId": "system",
         "AllowedUsages": "AutomatedFlows"
       }
   ]```
   

   Note

   To set the policy on a specific database, use database <DatabaseName> instead of cluster.

4. Run the following command to grant the managed identity Database Viewer permissions over all databases referenced by the update policy query.

   .add database <DatabaseName> viewers ('aadapp=<objectId>')
   

   Replace <DatabaseName> with the relevant database and <objectId> with the managed identity Object ID you saved earlier.

Create an update policy

Select one of the following tabs to create an update policy that runs on behalf of a user-assigned or system-assigned managed identity.

User-assigned

Run the .alter table policy update command with the ManagedIdentity property set to the managed identity object ID.

For example, the following command alters the update policy of the table MyTable in the database MyDatabase. It's important to note that both the Source and Query parameters should only reference objects within the same database where the update policy is defined. However, the code contained within the function specified in the Query parameter can interact with tables located in other databases. For example, the function MyUpdatePolicyFunction() can access OtherTable in OtherDatabase on behalf of a user-assigned managed identity. <objectId> should be a managed identity object ID.

.alter table MyDatabase.MyTable policy update
```
[
    {
        "IsEnabled": true,
        "Source": "MyTable",
        "Query": "MyUpdatePolicyFunction()",
        "IsTransactional": false,
        "PropagateIngestionProperties": false,
        "ManagedIdentity": "<objectId>"
    }
]
```

System-assigned

Run the .alter table policy update command with the ManagedIdentity property set to the managed identity object ID.

For example, the following command alters the update policy of the table MyTable in the database MyDatabase. It's important to note that both the Source and Query parameters should only reference objects within the same database where the update policy is defined. However, the code contained within the function specified in the Query parameter can interact with tables located in other databases. For example, the function MyUpdatePolicyFunction() can access OtherTable in OtherDatabase on behalf of your system-assigned managed identity.

.alter table MyDatabase.MyTable policy update
```
[
    {
        "IsEnabled": true,
        "Source": "MyTable",
        "Query": "MyUpdatePolicyFunction()",
        "IsTransactional": false,
        "PropagateIngestionProperties": false,
        "ManagedIdentity": "system"
    }
]
```