Demystifying encryption

An HR application contains lots of sensible data. This data often crystallize into pdf reports or office docs, and eventually they’ll get sent to remote systems. In this case you’ll probably use a VPN, or in the absence of it an sFTP port. Although this used to be enough, nowadays the security mantra is that you’re never too secure. In this post I try to explain how I set up a second layer of security.

Encryption is old, yet for people that have not used it before, may be synonymous of muddy waters. Leave it for the security administrator. Well, nowadays there are a few tools that makes the process really simple. I chose GnuPG since it is a free implementation of the openPGP standard, it can run in all platforms and is compatible with the proprietary software Symantec PGP, very expensive and widely used in big companies.

The idea is to encrypt the file on your server, and send it through your secure channel to the external target system.

The check list:

– Source and target systems will need to have an encryption software installed.

– A GnuPG standard installation is pretty straightforward. Follow the instructions and you’re done. You’ll end up with two keys, a private key and a public key. (If you’re in a windows platform use gpg4win which is an adaptation of GnuPG).

– In order to be able to decrypt your report, the target system will have to provide you with their public key. You will need to encrypt the files with it. Thus, they can decrypt it with their private key.

– Write a batch process that encrypts your file. There’s a lot of options you may want to use, but the basics come to this line:

%GNU_HOME%gpg2.exe –batch –yes -o %WORK_PATH%%FILE%.gpg -se –recipient target@publickey.com %WORK_PATH%%FILE%

Where %WORK_PATH% and %FILE% are the parameters for the batch
-o indicates the output file.
-se is for sign and encrypt.
and –recipient indicates that the file will be encrypted using the target’s public key, so only them could decrypt it.

If you need to tweak this, you’ll find more detailed information in the GnuPGP site.

– Call this batch process from peoplecode, using the Exec function for instance. Here you may pass the filename in case it changes per execution, which probably does.

&cmd = “cmd.exe /c \MyEncrypt.bat ” | &FileName;
&return = Exec(&cmd, %Exec_Synchronous + %FilePath_Absolute);

Finally I created two functions, one for encrypt and the other for decrypt (i.e. if the target sends a file back) placed them in a fieldformula event. The function accepts two parameters; the work directory and filename. This way I can reuse the functionality across the application.

A nice side effect of using encrypted files is that the encryption algorithm will do some file compression, so there are extra points for saving bandwidth and storage.

Trucos Peoplesoft

EXECuting scripts on the server with PeopleCode

This one is just a peoplecode curiosity.

I was having problems using the putattachment/addattachment functions to move files from one server to the other from an Application Engine. Probably I should have tried harder because I know it should work. However, I had a blurry day and decided to go a different path. I googled and found this post  (thanks John!)

And now I kind of prefer it.

Using the Exec function is possible to send commands to DOS command prompt or Unix Shell. The advantage for me is that I have more control on what’s going on in the server because I am more familiar with shell/cmd.

Here it is the piece of code that worked for me: short, easy.

/* create a new file where I write the script to be executed */
&MyScript = GetFile(“E:\Repo\scripts\FTPTransfer.bat”, “W”, %FilePath_Absolute); /* work folder of my choice */

&MyScript.writeline(“@echo off”);
&MyScript.writeline(“echo user ” | &User | “> ftpcmd.dat”);
&MyScript.writeline(“echo ” | &Pwd | “>> ftpcmd.dat”);
For &u = 1 To &RemoteFolders.Len
   &MyScript.writeline(“echo cd ” | &RemoteFolders [&u] | “>> ftpcmd.dat”);
End-For;
&MyScript.writeline(“echo bin>> ftpcmd.dat”);
&MyScript.writeline(“echo put E:\Repo\scripts\” | &FileName | “>> ftpcmd.dat”);
&MyScript.writeline(“echo quit>> ftpcmd.dat”);
&MyScript.writeline(“ftp -n -s:ftpcmd.dat ” | &Domain);
&MyScript.writeline(“del ftpcmd.dat”);
&MyScript.close();

