I recently published a lengthy diatribe about the problem of over-permissive access to data, the “silent killer” of security. Today, I’d like to direct my security rage towards a more specific culprit, over-privileges in Snowflake DB, and explain how Satori sees and tackles this problem. Let’s start with the problem statement: Why do we even have over-privileges? Let’s say that we have a business that exclusively sells Origami, and we have a team in charge of packing and shipping the orders, it’s quite simple to decide what data our team needs access to in order to successfully carry out their work! Unfortunately, the realities of most enterprises and organizations aren’t usually this simple:
- The packing team is a simple team. What about data scientists, business analysts and other teams and individuals who run and grow the company by leveraging data across the business?
- There are privacy regulations which may prevent employees from a certain region from accessing data of another region.
- We often lack the motivation or knowledge to reduce access to enable project work following that project’s completion.
Having a healthy privilege fitAs former data and security specialists and executives at large organizations, the Satori team appreciates the gains of attaining full data enlightenment and addressing privilege-related risks:
- It significantly reduces unnecessary risk
- It frees up data engineering teams to do actual data engineering instead of constantly rubber stamping permission grants
- It frees up data analytics teams and other data users to do actual data analytics, instead of having to sit on their thumbs as they await access grants to necessary resources
Staying on top of your privilege fitOne of the biggest challenges around privilege management is that they can be obscured by different factors. It’s challenging to manage the information of whether each role has grants to objects they don’t need in Snowflake with hundreds of roles and thousands of objects. This is especially true when permissions to objects change, usage of these objects changes, and when the objects themselves change. The ability to manage hierarchical roles in Snowflake further contribute to data obscurity; in some cases, users assigned to a certain role also receive unintended permissions that are inherited from the “upstream” roles. Similar confusion also reigns “parent” roles. In other words, while hierarchical roles can provide a smart way to manage permissions (change one setting instead of multiple settings), it can backfire and increase complexity.
Incremental risk reductionRisk isn’t binary, and you may not want to make a lot of configuration changes around your permissions in one “big splash”. In addition, the notion of having to do a major change in order to reduce risk postpones the risk reduction and causes the risk to stay. That’s why we believe that an approach to reduce the risk of over-privileges should allow for organizations to reduce their risk incrementally and according to a gradual plan that will reduce the effect of permissions changes on the business. Using Satori’s permissions analysis does just that. It gives you a prioritized dashboard displaying your organization’s over-privileged roles and users. Satori helps you mitigate their risks from day one, allowing you to obtain granular information for any role and speed up the process.
Analytics layer on top of your access controlI highly recommend leveraging an analytics layer on top of your Snowflake access control to gain an in-depth understanding of over-privileges and other security and operational issues. This can help you immediately reduce risk in your Snowflake data warehouse. Imagine a certain team used a certain table, and after a certain point stopped using that table. From that point onwards, they no longer provide value based on that data, but the risk involved in them having access to the data remains- and even grows when new data is added to that table. Satori’s permissions analysis helps you identify these scenarios. Below you can see a Satori report highlighting the roles having the most unused privileges: Usage within certain roles forms another prime example. Imagine that a certain role isn’t used by users with access. Those users have access to a role they don’t use but their ability to use that role still imposes one. By using Satori’s permission analysis, you can find out who they are. Below is a sample report displaying users usage of the ACCOUNTADMIN role. As indicated, the SHAHARSATORICYBER has no usage of this role, but still has the potential risk of running queries as an ACCOUNTADMIN. You can also see four tables which the role enables access to, but were never actually accessed:
How does it work?When designing Satori permission analytics, we wanted to create a process that would be as simple and efficient as possible. Our permission analytics reads the configuration and logs of your Snowflake account, a process that can take several minutes (this depending on the amount of activity and complexity of roles structure in your snowflake schema). Once the analysis is complete, the report is made available via the Satori management console. When you want to take another snapshot, you can simply generate another report. In subsequent reports, the preparation time is faster, as Satori only analyzes the difference between the current metadata and the last recorded metadata.
PrerequisitesIn order to use Satori’s permissions analytics, you need to create a Snowflake user for Satori, and grant privileges to Snowflake’s metadata tables. Note that this access is read-only by definition (as is the metadata), and the role does not provide access to any of your data, only to the metadata of your Snowflake account. You can either use an existing user with a role enabling such privileges, or create a user with sufficient privileges just for Satori. The following example will create a satori_reader user with a satori_reader_role role enabling it to read data from Snowflake metadata views. Note: Please change the <CHOOSE_PASSWORD> to a strong password of your choice. You must provide Satori with the name of the Snowflake warehouse that Satori will use. As with the service user, you can either use an existing warehouse, or create a data warehouse specifically for Satori, by using the CREATE WAREHOUSE command: Moreover, if your Snowflake account has a network policy restricting access to specific IP addresses, you will need to implement a network policy specific to the satori_reader user in order to enable network access from Satori to your Snowflake account. For example:
DeploymentLog in to your Satori management console. If you still don’t have an account, you can set it up here. In the navigation menu, choose “Permissions”: You will get a welcome screen, in which you need to click the GO! Button: Fill your Snowflake data store configuration and click CREATE: Next, fill in the details specific to the service user you created for Satori,. Be sure to test your connection before saving it: Once you save the details, Satori will automatically start reading your metadata, at which point you can begin working with our over-privilege reports.
Your first over-privilege report: incrementally reducing your riskNow that you’ve configured your Snowflake account with Satori, you can generate your first over-privilege report. To do that, go to the permissions page and look for the following parts of the report:
- Over privileged roles: lists the roles that have access to a high number of tables and views that they don’t actually use:
- Over privileged users: lists the users that have, through the roles they are granted, access to a high number of tables and views they don’t actually use:
- Inactive users: lists the users that had no activity in the last month (configurable):
- Least used roles: the roles that were used the least amount of times. You may want to consolidate these into other roles or drop them altogether.
- Least used tables: tables that are not in use or not used often. In many cases, eliminating stale data like this can be very useful to both risk reduction and compliance (i.e.GDPR article 25, which requires the deletion of data no longer in use).