Moving from read-only BI dashboards to interactive applications changes the requirements for data governance. Applications can be capable of writeback, can execute user-specified workflows and may be integrated with AI to act on behalf of the user. This presents a common governance dilemma on how to balance application usability against safeguarding the data.

Role-based access control (RBAC) in application code is something that scales very poorly and complicates reasoning about security. Snowflake RBAC controls should be tied to the data layer so they work regardless of the data consumption path and are the single source of truth. On the other hand, some controls can only exist in an application (e.g. "display this edit box only to an admin"), but they should be paired with hard controls on the data layer.

This post provides an overview of how exactly Streamlit in Snowflake (SiS) and other applications work for data access and provides some recommendations on implementing RBAC that safeguards the data without being too restrictive.

Owner's rights mirrors the classic service principal pattern of application authentication: a single identity is used to access the data on behalf of the users. Data entry applications are the typical example: the user does not need direct access to the underlying data. Users are only supposed to interact with the data or maybe a subset of it through the application, but never directly with the underlying tables.

Restricted caller's rights are (effectively) caller's rights with a ceiling: queries run as the visiting user, but only with those of the visitor's privileges an administrator has explicitly granted to the application. They are close to the architecture of a central Jupyter notebook service. When users access the service, they can interact with different objects, depending on their privileges in the system.

Thus, a rough mental model is:

SiS on container runtime supports two rights modes: owner's rights and restricted caller's rights. SiS applications can opt into using either or both modes when creating a session, through which all SQL statements are executed. The rights mode choice determines what data and which data containers a user can see when they interact with those objects through the application; that choice is made by the application developer.

Before talking about how the rights modes work in practice, let's take a look at how object access in Snowflake works in general.

An actor's (identity) interaction with an object (a table, a view, a row in a table) has two halves: what the actor can learn about it, and what the actor can change.

The objects live inside a container, typically inside a SCHEMA nested in a DATABASE. From coarsest to finest, the actor can learn the following things about the object:

1. That the container object exists: without USAGE on the database and schema, the object is unknowable regardless of what is granted on the object itself.

   This allows admins to set up a pattern where USAGE is mass-granted on applications that are deployed inside a SCHEMA that is private to a business unit.

2. That the object exists: the fact that a view named SSN_V is there implies that the corresponding data is stored somewhere, which may itself be sensitive.

3. The shape of the object: column names and types, via DESCRIBE or INFORMATION_SCHEMA.

4. The definition of the object: the view body or function source. A secure view separates the definition from the shape: the shape stays visible, the body (view definition) does not.

5. Aggregates over the rows: an aggregation policy lets an actor learn statistics over rows they can never retrieve individually; a join policy constrains how the object may be combined with others. What the actor gets depends on how the policy body is written.

6. The rows: a row access policy decides which rows exist for the actor.

7. The columns: a projection policy allows a column to be filtered and joined on without ever being returned in a result set.

8. The values: a masking policy returns the row and the column, but completely or partially redacts the cell.

While the above is not a strict sequence, container-level access needs to hold for containing objects to be available. For example, USAGE on the containers has to hold before anything inside them can be reached, and a row has to be visible before a masking policy on one of its columns matters. Boundaries at the same level are independent: a projection policy on one column and a masking policy on another do not constrain each other.

Two interaction types fall outside this categorization:

The dataset and a sample application below illustrate how the two modes function on top of the standard RBAC:

The code for PIPELINE_EXPLORER is:

When the PIPELINE_EXPLORER application is created, it becomes owned by the PIPELINE_APP_OWNER_RL role. This role is determined by the primary role of the user that executed CREATE STREAMLIT statement; secondary roles and other attributes of that user are discarded.

When the application runs, Snowflake generates an internal service user with SNOWFLAKE_SERVICE type and the name similar to STPLATSTREAMLIT.... This user gets only the PIPELINE_APP_OWNER_RL role.

This example uses a very flat role hierarchy. In real-world scenarios, you don't need to grant privileges directly to the roles, you can grant a privilege to a role and then grant that role to the role that is ultimately granted to an actor. Using Database Roles minimizes the privilege sprawl.

Both code lines ① and ② run on top of owner_session.

Minimal required privileges:

SALES_REP_RL needs no grants on ANALYTICS_DB or the objects inside it.

