Extract Access Details of Security Principals in Microsoft Fabric
Updated
•9 min read
In my previous article, I showed how to retrieve item-level access for users for an Entra account. I had utilized the Graph API to fetch a list of users from the Entra account and then fetched all the underlying permissions across all entities in Fabric for those users.
The limitation of that approach was that it couldn't retrieve accesses for service principals and Entra groups in Fabric. In this article I have used a different method to overcome those limitations.
In the previous approach I had used the List Access Entities API. I was retrieving the userId from the GraphAPI and assigning the userId values returned by the Graph API and assigning it to the corresponding userId identifier in the API. Unfortunately the API does not recognize service principal or the Group Id values returned by the Graph API.
For instance, if the Graph API returns a service principal ID of 123 and we then use this value (123) against the userId identifier in the API, it doesn’t return any results because it doesn’t recognize 123 as an existing userId. This was a biggest flaw in the approach.
In this new approach what I have done is that, I first fetch a list of all the items that exist in a given workspace using the List Items API and I then fetch the security principals that have access to these items using the List Item Access Details API.
This approach now provides comprehensive details of user access for a given item, in addition to identifying the user's type.
API response
Just like other API methods of Fabric, this API also has a limit to the number of requests that could be sent over a given duration .The limit is 200 requests in a hour.
To overcome this limit what I did was , I added a wait time of 5000 ms i.e. 5 seconds across each consecutive requests. The value of wait time should be configured on a case to case basis.
Probably the best approach would be to fetch access details across individual or a list of workspaces or items thereby limiting the number of http requests through the API. Inspite of the fact that the code is fetching details across all entities across all the workspaces, the code is quite flexible enough to accommodate the aforementioned requirements.
You can watch the video walkthrough here.
Setup
To get started, Install the following Nuget packages in a new Console application.Generation of Bearer token and authentication methods are similar to my previous articles.
dotnet add package Azure.Identity --version 1.8.0
dotnet add package Microsoft.Extensions.Configuration --version 6.0.0
dotnet add package Microsoft.Identity.Client --version 4.47.0
dotnet add package Newtonsoft.Json --version 13.0.4
dotnet add package Spectre.Console --version 0.49.1
Add the Client Id from the service principal in local.settings.json file and location to the csv file along with the path to the CSV file where user access details will be exported to.
{
"AllowedHosts": "*",
"ClientId": "Service Principal CLient Id",
"CsvLocation": "Location of the csv file"
}
Next, Grant the following access to the service principal account in Entra under which the code would run
Code
References used in the code
using Microsoft.Extensions.Configuration;
using Microsoft.Identity.Client;
using Newtonsoft.Json.Linq;
using Spectre.Console;
Variables
private static string clientId = "";
private static string[] scopes_f = new string[] { "https://api.fabric.microsoft.com/.default" };
private static string Authority = "https://login.microsoftonline.com/organizations";
private static string RedirectURI = "http://localhost";
private static string csvlocation = "";
private static string workspacename = "";
private static Dictionary<string, string> workspacelist = new();
private static Dictionary<string, string> itemlist = new();
private static Dictionary<string, string> lst = new ();
private static string endpoint = "https://api.fabric.microsoft.com/v1";
private static readonly HttpClient client = new HttpClient();
Read the config settings
public static void ReadConfig()
{
var builder = new ConfigurationBuilder()
.AddJsonFile($"local.settings.json", true, true);
var config = builder.Build();
clientId = config["ClientId"];
csvlocation = config["CsvLocation"];
}
Authenticate the request and generate a bearer token
public async static Task<AuthenticationResult> ReturnAuthenticationResult(string[] scopes)
{
;
PublicClientApplicationBuilder PublicClientAppBuilder =
PublicClientApplicationBuilder.Create(clientId)
.WithAuthority(Authority)
.WithCacheOptions(CacheOptions.EnableSharedCacheOptions)
.WithRedirectUri(RedirectURI);
IPublicClientApplication PublicClientApplication = PublicClientAppBuilder.Build();
var accounts = await PublicClientApplication.GetAccountsAsync();
AuthenticationResult result;
try
{
result = await PublicClientApplication.AcquireTokenSilent(scopes, accounts.First())
.ExecuteAsync()
.ConfigureAwait(false);
}
catch
{
result = await PublicClientApplication.AcquireTokenInteractive(scopes)
.ExecuteAsync()
.ConfigureAwait(false);
}
return result;
}
GET Async method
public async static Task<string> GetAsync(string url)
{
AuthenticationResult authenticationResult = await ReturnAuthenticationResult(scopes_f);
client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", authenticationResult.AccessToken);
HttpResponseMessage response = await client.GetAsync(url);
try
{
response.EnsureSuccessStatusCode();
return await response.Content.ReadAsStringAsync();
}
catch (HttpRequestException httpRequestException)
{
return null;
}
}
Write the data to csv file and replace empty values with NA
public static async void WriteToCsv(string path, string values)
{
string delimiter = ", ";
string[] parts = values.Split('~');
if (!File.Exists(path))
{
string createText = "Workspace " + delimiter + "ItemName " + delimiter + "ItemType " + delimiter + "SecurityPrincipalName" + delimiter + "SecurityPrincipalType" + delimiter + "Permissions" + delimiter + "AdditionalPermissions" + delimiter + Environment.NewLine;
File.WriteAllText(path, createText);
}
string appendText = parts[0].Split("||")[1].ToString() + delimiter + parts[1].Split("||")[1].ToString() + delimiter + await ReplaceInvalidValues(parts[2].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[3].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[4].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[5].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[6].Split("||")[1].ToString()) + delimiter + Environment.NewLine;
File.AppendAllText(path, appendText);
}
public static async Task<string> ReplaceInvalidValues(string s)
{
if (s == ("") || s == ("[]"))
return "NA";
else
return s;
}
Return the underlying user access for all items
public async static Task GetUserAccess()
{
string baseUrl = $"https://api.fabric.microsoft.com/v1/workspaces";
string response = await GetAsync(baseUrl);
JObject workspace_jobject = JObject.Parse(response);
JArray workspace_array = (JArray)workspace_jobject["value"];
AnsiConsole.MarkupLine($"Fetching a list of Workspaces");
foreach (JObject workspace_array_0 in workspace_array)
{
workspacelist.Add(workspace_array_0["displayName"].ToString(), workspace_array_0["id"].ToString()); //
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Fetched workspace [Red]{workspace_array_0["displayName"].ToString()}[/]");
Thread.Sleep(500);
}
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Done");
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Fetching a list of items across workspaces");
foreach (var workspace in workspacelist)
{
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Fetching items from the workspace [Red] {workspace.Key}[/]");
baseUrl = $"https://api.fabric.microsoft.com/v1/admin/items?workspaceid={workspace.Value}";
response = await GetAsync(baseUrl);
JObject item_jobject = JObject.Parse(response);
JArray item_array = (JArray)item_jobject["itemEntities"];
foreach (JObject item_array_0 in item_array)
{
itemlist.Add(workspace.Key + "~" + workspace.Value + "~" + item_array_0["name"].ToString(), item_array_0["id"].ToString());
}
}
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine("Done");
AnsiConsole.MarkupLine("");
int i = 0; int j = 0;
foreach (var item in itemlist)
{
AnsiConsole.MarkupLine($"Fetching access details for item [Red]{(itemlist.Keys).ToList()[i].ToString().Split("~")[^1]}[/] for workspace [Blue]{(itemlist.Keys).ToList()[i].ToString().Split("~")[^3]}[/] and exporting the details to csv located at [Red]{csvlocation}[/]");
AnsiConsole.MarkupLine("");
baseUrl = $"https://api.fabric.microsoft.com/v1/admin/workspaces/{(itemlist.Keys).ToList()[i].ToString().Split("~")[^2]}/items/{item.Value}/users";
response = await GetAsync(baseUrl);
Thread.Sleep(5000); // Wait for 5 seconds
JObject JObject = JObject.Parse(response);
JArray jarray_item = (JArray)JObject["accessDetails"];
foreach (JObject path in jarray_item)
{
JToken object_type = path["itemAccessDetails"]["type"];
JToken user = path["principal"]["displayName"];
JToken user_type = path["principal"]["type"];
JToken user_permissions = path["itemAccessDetails"]["permissions"];
string permissions = "";
foreach (JToken p in user_permissions)
{
permissions = permissions + ("/" + p.ToString()); p.ToString();
}
JToken additional_permissions = path["itemAccessDetails"]["additionalPermissions"];
string a_permissions = "";
foreach (JToken p in additional_permissions)
{
a_permissions = a_permissions + ("/" + p.ToString()); p.ToString();
}
lst.Add(j + "~" + (itemlist.Keys).ToList()[i].ToString().Split("~")[0] + "~" + (itemlist.Keys).ToList()[i].ToString().Split("~")[^1] + "~" + object_type, user + "~" + user_type + "~" + permissions + "~" + a_permissions);
j++;
}
AnsiConsole.MarkupLine($"Done");
AnsiConsole.MarkupLine("");
i++;
}
for (i = 0; i < lst.Keys.ToList().Count; i++)
{
WriteToCsv(csvlocation, "Workspace||" + lst.Keys.ToList()[i].ToString().Split("~")[^3] + "~ItemName||" + lst.Keys.ToList()[i].ToString().Split("~")[^2] + "~ItemType||" + lst.Keys.ToList()[i].ToString().Split("~")[^1] + "~UserName||" + lst.Values.ToList()[i].ToString().Split("~")[^4] + "~UserType||" + lst.Values.ToList()[i].ToString().Split("~")[^3] + "~Permissions||" + lst.Values.ToList()[i].ToString().Split("~")[^2] + "~AdditionalPermissions||" + lst.Values.ToList()[i].ToString().Split("~")[^1]);
i++;
}
}
The method above fetches access details of all the items for all security principals. It can be modified to fetch details for a single workspace or a list of workspaces or a single item or a list of specific items.
There are three API’s that are invoked in the above method
API to get a workspace list
API to get items across each workspace
API to fetch access details for each item in the workspace
Main method
static async Task Main(string[] args)
{
ReadConfig();
await GetUserAccess();
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"All details exported to the csv file located [Red]{csvlocation}[/]");
AnsiConsole.MarkupLine("");
}
Complete Code
using Microsoft.Extensions.Configuration;
using Microsoft.Identity.Client;
using Newtonsoft.Json.Linq;
using Spectre.Console;
namespace ItemAccess
{
internal class Program
{
private static string clientId = "";
private static string[] scopes_f = new string[] { "https://api.fabric.microsoft.com/.default" };
private static string Authority = "https://login.microsoftonline.com/organizations";
private static string RedirectURI = "http://localhost";
private static string csvlocation = "";
private static string workspacename = "";
private static Dictionary<string, string> workspacelist = new();
private static Dictionary<string, string> itemlist = new();
private static Dictionary<string, string> lst = new ();
private static string endpoint = "https://api.fabric.microsoft.com/v1";
private static readonly HttpClient client = new HttpClient();
static async Task Main(string[] args)
{
ReadConfig();
await GetUserAccess();
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"All details exported to the csv file located [Red]{csvlocation}[/]");
AnsiConsole.MarkupLine("");
}
public static async void WriteToCsv(string path, string values)
{
string delimiter = ", ";
string[] parts = values.Split('~');
if (!File.Exists(path))
{
string createText = "Workspace " + delimiter + "ItemName " + delimiter + "ItemType " + delimiter + "SecurityPrincipalName" + delimiter + "SecurityPrincipalType" + delimiter + "Permissions" + delimiter + "AdditionalPermissions" + delimiter + Environment.NewLine;
File.WriteAllText(path, createText);
}
string appendText = parts[0].Split("||")[1].ToString() + delimiter + parts[1].Split("||")[1].ToString() + delimiter + await ReplaceInvalidValues(parts[2].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[3].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[4].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[5].Split("||")[1].ToString()) + delimiter + await ReplaceInvalidValues(parts[6].Split("||")[1].ToString()) + delimiter + Environment.NewLine;
File.AppendAllText(path, appendText);
}
public static async Task<string> ReplaceInvalidValues(string s)
{
if (s == ("") || s == ("[]"))
return "NA";
else
return s;
}
public static void ReadConfig()
{
var builder = new ConfigurationBuilder()
.AddJsonFile($"local.settings.json", true, true);
var config = builder.Build();
clientId = config["ClientId"];
csvlocation = config["CsvLocation"];
}
public async static Task GetUserAccess()
{
string baseUrl = $"https://api.fabric.microsoft.com/v1/workspaces";
string response = await GetAsync(baseUrl);
JObject workspace_jobject = JObject.Parse(response);
JArray workspace_array = (JArray)workspace_jobject["value"];
AnsiConsole.MarkupLine($"Fetching a list of Workspaces");
foreach (JObject workspace_array_0 in workspace_array)
{
workspacelist.Add(workspace_array_0["displayName"].ToString(), workspace_array_0["id"].ToString()); //
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Fetched workspace [Red]{workspace_array_0["displayName"].ToString()}[/]");
Thread.Sleep(500);
}
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Done");
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Fetching a list of items across workspaces");
foreach (var workspace in workspacelist)
{
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Fetching items from the workspace [Red] {workspace.Key}[/]");
baseUrl = $"https://api.fabric.microsoft.com/v1/admin/items?workspaceid={workspace.Value}";
response = await GetAsync(baseUrl);
JObject item_jobject = JObject.Parse(response);
JArray item_array = (JArray)item_jobject["itemEntities"];
foreach (JObject item_array_0 in item_array)
{
itemlist.Add(workspace.Key + "~" + workspace.Value + "~" + item_array_0["name"].ToString(), item_array_0["id"].ToString());
}
}
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine("Done");
AnsiConsole.MarkupLine("");
int i = 0; int j = 0;
foreach (var item in itemlist)
{
AnsiConsole.MarkupLine($"Fetching access details for item [Red]{(itemlist.Keys).ToList()[i].ToString().Split("~")[^1]}[/] for workspace [Blue]{(itemlist.Keys).ToList()[i].ToString().Split("~")[^3]}[/] and exporting the details to csv located at [Red]{csvlocation}[/]");
AnsiConsole.MarkupLine("");
baseUrl = $"https://api.fabric.microsoft.com/v1/admin/workspaces/{(itemlist.Keys).ToList()[i].ToString().Split("~")[^2]}/items/{item.Value}/users";
response = await GetAsync(baseUrl);
Thread.Sleep(5000); // Wait for 5 seconds
JObject JObject = JObject.Parse(response);
JArray jarray_item = (JArray)JObject["accessDetails"];
foreach (JObject path in jarray_item)
{
JToken object_type = path["itemAccessDetails"]["type"];
JToken user = path["principal"]["displayName"];
JToken user_type = path["principal"]["type"];
JToken user_permissions = path["itemAccessDetails"]["permissions"];
string permissions = "";
foreach (JToken p in user_permissions)
{
permissions = permissions + ("/" + p.ToString()); p.ToString();
}
JToken additional_permissions = path["itemAccessDetails"]["additionalPermissions"];
string a_permissions = "";
foreach (JToken p in additional_permissions)
{
a_permissions = a_permissions + ("/" + p.ToString()); p.ToString();
}
lst.Add(j + "~" + (itemlist.Keys).ToList()[i].ToString().Split("~")[0] + "~" + (itemlist.Keys).ToList()[i].ToString().Split("~")[^1] + "~" + object_type, user + "~" + user_type + "~" + permissions + "~" + a_permissions);
j++;
}
AnsiConsole.MarkupLine($"Done");
AnsiConsole.MarkupLine("");
i++;
}
for (i = 0; i < lst.Keys.ToList().Count; i++)
{
WriteToCsv(csvlocation, "Workspace||" + lst.Keys.ToList()[i].ToString().Split("~")[^3] + "~ItemName||" + lst.Keys.ToList()[i].ToString().Split("~")[^2] + "~ItemType||" + lst.Keys.ToList()[i].ToString().Split("~")[^1] + "~UserName||" + lst.Values.ToList()[i].ToString().Split("~")[^4] + "~UserType||" + lst.Values.ToList()[i].ToString().Split("~")[^3] + "~Permissions||" + lst.Values.ToList()[i].ToString().Split("~")[^2] + "~AdditionalPermissions||" + lst.Values.ToList()[i].ToString().Split("~")[^1]);
i++;
}
}
public async static Task<string> GetAsync(string url)
{
AuthenticationResult authenticationResult = await ReturnAuthenticationResult(scopes_f);
client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", authenticationResult.AccessToken);
HttpResponseMessage response = await client.GetAsync(url);
try
{
response.EnsureSuccessStatusCode();
return await response.Content.ReadAsStringAsync();
}
catch (HttpRequestException httpRequestException)
{
return null;
}
}
public async static Task<AuthenticationResult> ReturnAuthenticationResult(string[] scopes)
{
;
PublicClientApplicationBuilder PublicClientAppBuilder =
PublicClientApplicationBuilder.Create(clientId)
.WithAuthority(Authority)
.WithCacheOptions(CacheOptions.EnableSharedCacheOptions)
.WithRedirectUri(RedirectURI);
IPublicClientApplication PublicClientApplication = PublicClientAppBuilder.Build();
var accounts = await PublicClientApplication.GetAccountsAsync();
AuthenticationResult result;
try
{
result = await PublicClientApplication.AcquireTokenSilent(scopes, accounts.First())
.ExecuteAsync()
.ConfigureAwait(false);
}
catch
{
result = await PublicClientApplication.AcquireTokenInteractive(scopes)
.ExecuteAsync()
.ConfigureAwait(false);
}
return result;
}
}
}
Video Walkthrough
Conclusion
With this approach, it's now possible to fetch the access details for the entities in a more efficient and manner but one has to aware of the limitations of the REST API request limit.
As previously mentioned limitations, can be overcomed by either introducing a wait time of x seconds between consecutive requests or by restricting the search to a specific set of workspaces or items.
Thanks for reading !!!
