PER_ALL_PEOPLE_F vs PER_ALL_ASSIGNMENTS_F: Which Drives Assignment Security? (Oracle Fusion HCM, 2026)

If your Oracle Cloud HCM security expectations feel “off by a lot” when you switch between PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F, you’re not imagining it. In Oracle HRMS security architecture, both of these tables are secured.

Key Takeaways

Question	What to check	Why it matters for assignment security
Which table actually limits visibility?	PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F under secure views and security profiles	Both tables are secured, so neither is “automatically less secure” in Oracle Fusion HCM
Why do my results differ by user?	Unrestricted vs Restricted security profiles, and “sec” definition	Restricted modes can filter People and Assignments differently
Why do queries inflate headcount?	Date-effective history filters	Datetracking means you can unintentionally count multiple time slices
Why does OTBI reporting sometimes “hide” rows?	OTBI/secure view exposure vs physical table access	OTBI reporting does not always expose legislative/raw security inputs
Where do you start for the right columns?	Use the Oracle HCM table reference and module mappings	Finding the correct table relationships and effective flags quickly reduces debug time

• Most common mistake: comparing PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F without aligning the same security profile and “sec” logic.
• What we recommend: validate both time-sliced alignment and access path, especially for reporting users using public synonyms.
• Handy reference: see the table deep dives at Oracle HCM table pages when you need production-ready SQL patterns.
• Practical next step: confirm whether your layer is OTBI reporting or physical SQL, because the exposure differs in oracle fusion hcm.

Dry truth, straight to the point: assignment security feels inconsistent because your security definition drives secure view filtering, and date-effective history drives row multiplicity.

Why People vs Assignments feels different in Oracle HCM tables

In oracle cloud hcm implementations, people-centric reporting and assignment-centric reporting look similar on the surface, because both ultimately map to the same workforce reality. But in practice, PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F interact with security profile rules and “sec” definitions in ways that are easy to misread.

This is not a bug. It’s the architecture.

We see the confusion most often when:

• You build a join from PER_ALL_PEOPLE_F to PER_ALL_ASSIGNMENTS_F, but you apply different filters (or none) to the date-effective history.
• You test as an admin or “unrestricted” profile user, then validate as a restricted reporting user, expecting identical visibility.
• You run OTBI reporting assumptions on physical table security, then notice rows missing or extra rows appearing.

And yes, both are part of the secured set in oracle hr tables security configuration. That’s why the real question becomes: which path is enforcing the restriction you care about, and at what level?

For developers, the fix is usually found in column-level filtering and in aligning security context, not in “switching tables.”

Stat Card 1: Both tables are secured, so security differences are definition-driven

Oracle HRMS security profiles, secure view filtering, and “sec” definition

To answer “which drives assignment security,” you have to stop thinking in terms of a single table and start thinking in terms of secure views and security profiles. The security model includes unrestricted and restricted modes, and the definition of sec controls what secure views expose.

There are two types of security profile: Unrestricted and Restricted. In an unrestricted profile, a responsibility has unrestricted access to data in Oracle HRMS tables. That means person vs assignment differences are less likely to show up as missing rows, because the secure view filtering aligns with base-table visibility.

In restricted security profiles, things change. Restricted security profiles may restrict access to entities including People and Assignments. If you are evaluating PER_ALL_PEOPLE_F vs PER_ALL_ASSIGNMENTS_F: Which Drives Assignment Security? in 2026, this is the key mechanism that explains why different users see different assignment row sets.

Also note the bypass scenario. When you connect to the APPS Oracle User with a restricted security profile you can access the secure tables directly if you want to bypass the security restrictions defined in your security profile. If your testing uses different connection patterns than your BI layer, you can get “contradictory” results.

Datetracking and effective rows: the #1 cause of inflated assignment counts

Even if security profiles are perfect, datetracking can still make it look like assignment security is wrong. PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F are datetracked, which means you have time-sliced rows. If your query logic is sloppy, you’ll see duplicates that are not duplicates, they’re different effective history slices.

Your query returns 3 rows for every employee. Your headcount report is off by 200%.

This is why developers and OTBI/BI Publisher analysts keep stumbling over the same pattern: they filter by business keys, not by the “current” effective slice. Most queries only want current state, and forgetting to filter for it is the #1 cause of inflated headcount numbers in Oracle HCM reporting.

Here’s the practical rule we enforce in production-ready SQL:

• Add WHERE EFFECTIVE_LATEST_CHANGE = 'Y' to every query against PER_ALL_ASSIGNMENTS_M.
• For PER_ALL_PEOPLE_F, apply the equivalent date-effective “current slice” logic based on the columns exposed in that table.
• Join on person identifiers that match the same effective slice when you need alignment, not merely existence.

