Multi-tenant agents with session attributes and fail-closed policies¶

Serve many tenants from one Cortex Agent: the app server names the end user in an immutable session attribute, and policies derive the tenant from an entitlement.

This recipe is a reference implementation: review the code and adapt it to your environment before production use.

Serve every tenant from one Cortex Agent, one semantic view, and one Snowflake service user. Your app server authenticates the end user, names that user in an immutable session attribute on each agent run, and Snowflake’s row access and masking policies derive the tenant from an entitlement table. You end with an agent where tenant A cannot see tenant B’s rows, a request with no attribute sees nothing, and a forged tenant ID is ignored.

When to use this¶

• End users are external (your customers’ users) and do not have Snowflake users of their own.
• Many tenants share the same tables, and onboarding a user should be a row in a table, not DDL.
• An application server sits between the end user and Snowflake and can be trusted to authenticate them.

When not to use it:

• End users have Snowflake identities. Use roles and a row access policy on CURRENT_ROLE(), as in multi-agent orchestration, or OAuth passthrough, as in OAuth token passthrough.
• You cannot put a trusted server in front of Snowflake. Anything that can call the agent as the service user can name any end user bound to it.

How it works¶

• The client never sends a tenant. The only attribute is end_user_id. The policy reads the tenant and access level from SECURITY.TENANT_ENTITLEMENTS.
• Entitlements are bound to the calling Snowflake user. Each entitlement row names the service user allowed to assert that end user (APP_USER = CURRENT_USER()). Another Snowflake user asserting the same end-user ID gets nothing.
• Fail closed. No attribute, an unknown end user, or a mismatched service user all evaluate to FALSE. Masking defaults to masked.
• Immutable attributes. is_immutable_session_attribute: true on REST and SET_SYS_CONTEXT on SQL set a value that the agent’s generated SQL, tools, and code execution cannot change. A second attempt to set it errors.
• The agent knows nothing about tenants. Isolation is in the policies, so it holds for any SQL the agent writes.

Trust boundary¶

Snowflake does not authenticate your end users; your app server does. Snowflake enforces whatever end user the app server names, within the set of end users bound to that service user. Treat the service user’s credential as able to read every tenant bound to it:

• Take end_user_id from the server-side authenticated session, never from the request body or the prompt.
• Keep the service user’s key or token on the server only, and rotate it.
• For stronger separation, use one service user per application (or per high-value tenant) and bind entitlements to it.

Prerequisites¶

Quickstart¶

1. sql/01_setup.sql as ACCOUNTADMIN: database, warehouse, admin role, app role, and the COOKBOOK_MULTI_TENANT_AGENTS_APP_SVC service user.

2. sql/02_data.sql: 30 orders across three tenants (Acme Health, Northwind, Contoso Bank) and four entitlements.

3. sql/03_policies.sql: the row access and masking policies.

4. sql/04_agent.sql: semantic view and ORDERS_AGENT.

5. sql/05_grants.sql: the app role’s grants. Re-run after re-running 04.

6. Give the service user a key pair. Run this yourself, as COOKBOOK_MULTI_TENANT_AGENTS_ADMIN: 01 grants that role MODIFY PROGRAMMATIC AUTHENTICATION METHODS on the service user, which is all SET RSA_PUBLIC_KEY needs. Keep app_svc.p8 on the app server only.

   Copy code

   openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out app_svc.p8 -nocrypt
   PUBKEY=$(openssl rsa -in app_svc.p8 -pubout 2>/dev/null | grep -v '^-----' | tr -d '\n')
   snow sql --role COOKBOOK_MULTI_TENANT_AGENTS_ADMIN \
     -q "ALTER USER COOKBOOK_MULTI_TENANT_AGENTS_APP_SVC SET RSA_PUBLIC_KEY = '$PUBKEY'"
   

