View Blog Post

Role-Based Row Level Security in Oracle APEX(global admin full control)

Published on 28 Jan 2026 by CHATURVEDINIDHI26@GMAIL.COM

Multi-tenant enterprise applications me data visibility sabse critical concern hoti hai.
Har role ka access clearly defined hona chahiye taaki:

❌ Unauthorized data exposure na ho
✅ Admins efficiently kaam kar saken
🔒 Security centralized & manageable rahe

Is article me hum ek real-world Oracle APEX security function explain kar rahe hain jisme:

Global Admin → sabka data dekh sakta hai
Tenant Admin → apni organization ka data
Managers → same org ka data
Normal Users → sirf apna data

🎯 Business Requirement

👥 Roles & Access Rules

Role	Data Access
Any User	Sirf apna data
Global Administrator	✅ All organizations ka data
Unrestricted User	✅ All data
Tenant Administrator	✅ Same organization
Managers / Heads	✅ Same organization

🧠 Design Philosophy

Access control ko page-wise ya report-wise logic me likhne ke bajay:

👉 Ek centralized PL/SQL function banaya gaya hai
jo har row ke liye decide karta hai:

“Kya current user is row ko dekh sakta hai ya nahi?”

🔑 Key Inputs

CREATED_BY → Row ka owner
ORG_PROFILE_ID → Organization / Tenant
ROLE_STATIC_ID → User ka role

🧩 Core Logic Explained (Step-by-Step)

1️⃣ Self Data – Highest Priority

Chahe koi bhi role ho, user apna data hamesha dekh sakta hai.

IF p_created_by = l_app_user THEN RETURN 1; END IF;

2️⃣ Logged-in User Context

User ka role, org aur unrestricted flag xxeway_org_users se nikala jata hai.

SELECT MAX(role_static_id), MAX(org_profile_id), MAX(NVL(unrestricted_fl,'N')) INTO l_role_static_id, l_org_profile_id, l_unrestricted_fl FROM xxeway_org_users WHERE user_name = l_app_user AND active_fl = 'Y';

3️⃣ Global / Unrestricted Access

Agar user:

GLOBAL_ADMINISTRATOR hai
ya unrestricted_fl = 'Y'

to usko kisi bhi organization ka data dikhaya jata hai.

IF l_role_static_id = 'GLOBAL_ADMINISTRATOR' OR l_unrestricted_fl = 'Y' THEN RETURN 1; END IF;

4️⃣ Tenant Admin – Same Organization

Tenant admin sirf apni organization ka data dekh sakta hai.

IF l_role_static_id = 'TENANT_ADMINISTRATOR' AND p_row_org_id = l_org_profile_id THEN RETURN 1; END IF;

5️⃣ Managers / Heads – Same Org Access

Department aur operational roles ko bhi org-level access milta hai.

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;

❌ Default: No Access

Agar koi condition match nahi karti:

RETURN 0;

✅ Final PL/SQL Function (Production Ready)

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_unrestricted_fl CHAR(1); l_app_user VARCHAR2(200); BEGIN l_app_user := SYS_CONTEXT('APEX$SESSION','APP_USER'); /* 1️⃣ SELF DATA */ IF p_created_by = l_app_user THEN RETURN 1; END IF; /* User context */ SELECT MAX(role_static_id), MAX(org_profile_id), MAX(NVL(unrestricted_fl,'N')) INTO l_role_static_id, l_org_profile_id, l_unrestricted_fl FROM xxeway_org_users WHERE user_name = l_app_user AND active_fl = 'Y'; /* 2️⃣ GLOBAL / UNRESTRICTED */ IF l_role_static_id = 'GLOBAL_ADMINISTRATOR' OR l_unrestricted_fl = 'Y' THEN RETURN 1; END IF; /* 3️⃣ TENANT ADMIN */ IF l_role_static_id = 'TENANT_ADMINISTRATOR' AND p_row_org_id = l_org_profile_id THEN RETURN 1; END IF; /* 4️⃣ MANAGERS */ 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; /