Security roles

• 1: Manage database security roles
• 2: Manage external table roles
• 3: Manage function roles
• 4: Manage materialized view roles
• 5: Referencing security principals
• 6: Security roles
• 7: Access control
• 7.1: Access Control Overview
• 7.2: Microsoft Entra application registration
• 7.3: Role-based access control
• 8: Manage table roles
• 8.1: Manage table security roles
• 8.2: Manage view access to tables

1 - Manage database security roles

Principals are granted access to resources through a role-based access control model, where their assigned security roles determine their resource access.

In this article, you’ll learn how to use management commands to view existing security roles and add and drop principal association to security roles on the database level.

Permissions

You must have at least Database Admin permissions to run these commands.

Database level security roles

The following table shows the possible security roles on the database level and describes the permissions granted for each role.

Show existing security roles

Before you add or remove principals, you can use the .show command to see a table with all of the principals and roles that are already set on the database.

Syntax

To show all roles:

.show database DatabaseName principals

To show your roles:

.show database DatabaseName principal roles

Parameters

Example

The following command lists all security principals that have access to the Samples database.

.show database Samples principals

Example output

Add and drop principal association to security roles

This section provides syntax, parameters, and examples for adding and removing principals to and from security roles.

Syntax

Action database DatabaseName Role ( Principal [, Principal…] ) [skip-results] [ Description ]

Parameters

Examples

In the following examples, you’ll see how to add security roles, remove security roles, and add and remove security roles in the same command.

Add security roles with .add

The following example adds a principal to the users role on the Samples database.

.add database Samples users ('aaduser=imikeoein@fabrikam.com')

The following example adds an application to the viewers role on the Samples database.

.add database Samples viewers ('aadapp=4c7e82bd-6adb-46c3-b413-fdd44834c69b;fabrikam.com')

Remove security roles with .drop

The following example removes all principals in the group from the admins role on the Samples database.

.drop database Samples admins ('aadGroup=SomeGroupEmail@fabrikam.com')

Add new security roles and remove the old with .set

The following example removes existing viewers and adds the provided principals as viewers on the Samples database.

.set database Samples viewers ('aaduser=imikeoein@fabrikam.com', 'aaduser=abbiatkins@fabrikam.com')

Remove all security roles with .set

The following command removes all existing viewers on the Samples database.

.set database Samples viewers none

Related content

• current_principal_details()

2 - Manage external table roles

Principals are granted access to resources through a role-based access control model, where their assigned security roles determine their resource access.

On external tables, the only security role is admins. External table admins have the ability to view, modify, and remove the external table.

In this article, you’ll learn how to use management commands to view existing admins as well as add and remove admins on external tables.

Permissions

You must have Database Admin permissions or be an External Table Admin on the specific external table to run these commands. For more information, see role-based access control.

Show existing admins

Before you add or remove principals, you can use the .show command to see a table with all of the principals that already have admin access on the external table.

Syntax

To show all roles:

.show external table ExternalTableName principals

To show your roles:

.show external table ExternalTableName principal roles

Parameters

Example

The following command lists all security principals that have access to the Samples external table.

.show external table Samples principals

Example output

Add and drop admins

This section provides syntax, parameters, and examples for adding and removing principals.

Syntax

Action external table ExternalTableName admins ( Principal [, Principal…] ) [skip-results] [ Description ]

Parameters

Examples

In the following examples, you’ll see how to add admins, remove admins, and add and remove admins in the same command.

Add admins with .add

The following example adds a principal to the admins role on the Samples external table.

.add external table Samples admins ('aaduser=imikeoein@fabrikam.com')

Remove admins with .drop

The following example removes all principals in the group from the admins role on the Samples external table.

.drop external table Samples admins ('aadGroup=SomeGroupEmail@fabrikam.com')

Add new admins and remove the old with .set

The following example removes existing admins and adds the provided principals as admins on the Samples external table.

.set external table Samples admins ('aaduser=imikeoein@fabrikam.com', 'aaduser=abbiatkins@fabrikam.com')

