Introduction
Security Tables in Oracle Fusion HCM are one of the most critical technical components every consultant must understand when working on reporting, integrations, or troubleshooting data access issues. In real-world implementations, most reporting errors or data visibility issues are not due to incorrect queries—but due to missing or misunderstood security layers.
Oracle Fusion HCM (Release 26A) uses a data security model that enforces access control through roles, privileges, and security profiles. These configurations are stored and enforced using backend security tables and views, which act as the backbone for securing person, assignment, and organization data.
As a consultant, if you are working with OTBI, BI Publisher (BIP), HDL, or OIC Gen 3 integrations, understanding how these security tables work will save you hours of debugging.
What are Security Tables in Oracle Fusion HCM?
Security tables in Oracle Fusion HCM are database objects (tables and secured views) that:
Control who can see what data
Restrict access based on roles and security profiles
Ensure data privacy and compliance
Apply row-level security (RLS) dynamically
Unlike legacy systems, Oracle Fusion does not allow direct access to base tables for reporting. Instead, it provides secured views like:
PER_PERSON_SECURED_LIST_VPER_ASSIGNMENT_SECURED_LIST_VHR_ORGANIZATION_UNITS_F_VL
These secured objects automatically apply security filters based on the logged-in user.
Key Features of Oracle Fusion HCM Security Tables
1. Role-Based Data Security
Users are assigned job roles, abstract roles, and data roles
Each role has data access policies
Security tables enforce these policies at runtime
2. Row-Level Security (RLS)
Data visibility is filtered row by row
Example: HR Manager sees only employees in their department
3. Security Profiles
Security profiles define the scope of data access:
Person Security Profile
Assignment Security Profile
Organization Security Profile
Position Security Profile
4. Secured Views Instead of Base Tables
You should never query base tables like
PER_ALL_PEOPLE_FdirectlyAlways use secured views like:
PER_PERSON_SECURED_LIST_VPER_ASSIGNMENT_SECURED_LIST_V
5. Dynamic Data Filtering
Security is applied dynamically based on:
Logged-in user
Assigned roles
Security profile conditions
Real-World Integration Use Cases
Use Case 1: BI Publisher Report Showing Limited Employees
Scenario:
Client reports that HR users cannot see all employees in a report.
Root Cause:
Report used base table PER_ALL_PEOPLE_F instead of secured view.
Fix:
Replace with PER_PERSON_SECURED_LIST_V.
Use Case 2: OIC Gen 3 Integration Missing Data
Scenario:
OIC integration fetches employee data but returns incomplete results.
Root Cause:
Integration user lacks proper data role or security profile.
Fix:
Assign correct Person Security Profile to integration user.
Use Case 3: OTBI Report Data Mismatch
Scenario:
OTBI shows fewer records than BIP report.
Root Cause:
Different security contexts between OTBI and BIP.
Fix:
Align roles and ensure consistent security profiles.
Architecture / Technical Flow
Understanding how security tables work internally is key.
Step-by-Step Flow:
User logs into Oracle Fusion
User is assigned:
Roles (Job/Data roles)
Security Profiles
Query is executed (OTBI/BIP/OIC)
Secured view is accessed
Security predicates are applied dynamically
Only authorized rows are returned
Important Insight
Security is enforced using Oracle VPD (Virtual Private Database) policies behind the scenes.
Important Security Tables and Views
Below are commonly used security-related tables and views:
| Table/View Name | Description |
|---|---|
| PER_PERSON_SECURED_LIST_V | Secured person data |
| PER_ASSIGNMENT_SECURED_LIST_V | Secured assignment data |
| PER_USERS | User information |
| PER_USER_ROLES | Mapping of users to roles |
| PER_ROLES_DN | Role definitions |
| PER_SECURITY_PROFILES | Security profile details |
| PER_SEC_PROFILE_ASSIGNMENTS | Profile assignments |
| FUN_USER_ROLE_DATA_ASGNMNTS | Data role assignments |
Prerequisites
Before working with security tables, ensure:
Access to BI Publisher / OTBI / SQL tools
Proper role (e.g., BI Developer Role)
Understanding of:
HCM data model
Security profiles
Role-based access control
Step-by-Step Build Process (Using Security Tables in Reporting)
Step 1 – Identify Requirement
Example:
“Fetch employee details visible to logged-in HR user”
Step 2 – Choose Secured View
Use:
Step 3 – Write Query
PERSON_ID,
PERSON_NUMBER,
FULL_NAME
FROM
PER_PERSON_SECURED_LIST_V
WHERE
EFFECTIVE_END_DATE > SYSDATE
Step 4 – Add Joins Carefully
Join with other secured views if needed:
Step 5 – Deploy in BI Publisher
Navigation:
Navigator → Tools → Reports and Analytics → Create Report
Step 6 – Assign Role for Testing
Ensure test user has:
Data role
Person security profile
Testing the Security Setup
Example Test Case
User: HR Manager – Finance Department
Expected: Should see only Finance employees
Validation Steps
Login as HR Manager
Run report
Verify:
Only Finance department employees are visible
No cross-department data leakage
Debug Tip
If no data appears:
Check role assignment
Check security profile conditions
Validate effective dates
Common Errors and Troubleshooting
Issue 1: No Data Returned
Cause: Missing security profile
Solution: Assign proper data role
Issue 2: Too Much Data Visible
Cause: Broad security profile
Solution: Restrict profile conditions
Issue 3: Data Mismatch Between Reports
Cause: Different data sources (secured vs base tables)
Solution: Standardize on secured views
Issue 4: OIC Integration Returns Partial Data
Cause: Integration user lacks role
Solution: Assign HCM data role to integration user
Best Practices from Real Projects
1. Always Use Secured Views
Never use:
PER_ALL_PEOPLE_F
Always use:
PER_PERSON_SECURED_LIST_V
2. Validate Security Early
During SIT phase:
Test reports with multiple roles
Validate edge cases
3. Use Dedicated Integration User
For OIC Gen 3:
Create separate user
Assign minimal required roles
4. Document Security Profiles
Maintain documentation for:
Profile logic
Role assignments
5. Avoid Overlapping Security Profiles
Overlapping profiles cause:
Duplicate data
Performance issues
6. Monitor Performance
Secured views add overhead:
Optimize queries
Avoid unnecessary joins
Real Implementation Scenario (End-to-End)
Scenario: Global Organization with Regional HR Access
Requirement:
US HR sees US employees
India HR sees India employees
Solution:
Create Person Security Profiles:
US Profile → Country = US
India Profile → Country = IN
Assign profiles to roles
Assign roles to users
Use secured views in reports
Result:
Dynamic filtering based on logged-in user
No manual filtering required
Frequently Asked Questions (FAQs)
1. Why should we use secured views instead of base tables?
Secured views enforce data security automatically. Base tables bypass security and can expose sensitive data, which is not recommended in Fusion.
2. How does Oracle enforce row-level security?
Oracle uses Virtual Private Database (VPD) policies to dynamically apply filters on secured views based on user roles and security profiles.
3. Why does my BI report show different data for different users?
Because data visibility depends on:
Assigned roles
Security profiles
Data access policies
Summary
Security Tables in Oracle Fusion HCM are not just backend objects—they are the foundation of data security and compliance in the system.
As a consultant, you must:
Understand secured views vs base tables
Know how roles and security profiles interact
Always test with real user scenarios
Design reports and integrations with security in mind
In real projects, mastering security tables will significantly improve your ability to:
Debug issues faster
Build secure reports
Deliver reliable integrations
For deeper reference, always review Oracle’s official documentation:
https://docs.oracle.com/en/cloud/saas/index.html