Skip to main content
Connect Oracle PeopleSoft to Oleria as an HR system and to see who has access to a PeopleSoft database. On PeopleSoft HCM and Campus Solutions databases, Oleria reads the HR worker record: employees, people, departments, and reporting lines, and links each user profile to its employee. On every PeopleSoft database, Oleria reads the PeopleTools security model, which every PeopleSoft application (HCM, Financials and Supply Chain, Campus Solutions, and others) shares: user profiles, the roles and permission lists they hold, and the service accounts and Integration Broker nodes that act on its behalf. Oleria reads PeopleSoft through Query Access Services (QAS) over a set of queries you create, and makes changes through a component interface-based web service you generate. HR worker records are optional: they need a second set of queries and data permission over every worker. Databases without HR data, such as Financials and Supply Chain, ELM, or CRM, connect with the security model only. One Oleria connection maps one PeopleSoft database, identified by its Integration Broker gateway and default local node. Connect additional databases as separate instances.

​
What Oleria discovers

Once connected, Oleria continuously discovers and maps the following from your PeopleSoft database.

​
HR worker records

When you create the optional HR queries on an HCM or Campus Solutions database, Oleria discovers:
  • Employees - one employee for each person (EMPLID) with a current job, including contingent workers, persons of interest who have a job, and terminated workers. Each employee carries a name, business email, job title, job code, job family and function, company, department ID as the cost center, employment type, start date, and, for a person with no active job, an end date. A person with several concurrent jobs is one employee, described by their primary job.
  • People - one person for each employee, with their home email address.
  • Departments - every department, including inactive ones, and which department each employee serves in.
  • Department hierarchy - which department contains which, read from the department security tree you name in Department tree. A department is linked to its parent only when both are departments in the same setID.
  • Manager relationships - the manager of each active employee: the single active holder of the reports-to position, else the supervisor ID, else the department’s manager.
  • Account-to-employee links - Oleria links each user profile to its employee by the profile’s employee ID (EMPLID) and company, and by email address. A linked profile also shows the employee’s department, job title, job code, and manager’s email, and the employee’s business email when the profile has none. Service accounts and nodes are never linked.

​
Security model

On every PeopleSoft database, Oleria discovers:
  • Accounts - every user profile, with its description, email address, employee ID (EMPLID), last sign-in, last password change, and failed sign-in count. Locked profiles appear as disabled accounts. Users with a customer or vendor ID type and no employee ID type appear as external users.
  • Roles - every PeopleSoft role and every permission list. Inactive roles are included and marked as inactive.
  • Role assignments - which accounts hold which roles, including whether each membership is static or maintained by a dynamic-role rule, and which permission lists each role contains.
  • Permission-list assignments - the four permission lists set directly on each user profile: primary, row security, process profile, and navigator homepage.
  • Administrators - users who hold the PeopleSoft Administrator role, or whose roles include a permission list that authorizes the user profile, role, or permission list security components.
  • Non-human identities - service-account user profiles, typed as Service account, and Integration Broker nodes, typed as Application. Oleria treats these user profiles as service accounts: the integration user, users you list in Service account user IDs, the default user ID of any node, PTWEBSERVER, and holders of the PeopleTools Web Server role. Each node is linked to its default user ID, because requests from the node that carry no user credentials run as that user.
  • Credentials - each service account’s password, with the date it was last changed, and each node’s password or certificate. Oleria never reads password or certificate values.
  • Activity - sign-ins and sign-outs, the most recent failed sign-in for each user ID, and, if you enable PeopleSoft’s database-level audit, user profile changes (created, deleted, locked, unlocked, password changed) and role grants and revocations.
Oleria doesn’t inventory PeopleSoft resources such as components, pages, component interfaces, web services, query access groups, or row-level security, and doesn’t show what each permission list grants. PeopleTools has no group or AI agent objects, so none are discovered. Oleria never reads personal data such as birth dates or national IDs, or compensation and tax data.

