In manufacturing, healthcare, and other industries, individuals and security groups need high visibility reporting on their data to enable quick decision-making. This can eliminate unnecessary downtime or solve potential operational problems in isolated locations. Additionally, restricting unnecessary visibility to data across facilities is best practice for zero trust operations.
The SOC, however, benefits from data analysis across the entirety of data within their global estate. How then, can we avoid duplicating data and deliver to each operational group the exact level of access they need? Responsible information security requires Availability, Integrity and Confidentiality. Azure Sentinel, as part of the rich ecosystem of Azure, with built-in tools for maintaining comprehensive security, allows for flexibility in addressing data access needs, securely.
We're able to use Azure Sentinel to allow SOC analysts access to a broad range of data for threat detection and remediation activities while, through portions of the same data set, allowing facility or data owners visibility to quickly eliminate or mitigate unforeseen operational issues and downtime. Although Azure Sentinel allows resource-context RBAC, some organizations may desire more granular access controls. Azure Data Explorer (ADX) allows granularity of control down to the row level using built-in functions.
In this blog post, we will walk through how to create a sample row-level access-based workbook, which can be extended for Enterprise use with solutions such as Azure Data Factory (ADF). A benefit of using ADF as a data pipeline into ADX is that it allows you to mask and remove data in transit. This may be useful if you only need to preserve a subset of data for forensics or investigation purposes, or if you need to mask sensitive data for any reason. For this sample, we will not be using ADF, but look out for a follow up blog!
Let's start with a few assumptions for this lab:
- We have a set of resources connected to IPs associated with each facility.
- Facility owners should have full access to connection and log details belonging to assets only within their facility ownership responsibilities.
- Resources, networks, assets and changes are tracked within a Central Management Database (CMDB).
- This CMDB is relatively static, so it doesn't make sense to ingest it into Azure Sentinel/Log Analytics, however, it is important to be able to reference against the logs that are flowing into Azure Sentinel, which the SOC uses for it's operations.
Now let's try it!
Create Security Groups
The first step we will take to ensure easy management of access control is to set up Security Groups in Azure AD aligning to each facility's permissions. Add users, as required, to each Security Group. For the SOC, you'll create or use a group that contains all SOC analysts that need access across all facilities (not shown below).
You'll see in the image below that these users can be B2B users as well as employees:
Create an Azure Data Explorer cluster and ingest data
The next step for our sample, is to create a new Azure Data Explorer (ADX) cluster by searching for this PaaS service in your Azure Portal and adding a database:
Read permissions do need to be specifically granted to the Resource Group in which the ADX cluster resides, as well as to the cluster and database, so you'll want to use the Access Control (IAM) settings and permissions to grant this permission to all users who will need partial access to the data.
The first step within the ADX cluster database will be to open the Query Editor and Run the ".create table" command to define your table schema. This should align to the fields of the Database to which you will be granting restricted and full access.
For our example, we'll use a CSV file to make this quick and easy, so if you'd like to follow along, you can use this command inside the "Query" window of the ADX Database, then click:
:
.create table CMDBData (Geo: string, Geo_Delivery_Lead: string, Description: string, Hostname: string, Location: string, IPAddress: string, Network_Desc: string, Plant_Owner: string, site: string)
Here, your table will be created with empty columns, which will be filled in a minute with an uploaded CSV file:
For the CSV file, we've whipped up some fake data, enough to prove the solution works and see this at a glance later on. Note that the column names and types align with the table fields that you've created above.
| Geo | Geo_Delivery_Lead | Description | Hostname | Location | IPAddress | Network_Desc | Plant_Owner | site |
| MFG_MI224 | Scott | DC | DC.domain | 22 Bldg | 172.16.16.100 | PCN-2 | {userA@domain.com} | MI22 |
| MFG_MI225 | Sally | DMZ Workstation | DMZ_Workstation | 22 Lab | 172.16.1.4 | PIN-2 | {userA@domain.com} | MI22 |
| MFG_MI226 | Sam | UserWS | Ecart | 22 Site | 158.81.67.141 | SIM-2 | {userA@domain.com} | MI22 |
| MFG_IL6112 | Erik | ProcessCntrlAsset | PCN_NTPSrvr | 611 Lab | 192.168.1.80 | PCN-1 | {userB@domain.com} | IL611 |
| MFG_IL6112 | Erin | Engineering Wkstn | S7EWSBldg6 | 611 Bldg | 192.168.1.81 | PIN-1 | {userB@domain.com} | IL611 |
| MFG_IL6114 | Elan | PLC | RTC_06 | 611 Site | 192.168.67.81 | SIM-1 | {userB@domain.com} | IL611 |
In the Plant_Owner column, add a user from one of your RLS restricted security groups for userA, and from the other RLS restricted security group for userB.
Once you create and save this .csv, you can upload it to ADX:
Build policies for restricted access
After your ingestion has succeeded, you'll build the Row Level Security Policy under "Query."
- Create the function that filters the table:
.create-or-alter function with () Plant_Data() {
let IsInGroup1 = current_principal_is_member_of('aadgroup=adxrls1;{tenantID}'); let IsInGroup2 = current_principal_is_member_of('aadgroup=adxrls2;{tenantID}');
let DataForGroup1 = CMDBData | where IsInGroup1 and site == ‘IL611’;
let DataForGroup2 = CMDBData | where IsInGroup2 and site == ‘MI22’;
union DataForGroup1, DataForGroup2}
{tenantID} -is your AAD tenant ID (from AAD Overview Screen),
Plant_Data() -is the Function Name (on which you’ll enable the RLS policy),
CMDBData -is the original table that you created to ingest data, and
site == “IL611"; site == “MI22"; - are the keys for the permissions filter.
2. Create the RLS (Row Level Security) Policy based on the function above:
.alter table CMDBData policy row_level_security enable 'Plant_Data'
CMDBData -is the original table that you created to ingest data, and
Plant_Data - is the function name on which you'll enable the RLS Policy.
Note: Once a policy has been defined, only users explicitly granted permission to access the data via the filters defined in the query will be able to read any data from the tables.
To only enforce the policy only at runtime and keep the data available to others while testing the function,
Use the format:
set query_force_row_level_security; Plant_Data
From here, you're ready to test the policy with the saved function name. Log on with on with one of the identities belonging to one of the restricted access security groups that you created earlier and you should see data only associated with site access for that user's group.
Ingest sample logs into Azure Sentinel
For an added step, we would also like to align this with logs in Sentinel to ensure the sample works as desired. Using logger -p with a Linux collector, you can emulate a few activities to align to the IP addresses from the .csv file that you imported into your ADX cluster database.
We've created a sample set that duplicates as a sample kill chain attack in Sentinel. This can be saved and run as a .sh script for ease of re-use if desired. Otherwise, just copy and paste in a CEF collector that's sending logs to Azure Sentinel.
#! /bin/bash
#NOW=`date '+%F %H:%M:%S'`;
NOW=`date -u`;
###Brute Force Attack###
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000064 - user name does not exist|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=StMarsh src=179.124.202.253 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC000006A - user name is correct but the password is wrong|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=KyBroflovski src=113.160.112.125 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000234 - user is currently locked out|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=KeMcCormick src=196.45.177.52 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000072- account is currently disabled|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=BuStotch src=196.45.177.52 dst=158.81.26.141 shost= dhost= dstdestinationDnsDomain=dc.domain"
###Sleep for 5 seconds###
sleep 5s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000064 - user name does not exist|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=WeTestaburger src=179.124.202.253 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC000006A - user name is correct but the password is wrong|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=TwTweak src=113.160.112.125 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000234 - user is currently locked out|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=BeStevens src=196.45.177.52 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000072- account is currently disabled|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=BrBiggle src=196.45.177.52 dst=158.81.26.141 shost= dhost= dstdestinationDnsDomain=dc.domain"
###Sleep for 5 seconds###
sleep 5s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000064 - user name does not exist|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=ClDonovan src=179.124.202.253 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC000006A - user name is correct but the password is wrong|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=CrTucker src=113.160.112.125 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000234 - user is currently locked out|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=JiValmer src=196.45.177.52 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000072- account is currently disabled|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=TiBurch src=196.45.177.52 dst=158.81.26.141 shost= dhost= dstdestinationDnsDomain=dc.domain"
###Sleep for 5 seconds###
sleep 5s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000064 - user name does not exist|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=RaMarsh src=179.124.202.253 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC000006A - user name is correct but the password is wrong|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=ShMarsh src=113.160.112.125 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000234 - user is currently locked out|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=JiKern src=196.45.177.52 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000072- account is currently disabled|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=BrBiggle src=196.45.177.52 dst=158.81.26.141 shost= dhost= dstdestinationDnsDomain=dc.domain"
###Sleep for 5 seconds###
sleep 5s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000064 - user name does not exist|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=GeBroflovski src=179.124.202.253 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC000006A - user name is correct but the password is wrong|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start=$NOW end=$NOW suser= duser=ShBroflovski src=113.160.112.125 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000234 - user is currently locked out|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=KySchwartz src=196.45.177.52 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|0xC0000072- account is currently disabled|4625 - An account failed to log on.|1|act=Failure deviceExternalID=4625 start= end= suser= duser=JaTenorman src=196.45.177.52 dst=158.81.26.141 shost= dhost= dstdestinationDnsDomain=dc.domain"
###Sleep for 10 seconds###
sleep 10s
###Successful login to one of the Brute Force targeted accounts; 158.81.26.141###
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|10 - RemoteInteractive|4624 - An account was successfully logged on.|2|act=Success deviceExternalID=4624 start= end= suser= duser=ErCartman src=196.45.177.52 dst=158.81.26.141 shost= dhost= destinationDnsDomain=dc.domain"
###Sleep for 30 seconds###
sleep 30s
###Login to 172.16.1.4(DMZ_Workstation)###
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|10 - RemoteInteractive|4624 - An account was successfully logged on.|2|act=Success deviceExternalID=4624 start= end= suser= duser=ErCartman src=dst=172.16.1.4 shost= dhost=DMZ_Workstation destinationDnsDomain=dc.domain"
###Sleep for 60 seconds###
sleep 60s
###Login to AD, change an existing IOT authorised user password create new user account, added to IOT authroized user group, reset password; 172.16.16.100 (dc.domain)###
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|2019|10 - RemoteInteractive|4624 - An account was successfully logged on.|2|act=Success deviceExternalID=4624 start= end= suser=ErCartman duser=StMarsh-sa src=172.16.1.4 dst=172.16.16.100 shost=DMZ_Workstation dhost=dc.domain destinationDnsDomain=dc.domain"
###Sleep for 10 seconds###
sleep 10s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|2019||4723 - An attempt was made to change an account's password|1|act=Failure deviceExternalID=4723 start= end= suser=StMarsh-sa duser=ErCartman-IOT src=dst=172.16.16.100 shost= dhost=dc.domain destinationDnsDomain=dc.domain"
###Sleep for 30 seconds###
sleep 30s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|2019||4720 - A user account was created.|2|act=Success deviceExternalID=4720 start= end= suser=StMarsh-sa duser=LiCartman-IOT src=dst=172.16.16.100 shost= dhost=dc.domain destinationDnsDomain=dc.domain"
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|2019||4724 - An attempt was made to reset an account's password|2|act=Success deviceExternalID=4724 start= end= suser=StMarsh-sa duser=LiCartman-IOT src=dst=172.16.16.100 shost= dhost=dc.domain destinationDnsDomain=dc.domain"
###Sleep for 20 seconds###
sleep 20s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|2019||4728 - A member was added to a security-enabled global group|2|act=Success deviceExternalID=4728 start= end= suser=StMarsh-sa duser=LiCartman-IOT src=dst=172.16.16.100 shost= dhost=dc.domain destinationDnsDomain=dc.domain dpriv=dc.domain\IOT-Engineering"
###Sleep for 60 seconds###
sleep 60s
###Login to Process control asset; 192.168.1.80(PCN_NTPSrvr)###
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|10 - RemoteInteractive|4624 - An account was successfully logged on.|2|act=Success deviceExternalID=4624 start= end= suser=ErCartman duser=LiCartman-IOT src=172.16.1.4 dst=192.168.1.80 shost=DMZ_Workstation dhost=PCN_NTPSrvr destinationDnsDomain=dc.domain"
###Sleep for 60 seconds###
sleep 60s
###Login to Engineering workstation; 192.168.1.81(S7EWSBldg6)###
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10|10 - RemoteInteractive|4624 - An account was successfully logged on.|2|act=Success deviceExternalID=4624 start= end= suser=LiCartman-IOT duser=S7EWSBldg6\cooladmin src=192.168.1.80 dst=192.168.1.81 shost=PCN_NTPSrvr dhost=S7EWSBldg6 destinationDnsDomain= "
###Sleep for 120 seconds###
sleep 120s
###Delete log traces - remove group membership, delete account, clear audit logs###
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10||1102 - The audit log was cleared|2|act=Success deviceExternalID=1102 start= end= suser=cooladmin duser= src=dst=192.168.1.81 shost= dhost=S7EWSBldg6 destinationDnsDomain=S7EWSBldg6"
###Sleep for 30 seconds###
sleep 30s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10||1102 - The audit log was cleared|2|act=Success deviceExternalID=1102 start= end= suser=LiCartman-IOT duser= src=dst=192.168.1.80 shost= dhost=PCN_NTPSrvr destinationDnsDomain=dc.domain"
###Sleep for 30 seconds###
sleep 30s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|2019||4729 - A member was removed from a security-enabled global group|2|act=Success deviceExternalID=4729 start= end= suser=StMarsh-sa duser=LiCartman-IOT src=dst=172.16.16.100 shost= dhost=dc.domain destinationDnsDomain=dc.domain dpriv=dc.domain\IOT-Engineering"
###Sleep for 30 seconds###
sleep 30s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|2019||4726 - A user account was deleted.|2|act=Success deviceExternalID=4726 start= end= suser=StMarsh-sa duser=LiCartman-IOT src=dst=172.16.16.100 shost= dhost=dc.domain destinationDnsDomain=dc.domain"
###Sleep for 30 seconds###
sleep 30s
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|10||1102 - The audit log was cleared|2|act=Success deviceExternalID=1102 start= end= suser=ErCartman duser= src=dst=172.16.1.4 shost= dhost=DMZ_Workstation destinationDnsDomain= "
logger -p auth.info -n localhost -t CEF "CEF:0|Microsoft|Microsoft-Windows-Security-Auditing|2019||1102 - The audit log was cleared|2|act=Success deviceExternalID=1102 start= end= suser=StMarsh-sa duser= src=dst=172.16.16.100 shost= dhost=dc.domain destinationDnsDomain= "
Build cross-solution workbook
Now we're ready to build the cross-solution workbook in Azure Sentinel. The first step we recommend is to test your external data query in your Sentinel logs and then validate the IP address match to the sample CEF logs you ran above.
adx("cmdbdata.eastus/CMDB_Sample").Plant_Data
//(Name of Cluster, reference in adx database)
| join (CommonSecurityLog)
on $left.IPAddress == $right.DestinationIP
| summarize count() by DeviceAction
From here, you can create a new workbook to visualize the completely different access views to row level restricted data based on the security group to which each authenticated user belongs.
"join" flavor Hint:
Your sample deliverable from this exercise should be the ability to access the same workbook in Azure Sentinel as two different users from two different security groups to see entirely different sets of data.
Here are a couple simple sample queries to start with for your workbook:
adx("{ADXClusterName.region/DatbaseName}").Plant_Data
| join (CommonSecurityLog)
on $left.IPAddress == $right.DestinationIP
| where TimeGenerated >= (7d)adx("{ADXClusterName.region/DatabaseName}").Plant_Data
| join (CommonSecurityLog)
on $left.IPAddress == $right.DestinationIP
| summarize
Successful_Connection = countif(DeviceAction=="Success" or DeviceAction=="Successful" or DeviceAction=="accept"),
Failed_Connection = countif(DeviceAction=="Failure")
by DeviceEventClassID, DestinationIP
We hope you had fun with this lab exercise and can think of some other uses for this ability to provide Row Level Access Restrictions in Azure Sentinel using Azure Data Explorer!
Special thanks to co-authors @umnagdev and @Innocent Wafula and also thanks to @Jeff_Chin, who's post Limitless Microsoft Defender for Endpoint Advanced Hunting with Azure Data Explorer (ADX) was the inspiration for this solution!
Posted at https://sl.advdat.com/3liIl7K