Implementing PS/nVision Ledger-Based Data Security

With PS/nVision row-level ledger security, you can restrict access to specified rows of ledger data. PS/nVision employs a view that joins an Authorization table (maintained by you through a page) with the Ledger table to select the rows a user is authorized to see. You can set up an Authorization table based on any ChartField.

When you define a ledger, you specify the physical table that stores the ledger data in the database. You can also define a record (view) for reporting purposes. If you define a reporting view, PS/nVision uses this record in place of the physical Ledger table. By joining the Ledger table with the Authorization table, the user only sees the rows that they’re authorized to see.

Image: PS/nVision security customization

This diagram illustrates the PS/nVision security customization.

The reporting view, like the Ledger table, is named on the Ledger template in the Secured Rptg Vw (Secured Reporting View) field.

To open the Ledger template, select General Ledgers > Ledgers > Templates > Record Definitions.

In the following example, this view (LED_RPTG_VW) is defined as:

SELECT A.OPRID, L.BUSINESS_UNIT, L.LEDGER, L.ACCOUNT,...
FROM PS_LEDGER L, PS_AUTH_TBL A
WHERE L.BUSINESS_UNIT = A.BUSINESS_UNIT
  AND L.LEDGER = A.LEDGER

Image: Record Definitions page

Example of the Record Definitions page that displays the Ledger Template and the Secured Rptg Vw field.

Normally, the view includes all the columns in the Ledger table, plus the OPRID field, but you are not required to include all ledger columns. The OPRID field is defined as a key to the view. Reporting views can be defined differently for different ledgers. The OPRCLASS or ROWSECCCLASS fields, or any combination of the OPRID, OPRCLASS, and ROWSECCLASS fields can be used instead of the OPRID field. In the user profile found in PeopleSoft Security, you can use the OPRID field as the user ID, the OPRCLASS field as the primary permission list, and the ROWSECCLASS field as the row-level security permission list for the user.

With this example view, only users that belong to the specified OPRID, OPRCLASS, or ROWSECCLASS from the LED_AUTH_TBL can see those business units and ledgers.

The application provides a template Authorization table and Reporting view. By changing the definition of the view and the underlying Authorization table, you can revise the security to be at the department level or any other ChartField. The personalized view can use BETWEEN, OR, and LIKE statements to implement more flexible (though less efficient) security views.

For example, you could add DEPT_FROM and DEPT_TO to the Authorization table. Each user would have access to all departments that fall within the ranges for their user ID and role. The Reporting view could include:

WHERE ... L.DEPTID BETWEEN A.DEPT_FROM AND A.DEPT_TO

This setting might carry a performance cost on some database platforms, but it can ease the process of maintaining the Authorization table, all transparent to PS/nVision.

Changes to the view are made using Application Designer. Changes to authorizations are made through a page that maintains authorized ChartField values for each user. No changes are required to PS/nVision.

To ignore security on a ledger, leave the Secured Rprt Vw (Secured Reporting View) field on the Ledger Definition page blank. PS/nVision will access the base ledger record.

The Authorization table is maintained by a page that can be personalized. Each valid combination of user ID or role and the secured ChartField values (or ranges) must be defined in this table. Since the system administrator who has access to this page (through the Administer Security pages) can change all security provisions, trickle-down authority does not occur.

To get data from a ledger, PS/nVision uses a Select statement from the Reporting view, not the actual Ledger table, using the user’s ID or primary permission list as part of the Where clause. PS/nVision checks the record definition of the Reporting view and determines whether it includes OPRID, OPRCLASS, or ROWSECCLASS in its keys, and generates the appropriate phrase in its Where clause. Individual users are not given direct database access, for example, using a GRANT to either the Ledger table or the Reporting view. (Using GRANT is specific to Oracle.)

This example shows Select of ledger data as used by PS/nVision, in which both OPRID and OPRCLASS are specified in the Authorization table:

SELECT ACCOUNT, SUM(POSTED_TOTAL_AMT)
 FROM PS_LED_RPTG_VW
WHERE BUSINESS_UNIT = 'NEWGN'
   AND LEDGER = 'ACTUALS'
   AND FISCAL_YEAR = 1993
   AND ACCOUNTING_PERIOD BETWEEN 9 AND 12
   AND ACCOUNT IN ('800100','800200','800300')
   AND DEPTID = '0300'
   AND OPRID = 'GLUSER' and OPRCLASS = ‘MANAGER’ 
GROUP BY ACCOUNT

Use the nVision Ledger Security page in PeopleSoft Security to restrict what ledgers a user is authorized to access.

Image: nVision Ledger Security page

This example illustrates the fields and controls on the nVision Ledger Security page.