PeopleSoft Security

Oh! A long break from blogging hasn't put me out of PeopleSoft - A technology I've come to love with each passing day. In this post I would discuss the recent security upgrade that I did - The world is as competitive as ever and so most people believe in keeping their smartness sealed, I'm certain that the shortcuts that I'm about to mention would have been tried and implemented by many of us in the industry, but I would do my best to reveal some of these shortcuts to upgrade/implement security.

The client had an existing 8.3 PeopleSoft system and wished to upgrade to 8.9 PeopleSoft. When I was involved in the initial project proposal I quoted 2 weeks for a technical security upgrade with one resource - Would certainly agree that I did not have any clue at that time about security, just that I knew that it could be done. Many a times I've seen people struggling with a Security Matrix for month, working online and setting up security and still having issues. At the end of my task the number of issues that I had on security were 5-7.

Here's how this could be done.

The customer that I was working with had all custom roles and permissions. I started off by getting a the results of the following SQL from 8.3 onto an excel. ( Sheet name: "83 Perm List Component")


select distinct a.classid, a.displayonly, a.authorizedactions, C.pnlgrpname, c.market
from psauthitem a, PSMENUITEM b, pspnlgroup C
where b.menuname = a.menuname and b.barname = a.barname
and b.itemname = a.baritemname and c.pnlgrpname = b.pnlgrpname
and c.market = b.market and c.itemname = a.pnlitemname

The results went onto an excel. Then the results of the below SQL from 8.9 PeopleSoft were saved onto a different sheet ("Original New Navg 4 Existn Cmpn") in the same excel workbook.
I'm not 100% sure about the below SQL, I just wrote it and did not execute the one below, nut it is something similiar.

SELECT A.MENUNAME, A.BARNAME, A.BARITEMNAME, B.ITEMNAME, A.PNLGRPNAME, A.MARKET
FROM PSMENUITEM A, PSPNLGROUP B WHERE A.PNLGRPNAME IN (<83>)
AND B.PLNGRPNAME = A.PNLGRPNAME AND B.MARKET = A.MARKET

Now click the command button labeled "Build SQL". On the sheet titled "83 Perm List Component". The insert statements will be generated in the Insert SQL sheets.

There would need to be some clean-up which would normally be based on MARKET and MENUNAME.

Take a back-up of PSAUTHITEM from 8.9. Create a PS_TMP_AUTHITEM table with same structure of PSAUTHITEM and with no Keys. Use the SQLs in the INSERT statement to load data to TMP_AUTHITEM. Delete and analyze the duplicate rows from TMP_AUTHITEM based on AUTHITEM key - Duplicates arise as we pulled Authorizations in 8.3 based on component and different pages in a single component will have different display only/access property. Dump the clean data to PSAUTHITEM, after you have purged all the custom roles in PSAUTHITEM.

I initially had the intent of minimizing the data analysis work required and hence preferred the PNLITEMNAME from 8.3 and tried matching it with 8.9, this is when I figured PeopleSoft likes changing the Page Names but will retain component names between releases. Don't pounce on me now, I was also responsible for 8.9 Talent acquisition manager and candidate gateway design and implementation - I know how much has changed with this module in terms of component names. Still, security implementation was done with sql scripts alone, the above SQLs must give you some pointers. Remember to run Portal Sync after the mass update to PSAUTHITEM.

Security doesn't end here, we still need to be cautious of the Web Libraries, Query trees, Row level security and Department trees. There we did it, Security Upgrade two weeks one resource. I've really enjoyed working on Security, Talent Acquisition Manager and Candidate Gateway. Doing something different in every project makes me feel good, must be the same for everyone out there.

Finally, here is the link for the excel file which would generate the Insert SQLs for PSAUTHITEM.

http://www.fileul.com/view.php?file=33SoQN

The above link is for an excel workbook that I saved from the original using Office 2007. If there are problems accessing this version, let me know.