EGL9.2: For Chartfield Security By User, the Job Loads All Rules From All Active Effective Dates Including History (Doc ID 2807707.1)

Last updated on OCTOBER 04, 2021

Applies to:

Symptoms

Chartfield Security for User is not assigning rules correctly.  When user runs the Security Build Request process, the job loads all rules from all active effective dates including historical effective dates for 'USER' assignment.
This should only look at the most current effective dated row and no history regardless of the Active/Inactive status.

Note:  The ChartField Security by Role works as expected, but not for ChartField Security by User.

Steps to replicate:

Test 1:

1.  Set up Chartfield Security by User ID (Set Up Financials/Supply Chain > Security > Chartfield Security > Secure Chartfield Options)
       Security Method = User ID
       Secure Fields = Department     
       Products (Source ID):
           Payables (FAP)
           Treasury (FTR)
           eProcurement (DPV)

2.  Set up Chartfield Security Rules (Set Up Financials/Supply Chain > Security > Chartfield Security > Maintain Security Rules > Define Security Rules)
       Security Rule = TSTDEPT (for DeptID 61000)
       Security Rule = TSTDEPT1 (for DeptID 62000)

3.  Assign Security Rule to User ID (Set Up Financials/Supply Chain > Security > Chartfield Security > Maintain Security Rules > Assign Role to User ID)
      For User ID = GLA2
      Effective Date = 01/01/2020
      Field Name = Department
      Security Rule = TSTDEPT

4.  Then, click ‘Build’ button.

5.  For now, select Process Type = Delete, As of Date (current date), and Field Name (Department).

6.  Query PS_SEC_DEPT_USER for User ID = GLA2. 
     Result: 0 fetched rows as expected.

7.  Re-build by clicking ‘Build’ button.
     This time, select Process Type = Build, As of Date (current date), and Field Name (Department).

8.  Query PS_SEC_DEPT_USER for User ID = GLA2.
     Result: 6 fetched rows as expected (2 each for different Source ID).

9.  Add a new effective dated row in Security Rule to User ID.
        For User ID = GLA2
        Effective Date = 01/01/2021
        Field Name = Department
        Security Rule = TSTDEPT1

    Note that both rows are in Active status.

10.  Then, click ‘Build’ button.
       For now, select Process Type = Delete, As of Date (current date), and Field Name (Department).

11.  Query PS_SEC_DEPT_USER for User ID = GLA2.  
       Result: 0 fetched rows as expected.

12.  Click ‘Build’ button.
      Select Process Type = Delete, As of Date (current date), and Field Name (Department).

13.  Query PS_SEC_DEPT_USER for User ID = GLA2.  
       Result: 9 fetched rows come up. This is not the expected behavior. (the rows highlighted, which were for earlier effective date still show).

       Note that CF Security by User ID does not work similar to CF Security by Role or Permission List.

Test 2:

1.  Set up Chartfield Security by Role:
2.  Security Method = RoleSecure Fields = Department
        Products (Source ID):
        Payables (FAP)
        Treasury (FTR)
         eProcurement (DPV)

2.  Set up Chartfield Security Rules:
        Security Rule = TSTDEPT2 (for DeptID 63000)
        Security Rule = TSTDEPT3 (for DeptID 64000)

3.  Assign Security Rule to Role:
        For Rolename = ANALYST
        Effective Date = 02/01/2020
        Field Name = Department
         Security Rule = TSTDEPT2

4.  Then, click ‘Build’ button.

5.  Select Process Type = Delete, As of Date (current date), and Field Name (Department).

6.  Query PS_SEC_DEPT_ROLE for Rolename = ANALYST.
     Result: 0 fetched rows as expected.

7.  Click ‘Build’ button again.
     Select Process Type = Build, As of Date (current date), and Field Name (Department).

8.  Query PS_SEC_DEPT_ROLE for Rolename = ANALYST.
     Result: 6 fetched rows as expected.

9.  Add a new effective dated row in Security Rule to the Role.
       For Role = ANALYST
       Effective Date = 02/01/2021
       Field Name = Department
       Security Rule = TSTDEPT3

      Note that both rows are in Active status.

10.  Click ‘Build’ button.
       For now, select Process Type = Delete, As of Date (current date), and Field Name (Department).

12.  Query PS_SEC_DEPT_ROLE for Rolename = ANALYST.
       Result: 0 fetched rows as expected.

13.  Click ‘Build’ button again.
        For now, select Process Type = Build, As of Date (current date), and Field Name (Department).

14. Query PS_SEC_DEPT_ROLE for Rolename = ANALYST.
      Result: 6 fetched rows as expected.

Detailed replication here.

Cause

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