Switching File Attachment Storage

In previous posts I have talked about the File Attachment storage options in PeopleSoft. The three basic options are Database, FTP or HTTP. My organization initially went with HTTP, but now we are looking to move to Database storage. The requirement is to move not only future file attachments, but attachments from the past as well. At first I thought this would require a lot of work, including custom conversion AE programs, etc. However, there is a delivered process called Copy File Attachements that does all the heavy lifting for you. There is an online and batch method to run this, but I highly recommend the batch mode. There are a few other steps needed to fully convert all attachments, but it is fairly straight forward. Below are the steps I used in FSCM 9.2.17, PeopleTools 8.54.18.

Create new File Attachment Server

  1. Navigate to Main Menu > Set Up Financials/Supply Chain > Common Defictions > File Attachments > Administer File Attachments
  2. Click Add Database Server
  3. Record Name will default to PV_ATT_DB_SRV. The default is fine, but change if you would like.
  4. Set Pick Active Server to match the ID of your new server.
  5. Save. Future File Attachments will now be stored in your database.

Copy File Attachments

  1. If needed, create new URL for Database storage – PeopleTools > Utilities > Administration > URLs. Values URL Identifier: PSA_ATT_DB, URLID:record://PV_ATT_DB_SRV
  2. Navigate to run control page PeopleTools > Utilities > Administration > Administer File Processing > Copy File Attachments (Batch)
  3. Values Source: URL.PSA_ATT_HTTP, Destination:URL.PSA_ATT_DB
  4. Run the App Engine COPYATTS through Process Scheduler.
  5. This will take a long time to run, possibly hours depending on your attachment count.
  6. After completion, you will want to review the trace file AE_COPYATTS_[PIN].trc file. The AE should produce this automatically.
  7. I used this trick to generate a log of ONLY errors from the trace:

    grep "EvalCopyAttachments: failed getting file" AE_COPYATTS_*.trc > AE_COPYATTS.errors

  8. Take action on any errors that occurred.

Update previous File Attachment URL

  1. These steps point the old HTTP server setup to point to the new DB server.
  2. Navigate to PeopleTools > Utilities > Administration > URLs. Values URL Identifier
  3. Open the URL used for the previous File Attachment Server setup. Example: PSA_ATT_HTTP
  4. Change the URLID value to the new Database server URL values. Example: record://PV_ATT_DB_SRV
  5. I recommend adding comments describing this conversion process – especially if HTTP or FTP is in the URL ID and it now points to the DB. This can help avoid confusion in the future.

After we completed these steps, we decided to keep our old storage location around just in case we found any issues after the fact. We ended up renaming the directory path, just to make sure nothing was still referencing the old location. After a few weeks of no issues, we went ahead and destroyed this old storage location. As mentioned above, this was done in the FSCM application which has its own File Attachment framework built on top of the one Tools delivers. You should be able to take a similar approach with other applications, but the Create new File Attachment Server section above won’t be relevant. Instead, you can simply complete the Update previous File Attachment URL steps after your copy is complete.

PUM Dashboard Queries and Backlog Report

Hopefully everyone has had a chance to play around with the new PUM Dashboard delivered in the 8.55 PIs. If not, Logesh from leanITdesigns has a very good write up on it. Dan and I also spend some time discussing it in Podcast #20 – PUM Dashboard. It is basically a new one stop shop for managing the PUM maintenance process. It uses a Fluid dashboard to help keep track of BUGs, customizations and test cases.

I have found the dashboard to be a very nice tool with a lot of promise. That said, I don’t think it is mature enough to really work for ALL your PUM maintenance planning needs. In the near future I can see both Oracle(tweaking the dashboard) and organizations(tweaking their maintenance planning processes) working together to make this dashboard truly useful.

Even before this dashboard, one thing I have been doing for our organization is providing a spreadsheet report that lists all the BUGs we have not yet applied. In theory, this can now be replaced by the dashboard. However, our group is pretty used to this spreadsheet and it gives them a little more personalized control of this data. As the dashboard improves, I’m hoping this report can go away and everyone can just be directed to the dashboard.

Queries

In the past, to generate this report I had to create a “Get Current” change package in PUM. This would of course list all the unapplied BUGs in a grid online. I would export the grid to excel, then copy and paste into the report template. This worked fine, but now there is a better way – leveraging the PUM Dashboard queries.

