Context Security

Overview

Beyond tenant isolation (source), Q01 Core APIs implement context-based security using peso (user level), ambiente (environment), and centro_dett (organizational unit). These context dimensions filter what data is visible and provide fine-grained access control within a tenant.

Context Dimensions:

Dimension	Purpose	Example Values
source	Tenant identifier	productManagement, storeA
centro_dett	Organizational unit	admin, warehouse, store_001
peso	User level (hierarchy)	1 (admin), 2 (manager), 3 (user)
ambiente	Environment	production, staging, test

Multi-Dimensional Filtering:

Every record filtered by:
    source (tenant)
        ↓
    centro_dett (organizational unit)
        ↓
    peso (user level - hierarchical)
        ↓
    ambiente (environment)

Peso (User Level)

What is Peso?

peso is a hierarchical user level where lower numbers have broader access:

• peso='1' - Admin (sees all levels: 1, 2, 3)
• peso='2' - Manager (sees levels: 2, 3)
• peso='3' - User (sees only level: 3)

Hierarchical Filtering:

peso=1 (Admin)
    ├── Can see peso=1 records ✅
    ├── Can see peso=2 records ✅
    └── Can see peso=3 records ✅

peso=2 (Manager)
    ├── Cannot see peso=1 records ❌
    ├── Can see peso=2 records ✅
    └── Can see peso=3 records ✅

peso=3 (User)
    ├── Cannot see peso=1 records ❌
    ├── Cannot see peso=2 records ❌
    └── Can see peso=3 records ✅

Peso in Table Schema

Every dimension table includes peso field:

CREATE TABLE TB_ANAG_PRD00 (
    PRD_ID VARCHAR(36) PRIMARY KEY,
    XPRD01 VARCHAR(255),
    XPRD02 DECIMAL(10,2),
    PRD_SOURCE VARCHAR(50) NOT NULL,
    PRD_CENTRO_DETT VARCHAR(50),
    PRD_PESO VARCHAR(1),              -- ⭐ User level
    PRD_AMBIENTE VARCHAR(20),
    TREC VARCHAR(1)
);

CREATE INDEX idx_prd_peso ON TB_ANAG_PRD00(PRD_PESO);

Peso Filtering

Automatic hierarchical filtering:

-- Admin user (peso='1') sees all records
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'storeA'
  AND PRD_CENTRO_DETT = 'admin'
  AND PRD_PESO <= '1'              -- ⭐ Sees peso 1, 2, 3
  AND PRD_AMBIENTE = 'production'
  AND TREC != 'C';

-- Manager user (peso='2') sees limited records
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'storeA'
  AND PRD_CENTRO_DETT = 'admin'
  AND PRD_PESO >= '2'              -- ⭐ Sees only peso 2, 3
  AND PRD_AMBIENTE = 'production'
  AND TREC != 'C';

-- Regular user (peso='3') sees minimal records
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'storeA'
  AND PRD_CENTRO_DETT = 'admin'
  AND PRD_PESO >= '3'              -- ⭐ Sees only peso 3
  AND PRD_AMBIENTE = 'production'
  AND TREC != 'C';

CoreQuery Implementation:

// Modules.php
public function applyPesoFilter(string $dimension, string $userPeso): string {
    // Admin (peso=1) sees all
    if ($userPeso === '1') {
        return " AND {$dimension}_PESO <= '1'";
    }

    // Manager (peso=2) sees peso >= 2
    if ($userPeso === '2') {
        return " AND {$dimension}_PESO >= '2'";
    }

    // User (peso=3) sees only peso >= 3
    return " AND {$dimension}_PESO >= '{$userPeso}'";
}

Peso Use Cases

Example 1: Sensitive Product Information

-- Admin-only product (peso='1')
INSERT INTO TB_ANAG_PRD00 (
    PRD_ID, XPRD01, XPRD02, PRD_SOURCE, PRD_PESO
) VALUES (
    'uuid-...', 'Confidential Product', 999.99, 'storeA', '1'
);

