Retrieve and export user access details for various objects in Microsoft Fabric
Updated
•9 min read
S
From Synapse Analytics, Power BI, Spark, Microsoft Fabric,ASP.NET Core and recently Agentic AI on .NET I try to explore, learn and share all aspects of Microsoft Data Stack in this blog.
Imagine not knowing which Entra users have access to various items within your Fabric tenant. With a combination of the Graph API and the Fabric REST API you can now retrieve detailed information about user-level access to different entities and artifacts.
Note: This method does not currently support fetching Entra Service Principal and Group access for items in the Fabric tenant which is a major drawback.I’ll be shortly writing a separate article on how to overcome this .
Earlier, I wrote a article on how to retrieve details of Entra users and service principals throught Graph API. You can refer to that article here.
The approach used in this article is to retrieve a list of Entra users and then send them to this Fabric REST API to retrieve the underlying user access.
Sample response of the list-access-entities method of the API.
You can watch the video walkthrough here.
Setup
I am using the same code to fetch the list of Entra users that I used in my earlier article. Also the generation of Bearer token and authentication methods are similar to my previous article on Graph API.
To get started, Install the following Nuget packages in a new Console application
dotnet add package Azure.Core --version 1.35.0
dotnet add package Azure.Identity --version 1.8.0
dotnet add package Microsoft.Extensions.Configuration --version 6.0.0
dotnet add package Microsoft.Graph --version 5.16.0
dotnet add package Microsoft.Identity.Client --version 4.47.0
dotnet add package Newtonsoft.Json --version 13.0.4
dotnet add package System.IdentityModel.Tokens.Jwt --version 6.28.0
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 in Entra under which the code would run
Code
Add references to the code
using Azure.Core;
using Azure.Identity;
using Microsoft.Extensions.Configuration;
using Microsoft.Graph;
using Microsoft.Identity.Client;
using Newtonsoft.Json.Linq;
using System.IdentityModel.Tokens.Jwt;
using Spectre.Console;
Declare a bunch of variables
private static string clientId = "";
private static string[] scopes_g = new string[] { "https://graph.microsoft.com/.default" };
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 access_token = "";
private static string csvlocation = "";
private static string endpoint = "https://api.fabric.microsoft.com/v1";
private static GraphServiceClient graph_Service_Client;
private static readonly HttpClient client = new HttpClient();
Note: As seen above, we are using two scopes. One for Graph REST API client and second for Fabric REST API client .
GraphServiceClient is what we are using for Graph API and normal HttpClient for Fabric REST API.
Code to 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"];
}
Create a class to override the ClientSecretCredentials class. GraphServiceClient expects client secret but we are overriding it with bearer token.
This step is completely optional. You can refer to this article for more details.
public class AccessTokenCredential : ClientSecretCredential
{
public AccessTokenCredential(string accessToken)
{
AccessToken = accessToken;
}
private string AccessToken;
public AccessToken FetchAccessToken()
{
JwtSecurityToken token = new JwtSecurityToken(AccessToken);
return new AccessToken(AccessToken, token.ValidTo);
}
public override ValueTask<AccessToken> GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
{
return new ValueTask<AccessToken>(FetchAccessToken());
}
public override AccessToken GetToken(TokenRequestContext requestContext, CancellationToken cancellationToken)
{
JwtSecurityToken token = new JwtSecurityToken(AccessToken);
return new AccessToken(AccessToken, token.ValidTo);
}
}
Method to authenticate request and generate a bearer token
public async static Task<AuthenticationResult> ReturnAuthenticationResult(string[] scopes)
{
string AccessToken;
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);
}
access_token = result.AccessToken;
return result;
}
GET method. We are using scopes_f declared earlier and is the scope for Fabric REST API.
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
{
if (response.IsSuccessStatusCode == true)
{
return await response.Content.ReadAsStringAsync();
}
else
{
return null;
}
}
catch (HttpRequestException httpRequestException)
{
return null;
}
}
Method to retrieve the list of Entra users through Graph API. We are using scope scopes_g declared earlier and is scope for Graph REST API.
public static async Task<IDictionary<string, object>> GetUserDetails(string filter)
{
AuthenticationResult authresult = await ReturnAuthenticationResult(scopes_g);
AccessTokenCredential tokenCredential = new AccessTokenCredential(authresult.AccessToken);
graph_Service_Client = new GraphServiceClient(tokenCredential, scopes_g);
Microsoft.Graph.Models.UserCollectionResponse result =
await graph_Service_Client.Users.GetAsync((requestConfiguration) => requestConfiguration.QueryParameters.Top = 999);
IDictionary<string, object> dictionary = new Dictionary<string, object>();
int i = 0;
foreach (var str in result.Value)
{
dictionary.Add(result.Value[i].DisplayName.ToString(), result.Value[i]);
i++;
}
if (filter == "All") { return dictionary.ToDictionary(); } else { return dictionary.Where(kvp => kvp.Key.Contains(filter)).ToDictionary(); }
return null;
}
Method to export the details to csv 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 = "User " + delimiter + "Item " + delimiter + "Name " + 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 + Environment.NewLine;
File.AppendAllText(path, appendText);
}
public static async Task<string> ReplaceInvalidValues(string s)
{
if (s == ("") || s == ("[]"))
return "NA";
else
return s;
}
Main method in the application.
static async Task Main(string[] args)
{
ReadConfig();
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Please enter your credentails to generate bearer token");
var userdetails = await GetUserDetails("All");
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Fetching a list of Entra users");
Thread.Sleep(1000);
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Done...");
AnsiConsole.MarkupLine("");
foreach (var user in userdetails)
{
AnsiConsole.MarkupLine($"Analyzing user access for the user [Red]{user.Key}[/]");
AnsiConsole.MarkupLine("");
string userId = ((Microsoft.Graph.Models.Entity)userdetails.Where(a => a.Key == user.Key).ToList()[0].Value).Id.ToString();
string response = await GetAsync(endpoint + "/admin/Users/" + userId + "/access");
JObject j_response = JObject.Parse(response);
JArray j_array = (JArray)j_response["accessEntities"];
foreach (JObject path in j_array)
{
JToken itemid = path["id"];
JToken item = path["itemAccessDetails"]["type"];
JToken name = path["displayName"];
AnsiConsole.MarkupLine($"Returning list of access for the user [Blue]{user.Key}[/] on item [Blue]{name}[/] ");
AnsiConsole.MarkupLine("");
JToken permissions = path["itemAccessDetails"]["permissions"];
JArray permissions_arr = (JArray)permissions;
string permission = "";
foreach (JToken p in permissions_arr)
{
permission = permission + ("/" + p.ToString()); p.ToString();
}
JToken additionalPermissions = path["itemAccessDetails"]["additionalPermissions"];
JArray permissions_ad_arr = (JArray)additionalPermissions;
string permission_a = "";
foreach (JToken p in permissions_ad_arr)
{
permission_a = permission_a + ("/" + p.ToString()); p.ToString();
}
WriteToCsv(csvlocation, "User||" + user.Key + "~Item||" + item + "~Name||" + name + "~Permissions||" + permission + "~AdditionalPermissions||" + permission_a);
AnsiConsole.MarkupLine($"Exporting the details to the csv file");
Thread.Sleep(300);
AnsiConsole.MarkupLine("");
}
}
AnsiConsole.MarkupLine($"All user accesses exporetd to the csv file located [Red]{csvlocation}[/]");
AnsiConsole.MarkupLine("");
}
The above method fetches access details of all the Entra users. It can be modified to fetch details only a single user or a list of specific users.
Complete Code
using Azure.Core;
using Azure.Identity;
using Microsoft.Extensions.Configuration;
using Microsoft.Graph;
using Microsoft.Identity.Client;
using Newtonsoft.Json.Linq;
using System.IdentityModel.Tokens.Jwt;
using Spectre.Console;
using Microsoft.Graph.Models;
namespace UserAccess
{
internal class Program
{
private static string clientId = "";
private static string[] scopes_g = new string[] { "https://graph.microsoft.com/.default" };
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 access_token = "";
private static string csvlocation = "";
private static string endpoint = "https://api.fabric.microsoft.com/v1";
private static GraphServiceClient graph_Service_Client;
private static readonly HttpClient client = new HttpClient();
static async Task Main(string[] args)
{
ReadConfig();
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Please enter your credentails to generate bearer token");
var userdetails = await GetUserDetails("All");
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Fetching a list of Entra users");
Thread.Sleep(1000);
AnsiConsole.MarkupLine("");
AnsiConsole.MarkupLine($"Done...");
AnsiConsole.MarkupLine("");
foreach (var user in userdetails)
{
AnsiConsole.MarkupLine($"Analyzing user access for the user [Red]{user.Key}[/]");
AnsiConsole.MarkupLine("");
string userId = ((Microsoft.Graph.Models.Entity)userdetails.Where(a => a.Key == user.Key).ToList()[0].Value).Id.ToString();
string response = await GetAsync(endpoint + "/admin/Users/" + userId + "/access");
JObject j_response = JObject.Parse(response);
JArray j_array = (JArray)j_response["accessEntities"];
foreach (JObject path in j_array)
{
JToken itemid = path["id"];
JToken item = path["itemAccessDetails"]["type"];
JToken name = path["displayName"];
AnsiConsole.MarkupLine($"Returning list of access for the user [Blue]{user.Key}[/] on item [Blue]{name}[/] ");
AnsiConsole.MarkupLine("");
JToken permissions = path["itemAccessDetails"]["permissions"];
JArray permissions_arr = (JArray)permissions;
string permission = "";
foreach (JToken p in permissions_arr)
{
permission = permission + ("/" + p.ToString()); p.ToString();
}
JToken additionalPermissions = path["itemAccessDetails"]["additionalPermissions"];
JArray permissions_ad_arr = (JArray)additionalPermissions;
string permission_a = "";
foreach (JToken p in permissions_ad_arr)
{
permission_a = permission_a + ("/" + p.ToString()); p.ToString();
}
WriteToCsv(csvlocation, "User||" + user.Key + "~Item||" + item + "~Name||" + name + "~Permissions||" + permission + "~AdditionalPermissions||" + permission_a);
AnsiConsole.MarkupLine($"Exporting the details to the csv file");
Thread.Sleep(300);
AnsiConsole.MarkupLine("");
}
}
AnsiConsole.MarkupLine($"All user accesses exporetd 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 = "User " + delimiter + "Item " + delimiter + "Name " + 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 + 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 static async Task<IDictionary<string, object>> GetUserDetails(string filter)
{
AuthenticationResult authresult = await ReturnAuthenticationResult(scopes_g);
AccessTokenCredential tokenCredential = new AccessTokenCredential(authresult.AccessToken);
graph_Service_Client = new GraphServiceClient(tokenCredential, scopes_g);
Microsoft.Graph.Models.UserCollectionResponse result =
await graph_Service_Client.Users.GetAsync((requestConfiguration) => requestConfiguration.QueryParameters.Top = 999);
IDictionary<string, object> dictionary = new Dictionary<string, object>();
int i = 0;
foreach (var str in result.Value)
{
dictionary.Add(result.Value[i].DisplayName.ToString(), result.Value[i]);
i++;
}
if (filter == "All") { return dictionary.ToDictionary(); } else { return dictionary.Where(kvp => kvp.Key.Contains(filter)).ToDictionary(); }
return null;
}
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
{
if (response.IsSuccessStatusCode == true)
{
return await response.Content.ReadAsStringAsync();
}
else
{
return null;
}
}
catch (HttpRequestException httpRequestException)
{
return null;
}
}
public async static Task<AuthenticationResult> ReturnAuthenticationResult(string[] scopes)
{
string AccessToken;
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);
}
access_token = result.AccessToken;
return result;
}
}
public class AccessTokenCredential : ClientSecretCredential
{
public AccessTokenCredential(string accessToken)
{
AccessToken = accessToken;
}
private string AccessToken;
public AccessToken FetchAccessToken()
{
JwtSecurityToken token = new JwtSecurityToken(AccessToken);
return new AccessToken(AccessToken, token.ValidTo);
}
public override ValueTask<AccessToken> GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
{
return new ValueTask<AccessToken>(FetchAccessToken());
}
public override AccessToken GetToken(TokenRequestContext requestContext, CancellationToken cancellationToken)
{
JwtSecurityToken token = new JwtSecurityToken(AccessToken);
return new AccessToken(AccessToken, token.ValidTo);
}
}
}
Video Walkthrough
Thanks for reading !!!