Remove all admins with .set

The following command removes all existing admins on the Samples external table.

.set external table Samples admins none

Related content

• current_principal_details()

3 - Manage function roles

Principals are granted access to resources through a role-based access control model, where their assigned security roles determine their resource access.

On functions, the only security role is admins. Function admins have the ability to view, modify, and remove the function.

In this article, you’ll learn how to use management commands to view existing admins as well as add and remove admins on functions.

Permissions

You must have Database Admin permissions or be a Function Admin on the specific function to run these commands. For more information, see role-based access control.

Show existing admins

Before you add or remove principals, you can use the .show command to see a table with all of the principals that already have admin access on the function.

Syntax

To show all roles:

.show function FunctionName principals

To show your roles:

.show function FunctionName principal roles

Parameters

Example

The following command lists all security principals that have access to the SampleFunction function.

.show function SampleFunction principals

Example output

Add and drop admins

This section provides syntax, parameters, and examples for adding and removing principals.

Syntax

Action function FunctionName admins ( Principal [, Principal…] ) [skip-results] [ Description ]

Parameters

Examples

In the following examples, you’ll see how to add admins, remove admins, and add and remove admins in the same command.

Add admins with .add

The following example adds a principal to the admins role on the SampleFunction function.

.add function SampleFunction admins ('aaduser=imikeoein@fabrikam.com')

Remove admins with .drop

The following example removes all principals in the group from the admins role on the SampleFunction function.

.drop function SampleFunction admins ('aadGroup=SomeGroupEmail@fabrikam.com')

Add new admins and remove the old with .set

The following example removes existing admins and adds the provided principals as admins on the SampleFunction function.

.set function SampleFunction admins ('aaduser=imikeoein@fabrikam.com', 'aaduser=abbiatkins@fabrikam.com')

Remove all admins with .set

The following command removes all existing admins on the SampleFunction function.

.set function SampleFunction admins none

Related content

• current_principal_details()

4 - Manage materialized view roles

Principals are granted access to resources through a role-based access control model, where their assigned security roles determine their resource access.

On materialized views, the only security role is admins. Materialized view admins have the ability to view, modify, and remove the materialized view.

In this article, you’ll learn how to use management commands to view existing admins as well as add and remove admins on materialized views.

Permissions

You must have Database Admin permissions or be a Materialized View Admin on the specific materialized view to run these commands. For more information, see role-based access control.

Show existing admins

Before you add or remove principals, you can use the .show command to see a table with all of the principals that already have admin access on the materialized view.

Syntax

To show all roles:

.show materialized-view MaterializedViewName principals

To show your roles:

.show materialized-view MaterializedViewName principal roles

Parameters

Example

The following command lists all security principals that have access to the SampleView materialized view.

.show materialized view SampleView principals

Example output

Add and drop admins

This section provides syntax, parameters, and examples for adding and removing principals.

Syntax

Action materialized-view MaterializedViewName admins ( Principal [, Principal…] ) [skip-results] [ Description ]

Parameters

Examples

In the following examples, you’ll see how to add admins, remove admins, and add and remove admins in the same command.

Add admins with .add

The following example adds a principal to the admins role on the SampleView materialized view.

.add materialized-view SampleView admins ('aaduser=imikeoein@fabrikam.com')

Remove admins with .drop

The following example removes all principals in the group from the admins role on the SampleView materialized view.

.drop materialized-view SampleView admins ('aadGroup=SomeGroupEmail@fabrikam.com')

Add new admins and remove the old with .set

The following example removes existing admins and adds the provided principals as admins on the SampleView materialized view.

.set materialized-view SampleView admins ('aaduser=imikeoein@fabrikam.com', 'aaduser=abbiatkins@fabrikam.com')

Remove all admins with .set

The following command removes all existing admins on the SampleView materialized view.

.set materialized-view SampleView admins none

Related content

• current_principal_details()

5 - Referencing security principals