7. Run the client as two different end users:

   Copy code

   export SNOWFLAKE_ACCOUNT=<account_identifier>
   # In the host name, replace any underscore in the account name with a hyphen.
   export SNOWFLAKE_ACCOUNT_URL=https://<account_identifier>.snowflakecomputing.com
   export SNOWFLAKE_USER=COOKBOOK_MULTI_TENANT_AGENTS_APP_SVC
   export SNOWFLAKE_PRIVATE_KEY_FILE=./app_svc.p8
   python client/tenant_client.py --end-user u-acme-admin "What is total revenue?"
   python client/tenant_client.py --end-user u-northwind-ops "What is total revenue?"
   

Walkthrough¶

The policy¶

ALTER ROW ACCESS POLICY TENANT_ISOLATION_POLICY SET BODY ->
  EXISTS (
    SELECT 1
    FROM COOKBOOK_MULTI_TENANT_AGENTS.SECURITY.TENANT_ENTITLEMENTS e
    WHERE e.END_USER_ID = SYS_CONTEXT('SNOWFLAKE$SESSION_ATTRIBUTES', 'end_user_id')
      AND e.APP_USER    = CURRENT_USER()
      AND e.TENANT_ID   = tenant_col
  );

SYS_CONTEXT returns NULL when the attribute is not set, so the EXISTS finds nothing and the row is hidden. The masking policy uses the same lookup with ACCESS_LEVEL = 'full'. Both policies are created once and updated with SET BODY, and the table is given exactly one row access policy (DROP ALL ROW ACCESS POLICIES before ADD). The app role has no grants on the SECURITY schema; the policies read it with their owner’s rights.

Onboarding and offboarding are DML on the entitlements table:

INSERT INTO COOKBOOK_MULTI_TENANT_AGENTS.SECURITY.TENANT_ENTITLEMENTS
  VALUES ('u-northwind-admin', 'COOKBOOK_MULTI_TENANT_AGENTS_APP_SVC', 'northwind', 'full');
DELETE FROM COOKBOOK_MULTI_TENANT_AGENTS.SECURITY.TENANT_ENTITLEMENTS WHERE END_USER_ID = 'u-northwind-admin';

REST path¶

The object endpoint takes the attribute in variables:

url = f"{account_url}/api/v2/databases/{db}/schemas/{schema}/agents/{agent}:run"
body = {
    "messages": [{"role": "user", "content": [{"type": "text", "text": question}]}],
    "variables": {
        "end_user_id": {"value": end_user_id, "type": "string",
                        "is_immutable_session_attribute": True},
    },
    "stream": False,
}

Each :run request carries its own variables, so one request serves exactly one end user.

SQL path¶

On a connection you open for this request only, set the attribute and run the agent. Both calls use bind parameters:

cur.execute("CALL SET_SYS_CONTEXT('SNOWFLAKE$SESSION_ATTRIBUTES', ?, ?)", ("end_user_id", end_user_id))
cur.execute("SELECT SNOWFLAKE.CORTEX.DATA_AGENT_RUN(?, ?)", (agent_fqn, json.dumps(body)))

Use one session per end user. The attribute cannot be changed once set: a second SET_SYS_CONTEXT on the same session fails with Cannot overwrite existing value for attribute 'end_user_id'. That fails closed, but it means pooled connections cannot be handed from one end user to the next.

Test the policies without the agent¶

-- as the service user, in a fresh session
CALL SET_SYS_CONTEXT('SNOWFLAKE$SESSION_ATTRIBUTES', 'end_user_id', 'u-acme-analyst');
SELECT TENANT_ID, COUNT(*), SUM(AMOUNT_USD), MIN(CUSTOMER_EMAIL) FROM COOKBOOK_MULTI_TENANT_AGENTS.APP.ORDERS GROUP BY ALL;

Verify¶

Same question, “What is total revenue and order count, and show one buyer email?”, through the client:

Direct SQL checks as the service user, each in a new session: no attribute returns 0 rows; each end user returns 10 rows for their own tenant only. As any other Snowflake user (including the admin), setting end_user_id = 'u-acme-admin' returns 0 rows, because the entitlement is bound to the service user.