/* executing the script */
&ExitCode = Exec(“‘E:\Repo\scripts\FTPTransfer.bat'”, %FilePath_Absolute);

/* evaluate the output */
If &ExitCode = – 1 Then
   MessageBox(0, “”, 0, 0, “File ” | &FileName | ” correctly transfered.”);
Else
   MessageBox(0, “”, 0, 0, “File ” | &FileName | ” NOT transfered. View details on PeopleTools > Utilities > Administration > URLs Definition > – – – Error Code: ” | &ExitCode | “.”);
End-If;

First I create a file where I write the script with dynamic values (conectivity, target folder location, and whatever I feel convinient), The script created is executed then in this line:

&ExitCode = Exec(“‘E:\Repositorio\scripts\FTPTransfer.bat'”, %FilePath_Absolute);

Where E:\Repo\scripts\ is a work folder of my choice. &User, &Pwd and &Domain refer to the ftp conection details. &RemoteFolders [&u] is an array where I stored the folder hierarchy on the ftp server side.

So, from now on, I will be using this method when I can. A disadvantage thou is that this code will be Server OS specific. As far as you’re aware of it there should be no problem.

Trucos Peoplesoft

Peoplesoft Object Type Reference

Peoplesoft Object Type Reference

This numeric field can be found in PSPROJECTITEM.OBJECTTYPE for instance.

0 – Record
1 – Indexes
2 – Fields
4 – Translated Value
5 – Page
6 – Menu
7 – Component
8 – Record Peoplecode
10 – Queries
11 – Tree Structure
17 – Business Process
18 – Activities
20 – Process Definition
21 – Server Definition
22 – Process Type Definition
23 – Job Definition
24 – Recurrence Definition
30 – SQL
31 – File Layout
32 – Component Interface
33 – Application Engine
34 – AE Section
35 – Message Node
36 – Message Channel
40 – Subscription Peoplecode
43 – AE Step Peoplecode
44 – Page Peoplecode
46 – Component Peoplecode
47 – Component Record Peoplecode
48 – Component Rec Fld Peoplecode
49 – Image
51 – HTML
53 – Permission Lists
55 – Portal Registry Structures
63 – Portal Registry User Favorites

Now, I’d like to extract peoplecode from the database. I know it’s stored in PSPCMPROG (and PSPCMNAME) in binary form. There has to be a tool or script that does the job, maybe in Perl, let me see…

BLOB for Binary Large Object… cause it can store more than 4000 chars

Also, Sql statements can be found here PSSQLTEXTDEFN.

PD: Never thought peoplecode was stored in such a complex way… see Vijay’s study (actually, seems that at the time of Vijay’s study, Peoplesoft 7 was around, and PSPCMPROG.PROGTXT was a Char Field).

Trucos Peoplesoft

Workflow PeopleBook Reference

A remainder for myself, from HRMS 8.9 peoplebooks

User ID	Select a system default user.
Worklists Active and Email Active	Specify which types of routings are active: Worklists Active: Enables worklists for your users. Email Active: Enables the system to send email to workflow users. Your application server Simple Mail Transfer Protocol (SMTP) system needs to be configured prior to sending email notifications.
HR Installed (human resources installed)	Specify whether you’re using PeopleSoft HCM applications. The system uses this setting to determine a role user’s supervisor: If the check box is cleared, a Supervisory Role User field appears on the user’s profile. The system uses the user ID in this field when it must forward a work item to a user’s supervisor. If the check box is selected, the system determines a user’s supervisor as it does throughout PeopleSoft HRMS applications: from the user’s PERSONAL_DATA record.
Delete Pooled WL When Select	Select to delete pooled worklists.
Resubmit VA Worklist	The last field for this group box controls how Virtual Approver handles reassigned and resubmitted approval process.

 

Resubmit VA worklist

To help you understand the options within this group box, the following scenarios are provided, which assume the process is already at manager level.

Scenario	Description
A	A worklist item is reassigned to a user within the supervisor level. The previous behavior of the system is as follows: Upon approval, a message appears stating that the Worklist needs Manager Approval (if a message catalog is used in the Approval Rule Set). At this point, Virtual Approver will send the worklist to the manager again. The worklist will be dropped from the user’s list.
B	The worklist item is reassigned to a user with no approval authority. The previous behavior of the system is as follows. Upon approval, the system completely drops the worklist from the user’s list with no message shown.
C	The worklist is reassigned to a user within the president level, who can then approve the item.

Note. These three scenarios also apply for a user accessing the approval page directly, in which case the
worklist will not be dropped from the list.

Specify how the system should handle reassigned Virtual Approver worklist items.

Not allowed	In scenario A, the system presents an error notifying the user that this approval had been submitted before. The system will not allow the user to resubmit for approval. In scenario B, the system presents an error expressing the fact that the user to which the worklist is being assigned does not have the appropriate approval authority. In both scenario A and B, the transaction can’t be completed and saved.
Admin/Role User only (administrator/role user only)	Only an administrator or the user that has the appropriate role on one of the approval steps can save and resubmit worklists for approval
Yes for everyone	The system does not present error messages. All users can save and resubmit worklists for approval.

Trucos Peoplesoft

New Search Record and Row Level Security

Back to basics again. This applies at least to PeopleTools 8.46. I guess it is the same until 8.49.

I needed to build a new component with a special search. It was also necessary to apply the related row level security on it. After some struggle I found out how to include the current Operator ID into the search view.

I used the standard view PERS_SRCH_GBL and applied a filter on the OPRID field.

The sql behind my search view looks like this

SELECT e.oprid 
 , e.emplid 
 , a.zlogin 
 , c.descr 
 , e.FIRST_NAME 
 , e.LAST_NAME 
 , e.SECOND_LAST_SRCH 
  FROM PS_PERS_SRCH_GBL e 
  , ps_job a 
  , ps_dept_tbl c 
 WHERE e.emplid = a.emplid 
   AND A.empl_rcd = ( 
 SELECT MAX(a2.empl_rcd) 
  FROM ps_job a2 
 WHERE a.emplid = a2.emplid) 
   AND a.effdt = ( 
 SELECT MAX(a3.effdt) 
  FROM ps_job a3 
 WHERE a.emplid = a3.emplid 
   AND a.empl_rcd = a3.empl_rcd) 
   AND a.effseq = ( 
 SELECT MAX(a4.effseq) 
  FROM ps_job a4 
 WHERE a.emplid = a4.emplid 
   AND a.empl_rcd = a4.empl_rcd 
   AND a.effdt = a4.effdt) 
   AND a.deptid = c.deptid 
   AND c.effdt = ( 
 SELECT MAX(c2.effdt) 
  FROM ps_dept_tbl c2 
 WHERE c.setid = c2.setid 
   AND c.deptid = c2.deptid)

And here comes the critical part of it. This view will filter by current logged User ID (OPRID value) only if the OPRID field is set as a Key, and not as a Search Key nor as a List Box Item. So, If you set it as a key, and also as a List Box Item or as a Search Key/Alternate Search Key, it won’t work. At least in this PT version.

Other standard security views can be used, for instance: 

– PERS_SRCH_EMP is an Employment Search record containing  all of the localized alternate search keys.  None of the localized search keys are maked as alternate searches.  However, it’s easy for a customer to mark any/all of them as alternate search keys if they choose to.

– PERS_SRCH_GBL is used as a search view to select person records for you to use when you access a panel.  This view shows you records only for persons in departments that your operator class is allowed to access.  All PER_ORG Types are returned.

– EMPLMT_SRCH_ALL is an Employment Search record containing  all of the localized alternate search keys. This view does not use the Department Security Tree.  It is only used by CI updates (CI_JOB_DATA) where the security was already handled by the process calling the CI. This allows processes that aren’t using Dept Security (such as Self Service and Kenmu) to update the Job rows that they need to even if the user running the CI does not have dept security access.  

– PERS_SRCH_CURR is used as a search view to select personnel  for you to use when you access a panel.  This view shows you records only for people in departments that your operator class is allowed to access. Similiar to PERS_SRCH_GBL – but it does not bring back Future JOB rows.

I am not sure if this is the best way to apply it. I noticed it is not the way it’s done in the main Components (JOB_DATA, PERSONAL_DATA…), and I’d be happy if any of you can explain it to me.

Trucos Peoplesoft

Generate Random Passwords

In the page Create User you can create a new group of users based on a template profile. If you want to go further, add some code in the DERIVED_USERS.CREATE_USERS.FieldChange CreateNewUsers() function in order to generate a random password for each new user.

The key sentence is;

sqlexec(“SELECT DBMS_RANDOM.STRING(‘X’, 8) FROM PS_INSTALLATION”, &RandomPwd);

It is also possible to update the password after the user has been created, directly with SQL. Don’t forget to encrypt  them with data mover in bootstrap mode afterwards.

Trucos Peoplesoft

Reminder: Peoplecode Rowset Processing

If you’re working with rowsets in peoplecode and there is a need of processing rows in a loop, it is necessary to process rows from the highest to the lowest position. This ensures that the renumbering of the rows do not affect the loop.

Here’s how you code it:

&RS2 = GetRowset(scroll.MY_GRID);

For &I = &RS2.ActiveRowCount To 1 Step -1
 If None(&CHECK_SEQ) Then
    &RS2.DeleteRow(&I);
 End-If;
End-For;

I know it’s quite basic, though sometimes it’s good to remember the basics 😉

Trucos Peoplesoft

When the Manager is out…

Workflow Attributes

Alternate User ID

Select an alternate role user to receive routings sent to this role user. Use this option when the role user is temporarily out (for example, on vacation or on leave).

If the edit box contains a role user name, the system automatically forwards new work items for whoever is assigned as the current role user to the alternate role user.

Note. The system forwards new work items to the alternate role user. It doesn’t reassign items already in the user’s worklist.

Note. When applying an alternate user ID in your workflow settings, make note of the fact that the system only sends workflow routings to the immediate alternate user ID. The system does not send routings down multiple levels of alternate user IDs. For example, assume user A specifies user B as the alternate user ID while user A is out of the office. Also assume that user B happens to be out of the office at a time during user A’s absence, and user B specifies user C as an alternate user ID. In this case, the system does not send workflow routings originally intended for user A to user C.

Note. The Alternate User ID feature is only intended for and only works in conjunction with the Virtual Approver. For example, it does not work with worklists outside of Virtual Approver, or with TriggerBusinessEvent workflow, or with notifications.

From Date and To Date

Enter the date on which the current role user is going to begin and return from a temporary vacancy. This edit box specifies the time period that the alternate user ID is used.

Supervising User ID

Select the user ID of the user’s supervisor from this drop-down list box. The system uses this value when it needs to forward information to the user’s supervisor.

The system uses the PERSONAL_DATA record to determine the user’s supervisor.

Note. If you’re using PeopleSoft Human Capital Management (PeopleSoft HCM) applications, this field shouldn’t appear. If it does, you must set your workflow system defaults.

Routing Preferences

Specify which types of routings this role user can receive. The Routing Preferences box shows the two places where the system can deliver work items: to a worklist or to an email mailbox. If the user doesn’t have access to one or both of these places, clear the check box. For example, if this person isn’t a PeopleSoft user, clear Worklist User.

Reassign Work

Re-assign Work To

Use to reassign pending work for this role user if positions change or a user is temporarily out, such as on leave or on vacation.

If this user has work items waiting (as shown by the Total Pending Worklist Entries in your Workflow interface), select this check box and select the user to whom work items should be forwarded from the drop-down list box. When you save the page, the system reassigns existing worklist entries to the specified user.

Note. If you don’t reassign pending work items, they remain unprocessed.

Total Pending Worklist Entries

Displays worklist items that require a user’s attention.

Trucos Peoplesoft

Creating Security Trees Automatically

You can create a security tree using an existing organizational structure. Use the following Structured Query Report (SQR) procedure to import the existing hierarchy and build your security tree. You import your department data into a temporary Department Table, and the system uses that data to build the security tree.

To set up a hierarchy of departmental entities and build your data security tree automatically:

1. Import the entity data.

Import the entity data into the temporary table R_PER507 using the PeopleSoft Import utility, a Structured Query Report (SQR), or another batch facility. You load department data into this temporary table, so before you use this utility, you must establish the reporting hierarchy for all the departments in your organization. To do this, use the REPORTS_TO_DEPT field in the R_PER507 temporary table. 

R_PER507 is included with PeopleSoft HRMS; it looks like DEPT_TBL, but it includes the following additional columns:

• SETID_RPDEPT Specifies the setID of the department that a particular department reports to. In addition to the other Department Table data, you must load data into this column.
• REPORTS_TO_DEPT Specifies department that a particular department reports to. In addition to the other Department Table data, you must load data into this column.
• ORGCODEFLAG Indicates whether the department is selected for processing as of a particular date. The system populates this column based on your department data and the REPORTS_TO_DEPT field values.
• ORGCODE Designates the position of the department in the hierarchy. The system populates this column based on your department data and the REPORTS_TO_DEPT field values.
• TREE_LEVEL_NUM Temporary work column.
• PARENT_NODE_NUM Temporary work column.
• TREE_NODE_NUM Temporary work column.
• TREE_NODE_NUM_END Temporary work column.

2. Set up the reporting hierarchy.

Run PER507 to set up the reporting hierarchy of your tree. This utility determines whether a department is active or inactive as of the date that you enter when you run the utility, and populates the ORGFLAG column in R_PER507 accordingly. The utility creates a structured organization code based on the REPORTS_TO_DEPT field values that you loaded and populates ORGCODE accordingly. This utility uses the ORGCODE values to set up the department hierarchy.

Run the SQR using sqrw.exe

The output of PER507, it will populate the ORGCODE column based on REPORTS_TO_DEPT

3. Build the department security tree. 

Run PER508 to build your DEPT_SECURITY tree. The effective date of the tree is the latest effective date of the departments that were processed in step 2.

What if there are unrelated departments?

Once the R_PER507 is correct, the output will look like this

In the temporary R_PER507 you’ll see how it worked out the levels

4. Transfer department data into the department component.

Run PER509 to transfer the information that you set up in R_PER507 into DEPT_TBL. You can’t view or update the Department component until you run this utility.

5. Renumber and insert numbered gaps in the security tree.

Run PTUGAPTR.SQR to renumber the nodes in your tree and insert numbered gaps between the nodes.

Additional Actions:

You can modify an existing tree by changing either the nodes or the levels. When you modify a security tree, the tree node numbers usually change, so you need to refresh the numbers. You also need to run the Refresh SJT_CLASS_ALL process to update the data access profiles and security join tables.

PeopleTools assigns each node a number and reserves a series of unused numbers, called gaps, which the system uses to make changes to sections of a security tree. When you move a node, the system renumbers the nodes that appear to the right of the node that you moved (the children of the node that you moved). When you save changes to a tree, the system saves only the parts of the tree that have changed. To refresh the unused numbers in the gaps between nodes, run the PTUGAPTR.SQR utility. Refresh unused numbers when:

• You load your security tree structure.

• You modify your security tree.

• An error message tells you to gap your tree.

Trucos Peoplesoft

Running SQRs on the Client Workstation

It has been a while I don’t run an sqr process with the sqrw utility. Since I found myself struggling to find detailed information on the available paramaters for sqr in the net, I decided to paste this from the PT8.49_install_Oracle official pdf.

Next time I’ll know where to find it right away. Hope it helps.

To run an SQR on the client workstation:
1. Select Start, Run, click Browse, and navigate to \bin\sqr\ORA\binw.
Select sqrw.exe and click Open.

The following list summarizes the SQR report arguments used by PeopleSoft. (For a full listing of report
arguments, press the Help button to view the SQR help topic for this dialog box.)

Flag Description
-I Specifies the directories that SQR will search for the #INCLUDE files. (A trailing slash is required.)
-f Specifies the directory where the report output will be sent.
If you use the –keep flag, specify a directory with an ending slash.
If you use the –printer flag, specify a full pathname with a filename for the HTML file.
-ZIF Sets the full path and name of the SQR initialization file. The -ZIF flag
should point to your \sqr\pssqr.ini file.
-keep Keeps the .SPF file after the program runs. This enables you to view
the report with the SQR viewer.
-printer:ht Generates the output file in HTML format. Specify the filename, with
path location, with the –f flag.

[In this toast, c:\fsdmo is the PS_HOME.]

Trucos Peoplesoft