-- Manager-level product (peso='2')
INSERT INTO TB_ANAG_PRD00 (
    PRD_ID, XPRD01, XPRD02, PRD_SOURCE, PRD_PESO
) VALUES (
    'uuid-...', 'Internal Product', 199.99, 'storeA', '2'
);

-- Public product (peso='3')
INSERT INTO TB_ANAG_PRD00 (
    PRD_ID, XPRD01, XPRD02, PRD_SOURCE, PRD_PESO
) VALUES (
    'uuid-...', 'Standard Product', 99.99, 'storeA', '3'
);

Query Results:

# Admin user (peso='1')
GET /api/v4/core/PRD
# Returns: All 3 products

# Manager user (peso='2')
GET /api/v4/core/PRD
# Returns: 2 products (Internal + Standard, no Confidential)

# Regular user (peso='3')
GET /api/v4/core/PRD
# Returns: 1 product (Standard only)

Example 2: Order Visibility

-- Executive orders (peso='1')
INSERT INTO TB_ANAG_ORD00 (...) VALUES (..., '1');

-- Manager orders (peso='2')
INSERT INTO TB_ANAG_ORD00 (...) VALUES (..., '2');

-- Sales rep orders (peso='3')
INSERT INTO TB_ANAG_ORD00 (...) VALUES (..., '3');

Ambiente (Environment)

What is Ambiente?

ambiente separates data by environment:

• production - Live production data
• staging - Pre-production testing
• test - Development/testing
• demo - Demo/sandbox environment

Complete Separation:

Database
├── Production Data (ambiente='production')
│   └── Real customer data
├── Staging Data (ambiente='staging')
│   └── Pre-release testing data
├── Test Data (ambiente='test')
│   └── Development data
└── Demo Data (ambiente='demo')
    └── Demonstration data

Ambiente Filtering

Strict environment isolation:

-- Production user (ambiente='production')
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'storeA'
  AND PRD_CENTRO_DETT = 'admin'
  AND PRD_PESO <= '1'
  AND PRD_AMBIENTE = 'production'  -- ⭐ Production only
  AND TREC != 'C';

-- Test user (ambiente='test')
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'storeA'
  AND PRD_CENTRO_DETT = 'admin'
  AND PRD_PESO <= '1'
  AND PRD_AMBIENTE = 'test'        -- ⭐ Test only
  AND TREC != 'C';

No Cross-Environment Access:

# Production user session
POST /auth/login
{
  "username": "user@example.com",
  "source": "storeA",
  "ambiente": "production"
}

# Query products
GET /api/v4/core/PRD
# Returns: Only production products (no test data visible)

Ambiente Use Cases

Example 1: Testing Without Production Impact

-- Create test products
INSERT INTO TB_ANAG_PRD00 (
    PRD_ID, XPRD01, PRD_SOURCE, PRD_AMBIENTE
) VALUES
    ('uuid-1', 'Test Product 1', 'storeA', 'test'),
    ('uuid-2', 'Test Product 2', 'storeA', 'test');

-- Create production products
INSERT INTO TB_ANAG_PRD00 (
    PRD_ID, XPRD01, PRD_SOURCE, PRD_AMBIENTE
) VALUES
    ('uuid-3', 'Real Product 1', 'storeA', 'production'),
    ('uuid-4', 'Real Product 2', 'storeA', 'production');

Result:

• Test users see only test products
• Production users see only production products
• No risk of test data polluting production

Example 2: Demo Environment

-- Create demo data for sales presentations
INSERT INTO TB_ANAG_PRD00 (
    PRD_ID, XPRD01, PRD_SOURCE, PRD_AMBIENTE
) VALUES
    ('uuid-demo-1', 'Demo Product', 'storeA', 'demo');

Centro_Dett (Organizational Unit)

What is Centro_Dett?

centro_dett identifies organizational units within a tenant:

