We recently announced our partnership and integration with Snowflake to give joint customers a seamless way to leverage Microsoft 365 and Google Workspace data in their security data lake. Over the next few months, we’ll be sharing examples on how to use this data to measure and improve your security posture.

In this post, I'll explain how to use Material and Snowflake to analyze shadow IT in your organization by discovering all the tools that employees use and comparing them to approved applications.

What is shadow IT?

As organizations grow and become more complex, it's not uncommon for users and teams to resort to using unapproved tools. Although this is rarely done with malicious intent, risks accumulate when these applications are leveraged for activities such as storing or sharing sensitive content. This phenomenon, known as shadow IT, creates a host of problems for organizations, including untracked security vulnerabilities, data loss, and regulatory compliance issues.

Understanding Shadow IT Risk with Material and Snowflake

One of the hardest parts of dealing with shadow IT is simply understanding which apps employees are using. To get full visibility into app usage, you need to account for various types of app access including SSO, OAuth, and direct email signups. This requires collecting and aggregating data from a few key sources and creating metrics you can monitor—a perfect task for a data platform like Snowflake.

Getting data into Snowflake

SSO Data

It’s fairly easy to collect data from common Identity Providers such as Okta into Snowflake using your existing ETL provider. For example, Fivetran customers can use the Okta connector to stream user info and system logs into Snowflake tables. There are also open source tools that can help, such as CloudQuery: Export data from Okta to Snowflake | CloudQuery. Once that’s done, you should have a table like this:

OAuth Data

Microsoft and Google both provide OAuth sign-in logs but exporting these logs to Snowflake traditionally required standing up your own service to poll the APIs or tail logs. In this case, Material does all the heavy lifting. Our tool ingests OAuth data from both Google Workspace and Microsoft and subsequently sends this data to Snowflake for you. 

To begin, set up the Snowflake integration within Material and choose which data to send to Snowflake:

Once the integration has been set up, you can easily query the OAuth data in Snowflake:

Direct sign-up Data

Discovering employees who have used a corporate email to directly sign-up for an application is very tricky. You can’t rely on APIs or network-based approaches. As it turns out, email data is an awesome way to provide visibility here. Material detects machine-generated emails such as password resets, sign-ups, and others to provide a comprehensive picture of direct sign-up app usage. This data is directly available in Snowflake via the above integration: 

Operationalizing the data

Now that we have the three data sources we need streaming  into Snowflake, let’s join them to provide ongoing monitoring of shadow IT:

That’s it! You now have a single view on all the apps being used by your organization based on various different signup types. In terms of operationalizing this information, there are many avenues to explore – you could:

1. Measure new apps each month that have been accessed without SSO, or applications requesting restricted or sensitive scopes. 
2. Check for OAuth apps that are not registered with Azure or Google Workspace. 
3. Pull in other datasets to understand which apps are under contract, cataloged, and may be possible to enroll in SSO. 

The resources in this blog post are a good starting point for monitoring Shadow IT risk, but there are many other use cases that Material Security and Snowflake can help you address as well. Whether you’re looking to uncover relevant security insights, generate custom reporting or detections, or automate investigations, the team at Material is happy to chat. Book a time with our team here.

Resources:

https://gist.github.com/maxpollard/5a918f11ad57adba10de210ee98a84cc