Azure kusto cluster table implement row level security with data columns based on which filtering would be done are across multiple tables?

user_7909565 0
Viewed 54

I am trying to implement row level security in Azure Kusto Cluster table but all the example which I see just works on a single table data. In my case to apply the filter, I need to join two tables. For e.g. I have tables X & Y, I need to apply row level security on table X but there is no column in table X which can help me apply filter, that column/data is there in table Y. So, is this scenario possible in ADX Or not?

row-level-security azure-data-explorer azure
1 Answers

Yes, it is supported.
Here is a quick demo

.set-or-replace MyEventsTable <|
range EventID from 1 to 10 step 1
| extend EventTimestamp = ago(365d*rand())
| extend UserID = tolong(rand(3))

MyEventsTable
EventID EventTimestamp UserID
1 2021-06-29T12:52:38.8227264Z 2
9 2021-09-11T12:53:42.8539446Z 0
8 2021-10-09T05:29:14.3731005Z 2
4 2021-11-09T09:59:12.1653628Z 0
7 2021-11-15T08:10:06.9281365Z 1
6 2022-01-02T09:46:46.9731912Z 1
2 2022-03-09T07:44:31.2875361Z 0
10 2022-03-14T01:10:23.1950573Z 0
3 2022-04-28T08:03:51.4099464Z 0
5 2022-05-04T05:08:15.941019Z 2 
.set-or-replace MyUsersTable <|
print UserEmail = pack_array("tic@microsoft.com", "tac@microsoft.com", current_principal_details().UserPrincipalName)
| mv-expand with_itemindex=UserID UserEmail to typeof(string)
| project-reorder UserID

MyUsersTable
UserID UserEmail
0 tic@microsoft.com
1 tac@microsoft.com
2 {manually redacted}@microsoft.com 
.create-or-alter function MyEventsTable_RLS(){
    MyEventsTable
    | where UserID == toscalar(MyUsersTable | where UserEmail == current_principal_details().UserPrincipalName | project UserID)
}

.alter table MyEventsTable policy row_level_security enable "MyEventsTable_RLS"

MyEventsTable
EventID EventTimestamp UserID
1 2021-06-29T12:52:38.8227264Z 2
8 2021-10-09T05:29:14.3731005Z 2
5 2022-05-04T05:08:15.941019Z 2
user_63364790
Related