Centralizing Authorization in SurrealDB: Row-Level and Field-Level Permissions

The Problem: Authorization Drift

In most application architectures, access control is duplicated. Developers write a database query to fetch data, then write application-layer middleware to verify if the user is allowed to see that specific record. This creates "authorization drift": if a new API endpoint is added but the developer forgets the filter, sensitive data leaks.

SurrealDB solves this by moving the authorization logic into the database schema. By using declarative permissions, the database evaluates access rights at the query layer. Unauthorized rows are filtered out before they ever reach the application, ensuring a single source of truth for security.

Declarative Permissions via SurrealQL

Permissions in SurrealDB are boolean conditions attached to tables or fields. They evaluate the request based on the authenticated session, represented by the $auth variable. This variable contains the claims embedded in the user's session token (such as user ID, role, or tenant ID).

A basic table-level permission restricts access to the record owner:

DEFINE TABLE user PERMISSIONS
  FOR select WHERE $auth.id = id,
  FOR update WHERE $auth.id = id,
  FOR delete WHERE $auth.id = id;

In this configuration, any SELECT query targeting the user table will automatically be constrained by the database to only return the record where the id matches the authenticated user's ID.

Implementing Multi-Tenant Isolation

For SaaS applications, row-level security (RLS) is critical for tenant isolation. Instead of adding WHERE tenant_id = '...' to every single query in your backend code, you can define the boundary once in the schema.

If your records contain a tenant_id field, the permission looks like this:

DEFINE TABLE document PERMISSIONS
  FOR select WHERE $auth.tenant_id = tenant_id,
  FOR create WHERE $auth.tenant_id = tenant_id,
  FOR update WHERE $auth.tenant_id = tenant_id,
  FOR delete WHERE $auth.tenant_id = tenant_id;

Regardless of whether the application executes a simple fetch or a complex graph traversal, SurrealDB ensures that data from other tenants is invisible to the current session.

Field-Level Masking for Sensitive Data

Not all data in a row should be visible to everyone who has access to that row. Field-level permissions allow you to hide specific columns based on the user's role or identity.

For example, in a profile table, you may want the username to be public, but the email to be visible only to the owner or an administrator:

DEFINE FIELD email ON profile TYPE string PERMISSIONS
  FOR select WHERE $auth.id = id OR $auth.role = 'admin';

When a non-admin user who does not own the record queries the profile, the email field is omitted from the result set, while other permitted fields are returned normally.

Worked Example: Collaborative Project Board

Consider a system where tasks belong to a workspace. Users have roles: owner, editor, or viewer. Tasks can be public (visible to the workspace) or private (visible only to the assignee).

1. Setup the Session Token
Assuming a token is generated with the following claims:

{ "user": "user:alice", "workspace": "workspace:acme", "role": "editor" }

2. Define Table Permissions

DEFINE TABLE task SCHEMAFULL PERMISSIONS
  FOR select WHERE 
    $auth.workspace = workspace_id AND 
    (visibility = 'public' OR assignee = $auth.user),
  FOR update WHERE 
    $auth.workspace = workspace_id AND 
    (assignee = $auth.user OR $auth.role = 'owner'),
  FOR delete WHERE 
    $auth.workspace = workspace_id AND $auth.role = 'owner';

3. Define Field Permissions

Restrict the internal_notes field to owners only:

DEFINE FIELD internal_notes ON task TYPE string PERMISSIONS
  FOR select WHERE $auth.role = 'owner';

Result: When Alice (an editor) queries tasks for workspace:acme, she sees all public tasks and her own private tasks. However, the internal_notes field is stripped from all results because she is not an owner.

Trade-offs and Limitations

• Query Performance: Complex permission logic can impact the query planner. If permissions involve deep record lookups, use EXPLAIN to ensure the database isn't performing full table scans.
• Version Variance: Permission syntax and the way restricted fields are handled (returning null vs. omitting the field) vary between SurrealDB versions. Always verify against your specific deployment version.
• Scope of Control: These permissions control data access, not business logic. They cannot replace application-level validation for complex workflows (e.g., "cannot move task to 'Done' unless 'Review' is complete").

Verification Checklist

1. Environment: Start a local instance (e.g., surreal start --user root --pass root memory).
2. Registration: Run INFO FOR DB to confirm that the defined permissions are active in the schema.
3. Negative Testing: Authenticate as a user from tenant_B and attempt to SELECT a record from tenant_A. The result should be an empty set.
4. Field Testing: Query a record as a non-privileged user and verify that restricted fields are absent from the JSON response.