Understanding the Restrict Statement in Kusto Query Language (KQL)

A powerful tool for controlling access to data within a query

The restrict statement in Kusto Query Language (KQL) is a powerful tool for controlling access to data within a query. It limits the set of table or view entities visible to subsequent query statements, making it particularly useful for implementing row-level security in middle-tier applications. In this blog post, we'll explore the syntax, parameters, and practical examples of using the restrict statement to enhance data security and query management.

What is the Restrict Statement?

The restrict statement is used to limit access to specific entities within a database or cluster. By specifying which tables, views, or patterns are accessible, it ensures that only authorized data is visible to the rest of the query. This is especially useful for applications that need to enforce security policies or restrict user access to sensitive data.

Syntax and Parameters

The syntax for the restrict statement is straightforward:

restrict access to (EntitySpecifiers)

Parameters:

• EntitySpecifiers: A comma-separated list of entity specifiers, which can include:

  • Identifiers defined by a let statement as tabular views.

  • Table or function references similar to those used by a union statement.

  • Patterns defined by a pattern declaration.

Practical Examples

1. Limiting Access to a Specific Table

To restrict access to a single table within the current database:

restrict access to (database().Table1);
Table1 | count

2. Restricting Access Across Databases

To restrict access to tables in multiple databases:

restrict access to (database().Table1, database('DB2').Table2);
union (Table1), (database('DB2').Table2) | count

3. Using Let Statements

Define views using let statements and restrict access to these views:

let Test = () { print x = 1 };
restrict access to (Test);
Test

4. Applying Patterns

Use wildcard patterns to match multiple entities:

let Test1 = () { print x = 1 };
let Test2 = () { print y = 1 };
restrict access to (*); // Access is restricted to Test1, Test2, and no tables/functions are accessible.

5. Preventing User Access to Other User Data

A middle-tier application can prepend a user's query with a logical model to prevent access to other users' data:

let RestrictedData = view () { Data | where UserID == "username@domain.com" };
restrict access to (RestrictedData);
RestrictedData | summarize MonthlySalary = sum(Salary) by Year, Month

Use Cases

1. Row-Level Security

Implement row-level security by restricting access to views that filter data based on user identity:

let UserView = view () { UserData | where UserID == "current_user_id" };
restrict access to (UserView);
UserView | summarize TotalPurchases = sum(PurchaseAmount) by Month

2. Multi-Tenant Applications

Ensure tenants can only access their own data by using restrict statements to limit visibility:

let TenantData = view () { AllData | where TenantID == "tenant_id" };
restrict access to (TenantData);
TenantData | summarize TotalUsage = sum(UsageAmount) by Service

TLDR

The restrict statement in KQL is a versatile tool for managing data access and enhancing security within queries. By understanding its syntax and practical applications, you can effectively control which entities are visible to your queries, ensuring that sensitive data remains protected and accessible only to authorized users. Whether you're implementing row-level security or managing multi-tenant applications, the restrict statement provides the flexibility and control needed to secure your data.