Oracle Fusion SQL Security Explained: Data Roles, Row-Level Security, and Sensitive Data Access

One of the most confusing aspects of Oracle Fusion reporting is that the same SQL query can return different results for different users. A report that works perfectly for one analyst may return fewer rows - or even no rows at all - for another user with a different security configuration.

This behavior is not caused by SQL errors. It is the result of Oracle Fusion's security architecture, which enforces access control through multiple layers including Functional Roles, Data Roles, HCM Security Profiles, and database-level row filtering mechanisms.

Understanding Oracle Fusion HCM Security, Oracle Fusion Data Roles, Oracle Fusion Security Profiles, and Oracle Fusion Row-Level Security is essential when building BI Publisher reports, validating OTBI results, or developing Oracle Fusion SQL across Finance, HCM, Procurement, and SCM modules.

Why Security Matters in Oracle Fusion SQL

Oracle Fusion is a multi-tenant SaaS platform designed to protect sensitive business and employee information. Unlike traditional on-premise Oracle environments, Fusion does not rely solely on application menus and responsibilities - security is enforced directly at the data access layer.

• Two users may run the same report and receive different row counts
• Sensitive columns may return null or masked values even when rows are visible
• A report can return zero rows despite valid SQL and correct joins
• OTBI and BI Publisher may return different row counts for the same data

Understanding these behaviors helps developers avoid spending hours troubleshooting SQL that is actually working correctly - and prevents inadvertent data exposure in production reports.

The Four Security Layers in Oracle Fusion

Why Reports Sometimes Return No Data

One of the most common Oracle Fusion support requests is: "The SQL works for me but returns zero rows for another user." This is almost always caused by a missing Data Role or incorrectly scoped Security Profile - not a SQL error.

SELECT
    person_number,
    person_id
FROM
    per_all_people_f
WHERE
    TRUNC(SYSDATE) BETWEEN effective_start_date
                       AND effective_end_date

This SQL is valid. But depending on security assignments, User A may see 15,000 workers, User B may see 250, and User C may see zero. The difference is always security context - not SQL design.

Sensitive Data Access in Oracle Fusion

Some information requires additional privileges beyond standard Data Roles. A user may be able to see an employee record while still being restricted from viewing salary or payroll information.

Common HCM Tables Affected by Security

Why OTBI and BI Publisher Sometimes Show Different Results

This is one of the most misunderstood Oracle Fusion reporting scenarios. OTBI and BI Publisher can show different row counts for the same business data - and the reason is security context, not SQL design.

FusionLens SQL: Security at the SQL Development Layer

Oracle Fusion's native security governs what end users can access through application screens, OTBI analyses, and BI Publisher report outputs. But it does not govern what a developer or analyst can query when they have direct SQL access to the Oracle Fusion data model.

In a standard development environment, a developer with access to the OTBI Compensation subject area can build an analysis that exposes salary amounts. A BI Publisher developer using a privileged service account can query CMP_SALARY directly and see every salary record in the result set. These scenarios exist within Oracle's documented security model - but they may conflict with your organisation's data governance policies.

FusionLens SQL adds a security governance layer at the SQL development tool itself - independent of and in addition to the Oracle Fusion security model.

Real-World Example: Protecting Salary Data During Report Development

A developer has access to the OTBI Compensation subject area. Company policy states they must not see actual salary values during report development - but Oracle Fusion itself permits the query.

SELECT
    person_number,
    salary_amount
FROM
    cmp_salary
WHERE
    TRUNC(SYSDATE) BETWEEN effective_start_date
                       AND effective_end_date

PERSON_NUMBER  SALARY_AMOUNT
100123         85000
100124         92000
100125         110000

PERSON_NUMBER  SALARY_AMOUNT
100123         ****
100124         ****
100125         ****

Real-World Example: Table Access Control

A Finance analyst attempts to query a payroll results table. Their Oracle database credentials would permit the query. FusionLens SQL intercepts it before it reaches the database.

SELECT *
FROM   pay_run_result_values
WHERE  ROWNUM <= 10