The authorization model allows for the use of Microsoft Entra user and application identities and Microsoft Accounts (MSAs) as security principals. This article provides an overview of the supported principal types for both Microsoft Entra ID and MSAs, and demonstrates how to properly reference these principals when assigning security roles using management commands.

Microsoft Entra ID

The recommended way to access your environment is by authenticating to the Microsoft Entra service. Microsoft Entra ID is an identity provider capable of authenticating security principals and coordinating with other identity providers, such as Microsoft’s Active Directory.

Microsoft Entra ID supports the following authentication scenarios:

• User authentication (interactive sign-in): Used to authenticate human principals.
• Application authentication (non-interactive sign-in): Used to authenticate services and applications that have to run or authenticate without user interaction.

Referencing Microsoft Entra principals and groups

The syntax for referencing Microsoft Entra user and application principals and groups is outlined in the following table.

If you use a User Principal Name (UPN) to reference a user principal, and an attempt will be made to infer the tenant from the domain name and try to find the principal. If the principal isn’t found, explicitly specify the tenant ID or name in addition to the user’s UPN or object ID.

Similarly, you can reference a security group with the group email address in UPN format and an attempt will be made to infer the tenant from the domain name. If the group isn’t found, explicitly specify the tenant ID or name in addition to the group display name or object ID.

Examples

The following example uses the user UPN to define a principal the user role on the Test database. The tenant information isn’t specified, so your cluster will attempt to resolve the Microsoft Entra tenant using the UPN.

.add database Test users ('aaduser=imikeoein@fabrikam.com') 'Test user (AAD)'

The following example uses a group name and tenant name to assign the group to the user role on the Test database.

.add database Test users ('aadgroup=SGDisplayName;fabrikam.com') 'Test group @fabrikam.com (AAD)'

The following example uses an app ID and tenant name to assign the app the user role on the Test database.

.add database Test users ('aadapp=4c7e82bd-6adb-46c3-b413-fdd44834c69b;fabrikam.com') 'Test app @fabrikam.com (AAD)'

Microsoft Accounts (MSAs)

User authentication for Microsoft Accounts (MSAs) is supported. MSAs are all of the Microsoft-managed non-organizational user accounts. For example, hotmail.com, live.com, outlook.com.

Referencing MSA principals

Example

The following example assigns an MSA user to the user role on the Test database.

.add database Test users ('msauser=abbiatkins@live.com') 'Test user (live.com)'

to manage data partitioning policies for tables

• Read the authentication overview

• Learn how to use the Azure portal to manage database principals and roles

• current_principal_details()

• Learn how to use management commands to assign security roles

• Learn how to use management commands to assign security roles

6 - Security roles

Principals are granted access to resources through a role-based access control model, where their assigned security roles determine their resource access.

When a principal attempts an operation, the system performs an authorization check to make sure the principal is associated with at least one security role that grants permissions to perform the operation. Failing an authorization check aborts the operation.

The management commands listed in this article can be used to manage principals and their security roles on databases, tables, external tables, materialized views, and functions.

To learn how to configure them in the Azure portal, see Manage cluster permissions.

Management commands

The following table describes the commands used for managing security roles.

Security roles

The following table describes the level of access granted for each role and shows a check if the role can be assigned within the given object type.

For a full description of the security roles at each scope, see Kusto role-based access control.

Common scenarios

Show your principal roles

To see your own roles on the cluster, run the following command:

To see your own roles on the eventhouse, run the following command:

.show cluster principal roles

Show your roles on a resource

To check the roles assigned to you on a specific resource, run the following command within the relevant database or the database that contains the resource:

// For a database:
.show database DatabaseName principal roles

// For a table:
.show table TableName principal roles

// For an external table:
.show external table ExternalTableName principal roles

// For a function:
.show function FunctionName principal roles

// For a materialized view:
.show materialized-view MaterializedViewName principal roles

Show the roles of all principals on a resource

To see the roles assigned to all principals for a particular resource, run the following command within the relevant database or the database that contains the resource:

