is it possible by e.g. a mapping table for users and their roles to enable users to only read specific values in database tables (filtered rows)? Kind regards,
1 Answers
Here is a quick demo
.set-or-replace MyEventsTable <|
range EventID from 1 to 10 step 1
| extend EventTimestamp = ago(365d*rand())
| extend UserEmail = tostring(pack_array("tic@microsoft.com", "tac@microsoft.com", current_principal_details().UserPrincipalName)[toint(rand(3))])
MyEventsTable
| EventID | EventTimestamp | UserEmail |
|---|---|---|
| 9 | 2021-07-31T04:22:12.2312141Z | {redacted manually}@microsoft.com |
| 4 | 2021-07-31T22:24:13.1675234Z | {redacted manually}@microsoft.com |
| 1 | 2021-08-11T06:30:55.6696579Z | {redacted manually}@microsoft.com |
| 6 | 2021-09-08T16:43:24.3680381Z | tic@microsoft.com |
| 3 | 2021-09-18T23:16:14.4731615Z | {redacted manually}@microsoft.com |
| 5 | 2021-09-26T04:46:08.4652178Z | tac@microsoft.com |
| 2 | 2022-01-08T13:21:33.908322Z | tac@microsoft.com |
| 10 | 2022-01-12T00:53:07.087596Z | {redacted manually}@microsoft.com |
| 7 | 2022-03-23T18:55:29.1402369Z | tac@microsoft.com |
| 8 | 2022-04-25T20:23:31.7764039Z | tic@microsoft.com |
.create-or-alter function MyEventsTable_RLS(){
MyEventsTable
| where UserEmail == current_principal_details().UserPrincipalName
| extend UserEmail = strcat("*****", extract("(@.*)", 1, UserEmail))
}
.alter table MyEventsTable policy row_level_security enable "MyEventsTable_RLS"
MyEventsTable
| EventID | EventTimestamp | UserEmail |
|---|---|---|
| 9 | 2021-07-31T04:22:12.2312141Z | *****@microsoft.com |
| 4 | 2021-07-31T22:24:13.1675234Z | *****@microsoft.com |
| 1 | 2021-08-11T06:30:55.6696579Z | *****@microsoft.com |
| 3 | 2021-09-18T23:16:14.4731615Z | *****@microsoft.com |
| 10 | 2022-01-12T00:53:07.087596Z | *****@microsoft.com |
Related