• admin - Administrative headquarters
• warehouse - Warehouse/distribution center
• store_001, store_002 - Individual store locations
• sales_north, sales_south - Sales regions

Centro_Dett Filtering

Exact match filtering (not hierarchical):

-- Admin user (centro_dett='admin')
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'retailChain'
  AND PRD_CENTRO_DETT = 'admin'    -- ⭐ Admin only
  AND PRD_PESO <= '1'
  AND PRD_AMBIENTE = 'production'
  AND TREC != 'C';

-- Store 001 user (centro_dett='store_001')
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'retailChain'
  AND PRD_CENTRO_DETT = 'store_001'  -- ⭐ Store 001 only
  AND PRD_PESO <= '3'
  AND PRD_AMBIENTE = 'production'
  AND TREC != 'C';

Centro_Dett Use Cases

Example 1: Retail Chain with Multiple Stores

-- HQ products (centro_dett='hq')
INSERT INTO TB_ANAG_PRD00 (
    PRD_ID, XPRD01, PRD_SOURCE, PRD_CENTRO_DETT
) VALUES
    ('uuid-1', 'Corporate Product', 'retailChain', 'hq');

-- Store 001 products (centro_dett='store_001')
INSERT INTO TB_ANAG_PRD00 (
    PRD_ID, XPRD01, PRD_SOURCE, PRD_CENTRO_DETT
) VALUES
    ('uuid-2', 'Store 001 Product', 'retailChain', 'store_001');

-- Store 002 products (centro_dett='store_002')
INSERT INTO TB_ANAG_PRD00 (
    PRD_ID, XPRD01, PRD_SOURCE, PRD_CENTRO_DETT
) VALUES
    ('uuid-3', 'Store 002 Product', 'retailChain', 'store_002');

Query Results:

# HQ user (centro_dett='hq')
GET /api/v4/core/PRD
# Returns: Only HQ products

# Store 001 user (centro_dett='store_001')
GET /api/v4/core/PRD
# Returns: Only Store 001 products

# Store 002 user (centro_dett='store_002')
GET /api/v4/core/PRD
# Returns: Only Store 002 products

Example 2: Warehouse Management

-- Warehouse inventory (centro_dett='warehouse')
INSERT INTO TB_ANAG_INV00 (
    INV_ID, XINV_QTY, INV_SOURCE, INV_CENTRO_DETT
) VALUES
    ('uuid-w1', 1000, 'logistics', 'warehouse');

-- Store inventory (centro_dett='store_001')
INSERT INTO TB_ANAG_INV00 (
    INV_ID, XINV_QTY, INV_SOURCE, INV_CENTRO_DETT
) VALUES
    ('uuid-s1', 50, 'logistics', 'store_001');

Complete Context Filtering

All Dimensions Together

Session Context:

{
  "user_id": "manager@retailchain.com",
  "source": "retailChain",
  "centro_dett": "store_001",
  "peso": "2",
  "ambiente": "production"
}

Generated Query:

SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'retailChain'          -- ⭐ Tenant
  AND PRD_CENTRO_DETT = 'store_001'       -- ⭐ Organizational unit
  AND PRD_PESO >= '2'                     -- ⭐ User level (manager)
  AND PRD_AMBIENTE = 'production'         -- ⭐ Environment
  AND TREC != 'C';                        -- Exclude deleted

Result:

• Only Store 001's products
• Only production data
• Only manager-level or lower (peso >= 2)
• Within retailChain tenant

JavaScript Context Example

class CoreAPIClient {
  constructor(sessionContext) {
    this.sessionContext = sessionContext;
    this.apiBase = 'https://api.example.com';
  }

  async getProducts(filters = {}) {
    // Session context automatically applied by server
    const response = await fetch(
      `${this.apiBase}/api/v4/core/PRD`,
      {
        headers: {
          'Authorization': `Bearer ${this.token}`,
          'Content-Type': 'application/json'
        }
      }
    );

    return response.json();

    // Server applies:
    // - source from token
    // - centro_dett from token
    // - peso from token
    // - ambiente from token
  }