// For a database:
.show database DatabaseName principals

// For a table:
.show table TableName principals

// For an external table:
.show external table ExternalTableName principals

// For a function:
.show function FunctionName principals

// For a materialized view:
.show materialized-view MaterializedViewName principals

Modify the role assignments

For details on how to modify your role assignments at the database and table levels, see Manage database security roles and Manage table security roles.

Related content

• Kusto role-based access control
• Referencing security principals

7 - Access control

7.1 - Access Control Overview

Access control is based on authentication and authorization. Each query and command on an Azure Data Explorer resource, such as a cluster or database, must pass both authentication and authorization checks.

Access control is based on authentication and authorization. Each query and command on a Fabric resource, such as a database, must pass both authentication and authorization checks.

• Authentication: Validates the identity of the security principal making a request
• Authorization: Validates the security principal making a request is permitted to make that request on the target resource

Authentication

To programmatically authenticate, a client must communicate with Microsoft Entra ID and request an access token specific to the Kusto service. Then, the client can use the acquired access token as proof of identity when issuing requests to your database.

The main authentication scenarios are as follows:

• User authentication: Used to verify the identity of human users.
• Application authentication: Used to verify the identity of an application that needs to access resources without human intervention by using configured credentials.
• On-behalf-of (OBO) authentication: Allows an application to exchange a token for said application with a token to access a Kusto service. This flow must be implemented with MSAL.
• Single page application (SPA) authentication: Allows client-side SPA web applications to sign in users and get tokens to access your database. This flow must be implemented with MSAL.

User authentication

User authentication happens when a user presents credentials to Microsoft Entra ID or an identity provider that federates with Microsoft Entra ID, such as Active Directory Federation Services. The user gets back a security token that can be presented to the Azure Data Explorer service. Azure Data Explorer determines whether the token is valid, whether the token is issued by a trusted issuer, and what security claims the token contains.

Azure Data Explorer supports the following methods of user authentication, including through the Kusto client libraries:

• Interactive user authentication with sign-in through the user interface.
• User authentication with a Microsoft Entra token issued for Azure Data Explorer.
• User authentication with a Microsoft Entra token issued for another resource that can be exchanged for an Azure Data Explorer token using On-behalf-of (OBO) authentication.

Application authentication

Application authentication is needed when requests aren’t associated with a specific user or when no user is available to provide credentials. In this case, the application authenticates to Microsoft Entra ID or the federated IdP by presenting secret information.

Azure Data Explorer supports the following methods of application authentication, including through the Kusto client libraries:

• Application authentication with an Azure managed identity.
• Application authentication with an X.509v2 certificate installed locally.
• Application authentication with an X.509v2 certificate given to the client library as a byte stream.
• Application authentication with a Microsoft Entra application ID and a Microsoft Entra application key. The application ID and application key are like a username and password.
• Application authentication with a previously obtained valid Microsoft Entra token, issued to Azure Data Explorer.
• Application authentication with a Microsoft Entra token issued for another resource that can be exchanged for an Azure Data Explorer token using On-behalf-of (OBO) authentication.

Authorization

Before carrying out an action on a resource, all authenticated users must pass an authorization check. The Kusto role-based access control model is used, where principals are ascribed to one or more security roles. Authorization is granted as long as one of the roles assigned to the user allows them to perform the specified action. For example, the Database User role grants security principals the right to read the data of a particular database, create tables in the database, and more.

The association of security principals to security roles can be defined individually or by using security groups that are defined in Microsoft Entra ID. For more information on how to assign security roles, see Security roles overview.

Group authorization

Authorization can be granted to Microsoft Entra ID groups by assigning one or more roles to the group.

When checking authorization for a user or application principal, the system first looks for an explicit role assignment that permits the specific action. If the role assignment doesn’t exists, then the system checks the principal’s membership in all groups that could authorize the action.

If the principal is a member of a group with appropriate permissions, the requested action is authorized. Otherwise, the action doesn’t pass the authorization check and is disallowed.

