SQL Predicate Generates UNION ALL SELECT After Modifying Criteria In Person Security Profile (Doc ID 2305145.1)

Last updated on APRIL 08, 2019

Applies to:

Symptoms

ACTUAL BEHAVIOR
---------------
SQL Predicate generates UNION ALL SELECT after modifying criteria in Person Security Profile

The SQL Predicate generates UNION ALL SELECT after modifying criteria in Person Security Profile. This is causing huge performance issues (Reports -> time-out) and long SQL predicate (strings sometimes longer than 4000 characters).


EXPECTED BEHAVIOR
-----------------------
SQL predicate should not create union all and increase in size

STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. For this example we’re using a Department Tree (inside Org. Sec. Profile) to determine which persons they query on. But we also saw the same issue when we’re using a list of Legal Employers (inside Org. Sec. Profile), and also when we’re using a Custom SQL statement. Then the SQL predicate will becomes twice as long.

Security Profile:
Include future people
Secure by Department
Assignments to Evaluate: Active, suspended, or most recently terminated

The SQL Predicate looks fine.

2. When  assigning this Person Security Profile to a Data role, and when we’re changing the person security profile again, the SQL predicate will increase.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.