Security

Overview

Q01 Core APIs implement defense-in-depth security with multiple independent layers protecting data access. Each request passes through authentication, authorization, tenant isolation, and context-based filtering before reaching data.

Security Layers:

1. Authentication - JWT token validation (who are you?)
2. Authorization - TB_MENU grant checking (what can you do?)
3. Tenant Isolation - Multi-tenant data separation (which tenant?)
4. Context Security - source/peso/ambiente/centro_dett filtering (what context?)
5. Field-Level Security - COD_ON_OFF visibility control (which fields?)
6. Rate Limiting - API throttling and quotas (how much?)
7. Audit Trail - Complete operation tracking (who did what?)

Architecture:

Client Request
    ↓
1. SSL/TLS Transport Security
    ↓
2. JWT Token Validation (401 if invalid)
    ↓
3. TB_MENU Grant Check (403 if denied)
    ↓
4. Tenant Isolation Filter (source filtering)
    ↓
5. Context Security (peso/ambiente/centro_dett)
    ↓
6. COD_ON_OFF Field Visibility
    ↓
7. Rate Limit Check (429 if exceeded)
    ↓
8. Execute Query/Write
    ↓
9. Audit Log Entry
    ↓
Response to Client

Authentication

JSON Web Tokens (JWT) authenticate all API requests.

Token Structure:

{
  "header": {
    "alg": "HS256",
    "typ": "JWT"
  },
  "payload": {
    "user_id": "user@example.com",
    "tenant_id": "tenant_123",
    "source": "productManagement",
    "centro_dett": "admin",
    "peso": "1",
    "ambiente": "production",
    "grants": ["products.read", "products.write"],
    "exp": 1735574400,
    "iat": 1735488000
  },
  "signature": "..."
}

Request Header:

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Validation:

• ✅ Signature verification (HMAC-SHA256)
• ✅ Expiration check (exp claim)
• ✅ Issuer verification (iss claim)
• ✅ Not-before check (nbf claim)

Token Lifecycle:

Login
    ↓
Generate access token (1 hour TTL)
Generate refresh token (30 days TTL)
    ↓
Client stores tokens
    ↓
API requests with access token
    ↓
Token expires → 401 Unauthorized
    ↓
Client uses refresh token → new access token
    ↓
Refresh token expires → redirect to login

Authorization

TB_MENU nested set model defines hierarchical permissions.

Grant Structure:

TB_MENU (nested set)
├── products (NLEFT=1, NRIGHT=10)
│   ├── products.read (NLEFT=2, NRIGHT=3)
│   ├── products.write (NLEFT=4, NRIGHT=5)
│   └── products.delete (NLEFT=6, NRIGHT=7)
└── orders (NLEFT=11, NRIGHT=20)
    ├── orders.read (NLEFT=12, NRIGHT=13)
    └── orders.write (NLEFT=14, NRIGHT=15)

Grant Checking:

-- Check if user has 'products.write' grant
SELECT COUNT(*) FROM TB_MENU parent
INNER JOIN TB_MENU child
    ON child.NLEFT BETWEEN parent.NLEFT AND parent.NRIGHT
WHERE parent.MENU_ID IN (user_grants)
  AND child.MENU_ID = 'products.write';

Hierarchical Inheritance:

• ✅ Parent grant includes all children
• ✅ products grant → includes products.read, products.write, products.delete
• ✅ Fine-grained or broad permissions

Tenant Isolation

Multi-tenant architecture ensures complete data separation between tenants.

Session Context:

{
  "tenant_id": "tenant_123",
  "source": "productManagement",
  "centro_dett": "admin",
  "peso": "1",
  "ambiente": "production"
}

Automatic Filtering:

Every query includes tenant filter:

-- User sees only their tenant's data
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'productManagement'  -- Tenant isolation
  AND PRD_CENTRO_DETT = 'admin'          -- Context filtering
  AND PRD_PESO <= '1'                    -- User level
  AND PRD_AMBIENTE = 'production'        -- Environment
  AND TREC != 'C';                       -- Exclude deleted

Benefits:

• ✅ Zero trust - all data filtered by default
• ✅ No cross-tenant data leakage
• ✅ Same database, complete logical isolation
• ✅ Transparent to application code

Context Security

Context chain (source/centro_dett/peso/ambiente) travels with every request and filters what's visible.

Context Components:

Component	Purpose	Example
source	Tenant/application identifier	productManagement
centro_dett	Organizational unit	admin, warehouse, store_001
peso	User level (1=admin, 2=manager, 3=user)	1
ambiente	Environment	production, staging, test

Hierarchical Filtering:

peso=1 (admin) → sees all records (peso 1, 2, 3)
peso=2 (manager) → sees peso 2, 3 only
peso=3 (user) → sees peso 3 only

Query Example:

GET /api/v4/core/PRD?peso=2

# Returns products with peso >= 2 (manager can't see admin-level products)

Field-Level Security

COD_ON_OFF bitmask controls field visibility per operation.

Flags:

• L - List view
• D - Detail view
• R - Search/filter
• N - Create (POST)
• M - Modify (PUT/PATCH)

Example:

-- XPRD03 (Product Code) - Read-only after creation
COD_ON_OFF = 'LDR'  -- Visible in list/detail/search, NOT modifiable

-- XPRD02 (Price) - Full access
COD_ON_OFF = 'LDRNM'  -- Visible everywhere, can create and modify

Enforcement:

// ❌ Bad - trying to modify read-only field
PATCH /api/v4/core/PRD/123
{
  "data": {
    "XPRD03": "PRD-2025-999"  // Error: FIELD_NOT_MODIFIABLE
  }
}

Rate Limiting

API throttling prevents abuse and ensures fair usage.