Force group membership refresh

Principals can force a refresh of group membership information. This capability is useful in scenarios where just-in-time (JIT) privileged access services, such as Microsoft Entra Privileged Identity Management (PIM), are used to obtain higher privileges on a resource.

Refresh for a specific group

Principals can force a refresh of group membership for a specific group. However, the following restrictions apply:

• A refresh can be requested up to 10 times per hour per principal.
• The requesting principal must be a member of the group at the time of the request.

The request results in an error if either of these conditions aren’t met.

To reevaluate the current principal’s membership of a group, run the following command:

.clear cluster cache groupmembership with (group='<GroupFQN>')

Use the group’s fully qualified name (FQN). For more information, see Referencing Microsoft Entra principals and groups.

Refresh for other principals

A privileged principal can request a refresh for other principals. The requesting principal must have AllDatabaseMonitor access for the target service. Privileged principals can also run the previous command without restrictions.

To refresh another principal’s group membership, run the following command:

.clear cluster cache groupmembership with (principal='<PrincipalFQN>', group='<GroupFQN>')

Related content

• Understand Kusto role-based access control.
• For user or application authentication, use the Kusto client libraries.
• For OBO or SPA authentication, see How to authenticate with Microsoft Authentication Library (MSAL).
• For referencing principals and groups, see Referencing Microsoft Entra principals and groups.

7.2 - Microsoft Entra application registration

Microsoft Entra application authentication requires creating and registering an application with Microsoft Entra ID. A service principal is automatically created when the application registration is created in a Microsoft Entra tenant.

The app registration can either be created in the Azure portal, or programatically with Azure CLI. Choose the tab that fits your scenario.

Portal

Register the app

1. Sign in to Azure portal and open the Microsoft Entra ID blade.

2. Browse to App registrations and select New registration.

   

3. Name the application, for example “example-app”.

4. Select a supported account type, which determines who can use the application.

5. Under Redirect URI, select Web for the type of application you want to create. The URI is optional and is left blank in this case.

   

6. Select Register.

Set up authentication

There are two types of authentication available for service principals: password-based authentication (application secret) and certificate-based authentication. The following section describes using a password-based authentication for the application’s credentials. You can alternatively use an X509 certificate to authenticate your application. For more information, see How to configure Microsoft Entra certificate-based authentication.

Through the course of this section, you’ll copy the following values: Application ID and key value. Paste these values somewhere, like a text editor, for use in the step configure client credentials to the database.

1. Browse to the Overview blade.

2. Copy the Application (client) ID and the Directory (tenant) ID.

   [!NOTE] You’ll need the application ID and the tenant ID to authorize the service principal to access the database.

3. In the Certificates & secrets blade, select New client secret.

   

4. Enter a description and expiration.

5. Select Add.

6. Copy the key value.

   [!NOTE] When you leave this page, the key value won’t be accessible.

You’ve created your Microsoft Entra application and service principal.

Azure CLI

1. Sign in to your Azure subscription via Azure CLI. Then authenticate in the browser.

   az login
   

2. Choose the subscription to host the principal. This step is needed when you have multiple subscriptions.

   az account set --subscription YOUR_SUBSCRIPTION_GUID
   

3. Create the service principal. In this example, the service principal is called my-service-principal.

   az ad sp create-for-rbac -n "my-service-principal" --role Contributor --scopes /subscriptions/{SubID}
   

4. From the returned JSON data, copy the appId, password, and tenant for future use.

   {
     "appId": "00001111-aaaa-2222-bbbb-3333cccc4444",
     "displayName": "my-service-principal",
     "name": "my-service-principal",
     "password": "00001111-aaaa-2222-bbbb-3333cccc4444",
     "tenant": "00001111-aaaa-2222-bbbb-3333cccc4444"
   }
   

You’ve created your Microsoft Entra application and service principal.

Configure delegated permissions for the application - optional

