The easiest and most secure way to expose your PeopleSoft data is by using SWS configured web services. This method gives the PeopleSoft admin control over what data is exposed to third parties and is a more secure model for most use cases.
A PeopleSoft power user:
Configures a new SWS configuration with either SQL or PsoftQL Syntax.
Defines parameters and filters
Grants security
Gives URL and Basic Auth Code to the integration partner
The integration partner ends up using a REST GET Service Operation called CHG_SWS. This web service checks security, resolves parameters, executes the SQL, encodes the data and returns it to the user.
Read more in the sections below.
1 - Configuration 🌟
How to Configure new SWS web services in the UI.
At the heart of SWS is a single PeopleSoft Service Operation called CHG_SWS_GET. This single service operation can serve infinite use cases. A PeopleSoft super-user who has read this documentation configures SWS to create new web services using a configuration page delivered as part of SWS. This configuration page lives inside your PeopleSoft database. It is the job of the CHG_SWS_GET service operation to interpret the configuration, enforce security, run SQL and export the results to the client.
This section discusses configuring this service in detail in this section as well as the security model and how to configure API client users.
Note
We assume that you understand REST and HTTP Concepts. If you do not, please read the What is HTTP and REST section.
There is a more advanced service operation for a more narrow user base. You probably do NOT need that but you can read about it here..
Planning a new web service
Before configuring a new web service there are a few things to think through. We will cover them here at a high level and then go into detail in the subsequent sections. New web services are trivial to set up with SWS. However, without a little upfront thought, you can end up with some confusion for the users of your APIs.
URLs and Paths
In REST web services, the URL paths have an implied meaning and provide a hierarchy that should map to some logical structure of the underlying data it exposes. This paradigm is carried forward into SWS. The URL path is used to find the logic to run to export data over the web services. So you need to think through the URL path structure and how it will map to the SQL or PsoftQL statements you want to expose.
Thinking about the path hierarchy gives the API clients a good structure to reason about. When reading the paths from left to right, they start out broad and narrow down the request. A URL Path structure could be something like this:
{prefix}/person/{emplid}
This output information about a person and could possibly include some child data like phones, addresses, etc.
{prefix}/person/{emplid}/phone
This would output phones for a person. You might give this out to a team that needs to know phone numbers for a person and are not interested in other data or have security to see that other data.
{prefix}/person/{emplid}/address/
This would output addresses for a person.
{prefix}/person/{emplid}/address/{address_type}}
This would output a specific address type for a person.
{prefix}/security/users/{oprid}/
This would output general information on a specific OPRID.
{prefix}/security/users/{oprid}/roles/
This would output roles a user was a member of.
In those examples above, they are fairly generic paths. You can also have more specific paths that are more targeted to a specific use case. Let’s imagine that you have an integration with your SSO system. You need to expose some data that they need. Their requests do NOT seem to fit into any other categories that you have configured before. Perhaps those other SWS configurations expose too much data or sensitive data the SSO team should not see. They have very limited information and that team cannot use the generic paths above because the data is too broad. So you can create a more specific path for them. Let’s imagine that they need to know information about a person as well as to know if a person is active or not. You could create a path like this:
{prefix}/sso/person/{emplid}/
This may return general information about a person like name, email, etc.
{prefix}/sso/person/{emplid}/status
This may return a simple yes/no if the person is active or not.
In the case above, we “scoped” the SSO end points to the /sso/* path. If you had some other integration partners with very specific requirements then you could create a scoped path as well like /acmecorp/*. You can create as many paths as you need. You can also create paths that are not scoped to a specific integration partner. You can create paths that are scoped to a specific business process. For example, you could create a path for all the data needed for a specific business process like expense reports or invoices.
SWS is flexible. It provides no guidance on how to structure your paths. You can create as many paths as you need.
In SWS, the Path string is used to look up a configuration that ends up running some SQL and exporting the results to the client. So you need to think through the path structure and how it will map to the SQL or PsoftQL statements you want to run. You also configure security to these paths. As we will see shortly, a path maps to a specific SWS configuration row in the database. That configuration row has a security grid that allows you to specify what permission lists are allowed to run that configuration.
Syntax Types
There are two syntax types that SWS supports. When you configure a new web service you must choose one.
SQL - You can configure a SQL statement to to run.
When an HTTP client is calling a specific SWS path, the code in the web service handler looks up the configuration for that path. The configuration has a syntax type. The handler will use that syntax type to determine how to export data to the client. There are pros and cons to each syntax type. Here is a quick summary:
SWS Configuration Types
SQL
PsoftQL
Accepts Client Parameters
Yes
Yes
Output JSON
Yes
Yes
Output XML
Yes
Yes
Output CSV
Yes
No
Nested Data parent/child data
No
Yes
Pagination
Yes (offset-based, via URL)
Yes (built-in, via request body)
Data Translation
Yes via SQL functions or Alias
No
Auto-Magic EFFDT Logic
None - Handled in SQL
EFFDT Logic automatically handled
Auto-Magic EFF_STATUS Logic
None - Handled in SQL
EFF_STATUS Logic automatically handled
Auto-Magic EFFSEQ Logic
None - Handled in SQL
EFFSEQ Logic automatically handled
Auto-Magic Field Exports
None - Handled in SQL
All record fields exported on table. No hard coding
Security
Security - Security is very important with PeopleSoft data as the database holds sensitive information. SWS is based on the PeopleSoft REST services. The only viable authentication mechanism for REST based services is “Basic Authentication” which is tied to a PeopleSoft OPRID and password stored in PSOPRDEFN. There is a very thorough document on REST Security in our Integration broker book. That should be your reference on how REST authentication works and the best practices. We assume you have read it and are following the best practices.
There is more explanation of security in the Security Page
Configuring a New Web Service
Configuring a new web service in SWS is easy and fast. You can deploy a new SQL or PsoftQL statement as a new web service in a few minutes. We will walk through all the configuration sections in this section. In later sections, we will show how to create different web services in order to give you some ideas of how this can be used.
Here is a screenshot of the SWS configuration page for a simple person example. Let’s go through each configuration at a high level. Then in later sections, we will drill into the detailed functionality offered.
Example SWS Configuration
In the example above we have configured the following PsoftQL statement to run at this path: /doc-example/person/{{emplid}}
Let’s decode this configuration. We are configuring a PsoftQL statement to run. It is asking for this structure.
PERSON
NAMES
EMAIL_ADDRESSES
SWS will return all email address types since there are no filters
PERSONAL_PHONE
SWS will return all phone types since there are no filters
ADDRESSES
SWS will return all effective dated and active addresses since there are no filters. This is SWS auto-magic handling of effective dates and EFF_STATUS
Additionally, we are asking that we exclude some fields from the PERSON record. We do not want to expose these fields to the client.
BIRTHDATE
BIRTHPLACE
BIRTHCOUNTRY
BIRTHSTATE
DT_OF_DEATH
We are also making this a single response for a single EMPLID. We have the {{emplid}} variable in the path. That will be substituted into the PsoftQL statement at run time.
For NAMES, we are limiting it to only return the PRI name type.
We are also asking for several description fields to be included in the output. This is a feature of PsoftQL.
This SWS configuration is limited to a single permission list SWS_DOC_ACCOUNT, which is a service account we setup for this example.
We can then call this web service using an HTTP Request like this:
GET https://127.0.0.1:8000/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/doc-example/person/FA0003
accept: application/json
authorization: Basic U1dTX0RPQ19BQ0NPVU5UOkRJUkdFLXNwZWxsaW5nLWphZGUtdml0aWF0ZQ==
Let’s cover each field on the SWS configuration page.
Unique Identifier: This is a system generated GUID that will be generated at save time. It is used to uniquely identify this configuration. You can not change this value and it should be safe to export to another database unlike a integer counter.
Description: This is the description of the configuration. This is for admin use and is used in PeopleSoft search records.
URL Path: This field is very important and will map to the full URL that a client will use to target your web service. This has to be unique across the database.
Active: Allows you to easily turn on or off the configuration.
Notes: This is a section for you to add notes about this web service. This might be internal notes or links to documentation or development tickets.
Request Format Type: PSOFT-QL or SQL - This defines what syntax SWS uses to query and return data.
If “Psoft-QL request format type was chosen then a long text box will show up called. Psoft-QL Text (JSON)
You enter the Psoft-QL that you want to run in response to a client request. This has a very particular format.
If “SQL” request format type was chosen then a long text box will show up called. SQL Statement Text
You enter the SQL that you want to run in response to a client request.
SQL Statement Text: This is the section where you enter the SQL statement that will run.
There is some special syntax for binding variables from the client request to the SQL statement. We will cover that detail in the next section.
Row Limit: Optional row limit. You can specify a non-zero number here and the handler will stop the output after that number of output rows has been encoded. Clients can override this at runtime by passing ?rowLimit=N on the URL, which also serves as the page size when the client paginates with ?pageNumber=N. See Pagination for SQL endpoints below.
Default Encoding: Used to set a default encoding scheme if the client does not include one in the request. All SWS web services automatically handle JSON, CSV and XML encoding.
Include metadata in Response: If checked then some additional information about the SWS request and response will be encoded. We will show some examples of this shortly.
(danger) Minutes to Cache Response: You can optionally configure to have the integration broker cache the response. This can be done for performance reasons if the SQL is very “expensive”. Please read the Caching article before enabling this. There are some hidden dangers.
Parameters Grid: This grid allows you to configure how the client passes parameters that are substituted into the SQL. If your SQL does not have any variables provided by the client, there will be no rows here. We will cover this in depth shortly.
Output Fields Grid: This will only show for a “SQL” Request Format Type. This grid is where you name the SQL columns that show up in the encoding section. For JSON and XML encoding, the values here will be the names of the properties and nodes. For CSV, these will be the header values.
Allowed Permission Lists Grid: This grid is where you configure SWS to tell what permission lists are authorized to run this SQL statement. There is a detailed security section below that documents how to use this and setup API client users.
Copy & Delete - Inside this group box are buttons to clone/copy the current configuration or to delete it.
DMS Export - Inside this group box is a generated DMS script template that can be used to export your configuration between databases.
HTTP Info Page - This page gives you both HTTP and curl examples on how to invoke the service operation. This removes the guesswork and speeds up the testing cycle.
Pagination for SQL endpoints
Any SQL-type SWS configuration can be paginated by the client with two optional URL query-string parameters. No configuration change is required on the SWS setup page — the parameters are applied automatically by the SWS handler when present.
Query parameter
Type
Default
Meaning
rowLimit
integer
Setup-configured Row Limit value
The maximum number of rows returned on a single page.
pageNumber
integer
1
1-based page index. Page 1 returns rows 1–rowLimit, page 2 returns the next rowLimit rows, and so on.
Pagination is offset-based. The handler fetches the full result set from the database and skips the rows that belong to earlier pages before it encodes the response. This means every paginated call still pays the cost of the underlying query, so pagination is not a substitute for an efficient WHERE clause when working with very large tables.
Example
Fetch the third page of 20 users from a SQL-backed /sql/allusers configuration:
GET /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/sql/allusers?pageNumber=3&rowLimit=20
Accept: application/json
Authorization: Basic <token>
The response payload includes three additional meta fields so the client knows where it is in the result set:
pageNumber and rowLimit echo the effective values used for this request.
nextPageNumber is present only when at least one more row remains after the current page. Its absence signals the last page.
For XML responses the same three fields appear under /response/meta/. For CSV responses they are emitted as the HTTP response headers x-pageNumber, x-rowLimit, and x-nextPageNumber (matching the existing x-rowCount, x-success, and other x-* headers used by CSV clients).
Backward compatibility
If a client sends neither pageNumber nor rowLimit, the SQL endpoint behaves exactly as it did before pagination was added: results are capped only by the setup-configured Row Limit field on the configuration row. Existing integrations do not need to change.
Ordering
Pagination relies on the natural ordering of the configured SQL statement. If the SQL does not include an ORDER BY clause, the database is free to return rows in any order, and that order can shift between calls — paging may then skip or duplicate rows. When you design a SQL configuration that clients will page through, add an explicit ORDER BY on a stable key.
2 - SWS Security Setup
How to setup users and security.
SWS has several layers of security for the web services. Additionally, security is required for a few custom components that are delivered with SWS that power-user use to configure SWS. We will discuss those here.
For SWS, there are two main areas of security.
API users who need to use call an SWS web service.
Security for Administrators to create and update SWS configurations.
This security is just needed by trusted users who will be configuring SWS web services.
SWS API User Security
SWS is based on PeopleSoft REST framework. The security mechanism that exists for REST is based on standard PeopleSoft PSOPRDEFNOPRIDs and passwords. We will walk through how this works and the best practices in this section. SWS is backed by a custom PeopleSoft REST Service.
For API users that need to invoke SWS web services, they need to be setup as a valid PeopleSoft user.
The technical design of SWS is actually structured around one PeopleSoft service operation called CHG_SWS_GET. All SQL statements that you deploy as a web service are handled by this one service operation and PeopleCode Handler. Therefore, all API users will need this base service operation as part of their security.
There are a few layers to security with SWS:
PeopleSoft OPRID and Password
The basic auth token provided must be a valid PeopleSoft OPRID and password. The PeopleSoft IB will authenticate the OPRID and password. If the OPRID is not valid or the password is incorrect, the IB will return an error.
The REST Service Account OPRID’s access to the Service Operation CHG_SWS_GET
The authenticated OPRID must have access to the CHG_SWS_GET service operation. If the OPRID does not have access, the IB will return an error.
The SWS Configuration Security setup for the Path.
The authenticated OPRID must have access to the SWS configuration for the path. If the OPRID does not have access, the SWS will return an error.
The configured SQL or PsoftQL could also have data security configured to limit what data is returned to the client.
@startuml
participant "HTTP Client" as client
participant "PeopleSoft IB" as psib
participant "SWS Handler" as pssws
client -> psib: HTTP Request\nBasic Auth Token
psib -> psib: Authenticate\nOPRID and Password
psib -> psib: Check Access\nTo Service Operation
psib -> pssws: Call Service Operation\nCHG_SWS_GET
pssws -> pssws: Lookup SWS\nConfiguration
pssws -> pssws: Is User Authorized?
pssws -> pssws: Convert Configuration into SQL
pssws -> pssws: Get Data
pssws -> client: Return Data
@enduml
The handler PeopleCode in the CHG_SWS_GET operation actually performs some additional checks on the current user to determine if they have access to execute the SQL statement at the path. Those permission lists are configured on the SWS setup table (COMPONENT: CHG_SWS_CONF_TBL). If an API user tries to invoke a SWS SQL statement and they do NOT have security, the SQL will NOT be run.
What is required to create an API User for SWS?
Create a new OPRID that represents the client using the application.
Give that OPRID a complex password. This password stored in PSOPRDEFN will be used in the authentication.
Give that OPRID access to the Service Operation CHG_SWS_GET
You can use the role CHG_SWS_USER for this purpose.
Give that user access to some other unique permission list that identifies it and that you can use to secure the SWS setup.
When the clients tries to trigger an SWS web service, the API User OPRID must have a permission list configured on the SQL statement. Each SWS SQL statement is tagged with permission lists that are allowed to execute it.
You can use the security objects we delivered as part of the project or use your own permission lists based on your own standard.
SWS API Delivered Security:
Permission List: CHG_SWS_USER
Service: CHG_SWS, Service Operation: CHG_SWS_GET
Role: CHG_SWS_USER
Permission List: CHG_SWS_USER
Example User ID and Basic Authentication Code
Let’s imagine we want to set up a test API user account to manually test web services.
Create a new OPRID called Z_TEST_API_USER
Create a complex randomly generated password. We will use proctor-consular-esther-hull-flood for this example.
The EMPLID can be set to nothing.
Set the other required fields to your system’s normal default values for a low-level non-privileged account.
Give OPRID Z_TEST_API_USER the role CHG_SWS_USER
Create a permission list called Z_TEST_API_USER. This will not have any real permission in the security tab. We will use it in the SWS configuration.
Create a new role called Z_TEST_API_USER and assign it the Z_TEST_API_USER permission list.
Grant user Z_TEST_API_USER the new Z_TEST_API_USER role.
Many text editors and HTTP test clients like Postman have base64 encoding functions built in. There are also tools online that will do this but I would not trust those with my production passwords.
The base64 encoded output servers as the token and is used in an HTTP Basic authorization header in the following form:
For these best practices, we are going to imagine three fictional internal systems that will be calling SWS web services that we can use in our example.
Stellar Wind - Internal Payment System
PRISM - Internal CRM system
MYSTIC - Internal Marketing System
Based on those three systems, we will show you the recommended security setup for these API users. Your security team may vary this depending on your own standards.
For each client or external system calling your web service, create a new and unique OPRID. Do not reuse a super user or real user account for this. Create a new account that only has the minimum API security and no PIA login ability.
For our three internal systems that may look like:
OPRID: Z_STELLAR_WIND_API_USER
OPRID: Z_PRISM_API_USER
OPRID: Z_MYSTIC_API_USER
Do not share API user accounts across systems.
Shared accounts make password rotation nearly impossible since you have to coordinate with more than one group.
Shared accounts also make auditing more difficult.
Having separate accounts makes it easy for you to shut off one system and not impact the others.
Each API User should have a complex password that is generated by a password manager.
Store your passwords in some sort of password database.
Do NOT email passwords to users. Emails persist forever.
The API Users should store the password in a secure location that easily facilitates password rotation.
The password should NOT be hard coded in the source code.
This could be an environment variable or something more advanced like Hashicorp Vault
Each API user may have a distinct permission list that identifies it and that you can use in SWS. The API user may have access to other PeopleSoft APIs. We often recommend creating a permission list and a role that is equal to the OPRID. Your security standard may be different. We find this simple model for API client OPRIDs is effective and allows visibility into what permissions an API user has. (OPRID = ROLENAME = CLASSID). API Clients tend to have very specific permissions that are not shared across other users.
For our three internal systems that may end up looking like this:
For users who will be maintaining SWS configuration, they need access to the setup component for SWS. You can use the permission list and role that we delivered with the project or use one that works with your security standard.
SWS Administrator Delivered Security:
Permission List: CHG_SWS_ADMIN
MENU: CHG_TOOLS, COMPONENT: CHG_SWS_CONF_TBL
Service: CHG_SWS, Service Operation: CHG_SWS_GET
Role: CHG_SWS_ADMIN
Permission List: CHG_SWS_ADMIN
Research Queries
This SQL will find the users who have access to the SWS Web Services.
Users who have access to Service Operation: CHG_SWS_PSOFTQL_POST should be extremely limited.
-- Find Users who have access to the
-- SWS Web Services
SELECTA.OPRID,B.ROLENAME,C.CLASSID,O.IB_OPERATIONNAME,AU.CLASSIDFROMPSOPRDEFNA,PSROLEUSERB,PSROLECLASSC,PSOPERATIONO,PSAUTHWSAUWHEREA.OPRID=B.ROLEUSERANDB.ROLENAME=C.ROLENAMEANDO.IB_OPERATIONNAMELIKE'CHG_SWS%'ANDO.IB_OPERATIONNAME=AU.IB_OPERATIONNAMEANDAU.CLASSID=C.CLASSIDORDERBYA.OPRID,B.ROLENAME;
Find users who can access CHG_SWS_GET and the paths they can execute
--- Find users who can access CHG_SWS_GET and the paths they can execute
SELECTA.OPRID,B.ROLENAME,C.CLASSID,O.IB_OPERATIONNAME,SWSC.CHG_DE_PATHFROMPSOPRDEFNA,PSROLEUSERB,PSROLECLASSC,PSOPERATIONO,PSAUTHWSAU,PS_C_SWS_CONF_TBLSWSC,PS_C_SWS_CONF_PLSWSPLWHEREA.OPRID=B.ROLEUSERANDB.ROLENAME=C.ROLENAMEANDO.IB_OPERATIONNAME='CHG_SWS_GET'ANDO.IB_OPERATIONNAME=AU.IB_OPERATIONNAMEANDAU.CLASSID=C.CLASSIDANDSWSPL.CLASSID=AU.CLASSIDANDSWSC.GUID=SWSPL.GUID;
Minutes to Cache Response: You can optionally configure to have the integration broker cache the response. This can be done for performance reasons if the SQL is very “expensive”.
SWS can tell the RESTListeningConnector servlet to cache the response. This caching is provided by the built-in caching mechanism in PeopleTools REST functionality. It can speed up requests for expensive SQL where the result is not expected to change often. There are some known caveats — see the PeopleSoft REST Caching integration broker book section before using this.
Warning
If your SWS setup uses %EMPLOYEEID or %OPERATOR then do NOT use caching if you have multiple clients.
4 -
@startuml
participant "HTTP Client" as client
participant "PeopleSoft IB" as psib
participant "SWS Handler" as pssws
client -> psib: HTTP Request\nBasic Auth Token
psib -> psib: Authenticate\nOPRID and Password
psib -> psib: Check Access\nTo Service Operation
psib -> pssws: Call Service Operation\nCHG_SWS_GET
pssws -> pssws: Lookup SWS\nConfiguration
pssws -> pssws: Is User Authorized?
pssws -> pssws: Convert Configuration into SQL
pssws -> pssws: Get Data
pssws -> client: Return Data
@enduml