That single column eliminates 95% of duplicate row problems.

In oracle fusion hcm reporting, the security profile decides which rows are eligible. The effective-slice filters decide how many rows eligible show up in your result set.

OTBI reporting vs physical SQL: where security enforcement actually happens

This is where teams often lose a day. OTBI reporting does not necessarily expose the raw legislative data or the full security inputs you can see with physical SQL. Most compliance reporting requires physical SQL because OTBI subject areas don't expose the raw legislative data needed for EEO-1 and ACA calculations.

So, when you ask about PER_ALL_PEOPLE_F vs PER_ALL_ASSIGNMENTS_F and assignment security, your “answer” can change depending on whether you’re running OTBI reporting, BI Publisher, or direct SQL against the physical tables.

Guidance:

• If you’re validating assignment visibility in OTBI reporting, assume secure views are involved, and validate behavior using the same user profile and same time period logic.
• If you’re validating data integrity or constructing custom compliance extracts, use physical SQL and explicitly control effective-date and security-relevant join paths.
• If you’re building a data feed using our HDL data loader patterns, treat security as a downstream enforcement layer, then verify the row set before insertion.

In other words, OTBI reporting is a consumption layer, physical SQL is a data extraction layer. Both matter, but they do not behave the same when “sec” filtering and legislative exposure diverge.

If you're reading this while debugging a join, don’t guess which layer is “wrong.” Confirm the layer first, then align filters and keys.

Best-practice query patterns for oracle hcm tables and hcm data model alignment

Your goal is deterministic output: one person, one assignment, one effective slice, within the rows allowed by the security profile. That’s what makes assignment security auditable in 2026.

Below are patterns we recommend when working with oracle hcm tables (including PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F) in an oracle fusion hcm and oracle cloud hcm context.

The “security first, time slice second” workflow

1. Set up test users representing unrestricted and restricted security profiles, then run the same inputs in the same reporting layer.
2. Start from the secured person or assignment entity and keep joins minimal until row counts stabilize.
3. Apply date-effective alignment (current slice) before you aggregate anything.
4. Only then join additional dimension tables that can create further multiplicity.

Join logic that avoids “silent multiplication”

• When you join People to Assignments, join using stable person identifiers and then apply effective logic on both sides.
• When you filter assignments by status or attributes, prefer columns that correspond to the assignment effective history, not only the person effective history.
• When you need position or organization context, attach it after you lock the person and assignment slice.

Use table references, not memory

The right columns and relationships are not always intuitive, especially across the oracle hr tables naming conventions. Getting the mappings right is faster when you can jump directly into table reference pages and module breakdowns.

We built HCM Tables specifically because Oracle's schema is so complex that even experienced HCM developers need a dedicated search engine for it (14,950 tables, 1.2 million columns). That complexity has a real cost. The fix is making sure you pull the correct columns for your current slice and your security-relevant join keys.

For example, when building enterprise extracts, you often need safe time-sliced access patterns for unrelated but date-effective tables as well, like absence balances (see ANC_ABSENCE_BALANCES_V) to validate your overall date handling strategy.

Stat Card 2: Restricted profiles can restrict People and Assignments, so assignment security is a profile outcome

What drives assignment security in practice (2026): the real decision tree

Let’s make this practical. The table that “drives” assignment security is not a single one, it is whichever entity is restricted by the security profile and which secure view logic your reporting layer uses.

So, how do you decide quickly which side is responsible? Use this decision tree.

Decision tree

• If unrestricted users see rows in both PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F, and restricted users do not, then the restriction is profile-driven, not query-driven.
• If restricted users lose only assignment rows (but still see People), then the restricted profile is filtering Assignments more aggressively than People, or your assignment join is exposing a different effective slice set.
• If restricted users lose both People and Assignments, then the sec definition is restricting entity visibility at both levels, and your join will naturally collapse.
• If you see inconsistent results between OTBI reporting and BI Publisher, confirm whether secure views and subject area exposure differ from your physical SQL logic.

This is why PER_ALL_PEOPLE_F vs PER_ALL_ASSIGNMENTS_F: Which Drives Assignment Security? has a stable answer conceptually, but operationally depends on your security profile and effective filters.

Where fast formula and downstream logic can make it look like security changed

Security visibility can look stable, yet your output still changes because downstream logic filters or computes differently. If you use fast formula, variable-hour logic, or status derivations, you can end up excluding rows from final reports even though the raw entity visibility was unchanged.

Getting variable-hour employee status wrong creates IRS exposure. The PAY_ query logic is non-obvious.