When Alice accesses the application, she would not be able to see any results in ① since CURRENT_USER() evaluates to the ephemeral user with PIPELINE_APP_OWNER_RL role. ② would show results since there is no check on the data. In this mode, Alice does not need USAGE on the APP_WH warehouse.

Both code lines ① and ② run on caller_session.

Minimal required privileges:

Note that a caller grant is not a privilege in and of itself: it authorizes the application to run with a privilege the visitor already holds, so both CALLER GRANTs to the owner role and standard GRANTs to the visitor are required. The data layer governance post in the references covers caller grants and their inherited form in depth.

Since the application code does not have an explicit USE WAREHOUSE statement, the application will try to use Alice's default warehouse. The ADHOC_WH warehouse thus needs both grants:

If these privileges are missing, the queries will error out with "No warehouse in session".

Unlike the previous example, ① returns the visitor's rows, since CURRENT_USER() evaluates to the visitor. ② now requires the visitor's role to hold access to the underlying data.

In this example line ① runs on caller_session, ② on owner_session.

Minimal required privileges:

In this example, ① will return results matching Alice's identity since CURRENT_USER() evaluates to the visitor. ② uses owner's rights, so the PIPELINE_APP_OWNER_RL needs to have proper privileges.

Let's consider the key invariants that the admin wants to uphold:

1. Policy enforcement follows the human.

   When the data layer is aware of the human identity, it does not matter which tool is used to access the data. RCR is naturally architected to support this, while owner's rights disables user-level policies.

   However, there are cases where the humans should not be able to access individual rows, but can see aggregated/pre-calculated data. This can be achieved with aggregation/projection policies combined with row-level security.

2. Audit trails

   Compliance frameworks dictate that accesses of sensitive columns/rows must be traceable to the human operator.

   Owner's rights breaks this, requiring application-side logging. Queries emitted by owner's rights sessions show up as "redacted" in the query history, so admins must rely on ACCESS_HISTORY to examine which objects are being accessed.

3. The application must not be able to query objects outside of its domain

   In standard (unrestricted) caller's rights, the executable can run with the full rights of the caller. This may be fine (and even required for sudo-type) stored procedures as they have a well-defined runtime behavior. Interactive applications have more surface and are thus more prone to malicious inputs.

   The Restricted part of RCR helps with enforcing this. It's important to note that Snowflake has no notion of negative grants; to express denial of a certain action, the admin should not grant the corresponding privilege.

4. Elevation of privileges must be controlled

   In some cases, such as writeback applications, users might need to write records into tables without having direct access to them. RCR fundamentally cannot be this gate as it requires direct write access to the underlying object. This invariant is the main use case for the mixed mode defined earlier.

The release of high-level caller privileges also allowed application code to call USE ROLE / USE SECONDARY ROLE statements, switch database/schema contexts. This means that if an access policy relies on CURRENT_ROLE, it may stop holding true in the middle of the application.

Due to the nature of the ephemeral owner user discussed above, for an owner's rights application to successfully run USE ROLE SOME_RL, that SOME_RL must be granted to the role owning the application (GRANT ROLE SOME_RL TO ROLE PIPELINE_APP_OWNER_RL).

A common approach is to use IS_ROLE_IN_SESSION when writing data access policies, as this function is more resilient to role changes. Data access policies may also rely on immutable session variables, similar to the approach outlined in Multi-tenancy for Cortex Agents.

When the data enters the application memory, it becomes controlled by the code logic and data-side RBAC no longer applies. To illustrate:

A developer wants to exfiltrate credit card numbers stored in a table in plaintext (let's pretend PCI DSS does not exist). Data is behind a row-level security policy, allowing only BILLING_ADMIN to see the full numbers.

That developer creates an application that runs SELECT against that table on behalf of the visitor, keeps data in RAM and shows the data if and only if the developer accesses the application. By tricking a user with BILLING_ADMIN role into opening the application, the malicious developer is able to exfiltrate the data.

CALLER SELECT should be granted to roles producing applications with a degree of source code trust, or CI checks should be run to guard against this pattern.

In addition to displaying the data in UI, data can be transported out to an external stage or API endpoint through an external access integration (EAI).

USAGE on such pathways should be very tightly controlled and granted to roles that really need that access.

The data may also be accidentally exposed if the query results are cached inside the application with a global cache scope, so two visiting users will see each other's query results. Specifically for Streamlit, a static code check during deployment should examine calls to st.cache_data.