  getContextInfo() {
    return {
      tenant: this.sessionContext.source,
      unit: this.sessionContext.centro_dett,
      level: this.sessionContext.peso,
      environment: this.sessionContext.ambiente
    };
  }
}

// Usage
const client = new CoreAPIClient({
  source: 'retailChain',
  centro_dett: 'store_001',
  peso: '2',
  ambiente: 'production'
});

console.log(client.getContextInfo());
// {
//   tenant: 'retailChain',
//   unit: 'store_001',
//   level: '2',
//   environment: 'production'
// }

const products = await client.getProducts();
// Only products matching ALL context dimensions

Best Practices

✅ DO:

Set complete context during login:

// ✅ Good - all dimensions specified
await login(username, password, {
  source: 'retailChain',
  centro_dett: 'store_001',
  peso: '2',
  ambiente: 'production'
});

Use peso for sensitive data:

-- ✅ Good - admin-only sensitive records
INSERT INTO TB_ANAG_PRD00 (..., PRD_PESO)
VALUES (..., '1');

Separate environments:

# ✅ Good - clear environment separation
# Production
POST /auth/login { "ambiente": "production" }

# Test
POST /auth/login { "ambiente": "test" }

Trust automatic filtering:

// ✅ Good - Core APIs handle all filtering
const products = await getProducts();

❌ DON'T:

Don't try to override context fields:

// ❌ Bad - context fields are read-only
await createProduct({
  XPRD01: 'Widget',
  PRD_PESO: '1'      // Validation error!
});

Don't mix environments:

// ❌ Bad - confusing production and test
if (ambiente === 'test') {
  createProduct(testData);
} else {
  createProduct(productionData);
}

// ✅ Good - separate sessions entirely

Don't assume hierarchical centro_dett:

// ❌ Bad - centro_dett is exact match, not hierarchical
// User with centro_dett='store_001' cannot see centro_dett='hq'

Don't hardcode context values:

// ❌ Bad - inflexible
const peso = '2';

// ✅ Good - from session
const peso = sessionContext.peso;

Summary

• ✅ Multi-dimensional context security: source + centro_dett + peso + ambiente
• ✅ Peso: Hierarchical user level (1=admin sees all, 3=user sees minimal)
• ✅ Ambiente: Environment isolation (production/staging/test/demo)
• ✅ Centro_Dett: Organizational unit (exact match filtering)
• ✅ All dimensions automatically applied to every query
• ✅ Context set during login, carried in JWT token
• ✅ Cannot override context fields (read-only)
• ✅ Complete isolation across all dimensions

Key Takeaways:

1. Context dimensions work together for fine-grained security
2. Peso is hierarchical (lower numbers see more)
3. Ambiente provides environment isolation
4. Centro_dett provides organizational unit separation
5. All context fields automatically filtered on every query
6. Context set once during login, cannot be changed per request
7. Trust automatic filtering - no manual WHERE clauses needed

Context Hierarchy:

source (tenant)
    ↓
centro_dett (organizational unit)
    ↓
peso (user level - hierarchical)
    ↓
ambiente (environment)
    ↓
Final filtered dataset

Example Query Flow:

User Login:
- source: 'retailChain'
- centro_dett: 'store_001'
- peso: '2'
- ambiente: 'production'

Query: GET /api/v4/core/PRD

Automatic Filtering:
WHERE PRD_SOURCE = 'retailChain'
  AND PRD_CENTRO_DETT = 'store_001'
  AND PRD_PESO >= '2'
  AND PRD_AMBIENTE = 'production'

Result: Only Store 001's manager-level production products

Related Concepts

• Tenant Isolation - source filtering
• Authentication - JWT with context
• Authorization - TB_MENU grants
• Security Overview - Multi-level security
• Context Chain - Context fundamentals