​
Prerequisites

  • PeopleSoft security administrator access, with rights to manage Integration Broker, Query Manager, and Tree Manager. Setup creates a user profile, a role, a permission list, a query tree, and 14 queries, plus up to 2 optional audit queries and 7 optional HR queries, and 1 more query if you enable remediation actions.
  • For HR worker records: a PeopleSoft HCM or Campus Solutions database, and your security team’s approval to give the integration user HCM data permission (row-level security) over every worker, including terminated workers.
  • An Integration Broker gateway reachable from Oleria over HTTPS from the internet at a public address. A typical setup is a DMZ web server or a reverse proxy that exposes only /PSIGW/RESTListeningConnector/* and /PSIGW/PeopleSoftServiceListeningConnector/*, and allows only Oleria’s IP addresses. Gateways whose address resolves to a private IP can’t be connected.
  • Admin access to the Oleria workspace
To get Oleria’s IP addresses for your allow list or firewall, contact support@oleria.com.

​
Set up PeopleSoft for Oleria

1

Check Integration Broker

Go to PeopleTools -> Integration Broker -> Configuration -> Gateways. Check that the gateway URL is set and active, and that the default local node is active. Under Service Configuration, check that the REST target location is the gateway’s public URL. Record the default local node name, for example PSFT_HR. You enter it as the Integration Broker node in Oleria.
2

Create the integration user

Go to PeopleTools -> Security -> User Profiles -> User Profiles and add a dedicated user profile, for example OLR_INTEGRATION. Give it ID type NONE, a symbolic ID, and a long password. Assign it only the role OLR_INTEGRATION_ROLE.
Exempt the integration user from password expiry, or schedule its rotation. An expired password stops every sync until you update it in Oleria.
3

Create the permission list

Create the permission list OLR_INTEGRATION_PL and attach it to OLR_INTEGRATION_ROLE:
  • Web Services - give the QAS REST service Full Access to the operations QAS_EXECUTEQRY_REST_GET and QAS_LISTQUERY_REST_GET. The service is named QAS_QRY_SERVICE or QAS_QRY_REST, depending on your PeopleTools release.
  • Query -> Query Profile - select Only Allowed to Run Queries, and set Max Rows Fetched to 0 (unlimited) or above 50000. Oleria checks this when you connect and at the start of every sync, and stops with an error if the limit is lower.
  • Query -> Access Group Permissions - add the tree OLR_SECURITY_QRY_TREE with the access group OLR_SECURITY_AG. For HR worker records, also add the access group OLR_HR_AG on the same tree.
4

Create the query tree

In Tree Manager, use the ACCESS_GROUP structure to create the tree OLR_SECURITY_QRY_TREE. Add the access group OLR_SECURITY_AG holding these records: PSOPRDEFN, PSUSEREMAIL, PSOPRALIAS, PSROLEDEFN, PSROLEUSER, PSCLASSDEFN, PSROLECLASS, PSAUTHITEM, PSAUTHWS, PSMSGNODEDEFN, PSPTLOGINAUDIT, PSACCESSLOG, and PSRECFIELD. PSRECFIELD holds only record and field names, and Oleria reads it to check Max Rows Fetched. If you enable database-level audit later, add its audit records too.
Restrict who can create public queries on this tree. Anyone who can could create a query that lets the integration user read password hashes from PSOPRDEFN.
5

Create the queries

In Query Manager, create queries 1 to 13 and query 23 from Query definitions as public queries, exactly as listed. Set each prompt’s Unique Prompt Name exactly as shown, because prompt names are case-sensitive.
6

Set up HR worker records (optional)

Skip this step on a database without HR data, such as Financials and Supply Chain. On an HCM or Campus Solutions database:
  1. In Tree Manager, add a second access group OLR_HR_AG to OLR_SECURITY_QRY_TREE, holding these records: JOB, NAMES, EMAIL_ADDRESSES, DEPT_TBL, JOBCODE_TBL, COMPANY_TBL, PSTREENODE, and PSTREEDEFN. Keeping them in their own access group lets you grant or revoke HR access separately. Don’t add PERSON, PERS_NID, PERSONAL_DATA, or COMPENSATION.
  2. Make sure OLR_INTEGRATION_PL grants OLR_HR_AG, as described in Create the permission list.
  3. Give the integration user data permission over every worker, including terminated workers. Create a row security permission list, for example OLR_HR_ROWSEC, and set it as Row Security on the integration user’s profile (General tab). Under Set Up HCM -> Security -> Core Row Level Security -> Security by Dept Tree, grant that permission list access to the top node of the department security tree. Then run the delivered security join table refresh processes (SJT_OPR_CLS and SJT_CLASS_ALL).
  4. In Query Manager, create queries 16 to 22 from Query definitions as public queries. Query 16, OLR_JOBS, turns on HR worker records. Once it exists, queries 17 and 19 are required too. Queries 18, 20, 21, and 22 are optional.
PeopleSoft applies row-level security to HR queries without any error. If the integration user can see only some workers, Oleria receives only those workers, and the user profiles of the rest link to employees by email only. Oleria can’t prove the grant is complete. It detects only a large shortfall, recording a warning for Oleria support when more than 2% of the user profiles that carry an employee ID name an employee the HR queries didn’t return.
7

Secure the QAS service operations

Go to PeopleTools -> Integration Broker -> Integration Setup -> Service Operations. For QAS_EXECUTEQRY_REST_GET and QAS_LISTQUERY_REST_GET, make the operation active, set Req Verification to Basic Authentication and SSL, and make sure the delivered any-to-local routing is active.
Never set Req Verification to None. Requests would then run as the node’s default user instead of the integration user.
8

Enable remediation actions (optional)

Skip this step for a read-only connection. To let Oleria lock and unlock users and grant and revoke roles:
  1. Go to PeopleTools -> Integration Broker -> Web Services -> CI-Based Services. Search for USER_PROFILE and select the Update method. Select Review, then Display Selected Actions, then Perform Selected Actions. This creates the service CI_USER_PROFILE and the operation CI_USER_PROFILE_UP.v1.
  2. On the CI_USER_PROFILE_UP service operation’s General tab, select User/Password Required and set Security Verification to SSL. Oleria sends the integration user’s ID and password in a WS-Security UsernameToken on every request.
  3. On OLR_INTEGRATION_PL, give CI_USER_PROFILE_UP Full Access under Web Services, and give the USER_PROFILE component interface Full Access to the Get, Save, Cancel, and Find methods.
  4. On the Role Grant page of OLR_INTEGRATION_ROLE, list the roles Oleria may grant and revoke. Avoid giving the integration user the PeopleSoft Administrator role instead.
  5. To grant and revoke roles, also create query 24, OLR_ROLE, from Query definitions. Oleria reads the role through it before each role action.
9

Enable more activity (optional)

  • Keep Enable Login Audit set to Y in the application server’s security settings. It’s on by default, and it records failed sign-ins.
  • To collect user profile and role changes:
    1. Make sure EnableDBMonitoring is set to 1 in each application server and Process Scheduler domain (PSADMIN). Without it, the audit triggers can’t record who made a change. On Oracle, also make sure the delivered GET_PS_OPRID function is installed (getpsoprid.sql).
    2. In Application Designer, create the audit records, for example AUDIT_PSOPRDEFN and AUDIT_PSROLEUSER, with AUDIT_OPRID, AUDIT_STAMP, and AUDIT_ACTN followed by the base record’s fields.
    3. Go to PeopleTools -> Utilities -> Audit -> Update Database Level Auditing. For PSOPRDEFN and PSROLEUSER, select the audit record, select Add, Change, and Delete, and generate the code. Run the trigger script from Perform Database Level Audit.
    4. Add the audit records to OLR_SECURITY_AG, and create queries 14 and 15 from Query definitions.

​
Query definitions

Create every query as a public query. Each query’s name must match exactly. Never select the password hash or salt columns (OPERPSWD, PTOPERPSWDV2, OPERPSWDSALT) or the PSUSERATTR record. Queries 23 and 24 are required by the base setup and by role actions, but are numbered after the HR queries. OLR_ROWCAP_PROBE must have no criteria and no prompts: Oleria asks it for 50,001 rows to confirm that Max Rows Fetched is 0 or above 50000. For OLR_PRIV_PERMLISTS, Oleria uses the FLAG value to find administrator and web service permission lists:
  • First select - PSAUTHITEM, with CLASSID and the expression 'SECADMIN' named FLAG. Criteria: CLASSID > :OLR_AFTER, MENUNAME = 'MAINTAIN_SECURITY', BARITEMNAME in USERMAINT, ROLEMAINT, and PERMLIST_GENERAL, and AUTHORIZEDACTIONS > 0. If your security menu or components have different names, use yours.
  • Second select - PSAUTHWS, with CLASSID and the expression 'WEBSVC' named FLAG. Criteria: CLASSID > :OLR_AFTER and AUTHORIZEDACTIONS > 0.
The :OLR_FROM_DATE and :OLR_TO_DATE prompts are date prompts in YYYY-MM-DD format. In OLR_SIGNON_AUDIT, define the prompts in this order: :OLR_AFTER, then :OLR_FROM_DATE, then :OLR_TO_DATE. For the HR queries, 16 to 22:
  • Effective dating - on JOB, NAMES, DEPT_TBL, JOBCODE_TBL, and COMPANY_TBL, keep the effective-date criterion that Query Manager adds by default (condition type EffDt <=, Current Date, effective sequence Last) unchanged. It returns the row currently in effect for each key, so future-dated hires and terminations take effect on their date. Don’t use Last Effective Date, which would show future-dated terminations early.
  • Exists subquery - in OLR_WORKER_NAMES and OLR_WORKER_EMAILS, add an exists criterion with a subquery on JOB joined on B.EMPLID = A.EMPLID, and delete the subquery’s own effective-date criterion. This limits names and emails to people who have a job, which matters on Campus Solutions databases.
  • Tree version - in OLR_DEPT_TREE, delete the default effective-date criterion. Set A.EFFDT equal to a subquery that selects MAX(B.EFFDT) from PSTREEDEFN where B.SETID = A.SETID, B.TREE_NAME = A.TREE_NAME, B.EFFDT <= the current date, and B.EFF_STATUS = 'A'. Define the prompts in this order: :OLR_TREE, then :OLR_AFTER. Oleria passes the Department tree value to :OLR_TREE. If your PSTREENODE has no PARENT_NODE_NAME field, select PARENT_NODE_NUM instead.
  • One record per query - don’t add outer joins. Oleria joins the HR queries itself.
  • No personal or compensation data - select only the fields listed. Never select birth, national ID, sex, marital, ethnicity, disability, military, citizenship, address, or phone data, compensation rates, salary plan, grade or step, pay group, FLSA status, union code, or tax IDs such as FEDERAL_EIN. Oleria drops any other field a query returns without storing it.
When you connect, Oleria checks that all 14 required queries (1 to 13, and 23) exist and names any that are missing. If the optional queries 14 and 15 are missing, Oleria connects and skips user profile and role change activity. If query 24 is missing, Oleria connects, and Add user to role and Remove user from role fail validation with an error that names OLR_ROLE.If query 16 is missing, Oleria connects without HR worker records. If query 16 exists, queries 17 and 19 must exist too, or Oleria rejects the connection. If query 18, 20, 21, or 22 is missing, Oleria connects and leaves out what it provides: employee and personal emails (18), job titles, families, and functions (20), company names, which fall back to the company code (21), and the department hierarchy (22).

​
Permissions Oleria uses

​
Connect Oracle PeopleSoft to Oleria

1

Open the integration

Go to your Oleria workspace, select Integrations -> select Oracle PeopleSoft.
2

Complete the connection form

3

Authenticate

Select Authenticate to validate and save the integration. Oleria signs in as the integration user, checks that every required query exists, runs OLR_USER for the integration user itself, and reads the first page of OLR_USERS. It then checks that Max Rows Fetched is 0 or above 50000 by reading 50,001 rows of OLR_ROWCAP_PROBE, runs every other required query for one row with its prompts, checks that the fields Oleria needs are selected, reads the integration user’s roles through OLR_USER_ROLES, and, if OLR_ROLE exists, reads one of those roles through it. If OLR_JOBS exists, Oleria also checks that OLR_WORKER_NAMES and OLR_DEPARTMENTS exist, runs a sample of each HR query you created with its prompts (OLR_DEPT_TREE with your Department tree), and checks that the fields Oleria needs are selected, including TERMINATION_DT when the sample has an inactive worker. It rejects the connection if OLR_JOBS returns no rows. If any check fails, Oleria rejects the connection with an error that names the query or the cause. Find the newly connected PeopleSoft database on the Connected tab of your Oleria Integrations page.

​
Verify the integration

Confirm the PeopleSoft database appears on the Connected tab of your Oleria Integrations page. After the first sync completes, you can review the discovered accounts, roles, and non-human identities in your Oleria workspace, and, if you set up HR worker records, the employees and departments.
If you change the integration user’s password in PeopleSoft, update it in Oleria by editing the integration. A rejected password causes the sync to fail.

​
Remediation actions

Oleria can take the following actions once you complete the optional Enable remediation actions step in Set up PeopleSoft for Oleria: Before each action, Oleria checks that the integration user can read its own profile through OLR_USER and that the CI_USER_PROFILE_UP operation is available. Oleria reads the current state first. If the user is already in the target state, the action succeeds and there’s nothing to reverse. Otherwise, Oleria sends the change once and reads it back:
  • If the change shows, the action succeeds.
  • If PeopleSoft accepted the change but the user still reads back in the old state, the action fails and nothing is reported as done.
  • If PeopleSoft refused a lock or a role removal with an error and a read-back shows the old state, the action fails with PeopleSoft’s message.
  • If PeopleSoft answered an unlock or a role grant with an error, the action fails and reports that the change may have been applied, with PeopleSoft’s message. The gateway also reports an application server timeout as an error, and the change can still be saved after Oleria reads the user back. Check the user profile in PeopleSoft before you run it again.
  • If PeopleSoft’s answer was lost or was a server error, and the change doesn’t show, the action fails and reports that the change may have been applied. The same applies when PeopleSoft answered with an error but Oleria couldn’t read the user back, because the gateway also reports an application server timeout as an error. Check the user profile in PeopleSoft before you run it again.
  • If PeopleSoft accepted a lock, unlock, or role grant but Oleria can’t read the user back, the action succeeds, is marked as unverified, and can still be reversed. A role removal always needs a read-back that shows the role gone.
A reversal is skipped, with the reason, if the user’s state has changed since the action or an unverified change never took effect.
Oleria refuses to:
  • lock the integration user, PTWEBSERVER, a holder of the PeopleTools Web Server role, or the default user ID of the ANONYMOUS node or of the default local node, or remove a role from any of them. Either would stop the integration, or break sign-in or Integration Broker for everyone. Reversing Enable user account or Add user to role for these users is refused for the same reason.
  • change the roles of the integration user, because that could remove the access the integration depends on, or widen it.
  • grant a role that doesn’t exist or is inactive, including when reversing Remove user from role.
  • remove a role membership maintained by a dynamic-role rule, because the next rule run would add it back. Change the role’s rule in PeopleSoft instead.
  • add or remove a permission list. Permission lists are granted through roles or set on the user profile in PeopleSoft.
If the integration user isn’t allowed to grant a role, PeopleSoft refuses the change and Oleria shows PeopleSoft’s message.

​
Known limitations

  • Role removal may fail on PeopleTools 8.54 and later. Oleria removes a role through the CI_USER_PROFILE service, and some PeopleTools releases don’t apply that removal. When the role is still assigned after the change, the action fails with “role still assigned” and nothing is reported as done. The same applies to reversing Add user to role. Remove the role in PeopleSoft under User Profiles -> Roles.
  • The gateway must be reachable from the internet. Oleria connects only to a gateway with a public HTTPS address. Gateways on a private network or VPN can’t be connected.
  • MFA and SSO status are unavailable. MFA and single sign-on in front of PeopleSoft are handled by your identity provider or web tier, which QAS can’t see. An account’s sign-in method shows as SSO only when its last successful sign-in used a PeopleSoft token. Otherwise it’s unavailable.
  • No account creation date. PeopleSoft doesn’t store when a user profile was created.
  • Email can be blank. Email is optional in PeopleSoft. Accounts without an email address still carry their employee ID (EMPLID). With HR worker records, an account linked to an employee takes the employee’s business email instead.
  • Only the last failed sign-in is available. PeopleSoft keeps only the most recent failed sign-in for each user ID, so earlier failures don’t appear as activity.
  • User profile and role changes need database-level audit. Without the optional audit records and queries 14 and 15, Oleria collects sign-in activity only. With them, changes appear only from when you created the audit triggers. A change the triggers recorded without a user, for example !NoOPRID because EnableDBMonitoring was off, is skipped.
  • Unlisted batch users appear as people. PeopleSoft has no flag that marks a user profile as a service account. Add batch and run-as users to Service account user IDs so Oleria types them as service accounts.
  • Some administrators may not be flagged. Oleria flags administrators by the PeopleSoft Administrator role and by roles whose permission lists include the security components in OLR_PRIV_PERMLISTS. A permission list that grants broad access without those components isn’t flagged. Permission lists set directly on the user profile (primary, row security, process profile, and navigator homepage) don’t grant component access, so they don’t make a user an administrator or give web service access.
  • Resources aren’t inventoried. Components, pages, component interfaces, web services, query access groups, and row-level security aren’t synced, so Oleria doesn’t show what each permission list grants.
  • Node activity isn’t available. Oleria doesn’t read Integration Broker message history, so nodes have no last-used date.
  • Large databases sync gradually. Because every query runs on your application server, Oleria paces each sync to about two requests per second to the gateway. Activity is read one day at a time, up to 50,000 sign-ins per day. A day with more sign-ins is truncated. If Max Rows Fetched is set to 50000 or lower, the sync stops with an error that names the setting instead of reading partial data.
  • Action permissions are confirmed when the action runs. A gateway can describe the CI_USER_PROFILE_UP operation to a user who lacks the web service or component interface grant. In that case, the action fails when it runs, and nothing changes.
  • No hire or terminate actions. Oleria doesn’t create, change, or end HR records. To remove a leaver’s PeopleSoft access, lock their user profile with Disable user account.
  • HR worker records need an HCM or Campus Solutions database. Financials and Supply Chain, ELM, and CRM databases have no worker record, so they connect with the security model only. Their user profiles link to employees from your HR integration by email only. On Campus Solutions databases, only people with a job are read, so students and applicants without one don’t appear.
  • Row-level security filters HR data silently. PeopleSoft returns only the workers the integration user may see, without an error. Oleria detects only a large shortfall (more than 2% of the user profiles that carry an employee ID naming an employee the HR queries didn’t return) and can’t prove the grant is complete. User profiles whose employee isn’t returned link to employees by email only.
  • HR data follows effective dates. Oleria reads the rows in effect on the database’s current date. A future-dated hire appears from the hire date, a future-dated termination takes effect on its date, and a rehire removes the end date.
  • Leave doesn’t end employment. Workers on leave of absence, paid leave, a short work break, or suspension remain employees with no end date. Their leave status isn’t shown.
  • No employee status field. Employment state shows only as start and end dates. An employee gets an end date only when none of their current jobs is active. Inactive departments are included and aren’t marked as inactive.
  • Some managers aren’t resolved. A reports-to position that’s vacant or held by several people falls through to the supervisor ID, then to the department manager. Terminated employees have no manager.
  • Work locations aren’t resolved. Oleria doesn’t map job or department locations to countries.
  • Removing OLR_JOBS turns HR off. If you delete OLR_JOBS, the next sync removes the employees, people, and departments, and Oleria lists the database as a business application instead of an HR system. If OLR_JOBS or OLR_DEPARTMENTS returns no rows, the sync fails instead of removing them.

​
Contact us

For questions about this integration, contact us at support@oleria.com.