This is the multi-page printable view of this section. Click here to print.
Manage table roles
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.
| Role | Permissions |
|---|---|
admins | View, modify, and remove the table and table entities. |
ingestors | Ingest data to the table without access to query. |
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
| Name | Type | Required | Description |
|---|---|---|---|
| TableName | string | ✔️ | The name of the table for which to list principals. |
Example
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 |
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
| 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. |
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
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