If your application needs to access your database using the credentials of the calling user, configure delegated permissions for your application. For example, if you’re building a web API and you want to authenticate using the credentials of the user who is calling your API.

If you only need access to an authorized data resource, you can skip this section and continue to Grant a service principal access to the database.

1. Browse to the API permissions blade of your App registration.

2. Select Add a permission.

3. Select APIs my organization uses.

4. Search for and select Azure Data Explorer.

   

5. In Delegated permissions, select the user_impersonation box.

6. Select Add permissions.

   

Grant a service principal access to the database

Once your application registration is created, you need to grant the corresponding service principal access to your database. The following example gives viewer access. For other roles, see Kusto role-based access control.

1. Use the values of Application ID and Tenant ID as copied in a previous step.

2. Execute the following command in your query editor, replacing the placeholder values ApplicationID and TenantID with your actual values:

   .add database <DatabaseName> viewers ('aadapp=<ApplicationID>;<TenantID>') '<Notes>'
   

   For example:

   .add database Logs viewers ('aadapp=00001111-aaaa-2222-bbbb-3333cccc4444;9876abcd-e5f6-g7h8-i9j0-1234kl5678mn') 'App Registration'
   

   The last parameter is a string that shows up as notes when you query the roles associated with a database.

   [!NOTE] After creating the application registration, there might be a several minute delay until it can be referenced. If you receive an error that the application is not found, wait and try again.

For more information on roles, see Role-based access control.

Use application credentials to access a database

Use the application credentials to programmatically access your database by using the client library.

. . .
string applicationClientId = "<myClientID>";
string applicationKey = "<myApplicationKey>";
string authority = "<myApplicationTenantID>";
. . .
var kcsb = new KustoConnectionStringBuilder($"https://{clusterName}.kusto.windows.net/{databaseName}")
    .WithAadApplicationKeyAuthentication(
        applicationClientId,
        applicationKey,
        authority);
var client = KustoClientFactory.CreateCslQueryProvider(kcsb);
var queryResult = client.ExecuteQuery($"{query}");

[!NOTE] Specify the application id and key of the application registration (service principal) created earlier.

For more information, see How to authenticate with Microsoft Authentication Library (MSAL) in apps and use Azure Key Vault with .NET Core web app.

Troubleshooting

Invalid resource error

If your application is used to authenticate users, or applications for access, you must set up delegated permissions for the service application. Declare your application can authenticate users or applications for access. Not doing so will result in an error similar to the following, when an authentication attempt is made:

AADSTS650057: Invalid resource. The client has requested access to a resource which is not listed in the requested permissions in the client's application registration...

Enable user consent error

Your Microsoft Entra tenant administrator might enact a policy that prevents tenant users from giving consent to applications. This situation will result in an error similar to the following, when a user tries to sign in to your application:

AADSTS65001: The user or administrator has not consented to use the application with ID '<App ID>' named 'App Name'

You’ll need to contact your Microsoft Entra administrator to grant consent for all users in the tenant, or enable user consent for your specific application.

Related content

• Kusto connection strings
• Create a Microsoft Entra application and service principal that can access resources

7.3 - Role-based access control

Azure Data Explorer uses a role-based access control (RBAC) model in which principals get access to resources based on their assigned roles. Roles are defined for a specific cluster, database, table, external table, materialized view, or function. When defined for a cluster, the role applies to all databases in the cluster. When defined for a database, the role applies to all entities in the database.

Azure Resource Manager (ARM) roles, such as subscription owner or cluster owner, grant access permissions for resource administration. For data administration, you need the roles described in this document.

Real-Time Intelligence in Fabric uses a hybrid role-based access control (RBAC) model in which principals get access to resources based on their assigned roles granted from one or both of two sources: Fabric, and Kusto management commands. The user will have the union of the roles granted from both sources.

Within Fabric, roles can be assigned or inherited by assigning a role in a workspace, or by sharing a specific item based on the item permission model.

Fabric roles

