Last Update: Sep 05, 2024 | Published: May 31, 2018
A recent exercise to analyze the potential leakage of information from an Microsoft 365 tenant following a hacking attack posed the question: “with whom outside the company do we share documents and files?” It’s a great question, and it’s one that has no obvious answer. The usage data for SharePoint Online available in the standard Office 365 reports tells us who shares documents inside and outside the tenant (Figure 1), but it tells us nothing about what they share.
Before we consider how to discover more about the sharing habits of tenant users, we need to understand how sharing happens within Office 365.
Sharing has always been a big part of email. Users attach files to messages and send to all and sundry on an as-needed basis. Message tracking logs have been part of Exchange for twenty-odd years, but (and rather bizarrely) have never captured attachment names. A transport agent might do the trick, but you cannot install transport agents within Exchange Online. Content searches can also find messages sent with attachments, but this is an activity best done during a formal investigation when you have some knowledge about what you are looking for.
Given that users will continue to share documents by email, if you want to protect sensitive documents, you have a choice of data loss prevention rules, message encryption (applied by users or by transport rules), or rights management templates. Later this year, Microsoft will unify Office 365 classification labels and Azure Information Protection labels to allow automatic application of a protection template when users assign labels to messages.
Getting back to documents, when you think about the volume of sharing that occurs within Office 365, you can only conclude that more sharing will happen over time. We encourage users to keep their files online in OneDrive for Business and SharePoint Online libraries instead of using local storage (or antiquated network shares). Microsoft has worked hard to make the sharing mechanism simple and easy to understand. Users get further encouragement from Outlook and OWA to use of smart attachments, where links to documents replace copies of files. And when documents live online, all sorts of interesting things can happen, like real-time co-authoring or Autosave (which can be a boon and a problem).
But the biggest influence on the increased use of SharePoint Online and OneDrive for Business is Teams, and to a lesser degree, Office 365 Groups. Both applications, which are only available in the cloud, replace the complexity of SharePoint’s browser interface with the simple idea that Files are where you put files, including those you want to share with others. Groups has its own take on Files, but either way, easier and more approachable interfaces have helped SharePoint usage grow.
In terms of numbers, Microsoft says that over 200,000 organizations now use Teams. Well, they all use Files and SharePoint Online, and I bet they all do some sharing.
SharePoint has a long history of being able to share with external users. Two kinds of sites exist within Office 365: old-style and modern. The modern sort use Office 365 Groups to manage membership while the old-style manage permissions based on user accounts.
In either case, the same kind of controls limit sharing outside the tenant, including the ability to share with external users known to the tenant directory. In other words, Azure Active Directory guest user accounts. You can add guest users through the Azure Active Directory portal or by issuing an invitation to an email address outside your tenant through Teams or Office 365 Groups.
SharePoint Online synchronizes information about tenant and guest users from Azure Active Directory to its own directory (SPODS). SharePoint then uses that information to manage site membership for old-style sites. We can check this information with the Get-SPOUser cmdlet (to read all users) or Get-SPOExternalUser cmdlet (just guests).
As an example, here’s some PowerShell using the Get-SPOUser cmdlet to scan down through all sites in a tenant and report guest users.
$Sites = Get-SPOSite -Limit All $Report = @() ForEach ($Site in $Sites) { Write-Host "Examining" $Site.Url $Users = Get-SPOUser -Limit All -Site $Site.Url -ErrorAction SilentlyContinue ForEach ($U in $Users) { If ($U.LoginName -like "*#EXT#*" -or $U.LoginName-like "*urn:spo:guest*") { $ReportLine = [PSCustomObject][Ordered]@{ User = $U.DisplayName UPN = $U.LoginName Site = $Site.Url } $Report += $ReportLine } }}
We use two tests to check for guest users. The first looks for guest accounts in the tenant directory. The second is for accounts that gain access through time-sensitive codes created to share without creating a guest account, which is the new approach taken by SharePoint Online and OneDrive for Business since late 2017
The output is an ordered array, so we can look at it in different ways. For instance, to we can sort by user principal name to see what sites each external user can access.
$Report | Sort UPN, Site | Select UPN, Site UPN Site --- ---- brianr_test.com#ext#@tenant.onmicrosoft.com https://tenant.sharepoint.com/sites/exchangegoms brianr_test.com#ext#@tenant.onmicrosoft.com https://tenant.sharepoint.com/sites/exchange brianr_test.com#ext#@tenant.onmicrosoft.com https://tenant.sharepoint.com/sites/O365Grp-Grp
A record generated for one-time access looks like this:
urn:spo:guest#[email protected] https://tenant.sharepoint.com/sites/seattlecoffee
One thing this exercise revealed for my tenant is that if you remove an external user from an Office 365 group, SharePoint does not remove their user profile from this site. Apart from making the output of Get-SPOUser unreliable for any site managed by an Office 365 Group, this doesn’t matter too much because the group controls access to the site.
We must be able to use Get-SPOExternalUser then to extract a reliable list of external people who have access to our SharePoint sites? Well, here’s some code using Get-SPOExternalUser. This cmdlet is funky because it only returns 50 objects. This might have been OK for on-premises SharePoint where site sharing is limited, but it’s not acceptable for SharePoint Online where an Office 365 Group can have up to 2,500 members. In any case, here’s some code to scan for external SharePoint users.
$Sites = @() $Sites = (Get-SpoSite -Limit All | ? {$_.SharingCapability -ne "Disabled" -and $_.Template -eq "Group#0"}) $Report = @() Write-Host "Processing" $Sites.Count "sites..." ForEach ($Site in $Sites) { Write-Host "Site:" $Site.Url "Title:" $Site.Title "Template:" $Site.Template $ExtUsers = $Null Try { for ($i=0;;$i+=50) { $ExtUsers = (Get-SpoExternalUser -Site $Site.Url -PageSize 50 -Position $i -ErrorAction Stop | Select DisplayName, Email, WhenCreated) }} Catch { } # $ExtUsers = (Get-SpoExternalUser -Site $Site.Url -PageSize 50 | Select DisplayName, Email, WhenCreated) If ($ExtUsers.Count -ne 0) { ForEach ($E in $ExtUsers) { $ReportLine = [PSCustomObject][Ordered]@{ User = $E.DisplayName Email = $E.EMail Group = $Site.Title Site = $Site.Url Count = $ExtUsers.Count WhenCreated = $E.WhenCreated Type = "SPOSite" } $Report += $ReportLine } } }
Once again, we generate an ordered array. Get-SPOExternalUser outputs the creation date of the guest user account in the tenant. It would be nicer if it told us when a user gained access to a site.
$Report | Sort Email, Site | Format-Table User, Site, WhenCreated Email Site WhenCreated ----- ---- ----------- Vasil Michev https://tenant.sharepoint.com/sites/brk3001 29/09/2016 23:22:09 Vasil Michev https://tenant.sharepoint.com/sites/exchangegoms 29/09/2016 23:22:09 Vasil Michev https://tenant.sharepoint.com/sites/exchange99 29/09/2016 23:22:09 Vasil Michev https://tenant.sharepoint.com/sites/mailfilterpatent 29/09/2016 23:22:09
Get-SPOExternalUser suffers from the same problem as Get-SPOUser – it reports external access to a site when that access vanished some time ago because a guest user left the membership of an Office 365 Group.
My attempt to find out who outside the tenant has access to information in SharePoint sites is somewhat successful. However, neither cmdlet we used reports correct information when a site is under the control if an Office 365 Group. That’s not good and Microsoft should fix the underlying synchronization problem.
We need a different approach to crack this problem.
In part 1 of this series, we explore how document sharing occurs within Office 365 and how to use two cmdlets in the SharePoint Online PowerShell module to understand with whom outside the tenant we share documents.
As noted, the Get-SPOUser and Get-SPOExternalUser cmdlets have some problems when processing group-enabled SharePoint sites. These sites use Office 365 Groups to manage membership, and the results returned by the cmdlets do not necessarily reflect the view of Office 365 Groups.
Given the popularity of Teams and Groups, it is likely that most sites now running inside Office 365 tenants use Groups for their membership. We can absolutely use the SharePoint cmdlets to process sites that are not group-enabled, but we need another solution to deal with the sites owned by Office 365 Groups.
The solution is to examine the membership of Office 365 Groups with guest members. Fortunately, Groups gives use a filterable property (GroupExternalMemberCount) that makes it easy to extract the set of groups with guest members. A similar property (GroupMemberCount) holds the total number of members in a group. Groups updates the two properties automatically as membership changes occur, including changes made through Teams.
Once we know what groups to examine, we can loop through each group to extract details of guest members. Here’s some code to do the trick.
$Groups = (Get-UnifiedGroup -Filter {GroupExternalMemberCount -gt 0} | Select Alias, DisplayName, SharePointSiteURL, GroupExternalMemberCount) If ($Groups.Count -gt 0) { Write-Host "Processing" $Groups.Count "groups with guest members" $Report = @() $NumExt = 0 $LargestGroup = $Null $LargestGroupNum = 0 ForEach ($G in $Groups) { Write-Host "Processing" $G.DisplayName $Users = Get-UnifiedGroupLinks -Identity $G.Alias -LinkType Members ForEach ($U in $Users) { If ($U.Name -Match "#EXT#" -and $U.Name -NotLike "*teams.ms*") { $NumExt++ $CheckName = $U.Name + "@yourtenant.onmicrosoft.com" $User = (Get-AzureADUser -ObjectId $CheckName).DisplayName $ReportLine = [PSCustomObject][Ordered]@{ Email = $U.Name User = $User Group = $G.DisplayName Site = $G.SharePointSiteURL } $Report += $ReportLine } } If ($G.GroupExternalMemberCount -gt $LargestGroupNum) { $LargestGroupNum = $G.GroupExternalMemberCount $LargestGroup = $G.DisplayName} } Write-Host $NumExt "guest user memberships found in" $Groups.Count "groups" Write-Host "Largest external group is" $LargestGroup "with" $LargestGroupNum "guests"
At the end of the script, we have a couple of lines to report statistics. As you can see in Figure 1, 37 groups have guest users in their membership when we scan these groups, we find 224 instances of guest membership. The group with most guests has 63.
The script creates an ordered array of guests found in group membership. It is easy to sort the array and look at the data in whatever way you like. For example, you could sort by user to be able to see what groups each user belongs to.
$Report | Sort User | Format-Table User, Group, Site -AutoSize User Group Site --- ----- ---- Ailbhe Smith (Hotmail) Office 365 Tenant Health https://tenant.sharepoint.com/sites/office365Health Ian Byrne Dynamic Passion https://tenant.sharepoint.com/sites/DynamicPass Ian Byrne Exchange Trades https://tenant.sharepoint.com/sites/exchangetrades
Of course, not all guest users will access documents as many will collaborate via email (for Office 365 Groups) or channel and private conversations (Teams). It’s possible that no one ever puts anything in the document library in the site collection. But if they do, those documents are accessible to guest users, who enjoy exactly the same rights over the documents as do users who belong to the tenant
We now have methods to extract details about who can share documents in old-style and new-style SharePoint sites. This is good information for tenant administrators to have because you never know when someone might ask who can access documents. It’s knowledge of sharing that might happen if a guest user accesses a document library.
However, SharePoint and OneDrive for Business also support sharing for individual documents and folders. To understand when this kind of sharing occurs, we must look elsewhere.
This article wraps up by considering how to interrogate the Office 365 audit log to understand who outside the tenant is accessing files in SharePoint. As you might know, the Office 365 audit log ingests audit events generated by many different workloads and normalizes the events so that they look almost the same. The audit log holds records for 90 days. If you need to go back further, you need to use Microsoft’s Advanced Security Management (part of Office 365 E5 or available as an add-on) or an ISV product.
SharePoint is a “chatty” application in that it generates many audit events, possibly because of its background in document management. The downside is that you might have to look harder to find the right information; the upside is that if you need to know something about who did what and when, SharePoint probably logs a record for that operation.
Among the data SharePoint records is when someone accesses (opens) a document in a library. Previously, we discovered who has access to sites, but the fact that someone has access really doesn’t matter if they never use that access. Things become more serious when someone opens a document.
The Office 365 Security and Compliance Center includes the audit log search feature. However, because SharePoint generates so many audit records, I find it easier to search with PowerShell.
The code below looks for all audit records for “FileAccessed” events between two dates and filters them to select only records belonging to guest users, which might access files through Office 365 Groups or Teams. We then process the records to extract information from the JSON-format audit data and throw away any records for common SharePoint files that people access as a by-product of opening a library, like “AllItems.aspx” and “_siteIcon_.jpg,” which we don’t need to worry about because they are SharePoint system files. In my experience, filtering these files out reduces the number of records by about 60%, which proves quite how pedantic SharePoint is at recording audit data.
$Records = (Search-UnifiedAuditLog -StartDate 17-Jan-2018 -EndDate 18-Apr-2018 -Operations FileAccessed -ResultSize 2000 | ? {$_.UserIds -Like "*#EXT#*" }) If ($Records.Count -eq 0) { Write-Host "No SharePoint file access records found for guest users." } Else { Write-Host "Processing" $Records.Count " SharePoint file access audit records..." $Report = @() ForEach ($Rec in $Records) { $AuditData = ConvertFrom-Json $Rec.Auditdata If ($AuditData.SourceFileName -NotLike "*aspx*" -And $AuditData.SourceFileName -NotLike "*jpg*" ) { $ReportLine = [PSCustomObject][Ordered]@{ TimeStamp = $AuditData.CreationTime User = $Rec.UserIds Action = $AuditData.Operation Workload = $AuditData.Workload URL = $AuditData.SiteUrl Document = $AuditData.SourceFileName } $Report += $ReportLine } } }
The output is an ordered array, which makes it very easy to sort in the order we want. For instance, here’s how we might look at the data sorted by user to discover what documents individual guest users have accessed.
$Report | Sort User, Document | Format-Table TimeStamp, User, Document -AutoSize TimeStamp User Document -------- ---- -------- 2018-03-22T22:54:39 john_contoso.com#ext#@Tenant.onmicrosoft.com Summit 2018.one 2018-03-29T11:58:41 mary_contoso.com#ext#@tenant.onmicrosoft.com Summit 2016.one 2018-03-29T11:28:41 mary_contoso.com#ext#@tenant.onmicrosoft.com Updates.docx 2018-03-15T08:37:32 terry_contoso.com#ext#@tenant.onmicrosoft.com Amazon Blurb.docx
Looking for FileAccessed audit records gives us the ability to keep an eye on the documents opened by guest users, but SharePoint also supports sharing through time-limited invitations based on access codes, a mechanism rolled-out to Office 365 tenants in early 2018 to avoid the need to create guest user accounts in the tenant directory.
As Microsoft explains in a support article, the underlying sharing mechanisms differ when a user opens a file because they have been granted access to that file (for example, because they are a member of a team) and when you use a sharing invitation to share a file. Boiling everything down, SharePoint logs different audit events to record the issuing and use of sharing links.
The support article illustrates the flow of sharing in Figure 1. Unfortunately, only one of the audit event names called out in the figure is correct. After examining the audit events logged for many sharing instances, it seems like SharePoint generates four events:
The support article suggests that you search the audit log and export results to a CSV and open that file with Excel for further analysis. That’s certainly a valid approach and a good one if you are more comfortable using Excel than PowerShell.
But as I started with PowerShell, I’ll continue and amend the code used previously to find files accessed by guest users to find sharing events instead.
$Records = (Search-UnifiedAuditLog -StartDate 28-Mar-2018 -EndDate 17-Apr-2018 -Operations SharingSet, SecureLinkUsed, SecureLinkCreated -ResultSize 2000) If ($Records.Count -eq 0) { Write-Host "No SharePoint sharing records found." } Else { Write-Host "Processing" $Records.Count " SharePoint sharing audit records..." $Report = @() ForEach ($Rec in $Records) { $AuditData = ConvertFrom-Json $Rec.Auditdata Switch ($AuditData.TargetUserOrGroupType) { "Member" { $SharedType = "Tenant User" } "SharePointGroup" { $SharedType = "SPO Sharing Link" } "Guest" { $SharedType = "Guest User" } } $ReportLine = [PSCustomObject][Ordered]@{ TimeStamp = $AuditData.CreationTime User = $Rec.UserIds Action = $AuditData.Operation Workload = $AuditData.Workload URL = $AuditData.ObjectId Document = $AuditData.SourceFileName SharedWith = $AuditData.TargetUserOrGroupName SharedType = $SharedType ItemType = $AuditData.ItemType Event = $AuditData.EventData TargetType = $AuditData.TargetUserOrGroupType ClientIP = $AuditData.ClientIP } $Report += $ReportLine } } $Report | Format-List TimeStamp, User, SharedEmail, SharedWith, SharedType, Document, Action, Event, TargetType
Here’s the first two events logged when sharing occurs. A user decides to share a document with someone outside the tenant. We see a SharingSet event and a SecureLinkCreated event.
TimeStamp : 2018-04-16T16:21:42 User : [email protected] SharedWith : SharingLinks.dd7d0f94-7757-4646-afcd-c1b824f8676d.Flexible.7a5975c0-8ac2-4167-ba4b-70296cd8df9d SharedType : SPO Sharing Link Document : Migration Datasheet.pdf Action : SharingSet TimeStamp : 2018-04-16T16:21:43 User : [email protected] SharedType : SPO Sharing Link Document : Migration Datasheet.pdf Action : SecureLinkCreated Event : View Event : Read</Permissions granted>
The target user receives the sharing invitation by email. When they use the link to receive a one-time access code to open the document, we see the SecureLinkUsed event.
TimeStamp : 2018-04-16T16:29:45 User : urn:spo:guest#[email protected] SharedType : SPO Sharing Link Document : Migration Datasheet.pdf Action : SecureLinkUsed
Finally, SharePoint logs a second SharingSet event to note that the external user has access. Notice the format of the username logged, where the target user’s email address is prefixed with “urn:spo:guest#.”
TimeStamp : 2018-04-16T16:29:45 User : urn:spo:guest#[email protected] SharedWith : Limited Access System Group SharedType : SPO Sharing Link Document : Migration Datasheet.pdf Action : SharingSet Event : Limited Access</Permissions granted>
By comparison, if the external user already has a guest account in the tenant directory, SharePoint does not need to generate sharing invitations as the target user is known. In this case, all we see is a SharingSet event to record the permission update to allow the guest to access the document.
TimeStamp : 2018-03-29T20:45:39 User : [email protected] SharedWith : drag24_outlook.com#ext#@tenant.onmicrosoft.com SharedType : Guest User Document : Compliance Framework_customer guidance.pdf Action : SharingSet Event : Read</Permissions granted> TargetType : Guest
Over this three-part series we have learned how to extract information about external users who can access traditional SharePoint sites and group-enabled SharePoint sites. That information helps us to understand where external users come from. The information presented here gives us the evidence of when external access happens to SharePoint content. The sum of the information should give us a solid picture of what people outside the tenant have access to inside the tenant.
SharePoint makes sharing easy. Too easy in the eyes of some. But an easy answer exists if you want to make sure that the content of sensitive documents does not leak outside the tenant. Protect those documents with an Azure Information Protection template that restricts access to tenant users. That way, even if a document finds its way to someone who shouldn’t have it, they won’t be able to view its content.