43 · Row-Level Security

Implement row-level security with @restrict where clauses, handler-based filters, and HANA Analytic Privileges.

Episode 43 — Row-Level Security in HANA

What you'll learn

• CAP's built-in row-level filtering with @restrict where
• Implementing user-scoped data isolation in handlers
• HANA Analytic Privileges for native row-level security
• Tenant-level data isolation patterns
• Testing row-level security

Why this matters

Row-level security ensures users see only their own data — a salesperson sees their orders, not everyone's. Without it, a single OData $filter bypass exposes all records. CAP has built-in support for this, and HANA doubles it at the database engine level.

CAP @restrict with where clause

// srv/orders-service.cds
service OrdersService @(requires: 'authenticated-user') {
  entity Orders as projection on my.Orders
    @(restrict: [
      { grant: 'READ',   to: 'Buyer',   where: 'buyer_ID = $user' },
      { grant: '*',      to: 'Admin'                               },
      { grant: ['READ'], to: 'Auditor', where: 'status = ''CLOSED''' }
    ]);
}

• $user → resolved to req.user.id at runtime
• $user.attr.costCenter → user attribute from XSUAA JWT
• Admins bypass the WHERE clause (no where)
• Auditors can only read CLOSED orders

Restriction with user attributes

entity SalesOrders as projection on my.SalesOrders
  @(restrict: [{
    grant: 'READ',
    to:    'SalesPerson',
    where: 'region = $user.attr.region'  // attribute from JWT token
  }]);

// XSUAA user token (decoded)
{
  "user_name": "alice",
  "xs.user.attributes": {
    "region": ["EMEA"]   // CAP resolves $user.attr.region = 'EMEA'
  }
}

Handler-based row filtering (fallback)

When @restrict where doesn't cover your logic, filter in a BEFORE handler:

this.before('READ', 'Orders', (req) => {
  const { id, is } = req.user

  if (is('Admin')) return   // No restriction for admin

  if (is('Buyer')) {
    // Append WHERE clause to the query
    req.query.where({ buyer_ID: id })
  } else if (is('RegionalManager')) {
    const region = req.user.attr.region
    req.query.where({ region })
  }
})

HANA Analytic Privilege (database-level RLS)

For maximum security — enforced at the HANA engine level, not application level:

-- gen/db/src/privileges/OrdersReadPrivilege.hdbanalyticprivilege
-- (XML-format, created with HANA modeler or HAA)
CREATE ANALYTIC PRIVILEGE "OrdersReadPrivilege"
  USING VIEW "MY_BOOKSHOP_ORDERS"
  FOR SELECT
  WHERE "BUYER_ID" = SESSION_USER;

HANA filters rows using the connected database user identity — even if a rogue query bypasses the app layer.

Multitenant isolation

// Tenant ID is always available in multitenant apps
this.before('READ', 'Orders', (req) => {
  // In MTXS-based multitenant apps, each tenant has a separate HDI container
  // req.tenant = 'tenant-acme-corp'
  // Data isolation is automatic via separate schema

  // For shared-schema multitenancy, filter explicitly:
  req.query.where({ tenant_ID: req.tenant })
})

Testing row-level security

// Test with different mock users
it('buyer sees only own orders', async () => {
  const { GET } = cds.test('.').in(__dirname + '/..')
    .with({ user: { id: 'alice', roles: ['Buyer'] } })

  const { data } = await GET('/orders/Orders')
  expect(data.value.every(o => o.buyer_ID === 'alice')).toBe(true)
})

it('admin sees all orders', async () => {
  const { GET } = cds.test('.').in(__dirname + '/..')
    .with({ user: { id: 'admin-user', roles: ['Admin'] } })

  const { data } = await GET('/orders/Orders')
  expect(data.value.length).toBeGreaterThan(1)
})

Bypass attempts and how CAP stops them

Client: GET /orders/Orders?$filter=buyer_ID ne 'alice'

Without RLS: Returns orders for all buyers
With @restrict where: CAP ANDs the where clause: buyer_ID='alice' AND buyer_ID ne 'alice' → 0 rows
With handler filter: req.query.where({ buyer_ID: id }) is prepended by CAP — same result

Common mistakes

Mistake	Symptom	Fix
Missing @(requires: ...) on service	Restriction ignored for unauthenticated	Add @requires to the service
Using = instead of == in where clause	CDS parse error	Use = (single equals) in CDS annotation strings
Forgetting $user.attr scope	Attribute undefined at runtime	Declare the attribute in xs-security.json first
No test for admin bypass	Admins also filtered	Test admin path explicitly — no where clause for admins

Hands-on exercise

1. Add @restrict to your Orders entity: Buyers see only their own, Admins see all
2. Write a BEFORE handler that verifies the filter is applied
3. Test with two mock users (alice as Buyer, admin as Admin)
4. Add a $filter bypass attempt in your test and verify CAP blocks it

Checkpoint ✓

• [ ] @restrict where: 'field = $user' auto-filters rows per user at query time
• [ ] $user.attr.attrName resolves XSUAA JWT attributes in where clauses
• [ ] Handler req.query.where(...) can append filters programmatically
• [ ] HANA Analytic Privileges enforce RLS at the engine level for maximum security

Sign in for progress tracking & Q&A →