Roles can further be defined on the data plane for a specific database, table, external table, materialized view, or function, by using management commands. In both cases, roles applied at a higher level (Workspace, Eventhouse) are inherited by lower levels (Database, Table).

Roles and permissions

The following table outlines the roles and permissions available at each scope.

The Permissions column displays the access granted to each role.

The Dependencies column lists the minimum roles required to obtain the role in that row. For example, to become a Table Admin, you must first have a role like Database User or a role that includes the permissions of Database User, such as Database Admin or AllDatabasesAdmin. When multiple roles are listed in the Dependencies column, only one of them is needed to obtain the role.

The How the role is obtained column offers ways that the role can be granted or inherited.

The Manage column offers ways to add or remove role principals.

Related content

• Manage view access to tables within the same database
• Manage function roles
• Manage cluster permissions
• Allow cross-tenant queries and commands

8 - Manage table roles

8.1 - Manage table security roles

Principals are granted access to resources through a role-based access control model, where their assigned security roles determine their resource access.

In this article, you’ll learn how to use management commands to view existing security roles as well as add and remove security roles on the table level.

Permissions

You must have at least Table Admin permissions to run these commands.

Table level security roles

The following table shows the possible security roles on the table level and describes the permissions granted for each role.

Show existing security roles

Before you add or remove principals, you can use the .show command to see a table with all of the principals and roles that are already set on the table.

Syntax

To show all roles:

.show table TableName principals

To show your roles:

.show table TableName principal roles

Parameters

Example

The following command lists all security principals that have access to the StormEvents table.

.show table StormEvents principals

Example output

Add and drop security roles

This section provides syntax, parameters, and examples for adding and removing principals.

Syntax

Action table TableName Role ( Principal [, Principal…] ) [skip-results] [ Description ]

Parameters

Examples

In the following examples, you’ll see how to add security roles, remove security roles, and add and remove security roles in the same command.

Add security roles with .add

The following example adds a principal to the admins role on the StormEvents table.

.add table StormEvents admins ('aaduser=imikeoein@fabrikam.com')

The following example adds an application to the ingestors role on the StormEvents table.

.add table StormEvents ingestors ('aadapp=4c7e82bd-6adb-46c3-b413-fdd44834c69b;fabrikam.com')

Remove security roles with .drop

The following example removes all principals in the group from the admins role on the StormEvents table.

.drop table StormEvents admins ('aadGroup=SomeGroupEmail@fabrikam.com')

Add new security roles and remove the old with .set

The following example removes existing ingestors and adds the provided principals as ingestors on the StormEvents table.

.set table StormEvents ingestors ('aaduser=imikeoein@fabrikam.com', 'aaduser=abbiatkins@fabrikam.com')

Remove all security roles with .set

The following command removes all existing ingestors on the StormEvents table.

.set table StormEvents ingestors none

Related content

• current_principal_details()

8.2 - Manage view access to tables

Principals gain access to resources, such as databases and tables, based on their assigned security roles. The viewer security role is only available at the database level, and assigning a principal this role gives them view access to all tables in the database.

In this article, you learn methods for controlling a principal’s table view access.

Structure data for controlled access

To control access more effectively, we recommend that you separate tables into different databases based on access privileges. For instance, create a distinct database for sensitive data and restrict access to specific principals by assigning them the relevant security roles.

Restricted View Access policy

To restrict access to specific tables, you can turn on the Restricted View Access policy for those tables. This policy ensures that only principals with the unrestrictedViewer role can access the table. Meanwhile, principals with the regular viewer role can’t view the table.

Row Level Security policy

The Row Level Security (RLS) policy allows you to restrict access to rows of data based on specific criteria and allows masking data in columns. When you create an RLS policy on a table, the restriction applies to all users, including database administrators and the RLS creator.

Create a follower database

Create a follower database and follow only the relevant tables that you’d like to share with the specific principal or set of principals.

Create a database shortcut in Fabric and follow only the relevant tables that you’d like to share with the specific principal or set of principals.

Related content

• Learn more about role-based access control
• Use management commands to assign security roles