PAY_RUN_RESULT_VALUES is restricted for
the current user group (Finance Analyst).

Contact your FusionLens SQL administrator
to request access to this table.

Summary: how FusionLens SQL governance applies across roles

How Oracle Fusion Security Affects SQL Development

The most common security confusion Oracle Fusion developers encounter is this: the same query returns different row counts depending on where and as whom it is executed. This is not a SQL error - it is security working as designed.

Consider this example:

SELECT COUNT(*)
FROM   per_all_people_f
WHERE  TRUNC(SYSDATE)
           BETWEEN effective_start_date
               AND effective_end_date

The 9,650 missing rows are not a bug. The end user's security configuration limits visibility to their assigned scope. The reasons are always one or more of the following:

• Person Security Profile - limits the worker population visible to this user
• Data Role - restricts access to a specific Business Unit, Legal Employer, or Country
• Legal Employer Scope - the user's Data Role covers only workers in certain legal entities
• Business Unit Scope - assignment-level visibility is filtered by Business Unit assignment

Security Concepts Every Oracle Fusion SQL Developer Should Know

Building Security-Aware BI Publisher Reports

Before deploying a report, validate it using accounts with different security profiles. These validation queries help confirm what each user type can actually see.

Query 1 - Worker Population Validation

Run this as each intended report user to confirm how many workers their security context exposes. A zero result means the Person Security Profile is missing or incorrectly scoped.

SELECT
    COUNT(*) AS worker_count
FROM
    per_all_people_f p
WHERE
    TRUNC(SYSDATE) BETWEEN p.effective_start_date
                       AND p.effective_end_date

Query 2 - Active Assignment Validation

Validates how many active assignments are visible. Compare results across HR Administrator, Business Unit Manager, and a restricted HR role to understand the security boundary.

SELECT
    COUNT(*) AS active_assignments
FROM
    per_all_assignments_m a
WHERE
    a.primary_flag = 'Y'
    AND TRUNC(SYSDATE) BETWEEN a.effective_start_date
                           AND a.effective_end_date

Query 3 - Salary Visibility Validation

If Compensation users can see results but HR users cannot, the issue is a missing Compensation Data Role - not a SQL problem. Depending on the security configuration, salary records may be hidden entirely, filtered from the result set, or returned with restricted values for users without the Compensation Manager or Payroll Data Role.

SELECT
    assignment_id,
    salary_amount,
    annual_salary
FROM
    cmp_salary
WHERE
    TRUNC(SYSDATE) BETWEEN effective_start_date
                       AND effective_end_date

Note: Compensation tables are among the least standardized areas across Oracle Fusion releases. The effective-dating pattern can vary - some environments use date_to IS NULL for current records, others use the BETWEEN pattern shown above. Always validate the effective-dating convention in your own environment before using queries in production.

Query 4 - Masking PII Columns in BI Publisher SQL

Even when a user is authorized to access a row, reports shared beyond HR may need to mask PII. Apply masking directly in SQL so the data model layer controls exposure - not the report layout layer.

SELECT
    person_number,
    full_name,
    REGEXP_REPLACE(
        national_identifier,
        '.(?=.{4})',
        '*'
    ) AS masked_national_id
FROM
    per_people_legislative_f
WHERE
    TRUNC(SYSDATE) BETWEEN effective_start_date
                       AND effective_end_date

Output example: *******1234 - reduces PII exposure in exported Excel or CSV reports without restricting the row itself.

Security Beyond HCM: Finance and Procurement

Oracle Fusion SQL security is not limited to worker data. Finance and Procurement modules apply their own Data Role and security scoping mechanisms - and the same principle applies: two users running the same query may see entirely different result sets.

Oracle Fusion Tables Commonly Restricted by Security Policies

The following tables are frequently subject to Data Role restrictions, Security Profile scoping, or column-level sensitivity requirements. Developers who query these tables directly should expect result sets to vary significantly based on the executing user's security context.

Security Best Practices for Oracle Fusion SQL Reports