Limits:

Per User:
- 1000 requests/hour
- 100 requests/minute
- 10 requests/second

Per Tenant:
- 10,000 requests/hour
- 1000 requests/minute

Response Headers:

X-RateLimit-Limit: 1000
X-RateLimit-Remaining: 847
X-RateLimit-Reset: 1735574400

Exceeded Response:

{
  "error": "RateLimitExceeded",
  "message": "Rate limit exceeded. Retry after 300 seconds.",
  "code": "RATE_LIMIT_EXCEEDED",
  "status": 429,
  "retry_after": 300
}

Audit Trail

Complete tracking of all operations for compliance and debugging.

Logged Information:

• User identity (USER field in JWT)
• Operation type (GET/POST/PUT/PATCH/DELETE)
• Dimension and record ID
• Timestamp (CREATED_AT, UPDATED_AT)
• IP address and user agent
• Request payload (for writes)
• Response status

Audit Fields:

Every record includes audit metadata:

-- Automatic audit fields
CREATED_BY VARCHAR(255)      -- Who created
CREATED_AT VARCHAR(14)        -- When created
UPDATED_BY VARCHAR(255)       -- Who last updated
UPDATED_AT VARCHAR(14)        -- When last updated
DELETED_BY VARCHAR(255)       -- Who deleted (soft delete)
DELETED_AT VARCHAR(14)        -- When deleted

Audit Query:

-- Who modified product 123?
SELECT UPDATED_BY, UPDATED_AT, XPRD01, XPRD02
FROM TB_ANAG_PRD00
WHERE PRD_ID = '123'
ORDER BY UPDATED_AT DESC;

Security Best Practices

✅ DO:

Always use HTTPS in production:

# ✅ Good - encrypted transport
https://api.example.com/api/v4/core/PRD

# ❌ Bad - plaintext credentials
http://api.example.com/api/v4/core/PRD

Rotate JWT secrets regularly:

# ✅ Good - monthly secret rotation
JWT_SECRET=new_secret_2025_01

Implement token refresh:

// ✅ Good - refresh before expiration
if (isTokenExpiringSoon()) {
  token = await refreshToken();
}

Validate all inputs:

// ✅ Good - sanitize user input
const sanitized = sanitizeInput(userInput);
await createProduct({ XPRD01: sanitized });

❌ DON'T:

Don't store JWT in localStorage:

// ❌ Bad - XSS vulnerable
localStorage.setItem('token', jwt);

// ✅ Good - httpOnly cookie
document.cookie = `token=${jwt}; HttpOnly; Secure; SameSite=Strict`;

Don't hardcode credentials:

// ❌ Bad - credentials in code
const token = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...';

// ✅ Good - environment variables
const token = process.env.API_TOKEN;

Don't bypass validation:

// ❌ Bad - skipping validation
const data = req.body;  // Direct use
await createProduct(data);

// ✅ Good - validate first
const validated = validate(req.body);
await createProduct(validated);

Common Security Issues

Issue 1: Token Expiration

Symptom: API returns 401 Unauthorized

Cause: Access token expired

Solution:

async function apiRequest(url, options) {
  let response = await fetch(url, options);

  if (response.status === 401) {
    // Token expired - refresh
    const newToken = await refreshToken();
    options.headers.Authorization = `Bearer ${newToken}`;
    response = await fetch(url, options);
  }

  return response;
}

Issue 2: Missing Grant

Symptom: API returns 403 Forbidden

Cause: User lacks required permission

Solution:

// Check grants before showing UI
if (!hasGrant('products.write')) {
  hideCreateButton();
}

Issue 3: Cross-Tenant Data Leak

Symptom: User sees data from other tenants

Cause: Missing source/centro_dett in session context

Solution:

// Always set complete session context
const sessionContext = {
  source: 'productManagement',
  centro_dett: 'admin',
  peso: '1',
  ambiente: 'production'
};

Issue 4: Rate Limit Exceeded

Symptom: API returns 429 Too Many Requests

Cause: Too many requests in short time

Solution:

// Implement exponential backoff
async function apiRequestWithBackoff(url, maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    const response = await fetch(url);

    if (response.status !== 429) {
      return response;
    }

    const retryAfter = response.headers.get('Retry-After');
    await sleep(retryAfter * 1000);
  }

  throw new Error('Rate limit exceeded after retries');
}

Summary

• ✅ Multi-level security: authentication → authorization → tenant isolation → context filtering
• ✅ JWT tokens for stateless authentication
• ✅ TB_MENU nested set for hierarchical permissions
• ✅ Automatic tenant isolation via source/centro_dett
• ✅ Context-based filtering (peso/ambiente)
• ✅ Field-level security with COD_ON_OFF
• ✅ Rate limiting for abuse prevention
• ✅ Complete audit trail for compliance

Key Takeaways:

1. Every request passes through multiple security layers
2. JWT tokens carry user identity and context
3. TB_MENU defines what users can do
4. source/centro_dett ensures tenant isolation
5. peso/ambiente filter what's visible
6. COD_ON_OFF controls field access
7. Rate limiting prevents abuse
8. Audit trail tracks all operations

Security Checklist:

• HTTPS in production
• JWT secret rotation
• Token refresh implementation
• Input validation
• HttpOnly cookies (not localStorage)
• Complete session context (source/centro_dett/peso/ambiente)
• Rate limit handling
• Error logging and monitoring
• Regular security audits

Related Concepts

• Authentication - JWT tokens and refresh tokens
• Authorization - TB_MENU grants and nested set
• Tenant Isolation - Multi-tenancy architecture
• Context Security - source/peso/ambiente filtering
• Rate Limiting - API throttling
• Audit Trail - Operation tracking
• Security Checklist - Best practices