The PUM Dashboard drives off of a set of queries. These queries all have a prefix of PTIA and can be found under the normal Query Viewer or Manager component in your PI. Here is a list of a few of them:

  • PTIA_BUG_TARGET_DETAIL
  • PTIA_BUG_IMAGE_DETAIL
  • PTIA_BUG_PRODUCT_DETAIL
  • PTIA_STATUS_BY_IMAGE
  • PTIA_STATUS_BY_PROD

I found the PTIA_BUG_TARGET_DETAIL to be the most useful. This query was basically the same output I used to get with my change package grid export. One thing I did add to this detail was a link directly to the BUG Matrix in My Oracle Support(https://support.oracle.com/epmos/faces/BugMatrix?id=YOUR_BUG_ID_HERE). Our group has found this very useful when researching a BUG and wanting a little more detail than what is listed in PUM.

PUM Backlog Report

I have packaged up my spreadsheet report and posted it to GitHub, in case anyone else is interested. Since this report shows all unapplied BUGs from PUM, I have titled it the PUM Backlog Report. The instructions on how to import your PUM data is included in the readme file on GitHub, as well as in the spreadsheet itself. If you have questions or ideas for improvement, feel free to open an issue on GitHub or post in the comments below.

You can find the report on the psadmin.io GitHub site HERE.

pum_backlog_detail

pum_backlog_chart

8.55 – Build PeopleSoft Images with DPKs (Part 3)

As we have discussed in part 1 and part 2 of this discussion, Oracle is now shipping PeopleSoft Images with 8.55 and Deployment Kits(DPKs). Dan and I have talked quite a bit about our experiences using these DPKs with VirtualBox and NativeOS installments on Windows, so naturally Linux is up next. This is the OS that I spend most of my time in, so I have been excited to give it a try.

To get started, I took another read through the PeopleSoft Deployment Packages for Update Images Installation guide. Again, this can be found under Installation Documentation on the PUM Home Page. In this document it clearly states that Oracle Linux is supported for this installation. I normally don’t run Oracle Linux, so I was curious if it would work on other flavors. I gave it a try on both SuSE and Ubuntu without success. The bootstrap script basically fails right away, and I didn’t dig any further. So, I spun up a fresh lab install of Oracle Linux 7.2 and used that instead.

As with the other styles of DPK, the first step after you download the Linux .zip files is to extract the first file. Once extracted, you will find a setup directory which contains the bootstrap script psft-dpk-setup.sh. Before running this script, you will need to enable execution by running sudo chmod +x psft-dpk-setup.sh. After that, execute the bootstrap script and you are off and running.

I ran into a few issues with the installation, all related to dependencies(Update: More info here.). I ended up having to install all these packages to get past the issues:

sudo yum install libc.so.6 libgcc_s.so.1 libselinux.so.1 libxml2.so.2 libcrypto.so.10 libdb-5.3.so libffi.so.6 libgdbm.so.4 libncurses.so.5 libncursesw.so.5 libreadline.so.6 libssl.so.10 libtinfo.so.5 ruby rubygem oracle-rdbms-server-11gR2-preinstall

After getting these installed, it was time to give it another try. Running the bootstrap script again was not needed for this. Instead, I simply ran puppet apply site.pp which needed to be executed from the /etc/puppet/manifests directory. This time everything ran to success.

I chose the default initialization process, but you may want to make a few changes in your deployment. The changes you are most likely to make are related to security. By default the DPK will create 4 local user accounts: psadm1, psadm2, psadm3 and oracle2. This may not fit in with your security polices, so changing this could be crucial. In the installation guide, search for Task 6-1, which will walk you through the changes needed in your psft_customization.yaml file. If you do choose the defaults, then take a look at Task 7-1. This will cover all the default installation directories, as well as the default users and how they are used.

As always, if you ran into any other issues or have other observations related to the Linux NativeOS install, please let us know about it in the comments below!

Update: If you want to learn more about the DPK, check out our new PeopleSoft DPK QuickStart course. This free course will introduce you to the DPK, show you how to use the DPK with PeopleSoft Images, and show you how to customize the DPK for your servers.

 

Safely Using File Attachments

Allowing users to upload file attachments to multiple types of transactions within PeopleSoft is a very important PeopleTools feature. Adding files like resumes, expense receipts and other types of documents can be a huge win for organizations looking to move away from paper and fully leverage their ERP system. That being said, giving users the option to upload a file into your system has risks. There are plenty of people out there who would love to abuse this functionality.  You could be leaving your systems vulnerable to nasty viruses and malware.  Also, if not properly secured, these repositories of documents could be a data gold mine for people with malicious intent. Thankfully, PeopleTools does give us a few options to protect ourselves from these dangers. These are just a few considerations for safely setting up file attachments.

File Extension List

When we allow a user to upload a file related to a PeopleSoft transaction, we have a general idea what type of file to expect. Common file types would be images, Word documents or PDFs. Something like a .exe or .zip file is something that we would not expect, and should generally not allow. Well, good news! PeopleTools lets us specify what file types are allowed. By utilizing File Extension Lists, we can specify a list of extension types to accept or reject. These lists can then be associated with a URL by specifying the FILE_EXT_LIST property, using the URL Maintenance Page. When the file attachment architecture uses a URL for storage, it will look for this File Extension List and preform validation before a user is allowed to attach a file.

Virus Scanning

Limiting the file extensions of uploads is a good first step, but it really doesn’t get you very far. To really safe guard yourself from malicious file uploads, you need to be actively scanning all files for viruses. PeopleTools delivers a nice hook in the file attachment architecture to accomplish this. Buried in the PIA_HOME you will find the VirusScan.xml file. This can be used to configure virus scanning. Once this is done, all file attachment uploads will be scanned for viruses. If any are found, it will prevent the file from being saved on your system. Oracle doesn’t give us a lot of details on what exact virus scanning software is supported. However, it was built to work with ICAP supported scanners. So, if you own a virus scanner that works with ICAP then you are most likely in luck. There is also some level of configuration in the xml file to deal with different response codes.

Storage Locations

There are three delivered storage locations for file attachments in PeopleTools. All three options have their own pros and cons, so you will have to decide which to use depending on your needs. There will be a few things to consider with each approach to make sure each protocol is used safely.

Database

This is probably the most secure option. Generally the database is already very locked down, since the majority of other application data is stored there. No extra controls are really needed to make sure your files are safe. To be truly safe though, you really want to make sure this data is encrypted in flight and at rest. So if you do your due diligence with the security of your app as a whole, storing with this method takes care of itself.

FTP

This protocol has been around forever, but really has fallen out of favor in most organizations. You should under no circumstances be using plain old unencrypted FTP. PeopleTools does however support both FTPS and SFTP, a more secure option. To use these protocols you must use URL objects, not URL strings, in your PeopleCode. Take care to secure the attachments on the file system the FTP server is pointed to.

HTTP

The final storage option is HTTP. Similar to a Report Repository, this method leverages a PIA servlet(psfiletransfer) to handle the file storage. This setup is explained very well in

MOS Doc ID 1321581.1.  Similar to FTP, you should be using the secure option – HTTPS. To leverage this secure option you will need to make sure certificates are in place and your URL object is configured correctly. Review the MOS Doc ID 1408853.1 for further details. And again, make sure the attachments are secure in the file system.

8.55 – Build PeopleSoft Images with DPKs (Part 2)

As you have likely heard – and if you haven’t, go see Dan’s post now– the first 8.55 PI has been released. It is a big change from how 8.53 and 8.54 PIs were delivered to us in the past. Oracle is now leveraging DPKs to get us our images. As Dan had mentioned in his previous post, there are two options with this new delivery method: VirtualBox and Native OS. Now, you may see VirtualBox and assume you will still be downloading zip files and then running good old ova_gen.bat to generate a PI .ova file. This is actually NOT the case anymore. The new ‘old’ way is still run in VirtualBox but delivered via DPK. Here is a basic run down of what happens now:

  1. Download the Update Image VirtualBox zip files.
  2. Extract the first zip file, which should produce a setup folder.
  3. Run the `psft-dpk-setup.ps1` script via PowerShell. Note: I had to adjust my Execution Policy to get this to work.
  4. The rest of the zip files will be extracted.
  5. A bunch of validation happens related to VirtualBox.
  6. If validated, a shell VM will be imported into your VirtualBox via a delivered .ova file.
  7. You will be asked some networking questions.
  8. The script will launch the shell VM and finish.
  9. The shell will now be running and prompt you with a few more questions.
  10. At this point the DPK files are actually imported into the shell VM.
  11. You are prompted with a few options to customize your deployment.
  12. The DPKs are deployed and the PI is up and running!

That was pretty high level, but I wanted to point out that the VirtualBox method is using DPKs – similar to the Native OS option. So again, don’t expect to find a ova_gen.bat file and full .ova file to drop into VirtualBox. There will be a slight learning curve with the new DPK method, even if you stick with VirtualBox.

Luckily, Oracle does a pretty good job going into detail in the DPK for Update Images installation guide. To find this guide in MOS, navigate to the PUM Home Page and then your Update Image Home Page. Under the Installation Documentation section, you should find a link to the document – PeopleSoft Deployment Packages for Update Images Installation (PeopleSoft PeopleTools 8.55). This is a fairly lengthy document, walking you through all the different use cases and requirements for installation. It also covers the PeopleTools Client DPKs, which we will be covering in a Part 3 future post coming soon.

As always, feel free to join the conversation in the comments below. I would be interested to hear how others are fairing with this new process so far, and how it may change the way they currently operate.

Disabling the App Engine Server

I was having a discussion with another admin recently, and he questioned why we were running our process schedulers with Application Engine Server(PSAESRV) enabled. He pointed out that there is very little gained by running PSAESRV, so why have it turned on?

Now, you aren’t completely without loss in turning this off. Depending on what applications you are running and what your system’s needs are, you will have to make that decision for yourself. Check out Doc ID 651970.1 in MOS for a run down on the pros and cons for each method of running App Engines on your process schedulers.

 

Maintenance Page with Backdoor Login

A standard requirement when doing PeopleSoft maintenance is to change the normal sign in page. When the system is offline, we often like to display a maintenance page that informs users that the system will be down and prevents them from logging in. This is done easily enough by changing the signin.html page, adding a message and removing the login form.

However, there is one more requirement that is a little tricky. Once the maintenance is complete, there are often tasks that need to be completed before handing the system back to the end users. These can be configuration changes, running batch processes or simply completing validation that everything was applied correctly and is in working order. How can the core team sign on to the system and complete these tasks, all while preventing end user access?

I recently came up with a bit of JavaScript that gave us a backdoor to the system during our last PeopleTools upgrade. What the script does is hide the login form on the sign in page, preventing login.  The trick was that our core team knew the key to the backdoor. After the sign in page would load, when they pressed Ctrl+Space the login form would be reveled. The combination of keys was only known to the team, so they were the only ones able to get in. Keep in mind if you had some crafty end users this could obviously be worked around, but it did the trick for us.

The JavaScript and HTML to accomplish this is listed below.  Adding the script and HTML changes to your signin.html page is pretty straight forward. First include the file containing the JavaScript, or write it inline. You then need to wrap your login form elements in a <div> with an id=loginbox. I would suggest starting before the <div> containing ptLabelUserid and ending after the <div> containing the submit button. Lastly, you need to add hideLogin(true); to the body onload attribute.

Keep in mind the key doesn’t have to be Ctrl+Space, it can be any key combination really.You will see in the comments of the script a link to information about other keycodes that can be used.

Updated: 10/11/2016
Instead of hard coding a true value for your hideLogin() parameter, why not use a Custom Property set in the Web Profile? You can create any Custom Property you would like, for example: login.isLoginHidden and set to true. Then reference the property in your signin.hmtl page like this: hideLogin("<%=login.isLoginHidden%>"). This will allow you to toggle the hide login functionality by updating the web profile and bouncing the server.

Click here to see a working demo. Enjoy!


Disabling PS_TOKEN with PSEatCookies Filter

As many of you have probably heard, there has been much discussion this year regarding vulnerabilities in PeopleSoft’s PS_TOKEN. The talk all started after a presentation from ERPScan, which basically said that a PeopleSoft node’s password can be gained by brute force against a PS_TOKEN. This would allow someone to generate their own PS_TOKEN for any userid.

Now, Oracle plans to bump up it’s SHA-1 salted encryption up to SHA-2 with PeopleTools 8.55. However, it is probably a long ways out before most of us get to 8.55. And when we do get there, who’s to say how long this new encryption will be considered secure?  One option is to simply disable the PS_TOKEN, and therefore prevent this vulnerability altogether! The problem is, PeopleSoft does not give us the option to disable it.

I decided to come up with a proof of concept for a custom solution to this issue. I wrote a Java servlet filter, called PSEatCookies, that will prevent a PS_TOKEN, or any other Cookie you specify, from being added to the ServletHttpResponse. The basic structure and setup is very similar to the filter delivered in the Kerberos Desktop Single Signon solution. I have added the java source, classes and an example web.xml entry to a GitHub repository. You can download it and follow the instructions for setup in the readme file here: https://github.com/psadmin-io/ps-eat-cookies.

I can see this filter being handy when you have a web server located in your DMZ for a single PeopleSoft application. This way you can turn on this filter without impact to your internal users, who most likely would need their PS_TOKEN to jump between multiple applications. Otherwise, you would have to really build this out with special rules and logic, or purchase a third-party product that allows for more configuration.

If you find any problems, have ideas for enhancements or just have a question, feel free to open an issue on GitHub!

Note: This post originally appeared at kylebenson.com