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 calling the IB ServiceCHG_SWS (the URL prefix), handled by the Service OperationCHG_SWS_GET. That one service operation checks security, resolves parameters, executes the SQL, encodes the data, and returns it to the user — every SWS-configured path is served by the same handler.
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.
Naming convention used throughout these docs
PeopleSoft Integration Broker distinguishes a Service from a Service Operation. SWS uses both:
CHG_SWS — the Service. This is the URL prefix that callers see (/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/your/path).
CHG_SWS_GET — the Service Operation. This is the PeopleCode handler that actually runs when a request comes in. All admin-configured GET paths are dispatched through this one handler.
When the docs say “configure security on the service operation,” they mean CHG_SWS_GET. When you see “the CHG_SWS URL,” that is the Service prefix in the URL. (CHG_SWS_PSOFTQL, the advanced caller-driven service operation, is documented separately.)
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
Data Access Models — CHG_SWS vs CHG_SWS_PSOFTQL
SWS ships two service operations, and they enforce data access in completely different ways. Knowing which model applies to a given endpoint determines what an administrator must do to grant or revoke access.
Service operation
CHG_SWS_GET (this page)
CHG_SWS_PSOFTQL
Who chooses the SQL?
The PeopleSoft admin, in the SWS configuration row
The client, in the POST body
What gates access?
A permission-list grid on the configuration row
A whitelist table (C_SWS_REC_WL) keyed by permission list
Granularity
Per URL path
Per record name
Caller can request
Only the SQL the admin wrote at that path
Any record present in the whitelist for the caller’s permission list
CHG_SWS (this page) — admin-defined paths. The admin writes the SQL or PsoftQL into a configuration row, attaches a list of permission lists allowed to call that path, and the caller GETs the URL. The caller cannot pick which record to read — the admin did that. Adding a record to the response means editing the SWS configuration. Granting a new partner access means adding their permission list to the configuration row’s permission-list grid.
CHG_SWS_PSOFTQL — caller-defined queries. The caller POSTs a PsoftQL body that names the records they want. Before running anything, the handler checks C_SWS_REC_WL to confirm every record in the request is whitelisted for the caller’s permission list. If any record is not whitelisted, the request is rejected with responseCode: 400 and the message “At least one record in your request is not whitelisted or is not a real record name.” — see Error Responses. Adding a record means inserting a row into C_SWS_REC_WL. Granting a new partner means giving their permission list whitelist entries for the records they need. The C_SWS_REC_WL table is described in the PsoftQL Web Service page.
The two models are independent. A given OPRID can have access to a curated set of CHG_SWS paths and a separate whitelist for CHG_SWS_PSOFTQL — those settings do not overlap. If you only ever expose data through CHG_SWS, the whitelist table doesn’t matter.
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.
The Parameters Grid
The Parameters Grid binds values from the inbound HTTP request to placeholders in your SQL or PsoftQL. Each row in the grid declares one parameter that the SWS handler will accept and substitute at run time. If your SQL has no :name bind variables (or your PsoftQL has no {{name}} placeholders), this grid stays empty and the handler simply runs the statement as-is.
Anatomy of a Parameters Grid row
Each row carries the fields below. The exact column labels match the labels on the PeopleSoft setup page.
Column
What to enter
Parameter Name
The name you will reference in the statement. For SQL, write :paramName in the SQL Statement Text. For PsoftQL, write {{paramName}} inside sqlWhereClause or a path segment.
Source
Where the value comes from. The two supported sources are URL Path Segment and Query String. URL Path Segment is for {paramName} tokens in the URL Path field; Query String is for ?paramName=value on the request URL.
Required
If checked and the parameter is absent at request time, the handler short-circuits and returns an error without running your SQL. Leave unchecked for optional filters whose defaults are handled inside the SQL.
Default Value
Optional. Used when the parameter is not supplied and Required is unchecked. Skip when the SQL itself handles the default (e.g. WHERE EFFDT <= COALESCE(:asof, %CURRENTDATEIN)).
Data Type / Length
Informational on the grid; the handler does not coerce types. The bind happens as a string and your SQL’s column type drives any implicit conversion. Use this column as documentation for whoever maintains the configuration.
Wiring a URL path parameter — step by step
Goal: serve GET /acmecorp/people/{emplid} and substitute {emplid} into the SQL.
On the URL Path field, enter acmecorp/people/{emplid}. The braces are literal — they tell SWS to capture whatever segment appears in that position at request time.
In the SQL Statement Text, reference the value as :emplid:
Save. At request time, GET .../acmecorp/people/KU0001 runs the SQL with :emplid bound to 'KU0001'. The actual SQL executed appears in the response’s meta.finalSQL — useful for verifying the substitution worked.
Wiring a query-string parameter
Goal: optional ?activeOnly=Y filter on the same endpoint.
GET .../users returns everyone (default 'N'); GET .../users?activeOnly=Y filters to non-locked accounts. The response’s meta.QueryString echoes back the literal query string the client supplied, which is handy for confirming the value was actually received.
Parameter binding gotchas
SQL bind variables are strings on the wire. If you need numeric semantics in the SQL, cast explicitly: WHERE A.PERSON_NUM = TO_NUMBER(:personNum) on Oracle.
Names are case-sensitive in PsoftQL {{...}} substitution but match by position in SQL :name binding. Pick a consistent convention (lowerCamelCase is common) and stick with it.
Path-segment names must match between the URL Path field and the grid. A typo in either silently treats the parameter as absent.
Never concatenate parameter values into the SQL string yourself. Always use :name binds — SWS handles SQL injection prevention only when you use the binding path.
The Output Fields Grid
The Output Fields Grid lives only on SQL configurations. It maps each SQL column position to the field name that appears in the response. The grid serves three encoders from one source of truth:
JSON — the grid value becomes the property name in each row object
XML — the grid value becomes the element tag inside each <row>
CSV — the grid value becomes the column header in the first row
Filling out the grid
The grid has one row per SELECT column, in the same order as the SQL. If your SQL is:
The number column auto-increments — what you control is the Output Field Name for each position. Names can be anything the encoders accept; lowerCamelCase or snake_case both work, but pick one and stay consistent across your endpoints.
What happens if I skip a row?
If the grid has fewer rows than the SQL has columns, the missing columns fall through to the original PeopleSoft field name in upper case (EMPLID, NAME, etc.). If the grid has more rows than the SQL has columns, the extras are ignored. Avoid both — match the row count exactly so the configuration documents the contract clearly.
Re-ordering columns
Re-ordering the grid does not re-order the response. The position number is what binds a grid row to a SELECT column. To change column order in the response, change the SELECT list in the SQL and renumber the grid to match.
Why no Output Fields Grid for PsoftQL?
PsoftQL responses are nested PeopleSoft records, not flat rowsets. The property names come straight from the record’s field names — there is nothing to alias. If you need different output field names from a PsoftQL response, transform the response in your client or switch the configuration to SQL.
Worked Example — SQL Configuration with a Path Parameter
The configuration walkthrough above used a PsoftQL example. This section shows the same idea using SQL — useful when you want CSV output, SQL functions, or a hand-tuned WHERE clause. The endpoint demonstrated here is deployed on our public demo system; you can reproduce the same configuration in your own database.
The configuration
Field
Value
URL Path
test/sql/security/users/{oprid}
Request Format Type
SQL
SQL Statement Text
SELECT A.OPRID, A.ACCTLOCK FROM %TABLE(PSOPRDEFN) A WHERE OPRID = :oprid
Parameters Grid
One row: name oprid, source = URL path segment
Output Fields Grid
OPRID → oprid, ACCTLOCK → locked
Default Encoding
JSON
Include metadata
Checked
Allowed Permission List
(whichever permission list represents your API client; the demo uses CHG_PSOFTLENS_API_USER)
Three notes on the SQL above:
%TABLE(PSOPRDEFN) is a PeopleSoft meta-SQL macro that resolves to the actual physical table name (PSOPRDEFN) at run time. Using it lets your configuration survive renames between releases.
:oprid is the SQL-bind form of the path parameter. SWS routes the value the client supplied in the {oprid} URL segment into the bind variable. SWS handles parameter substitution safely; do not concatenate request values directly into the SQL string.
The Output Fields Grid renames OPRID to oprid and ACCTLOCK to locked so the response uses lower-case, vendor-friendly names rather than the underlying record’s all-caps PeopleSoft field names.
The HTTP call
GET http://your-ib-host/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/test/sql/security/users/PS
Authorization: Basic ...redacted...
Accept: application/json
JSON response
HTTP/1.1200OKcontent-type:application/json; encoding=UTF-8{"data":[{"oprid":"PS","locked":"0"}],"errors":"","meta":{"rowCount":"1","sqlIDExecuted":"2d43c8aa-40b2-4c39-9b2a-4a115804c685","success":"True","pageNumber":"1","rowLimit":"9999999","URLPath":"test/sql/security/users/PS","finalSQL":"SELECT A.OPRID, A.ACCTLOCK FROM %TABLE(PSOPRDEFN) A WHERE OPRID = 'PS'","productVersion":"2026-04-20","toolsVer":"8.61.03","currentUser":"CHG_PSOFTLENS_API_USER","dbname":"CS92DEV","dbType":"ORACLE"}}
The two interesting fields in meta:
finalSQL shows the SQL after parameter substitution — WHERE OPRID = 'PS'. Use this when debugging “why didn’t I get the rows I expected?”: you can copy finalSQL straight into a SQL client and run it yourself.
sqlIDExecuted is a per-execution GUID that links the response back to log entries in C_SWS_RUN_LOG for audit and troubleshooting.
Same endpoint, CSV encoding
Change the Accept header and SWS swaps the encoder. The response body becomes a CSV document and the metadata moves into x-* HTTP response headers (CSV has no place for a nested meta block):
GET http://your-ib-host/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/test/sql/security/users/PS
Authorization: Basic ...redacted...
Accept: text/csv
HTTP/1.1200OKContent-Type:text/csv; encoding=UTF-8x-rowCount:1x-pageNumber:1x-URLPath:test/sql/security/users/PSx-sqlIDExecuted:2d43c8aa-40b2-4c39-9b2a-4a115804c685x-success:True"oprid","locked""PS","0"
The header row uses the names from the Output Fields Grid (oprid, locked), confirming that the same grid feeds JSON property names, XML element names, and CSV header names from a single source of truth.
Pagination over a SQL endpoint
The exact same configuration paginates automatically if the client adds query-string parameters — no setup change required. See Pagination for SQL endpoints below for the full parameter list. Quick example, page 2 with 3 rows per page against test/sql/allusers:
Notice that finalSQL is the original SQL — pagination is layered on top by the handler, not by rewriting the statement. The client controls page size and offset; the admin controls what SQL runs and what rows it can see.
SQL Response Metadata
Every SQL-type response (JSON or XML) carries a meta block; CSV responses surface the same values as x-* HTTP headers. The PsoftQL response page has a companion reference covering the fields shared between SQL and PsoftQL responses (toolsVer, dbname, psftTransactionId, etc.). The table below covers the SQL-specific fields you only see on CHG_SWS_GET responses.
Property
Type
Use
finalSQL
string
The SQL that ran, after parameter substitution. Copy/paste this into a SQL client to reproduce the result by hand. Indispensable for debugging “why didn’t I get the rows I expected?”.
sqlIDExecuted
string
Per-execution GUID. Links the response back to the audit row written to C_SWS_RUN_LOG and to any application-server log entries that mention it.
rowCount
string
Number of rows in the response after any rowLimit cap. Quoted as a string — older PeopleTools quirk; parse to integer in your client.
URLPath
string
Path segment that resolved to this configuration row (e.g. "test/sql/security/users/PS"). Useful when one OPRID has access to many similar paths and you want to confirm which one served the response.
QueryString
string
Raw query string from the request (or the literal "NULL" when no query string was supplied).
pageNumber
string
Page index the client requested. Quoted as a string for the same reason as rowCount.
rowLimit
string
Effective page size — the lower of the client’s ?rowLimit=N and the configuration’s row limit.
success
string
"True" or "False". Redundant with HTTP status but the only success indicator inside the CSV x-* headers.
debugMessage
string
Handler debug trace when the configuration is in debug mode. Empty otherwise. Note the singular spelling — distinct from PsoftQL’s plural debugMessages.
For CSV responses the same fields are emitted as response headers with an x- prefix: x-finalSQL, x-sqlIDExecuted, x-rowCount, x-URLPath, etc. The HTTP header field names preserve the original camelCase, which is unusual but matches what callers see on the wire.
A few specific tips:
Treat meta as informational, not a contract. Field set varies by PeopleTools version. Newer builds emit productVersion; older ones emit apiVersion. Code defensively.
Don’t log finalSQL to public sinks. It can contain values from path parameters and query strings — including ones a caller might consider sensitive (EMPLID, OPRID, etc.). Treat it like a SQL trace.
psftTransactionId is your support handle. Anything you can’t reproduce — capture this GUID and PeopleSoft support can correlate it across app-server logs, IB monitor, and the SWS run log.
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;
SWS response caching is provided by the PeopleSoft RESTListeningConnector servlet — SWS does not implement its own cache, it only opts an endpoint in. The cache has several non-obvious behaviors that can leak data between callers or serve stale data indefinitely. Read the companion PeopleSoft REST Caching chapter in the Integration Broker book for the underlying mechanics; this page covers how it applies specifically to SWS.
How SWS uses the PeopleTools REST cache
When you set Minutes to Cache Response to a non-zero value on an SWS configuration row, the handler adds a Cache-Control: max-age=<N*60> header to the response. The PeopleTools RESTListeningConnector servlet then stores the full response in memory and replays it for subsequent matching requests until the TTL expires. The cache lives in the PIA web-server JVM, not in PeopleSoft application-server memory — restarting PIA empties it; restarting the app server does not.
No code change is required to opt in. Set a non-zero value on the configuration row, save, and the very next request from that endpoint will populate the cache.
What gets cached, and what the cache key actually is
The cache key is the full request URL — that is, path and query string — combined with the Accept header. Two requests are treated as cache hits only when all three match exactly.
Component
Part of cache key?
Notes
URL path (e.g. /acmecorp/people/PS)
yes
Different path = different cache entry. {emplid} path parameters create one entry per distinct value.
Query string (e.g. ?rowLimit=50)
yes
?rowLimit=10 and ?rowLimit=20 are independent cache entries. So is the absence of any query string.
Accept header
yes
JSON, XML, and CSV responses are cached independently. Three different Accept values populate three cache entries.
Authorization header (basic-auth user)
no
This is the central caching gotcha. Two different callers hitting the same URL get the same cached response — see below.
Request body (POST endpoints)
no
The REST cache is GET-oriented. CHG_SWS_PSOFTQL (a POST endpoint) effectively does not benefit from this cache for variable input.
Choosing a TTL
The right cache duration is the longest interval during which a stale response is still acceptable to the caller. Some patterns we’ve seen work well:
Endpoint pattern
Recommended TTL
Why
XLAT / setup lookups (subjects, campuses)
60 minutes (or 1440 / 24h)
These change on a release cadence — minutes-old data is fine.
Reference tables editable only by power users
5–30 minutes
Long enough to absorb burst traffic, short enough that an admin’s edit shows up within the hour.
Reporting / dashboard rollups
5–15 minutes
Dashboards typically refresh on this cadence anyway; caching aligns with the refresh budget.
Real-time-ish data (current term enrollments)
1–2 minutes
Use only when the source data changes slower than the TTL.
Per-user data (uses %OPERATOR / %EMPLOYEEID)
0 (disabled)
Caching across users leaks data. See the security alert below.
Cheap queries (<100ms server time)
0 (disabled)
The cache itself adds overhead. If the query isn’t slow, don’t cache.
There is no upper limit enforced by SWS. Practical ceiling: anything beyond a few hours starts feeling like a static asset, at which point you should consider regenerating the data into a separate table refreshed by a scheduled job rather than caching an unbounded query.
No manual cache invalidation
There is no SWS or PeopleSoft UI to flush the REST cache. Your options when cached data is wrong are:
Wait out the TTL. The cache entry expires naturally and the next request re-runs the SQL.
Bounce PIA. Restarting the PeopleSoft web server tier empties the JVM cache. This is heavy-handed but effective.
Change the URL. Adding a cache-busting query-string parameter (?_v=2) creates a new cache entry rather than replacing the old one — works as a per-caller workaround when you can change the integration partner’s URL.
Choose your TTL with the assumption that you cannot intervene mid-window.
Security: the cross-user data-leak risk
The cache key does not include the caller’s OPRID. Two callers hitting the same URL within the TTL window receive the same response, even when:
They were authenticated as different OPRIDs.
They belong to different permission lists.
The underlying SQL uses %OPERATOR or %EMPLOYEEID and would otherwise return different rows per user.
This means that an endpoint where caller A hits /student/grades and gets caller A’s grades will, if cached, return caller A’s grades to caller B’s next call as well.
Hard rule
Never enable caching on an SWS configuration whose SQL contains %OPERATOR, %EMPLOYEEID, or any other per-caller binding — including SQL that filters by the caller’s row-level-security setid. The handler does not warn you about this; nothing in the UI prevents it. The first cross-user request after the cache populates will return the wrong person’s data.
Safe caching candidates: endpoints whose response depends only on the URL, query string, and database state — never on which OPRID is calling.
Detecting whether a response was served from cache
There is no SWS-emitted “cache hit” header. Inspect these signals instead:
meta.responseDTTM in JSON/XML responses (or x-requestTime for CSV) is the timestamp the response was originally assembled. On a cache hit it stays at the populate-time value, not the current wall clock. If two calls 30 seconds apart return identical responseDTTM strings, the second was a cache hit.
meta.psftTransactionId is also frozen at populate time. Identical transaction IDs across calls = same cached entry.
Wall-clock latency drops sharply on a hit — typically from hundreds of milliseconds (real SQL execution) to single-digit milliseconds (servlet returning cached bytes).
Combine the three: if responseDTTM and psftTransactionId match across two calls and latency is sub-10ms, you’re seeing a cache hit.
See also
Caching limitations — same restrictions listed alongside other product boundaries.
