Enterprise applications me data security sirf login tak limited nahi hoti.
Sabse important hota hai “kaun sa user kaunsa data dekh sakta hai” — especially jab multi-tenant architecture ho.

Is article me hum dekhenge:

• Oracle APEX me row-level access control
• Global Admin, Tenant Admin aur Team users ke liye clear rules
• Ek reusable PL/SQL function ke through secure implementation

🎯 Business Requirement

Application me multiple organizations (tenants) hain aur users ke roles defined hain:

👥 Roles & Access Rules

👉 Key Rule:

Global admin hone ka matlab “sab kuch dekhna” nahi hota.

🧠 Design Approach

Humne access control ko UI ya page-level pe hardcode karne ke bajay,
ek centralized PL/SQL function me define kiya.

Benefits:

• ✅ Single source of truth
• ✅ Easy to maintain
• ✅ Reports, IGs, REST APIs sab jagah reusable
• ✅ Secure & scalable

🧩 Core Concept

Har data row ke saath do important columns maan ke chalte hain:

• CREATED_BY → Data ka owner
• ORG_PROFILE_ID → Kis organization ka data

Logged-in user ka context hum nikalte hain:

SYS_CONTEXT('APEX$SESSION','APP_USER')

🧪 Final PL/SQL Function

CREATE OR REPLACE FUNCTION xxeway_can_access_user (    p_created_by   IN VARCHAR2,    p_row_org_id   IN NUMBER ) RETURN NUMBER IS    l_role_static_id  xxeway_org_users.role_static_id%TYPE;    l_org_profile_id  xxeway_org_users.org_profile_id%TYPE;    l_app_user        VARCHAR2(200); BEGIN    l_app_user := SYS_CONTEXT('APEX$SESSION','APP_USER');    /* 1️⃣ SELF DATA – ALL USERS */    IF p_created_by = l_app_user THEN        RETURN 1;    END IF;    /* Logged-in user role & org */    SELECT MAX(role_static_id),           MAX(org_profile_id)    INTO   l_role_static_id,           l_org_profile_id    FROM   xxeway_org_users    WHERE  user_name = l_app_user    AND    active_fl = 'Y';    /* 2️⃣ GLOBAL ADMIN – ONLY SELF DATA */    IF l_role_static_id = 'GLOBAL_ADMINISTRATOR' THEN        RETURN 0;    END IF;    /* 3️⃣ TENANT ADMIN – TEAM DATA (SAME ORG) */    IF l_role_static_id = 'TENANT_ADMINISTRATOR'       AND p_row_org_id = l_org_profile_id THEN        RETURN 1;    END IF;    /* 4️⃣ MANAGERS – SAME ORG */    IF l_role_static_id IN (           'DEPARTMENT_HEAD',           'PROCUREMENT_MANAGER',           'WAREHOUSE_MANAGER'       )       AND p_row_org_id = l_org_profile_id THEN        RETURN 1;    END IF;    RETURN 0; END; / 

📊 How to Use in APEX Reports / IG

SELECT * FROM   your_table WHERE  xxeway_can_access_user(created_by, org_profile_id) = 1;

✔ Automatically filtered data
✔ No extra logic in page
✔ Security handled centrally

🔐 Why This Pattern Is Powerful

• 🔒 True row-level security
• 🔁 Reusable across modules
• 🧩 Easy to extend (future roles)
• 🚀 Production-ready architecture

🚀 Possible Enhancements

• Convert this logic into Authorization Scheme
• Implement Database VPD (DBMS_RLS)
• Add read-only vs edit access
• Enable audit logging

✅ Conclusion

Is approach se:

• Global admin overpowered nahi hota
• Tenant apni team manage kar sakta hai
• Har user ka data secure rehta hai

Ye real-world enterprise-grade security model hai jo Oracle APEX applications ko robust & compliant banata hai.