Manage table security roles
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer
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.
Note
A principal must have access on the database level to be assigned table specific security roles.
You must have at least Table Admin permissions to run these commands.
The following table shows the possible security roles on the table level and describes the permissions granted for each role.
Role | Permissions |
---|---|
admins |
View, modify, and remove the table and table entities. |
ingestors |
Ingest data to the table without access to query. |
Note
To learn how to grant a principal view access to a subset of tables within a database, see manage table view access.
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.
To show all roles:
.show
table
TableName principals
To show your roles:
.show
table
TableName principal
roles
Learn more about syntax conventions.
Name | Type | Required | Description |
---|---|---|---|
TableName | string |
✔️ | The name of the table for which to list principals. |
The following command lists all security principals that have access to the StormEvents
table.
.show table StormEvents principals
Example output
Role | PrincipalType | PrincipalDisplayName | PrincipalObjectId | PrincipalFQN |
---|---|---|---|---|
Table StormEvents Admin | Microsoft Entra user | Abbi Atkins | cd709aed-a26c-e3953dec735e | aaduser=abbiatkins@fabrikam.com |
This section provides syntax, parameters, and examples for adding and removing principals.
Action table
TableName Role (
Principal [,
Principal...] )
[skip-results
] [ Description ]
Learn more about syntax conventions.
Name | Type | Required | Description |
---|---|---|---|
Action | string |
✔️ | The command .add , .drop , or .set ..add adds the specified principals, .drop removes the specified principals, and .set adds the specified principals and removes all previous ones. |
TableName | string |
✔️ | The name of the table for which to add principals. |
Role | string |
✔️ | The role to assign to the principal. For tables, this can be admins or ingestors . |
Principal | string |
✔️ | One or more principals. For guidance on how to specify these principals, see Referencing security principals. |
skip-results |
string |
If provided, the command won't return the updated list of table principals. | |
Description | string |
Text to describe the change that will be displayed when using the .show command. |
Note
The .set
command with none
instead of a list of principals will remove all principals of the specified role.
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.
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')
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')
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')
The following command removes all existing ingestors
on the StormEvents
table.
.set table StormEvents ingestors none