Fine Grained Access Control for PeopleSoft database – I

Today, I will demonstrate use of Fine Grained Access Control to create row-level security for the database to mimic the row level security setup in the PeopleSoft application. Let me start with asking a few questions.

In your PeopleSoft database, do you have Oracle ids created for the PeopleSoft users? Most of the PeopleSoft shops create Oracle ids for Functional support teams, so that they can query the database when they are troubleshooting some issues.

If you answered ‘yes’ to the above question then what kind of access has been given to these ids? Most of the PeopleSoft shops will provide the Oracle ids SELECT access to all access id owned tables. Is this how you have setup your ids? This basically allows the Oracle id to bypass the security setup in PeopleSoft and be able to view all the data in the table that the user has access

Won’t you love to have online query row level security available in the database such that it will only retrieve the rowsets that the user should have access?

Providing anyone with SELECT privileges to all PeopleSoft application or tools tables is not recommended. You might want to consider identifying sensitive datasets or tables and implement FGAC policies on them. The below scenario demonstrates the need to have this feature in the database.

1.In this example, I have identified PS_PERSONAL_DATA as the table on which I want to replicate the online security in the database

2.As shown below, the Query Security Record for PS_PERSONAL_DATA is PERS_SRCH_QRY

3.Now, lets try to create a query in Query Manager and check out the SQL generated by PeopleSoft

4.  The Query Manager has automatically added a join with PS_PERS_SRCH_QRY and an additional filter by OPRID.

5.   If I run this query then it fetches 1901 rows

6.  Now, I logon to Oracle database as PS and query PS_PERSONAL_DATA. And below are the results

7.  As we knew, PS has access to all the rows in PS_PERSONAL_DATA when queried from the database.

In my next post, I will share steps to implement the Fine Grained Access Control feature.