Gotchas¶

Teardown¶

Run sql/99_teardown.sql as ACCOUNTADMIN. It drops the database (agent, semantic view, policies, tables), the warehouse, the service user, and both roles. Delete the local .p8 key file.

Adapt this recipe¶

Keep these, so a customized version keeps the recipe’s security model:

• Least privilege: ACCOUNTADMIN only for the scripts listed under Privileges; everything else runs as the recipe’s own role.
• No grants to PUBLIC.
• Row access policies fail closed: a role with no entitlement sees no rows. Keep each policy attached when you rename or add tables, and add entitlements for new roles instead of widening the policy.
• Masking policies stay on the same columns, and on any new column that holds the same kind of data.
• Service users stay TYPE = SERVICE and sign in with a key pair or token, never a password.
• Credentials: create keys, tokens, and secrets yourself, in a separate terminal, and read them from environment variables. Never put them in a script or give them to an agent.
• Dynamic SQL, if you add any, uses bind variables or an allowlist of identifiers, never text concatenated from user or model input.

Change these to fit your use case:

• Object names. Rename them in every script, including teardown, then check that teardown still removes everything.
• Roles, personas, and users: map them to your own, each granted no more than the recipe grants.
• Data: point the scripts at your tables instead of the sample data, then run Verify again.
• Models and warehouse sizes.

To have a coding agent adapt it:

Adapt the multi-tenant-agents recipe in multi-tenant-agents/ (unzipped from multi-tenant-agents.zip) to <describe your use case>.
Read README.md first. Keep everything the "Adapt this recipe" section says to keep.
Change only names, roles and personas, data, and models.
Show me each changed file before you run anything, then run the recipe's Verify steps.

Run it with a coding agent¶

Run the recipe in multi-tenant-agents/.
1. Run SELECT CURRENT_ACCOUNT(), CURRENT_REGION(), CURRENT_ROLE(),
   CURRENT_WAREHOUSE(); show me the result and wait for my go-ahead.
2. Show me sql/01_setup.sql and run it as ACCOUNTADMIN only after I confirm:
   it creates roles and the COOKBOOK_MULTI_TENANT_AGENTS_APP_SVC service user.
   Then run sql/02 to sql/05 with snow sql -f (each script sets
   COOKBOOK_MULTI_TENANT_AGENTS_ADMIN itself). Stop on the first error.
3. Do not generate, read, or print the private key. Ask me to run Quickstart
   step 6 myself in a separate terminal, outside this session (the openssl and
   snow sql --role COOKBOOK_MULTI_TENANT_AGENTS_ADMIN commands), and to export
   SNOWFLAKE_ACCOUNT, SNOWFLAKE_ACCOUNT_URL, SNOWFLAKE_USER and
   SNOWFLAKE_PRIVATE_KEY_FILE.
4. Install client/requirements.txt in a virtual environment and run
   client/tenant_client.py for u-acme-admin, u-northwind-ops, and u-nobody
   (REST), and u-acme-analyst and u-contoso-admin (SQL). Compare with the
   Verify table and report the actual answers.
5. As the service user, confirm a session with no attribute returns 0 rows
   from APP.ORDERS, and that a second SET_SYS_CONTEXT in one session fails.
6. When I say done, run sql/99_teardown.sql as ACCOUNTADMIN and confirm
   nothing named COOKBOOK_MULTI_TENANT_AGENTS% remains. Remind me to delete
   app_svc.p8.

References¶

• Multi-tenancy for Cortex Agents
• SET_SYS_CONTEXT
• SYS_CONTEXT (SNOWFLAKE$SESSION_ATTRIBUTES namespace)
• DATA_AGENT_RUN (SNOWFLAKE.CORTEX)
• Cortex Agents: access control and authentication
• Cortex Agent evaluations: known limitations
• Key-pair authentication
• Row access policies