56 · Advanced Auth Patterns

Implement hierarchical roles, status-based restrictions, multi-tenant isolation, and a security test matrix.

Episode 56 — Authorization Patterns & Best Practices

What you'll learn

• Fine-grained @restrict patterns for real-world scenarios
• Multi-tenant user isolation
• Preventing privilege escalation
• Security testing methodology
• Authorization architecture review checklist

Why this matters

Authorization bugs are the most common high-severity security finding in SAP BTP app audits. This lesson consolidates all the patterns into a checklist and shows the failure modes that real apps hit in production.

Pattern 1: Hierarchical roles

// Lower roles always have a WHERE clause; higher roles don't
entity Tickets as projection on my.Tickets
  @(restrict: [
    { grant: '*',    to: 'SupportAdmin' },   // no filter — sees everything
    { grant: 'READ', to: 'TeamLead', where: 'team_ID in ($user.attr.teams)' },
    { grant: ['READ','WRITE'], to: 'Agent', where: 'assignee_ID = $user' }
  ]);

Pattern 2: Status-based restrictions

entity Orders as projection on my.Orders
  @(restrict: [
    { grant: 'READ',   to: 'Admin' },
    { grant: 'READ',   to: 'Buyer', where: 'buyer_ID = $user' },
    { grant: 'UPDATE', to: 'Buyer', where: 'buyer_ID = $user and status = ''DRAFT''' },
    { grant: 'DELETE', to: 'Admin', where: 'status = ''CANCELLED''' }
  ]);

Buyers can only edit their own DRAFT orders. Admins can delete only CANCELLED orders.

Pattern 3: Multi-tenant isolation

// For shared-schema multitenancy — always filter by tenant
this.before('*', '*', (req) => {
  if (req.user?.tenant) {
    // Append tenant filter to every query automatically
    if (req.query?.SELECT) req.query.where({ tenant_ID: req.user.tenant })
    if (req.data && !req.data.tenant_ID) req.data.tenant_ID = req.user.tenant
  }
})

For HDI-based multitenancy (MTXS), each tenant has a separate schema — no code-level filtering needed.

Pattern 4: Preventing privilege escalation

// WRONG — user can claim any role by sending it in the body
this.before('UPDATE', 'Users', (req) => {
  // Don't let users elevate their own roles
  if (req.data.roles) {
    if (!req.user.is('Admin')) delete req.data.roles
  }
})

// BETTER — read-only fields managed by the system
entity Users {
  key ID    : UUID;
  name      : String;
  email     : String;
  roles     : String  @readonly;   // @readonly prevents client from setting it
}

Pattern 5: Audit trail for sensitive operations

const sensitiveOps = ['DELETE', 'cancelOrder', 'eraseCustomer']

this.before(sensitiveOps, '*', async (req) => {
  await INSERT.into('AuditTrail').entries({
    ID:        cds.utils.uuid(),
    user_ID:   req.user.id,
    operation: req.event,
    entity:    req.target?.name,
    timestamp: new Date(),
    reason:    req.data?.reason || '(no reason provided)'
  })
})

Security testing methodology

Layer 1: Unit tests (handlers)

it('handler rejects wrong role', async () => {
  const req = mockRequest({ user: { id: 'alice', roles: ['Buyer'] }, event: 'DELETE' })
  await expect(handler(req)).rejects.toMatchObject({ status: 403 })
})

Layer 2: Integration tests (HTTP)

// Test every role × every operation × own vs other data
const matrix = [
  { user: 'alice', op: 'GET',    own: 200, other: 200 },  // reader
  { user: 'bob',   op: 'DELETE', own: 204, other: 403 },  // buyer can only delete own
  { user: 'admin', op: 'DELETE', own: 204, other: 204 },  // admin deletes anything
]

Layer 3: Negative tests (bypass attempts)

it('cannot bypass filter with $filter override', async () => {
  // Try to bypass buyer_ID filter by explicitly filtering for another user
  const { data } = await GET('/orders/Orders?$filter=buyer_ID eq ''carol''', { auth: alice })
  // CAP ANDs the @restrict where clause — result must be empty
  expect(data.value).toHaveLength(0)
})

it('cannot set buyer_ID in request body', async () => {
  const { data: created } = await POST('/orders/Orders', 
    { buyer_ID: 'carol' },   // try to impersonate carol
    { auth: alice }
  )
  expect(created.buyer_ID).toBe('alice')   // overridden by BEFORE handler
})

Authorization review checklist

□ All services have @requires (not all 'any')
□ All entities with user-specific data have @restrict with where clause
□ Buyer/user-role WHERE always uses $user or $user.attr.* (not req.data)
□ No role is granted 'any' on write operations
□ Admin bypass paths are explicit — not implied
□ Sensitive field changes are audit-logged
□ Security-relevant fields (buyer_ID, tenant_ID) set from req.user, not req.data
□ @restrict WHERE clauses tested with bypass attempts
□ Token expiry tested on staging (not dev mock)
□ Service-to-service tokens cannot perform user-only operations

Hands-on exercise

1. Apply all 5 patterns to your Orders service
2. Write the full test matrix (all roles × all operations × own/other data)
3. Write 3 bypass tests: $filter override, body spoofing, and missing WHERE
4. Run the authorization review checklist against your service — fix any gaps

Checkpoint ✓

• [ ] @restrict where auto-applies for filtered access; no where = unrestricted
• [ ] Multi-tenant isolation: either separate HDI schemas (MTXS) or explicit tenant_ID filter
• [ ] Always set security fields from req.user — never from request body
• [ ] Security testing requires negative tests (bypass attempts) not just happy path