Turn over-privilege risk into a healthy Snowflake DB permission fit
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.
In an organization that fits this far more realistic scenario, the sheer magnitude of data involved and unlikelihood of security-mad employees tracking what data the organization has, where it is and how others are using it leave little doubt to the high level of unmanaged risk involved.
From time to time, an incident or audit may trigger an attempt at remediation that very quickly turns into an interdepartmental hot potato as teams contend with mapping their data usage, invasive auditing. Even when successful, these tests in strength take place too infrequently to sufficiently resolve the issue.
The truth is that data breaches can only ever be truly prevented or mitigated with consistently monitored and appropriate privilege fits.
Having a healthy privilege fit
As 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
Moreover, reducing risk can prevent huge amounts of private information from de-privatization and promote further enterprise innovation by granting teams more time to work on more meaningful and satisfying work!
Staying on top of your privilege fit
One 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 reduction
Risk 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 control
I 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.
In 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.
// Creating the Satori user CREATE USER satori_reader PASSWORD='<CHOOSE_PASSWORD>'; // Creating the Satori role CREATE ROLE satori_reader_role; // Granting read-only privileges on the metadata views for the Satori role GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO satori_reader_role; // Granting the Satori role to the Satori user GRANT ROLE satori_reader_role TO USER satori_reader;
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:
// Create a warehouse specific to the Satori user CREATE WAREHOUSE satori_warehouse WITH WAREHOUSE_SIZE=XSMALL; // Enable the warehouse for the Satori user GRANT OPERATE ON WAREHOUSE satori_warehouse TO ROLE satori_reader_role;
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:
CREATE OR REPLACE NETWORK POLICY satori ALLOWED_IP_LIST=('0.0.0.0/0'); ALTER USER satori_reader SET NETWORK_POLICY = satori;
Log 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 risk
Now 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).
When you are starting to actually revoke part of the privileges, drilling down may be useful. If we drill down to a specific role, we can access details about the user’s activity using this role, as well as the tables they use.
The big picture: over-privileges is only a part of the picture
Satori offers so much more than over-privilege risk mitigation. Satori allows you to set smart access controls based on the types of data being queried, and also provides full visibility and analytics of your data access. We help enterprises meet compliance requirements, provide better privacy, harmonize interdepartmental relations and promote innovation.
If you’re interested in taking Satori for a test drive, you can schedule a 30 mins demo to discuss your use-case and see how Satori can help.