Snowflake: Simplify RBAC with Owner's Rights Procedures.

Owner's rights procedures are a tool that allows users to perform specific tasks that require elevated privileges, without actually granting the underlying privileges themselves. Furthermore, they grant administrators the ability to control exactly how users interact with specific database objects.

Owner's Rights Procedures

While stored procedures are far from the most exciting feature Snowflake has to offer, the ability to run them with owner's rights establishes them as an invaluable tool for managing access controls within your Snowflake environment. The concept is simple enough to understand; however, the scenarios where they are most impactful are often easy to overlook. Throughout this article, we will highlight how owner's rights procedures can be utilized to tackle two key challenges:

                Simplifying Management

When a user hits a roadblock with respect to access, Snowflake makes it easy to grant the required privileges to an existing role or to quickly spin up a new role entirely. What's not easy - is untangling your RBAC framework after multiple deviations from your original plan. Over time, roles evolve to gain privileges well beyond their intended scope, the total number of roles grows to an unmanageable number, and the amount of overlap between roles and their privileges renders them indistinguishable.  

Not only does this present challenges concerning role management, but it also opens the door to bigger problems that often don't present themselves until it's too late. Protecting data integrity becomes a problem as you are unsure which roles can modify your tables, pipelines begin to fail as object ownership changes, and security risks arise as users unintentionally inherit elevated privileges. Owner's rights procedures empower users with an alternative to continuously modifying their RBAC framework on an ad hoc basis.

Enforcing Compliance

We have covered the downsides of having an RBAC framework that is too loosely defined, but an equally frustrating environment to work in is one where access controls are too tight. While it is important to limit the access users have, we also do not want them to be at the mercy of an administrator's availability for any task requiring even slightly elevated privileges. We want to empower our users by granting them the appropriate privileges required to perform their jobs. Still, as we increase privileges, we also increase the impact that potential mistakes have on our environment. 

What if we could guarantee that users would never misuse these privileges? What if they could carry out their responsibilities without ever being granted the underlying privileges required to do so in the first place? If you grant elevated privileges to a role for a single, specific use case, it is likely a better approach to create an owner's rights procedure instead. In general, if you can define concise and objective guidelines for using a privilege, the privilege likely doesn't need to be granted at all.

What Exactly Are Owner's Rights Procedures?

With caller's rights, Snowflake executes the statements within a procedure using the role that calls the procedure. If the role that calls the procedure does not have sufficient privileges to execute all of the statements defined within the procedure, then it will fail.

With owner's rights, Snowflake executes the statements within a procedure using the role that owns the procedure. Even if the role calling the procedure does not have sufficient privileges to execute the statements, the procedure will succeed as long as the role that owns the procedure does. One way to look at it is that by granting a role usage on an owner's rights procedure, we are granting usage on the owner role itself for the sake of running the statements defined within a procedure. This is exceptionally useful in scenarios where we want to authorize roles to perform specific tasks that require elevated privileges, however, we do not want to explicitly grant those underlying privileges required to do so.

Example 1: Privilege Roadblocks

Suppose we have a table, INT.EMPLOYEES, and our data engineer identifies a problem. Specifically, the number ‘1’ is appended to the FIRST_NAME and LAST_NAME columns for all rows.

The engineer believes they know how to fix the issue, so they prepare to create a copy of this table to test their solution. To do so, they execute the below statement with their role, ENGINEER;

CREATE OR REPLACE TABLE CLONES.EMPLOYEES CLONE INT.EMPLOYEES;

Snowflake returns an error message saying that the ENGINEER role has insufficient privileges to create a table in the CLONES schema. As the administrator, you have designed your RBAC framework so that the ENGINEER role only has SELECT privileges throughout the database, but you realize that they require CREATE TABLE privileges to perform their job.

Solution 1 - Too Loose

One path users may take is to grant the ENGINEER role additional privileges or to create a new role designed specifically to create and modify tables within the CLONES schema. The problem with either option is that both provide more access than is needed, while also unnecessarily adding a layer of complexity to the RBAC framework. We have only identified a single use case where engineers require this privilege, yet we are granting them privileges to create tables for any purpose within this schema.

In a perfect world, our developers will only ever use this new privilege for the purposes that we outlined. However, in reality, the CLONES schema will likely become populated with all sorts of backups and test tables that were never authorized to be created. Not only is this frustrating from a schema management perspective, but we end up spending credits on storage costs for tables that were never permitted to exist in the first place.

Solution 2 - Too Tight

Another option is to assign this task to a user with an ADMIN role that already has the required privileges assigned to it. This user could then clone the table into the CLONES schema and grant INSERT and UPDATE privileges on the cloned table to the ENGINEER role. The developer can now test their fix on a non-production copy of the table, and no additional layers have been added to your RBAC framework. While this solution checks all of our boxes, we are now requiring an administrator to intervene in a problem that should be handled by an engineer.

Solution 3 - Owner's Rights

We want to authorize a role to perform a specific task that requires elevated privileges, but we do not want to grant the elevated privileges required to do so. This is the criteria we outlined at the beginning of this article which identifies a prime candidate for using owner’s rights procedures.

For our scenario, we can create an owner's rights procedure that takes the schema and table name as parameters and then clones that table into the CLONES schema. This allows the engineer to perform the steps required to complete this task, and nothing more. 

All that is required to implement this logic is to include two statements in your procedure, and then grant usage on that procedure to whichever role(s) you are authorizing to perform this task:

Example 2: Controlling Actions

To avoid wasting credits on out-of-control queries, suppose we have set our STATEMENT_TIMEOUT_IN_SECONDS parameter to be one hour at the account level. This means that any query taking longer than one hour will be aborted. However, we recognize that from time to time our engineers may have queries they need to run that take longer than one hour. 

We decide that we want to give our engineers the ability to alter this parameter, but we ask that they make a log entry any time they do so. Also, we want to enforce a minimum and maximum value that this parameter can be set to. To ensure that all of the requirements are met, we create a logging table and an owner’s rights procedure as below:

Our engineers can now alter the account-level parameter, we eliminate any concerns of the value being set too low or too high, and we have a record of exactly when this parameter was altered and by whom.

Wrapping it up: 

The importance of maintaining a consistent, well-structured RBAC framework can not be overstated. The dynamics between roles, privileges, and objects can become exceptionally complex depending on your environment, and it may not always be obvious how modifying one will affect another. Owner's rights procedures allow us to adapt to the challenges we will inevitably face while simultaneously providing the greatest level of control over our environment and the least amount of impact on our RBAC framework.