Eventhouse and KQL database security

[datacoffee]

Different roles in the Eventhouse

In Fabric we have different ways of securing the items and data in the tenant - workspace level, item level, object level etc. In Eventhouse we can also define specific access roles and principals.

The Eventhouse comes with a list of 6 different roles:

• admins: Can do everything on all object levels in the Eventhouse, from databases to functions.
• users: Can interact with objects (databases, tables, views, functions) and create new sub-objects based on the current access levels.
• viewers: Can view objects - though if the **RestrictedViewAccess** has been turned on for an object, the viewer role cannot access that object. The object level for a viewer is only applicable to the database level.
• unrestrictedviewers: An addition to the viewers role, where the user can view objects even if the **RestrictedViewaccess** has been turned on. This also requires the user to have one of the roles: admin, viewers or users.
• ingestors: A really nice role. This role can ingest data, but not query it
• monitors: Can view metadata from the objects, such like schemas, operations and permissions.

If you come from the SQL world, you might already recognize some of the roles above. The special ones I really like is the unrestrcitedviewers and the ingestors built in roles. These are, in combination with the other roles, really handy when it comes to access management.

The unrestrictedviewers role

From Microsoft Learn:

*When this policy is enabled for a table, data in the table can only be queried by principals who have an UnrestrictedViewer role in the database*

With this role we can restrict the reads from a table even if a user has the **uses** role assigned. This is very convenient as we can lock down sensitive data in some tables and still have the rest of the tables in the database open for reads.

Manage roles in the database

All roles in the database, weather it is on table, function, view or database level is managed by the meta-data commands:

• .show
• .add
• .drop
• .set

The .show command

This command accepts an objecttype objectname pair and either principals for all users or principal role for your own role on the object.

Some examples:

// show all specified users on the table named MachineLogs
.show table MachineLogs principals

// show your own specified role from the KQL database named Streaming
.show database Streaming principal roles

The .add command

With this command we can add new roles to users and groups.

This command accepts an objecttype objectname pair, the name of the role needed and the principal from Entra Id.

The principal from Entra Id can be user, group or app and both name and id is accepted.

Some examples:

// add the username brian@decode.bi to the users role of the KQL datbase named Streaming
// NOTE: the aaduser is all small letters
.add database Streaming users ('aaduser=brian@dcode.bi')

// add the user group Engineering to the viewers role in the table named MachineLogs
// NOTE the aadGroup has a capital 'G'
.add table MachineLogs viewers ('aadGroup='Engineering;dcode.bi')

The .drop command

As we can create roles and assign users, so can we drop those assignments again.

This command accepts an **objecttype objectname** pair, the name of the role needed and the principal from Entra Id.

The principal from Entra Id can be *user, group or app* and both **name** and **id** is accepted.

Some examples:

// remove the username brian@decode.bi to the users role of the KQL datbase named Streaming
// NOTE: the aaduser is all small letters
.drop database Streaming users ('aaduser=brian@dcode.bi')
// remove the user group Engineering to the viewers role in the table named MachineLogs
// NOTE the aadGroup has a capital 'G'
.drop table MachineLogs viewers ('aadGroup='Engineering;dcode.bi')

The .set command

This is my favorite - the "kill all" button.

With this command we can drop all specific assigned roles on an object.

This command accepts an objecttype objectname pair, a role name and finally the word "none".

Some examples:

// remove all from the users role of the KQL datbase named Streaming
.set database Streaming users none
// remove all from the viewers role in the table named MachineLogs
.set table MachineLogs viewers none

A note on accounts not in a business Entra Id

The KQL database also accepts users outside of a business Entra Id - also called non-organisational user accounts. They can only come from the MSA group (the Microsoft accounts).

The syntax for adding these users to a role is by replacing the () at the end with this:

('msauser=<email>') '<account name>'

Wow - that is alot to handle.

Yes - the security perspective of the KQL engine is extensive and can be complicated. But this is the case for every security aspect. This is also why a security officer, a DPO, a CDO or similar has to be involved in designing the high-level approach and agree on how to implement it.

And I haven't even started on the Row Level Security part of the KQL engine - that is for a future post.

Further reading can be found right here:

Manage database security roles - Kusto | Microsoft Learn

Enjoy and happy coding.