Manage database security roles

  • Article
  • 09/30/2024

Applies to: ✅ 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 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.

Note

To delete a database, you need at least Contributor Azure Resource Manager (ARM) permissions. To assign ARM permissions, see Assign Azure roles using the Azure portal.

Database level security roles

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

Role Permissions
admins View and modify the database and database entities.
users View the database and create new database entities.
viewers View tables in the database where RestrictedViewAccess isn't turned on.
unrestrictedviewers View the tables in the database even where RestrictedViewAccess is turned on. The principal must also have admins, viewers, or users permissions.
ingestors Ingest data to the database without access to query.
monitors View database metadata such as schemas, operations, and permissions.

Note

It isn't possible to assign the viewer role for only some tables in the database. For different approaches on how to grant a principal view access to a subset of tables, see manage table view access.

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

Learn more about syntax conventions.

Parameters

Name Type Required Description
DatabaseName string ✔️ The name of the database for which to list principals.

Example

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

.show database Samples principals

Example output

Role PrincipalType PrincipalDisplayName PrincipalObjectId PrincipalFQN
Database Samples Admin Microsoft Entra user Abbi Atkins cd709aed-a26c-e3953dec735e aaduser=abbiatkins@fabrikam.com

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 ]

Learn more about syntax conventions.

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.
DatabaseName string ✔️ The name of the database for which to add principals.
Role string ✔️ The role to assign to the principal. For databases, roles can be admins, users, viewers, unrestrictedviewers, ingestors, or monitors.
Principal string ✔️ One or more principals or managed identities. To reference managed identities, use the "App" format using the managed identity object ID or managed identity client (application) ID. For guidance on how to specify these principals, see Referencing Microsoft Entra principals and groups.
skip-results string If provided, the command won't return the updated list of database principals.
Description string Text to describe the change that displays 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.

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