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.
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:
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.
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.
EXECUTE AS OWNER or EXECUTE AS CALLER in the procedure definition.CREATE OR REPLACE PROCEDURE PROC()RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTE AS {OWNER | CALLER} 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.
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.
|
EMPLOYEE_ID |
FIRST_NAME |
LAST_NAME |
|
EMP55379 |
ADAM1 |
TOBIN1 |
|
EMP55498 |
NANCY1 |
GREEN1 |
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:
CREATE OR REPLACE PROCEDURE CLONE_TABLE(SCHEMA_NM VARCHAR(150), TABLE_NM VARCHAR(150))RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTE AS OWNER AS $$.../* 1)Include one line to create the table. This gives us the control on where the clone is being created, and also limits the number of active clones per table to one by using create OR REPLACE.*/snowflake.execute({sqlText: `CREATE OR REPLACE TABLE CLONES.${TABLE_NM} CLONE ${SCHEMA_NM}.{TABLE_NM};`});.../* 2)Include one line to grant ownership on the table. This allows the ENGINEER ROLE to insert into and update the table, as well as drop the table when they are finished.*/snowflake.execute({sqlText: `GRANT OWNERSHIP ON TABLE CLONES.${TABLE_NM} TO ROLE ENGINEER;`});...$$;GRANT USAGE ON PROCEDURE CLONE_TABLE(VARCHAR, VARCHAR) TO ROLE ENGINEER: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:
---- Step 1: Create the logging tableCREATE TABLE STATEMENT_TIMEOUT_LOG(ALTERED_AT DATETIME,ALTERED_BY VARCHAR(150),ALTERED_VALUE INT);----Step 2: Create the owner's rights procedureCREATE OR REPLACE PROCEDURE ALTER_STATEMENT_TIMEOUT(SECONDS FLOAT)RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTE AS OWNERAS $$.../* Include Logic to disallow users from setting the parameter too low or too high */if (SECONDS < 3600 || SECONDS > 10800){ return 'Error: Value falls outside of allowed range.';}.../* Alter the STATEMENT_TIMEOUT_IN_SECONDS parameter based on the parameter */snowflake.execute({sqlText: `ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = ${SECONDS};`});.../* insert an entry into the logging table. */snowflake.execute({sqlText: `INSERT INTO STATEMENT_TIMEOUT_LOG (ALTERED_AT, ALTERED_BY, ALTERED_VALUE) VALUES (CURRENT_TIMESTAMP(), CURRENT_USER(), ${SECONDS});`});...$$;---- Step 3: Grant usage on the procedure to the specified role(s).GRANT USAGE ON PROCEDURE ALTER_STATEMENT_TIMEOUT(FLOAT) TO ROLE ENGINEER;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.
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.