In assignment-security troubleshooting, treat downstream logic as a second layer. Confirm access first (secure views or physical SQL result set), then confirm transformation logic (fast formula rules, status flags, and aggregation logic).

If your results differ after you “fix” date-effective filtering, inspect which fields are derived and whether they depend on the same effective slice you thought you were using.

Using our schema reference approach to speed up security debugging

We tell teams to stop hunting through an endless hcm data model by memory. In oracle fusion hcm environments with oracle hcm tables and oracle hr tables, the correct columns and relationships matter, and the fastest route is table-based discovery.

For schema reference, Oracle HCM Tables access includes an offline schema reference PDF with module breakdowns. The pricing on the reference entry point is $1.50 for Oracle HCM Tables access (unlimited search unlock), and the schema reference describes 14,950 tables with key columns, suffixes, and module breakdowns.

If you’re building an assignment security investigation toolkit for 2026, the time you save by mapping the right join keys and effective flags is usually more valuable than trying to “reason it out” from scattered samples.

And when your investigation expands beyond People and Assignments into compliance-adjacent areas like calendars and event sets, the same date-effective discipline applies. For example, reference patterns often show up when you work with absence or event calendars such as ANC_CALENDAR_SUBSCRIBER and ANC_CALENDAR_VL, where effective dating can create the same “multiplicity illusion” if you filter incorrectly.

How to implement the fix: a production-ready checklist for oracle fusion hcm

Here’s the checklist we use to make PER_ALL_PEOPLE_F vs PER_ALL_ASSIGNMENTS_F security debugging fast and repeatable for oracle cloud hcm and oracle fusion hcm reporting in 2026.

• Align your security context: test the same user, same security profile, same connection method (OTBI reporting vs physical SQL).
• Lock current slices: apply the “current effective row” logic on both People and Assignments so your join does not multiply history.
• Validate entity filtering: check whether restricted mode is excluding People, Assignments, or both.
• Control join order: join dimensions only after you stabilize person and assignment row counts for your effective slice.
• Reconcile downstream logic: if reports use fast formula, validate that derived flags do not hide rows that were visible at the entity layer.

If you do those five things, you will stop debating which table is “more secure,” and you will start measuring what your security profile definition is actually doing.

Conclusion

PER_ALL_PEOPLE_F vs PER_ALL_ASSIGNMENTS_F: Which Drives Assignment Security? The direct answer is: security is profile-driven, both tables are secured, and restricted security profiles may restrict People and Assignments. The operational answer is: which table appears to “drive” the outcome depends on your security profile and your secure view or physical SQL path, plus correct datetracking filters.

For 2026, the reliable approach is to treat security context and effective-slice logic as two separate layers. Lock one, then debug the other. That’s how you make oracle hcm tables behavior predictable in OTBI reporting, BI Publisher, and physical SQL extracts.

Frequently Asked Questions

Is PER_ALL_PEOPLE_F or PER_ALL_ASSIGNMENTS_F more secure in 2026?

Neither table is inherently “more secure” by default, because PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F are both secured in Oracle HRMS. In 2026, what changes is the restricted security profile and the definition of “sec,” which can filter People and Assignments differently.

Why do I see different assignment rows when I switch from PER_ALL_PEOPLE_F to PER_ALL_ASSIGNMENTS_F?

The usual causes are restricted profile filtering plus date-effective history alignment. Since both tables are datetracked, missing “current slice” filters will inflate or fragment your assignment results, even when the underlying security visibility is consistent.

How do I fix duplicate rows in Oracle HCM when joining PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F?

Apply effective-slice filters consistently on both sides of the join, and validate you are counting the correct time slice. A common high-impact rule is to filter assignments with EFFECTIVE_LATEST_CHANGE = 'Y' when using assignment rows, because it eliminates most duplicate-row problems.

Does OTBI reporting enforce the same security logic as physical SQL for PER_ALL_PEOPLE_F vs PER_ALL_ASSIGNMENTS_F?

Not always. OTBI reporting depends on subject area exposure and secure view behavior, while physical SQL lets you control extraction and joins directly against the underlying data model and date-effective logic.

What is “sec definition” and how does it change PER_ALL_PEOPLE_F vs PER_ALL_ASSIGNMENTS_F results?

Secure view information visibility depends on the definition of “sec,” which is tied to your security profile. In restricted mode, that definition can change how secure views filter People and Assignments, so the same query can return different row sets for different users.

Can restricted security profiles restrict assignments even if I can see people?

Yes. Restricted security profiles may restrict access to People and Assignments independently, so it’s possible to see People rows while assignment visibility is narrowed. This typically shows up when analyzing PER_ALL_PEOPLE_F vs PER_ALL_ASSIGNMENTS_F in oracle fusion hcm security investigations.