> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oleria.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Oracle PeopleSoft
> Connect Oracle PeopleSoft to Oleria as an HR system: discover HR worker records, departments, and managers from PeopleSoft HCM, plus user profiles, roles, permission lists, service accounts, Integration Broker nodes, and sign-in activity, and lock users and manage role membership.
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](mailto:support@oleria.com).
## Set up PeopleSoft for Oleria
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.
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.
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.
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`.
In **Query Manager**, create queries 1 to 13 and query 23 from [Query definitions](#query-definitions) as **public** queries, exactly as listed. Set each prompt's **Unique Prompt Name** exactly as shown, because prompt names are case-sensitive.
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](#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.
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.
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](#query-definitions). Oleria reads the role through it before each role action.
* 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).
### 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.
| # | Query | Record | Fields | Criteria (prompts) | Order by |
| :- | :- | :- | :- | :- | :- |
| 1 | `OLR_USERS` | `PSOPRDEFN` | `OPRID`, `OPRDEFNDESC`, `EMPLID`, `EMAILID`, `OPRCLASS`, `ROWSECCLASS`, `PRCSPRFLCLS`, `DEFAULTNAVHP`, `SYMBOLICID`, `ACCTLOCK`, `LASTPSWDCHANGE`, `FAILEDLOGINS`, `LASTSIGNONDTTM`, `LASTUPDDTTM`, `LASTUPDOPRID`, `USERIDALIAS`, `LANGUAGE_CD` | `A.OPRID > :OLR_AFTER` | `OPRID` |
| 2 | `OLR_USER` | `PSOPRDEFN` | Same as query 1 | `A.OPRID = :OLR_USERID` | - |
| 3 | `OLR_USER_EMAILS` | `PSUSEREMAIL` | `OPRID`, `EMAILTYPE`, `EMAILID`, `PRIMARY_EMAIL` | `A.OPRID > :OLR_AFTER` | `OPRID`, `EMAILTYPE` |
| 4 | `OLR_USER_IDTYPES` | `PSOPRALIAS` | `OPRID`, `OPRALIASTYPE`, `OPRALIASVALUE` | `A.OPRID > :OLR_AFTER` | `OPRID`, `OPRALIASTYPE` |
| 5 | `OLR_ROLES` | `PSROLEDEFN` | `ROLENAME`, `DESCR`, `ROLETYPE`, `ROLESTATUS`, `LASTUPDDTTM`, `LASTUPDOPRID` | `A.ROLENAME > :OLR_AFTER` | `ROLENAME` |
| 6 | `OLR_ROLE_USERS` | `PSROLEUSER` | `ROLEUSER`, `ROLENAME`, `DYNAMIC_SW` | `A.ROLEUSER > :OLR_AFTER` | `ROLEUSER`, `ROLENAME` |
| 7 | `OLR_USER_ROLES` | `PSROLEUSER` | `ROLEUSER`, `ROLENAME`, `DYNAMIC_SW` | `A.ROLEUSER = :OLR_USERID` | `ROLENAME` |
| 8 | `OLR_PERMLISTS` | `PSCLASSDEFN` | `CLASSID`, `CLASSDEFNDESC`, `LASTUPDDTTM`, `LASTUPDOPRID` | `A.CLASSID > :OLR_AFTER` | `CLASSID` |
| 9 | `OLR_ROLE_PERMLISTS` | `PSROLECLASS` | `ROLENAME`, `CLASSID` | `A.ROLENAME > :OLR_AFTER` | `ROLENAME`, `CLASSID` |
| 10 | `OLR_PRIV_PERMLISTS` | Union of `PSAUTHITEM` and `PSAUTHWS`, with **Distinct** selected | `CLASSID`, `FLAG` | `CLASSID > :OLR_AFTER` in both selects (see below) | `CLASSID` |
| 11 | `OLR_NODES` | `PSMSGNODEDEFN` | `MSGNODENAME`, `DESCR`, `NODE_TYPE`, `ACTIVE_NODE`, `LOCALDEFAULTFLG`, `AUTHOPTN`, the default user ID (`USERID` or `DEFAULT_USER`), `LASTUPDDTTM`, `LASTUPDOPRID`. Never the node password. | `A.MSGNODENAME > :OLR_AFTER` | `MSGNODENAME` |
| 12 | `OLR_SIGNON_AUDIT` | `PSPTLOGINAUDIT` | `PTSIGNONID`, `PT_SIGNON_STATUS`, `OPRID`, `LASTSIGNONDTTM`, `FAILEDLOGINS`, `PT_AUTH_TYPE` | `A.PTSIGNONID > :OLR_AFTER` and `%DatePart(A.LASTSIGNONDTTM)` between `:OLR_FROM_DATE` and `:OLR_TO_DATE` | `PTSIGNONID`, `PT_SIGNON_STATUS` |
| 13 | `OLR_ACCESS_LOG` | `PSACCESSLOG` | `OPRID`, `LOGIPADDRESS`, `LOGINDTTM`, `LOGOUTDTTM`, `PT_SIGNON_TYPE` | `%DatePart(A.LOGINDTTM)` between `:OLR_FROM_DATE` and `:OLR_TO_DATE` | `LOGINDTTM`, `OPRID` |
| 14 | `OLR_AUDIT_OPRDEFN` (optional) | Your database-level audit record on `PSOPRDEFN` | `AUDIT_OPRID`, `AUDIT_STAMP`, `AUDIT_ACTN`, `OPRID`, `ACCTLOCK`, `LASTPSWDCHANGE`, `OPRCLASS`, `ROWSECCLASS`, `PRCSPRFLCLS` | `%DatePart(A.AUDIT_STAMP)` between `:OLR_FROM_DATE` and `:OLR_TO_DATE` | `AUDIT_STAMP`, `OPRID` |
| 15 | `OLR_AUDIT_ROLEUSER` (optional) | Your database-level audit record on `PSROLEUSER` | `AUDIT_OPRID`, `AUDIT_STAMP`, `AUDIT_ACTN`, `ROLEUSER`, `ROLENAME`, `DYNAMIC_SW` | `%DatePart(A.AUDIT_STAMP)` between `:OLR_FROM_DATE` and `:OLR_TO_DATE` | `AUDIT_STAMP`, `ROLEUSER` |
| 16 | `OLR_JOBS` (optional; turns on HR worker records) | `JOB` | `EMPLID`, `EMPL_RCD`, `EFFDT`, `EFFSEQ`, `PER_ORG`, `EMPL_STATUS`, `HR_STATUS`, `ACTION`, `ACTION_REASON`, `JOB_INDICATOR`, `COMPANY`, `BUSINESS_UNIT`, `SETID_DEPT`, `DEPTID`, `SETID_JOBCODE`, `JOBCODE`, `POSITION_NBR`, `REPORTS_TO`, `SUPERVISOR_ID`, `SETID_LOCATION`, `LOCATION`, `REG_TEMP`, `FULL_PART_TIME`, `EMPL_CLASS`, `HIRE_DT`, `LAST_HIRE_DT`, `TERMINATION_DT`, `LAST_DATE_WORKED`, `LASTUPDDTTM`, `LASTUPDOPRID` | The default effective-date criterion, kept; `A.EMPLID > :OLR_AFTER` | `EMPLID`, `EMPL_RCD` |
| 17 | `OLR_WORKER_NAMES` (required with query 16) | `NAMES` | `EMPLID`, `NAME_TYPE`, `EFFDT`, `NAME_DISPLAY`, `FIRST_NAME`, `MIDDLE_NAME`, `LAST_NAME`, `LASTUPDDTTM` | The default effective-date criterion; `A.NAME_TYPE` in `'PRI'` and `'PRF'`; `A.EMPLID > :OLR_AFTER`; an exists subquery on `JOB` (see below) | `EMPLID`, `NAME_TYPE` |
| 18 | `OLR_WORKER_EMAILS` (optional) | `EMAIL_ADDRESSES` | `EMPLID`, `E_ADDR_TYPE`, `EMAIL_ADDR`, `PREF_EMAIL_FLAG` | `A.E_ADDR_TYPE` in `'BUSN'` and `'HOME'`; `A.EMPLID > :OLR_AFTER`; an exists subquery on `JOB` (see below) | `EMPLID`, `E_ADDR_TYPE` |
| 19 | `OLR_DEPARTMENTS` (required with query 16) | `DEPT_TBL` | `SETID`, `DEPTID`, `EFFDT`, `EFF_STATUS`, `DESCR`, `DESCRSHORT`, `COMPANY`, `MANAGER_ID`, `MANAGER_POSN`, `SETID_LOCATION`, `LOCATION` | The default effective-date criterion; `A.DEPTID > :OLR_AFTER` | `DEPTID`, `SETID` |
| 20 | `OLR_JOBCODES` (optional) | `JOBCODE_TBL` | `SETID`, `JOBCODE`, `EFFDT`, `EFF_STATUS`, `DESCR`, `JOB_FAMILY`, `JOB_FUNCTION` | The default effective-date criterion; `A.JOBCODE > :OLR_AFTER` | `JOBCODE`, `SETID` |
| 21 | `OLR_COMPANIES` (optional) | `COMPANY_TBL` | `COMPANY`, `EFFDT`, `EFF_STATUS`, `DESCR` | The default effective-date criterion; `A.COMPANY > :OLR_AFTER` | `COMPANY` |
| 22 | `OLR_DEPT_TREE` (optional) | `PSTREENODE` | `SETID`, `TREE_NAME`, `EFFDT`, `TREE_NODE`, `PARENT_NODE_NAME`, `TREE_LEVEL_NUM`, `TREE_NODE_NUM` | Delete the default effective-date criterion, then `A.TREE_NAME = :OLR_TREE`; `A.EFFDT` equal to a tree-version subquery (see below); `A.TREE_NODE > :OLR_AFTER` | `TREE_NODE`, `SETID` |
| 23 | `OLR_ROWCAP_PROBE` | `PSRECFIELD` | `RECNAME`, `FIELDNAME` | None | - |
| 24 | `OLR_ROLE` (for role actions) | `PSROLEDEFN` | Same as query 5 | `A.ROLENAME = :OLR_ROLENAME` | - |
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
| PeopleSoft grant | Used for |
| :- | :- |
| Web service operation `QAS_LISTQUERY_REST_GET`, Full Access | Checking that the queries exist when you connect. |
| Web service operation `QAS_EXECUTEQRY_REST_GET`, Full Access | Running the queries for every sync, and the reads before and after each action. |
| Access group `OLR_SECURITY_AG` on tree `OLR_SECURITY_QRY_TREE`, with **Max Rows Fetched** `0` or above `50000` | Reading the records behind the queries. |
| Access group `OLR_HR_AG` on tree `OLR_SECURITY_QRY_TREE` | Reading the HR records behind queries 16 to 22. Required for HR worker records. |
| A row security permission list on the integration user's profile with data permission over every worker, including terminated workers | Returning every worker from the HR queries. Required for HR worker records. |
| Web service operation `CI_USER_PROFILE.CI_USER_PROFILE_UP`, Full Access | Required for actions. |
| Component interface `USER_PROFILE` (**Get**, **Save**, **Cancel**, **Find**), Full Access | Required for actions. |
| The target roles on the **Role Grant** page of `OLR_INTEGRATION_ROLE` | Required for the role actions. |
| Query 24, `OLR_ROLE` | Reading the role before each role action. Required for the role actions. |
## Connect Oracle PeopleSoft to Oleria
Go to your Oleria workspace, select **Integrations** -> select **Oracle PeopleSoft**.
| Field | Notes |
| :- | :- |
| Base URL | Required. The public HTTPS address of the Integration Broker gateway, for example `https://hr.example.com`. A trailing `/PSIGW` is accepted. Oleria doesn't follow redirects, so use the gateway's final address. You can't change it after the integration is saved. |
| Integration Broker node | Required. The default local node name you recorded, for example `PSFT_HR`. You can't change it after the integration is saved. Oleria identifies the database by the gateway address and the default local node it reads from `OLR_NODES` when you connect, so connecting the same database again through the same gateway is refused, even with another node name. Use one gateway address per database. |
| PeopleSoft user ID | Required. The user ID of the integration user, for example `OLR_INTEGRATION`. |
| Password | Required. The integration user's password. When you edit the integration later, leave it blank to keep the stored password. A new user ID needs its password. |
| Service account user IDs | Optional. A comma-separated list of user IDs to inventory as service accounts, such as batch or run-as users, for example `PS_BATCH, PSAPPS`. The integration user, node default users, and `PTWEBSERVER` are detected automatically. |
| Database time zone | Optional. The IANA time zone name of the PeopleSoft database, in its exact case, for example `America/Chicago`. A UTC offset such as `-06:00` isn't accepted. Oleria uses it for timestamps that carry no offset. The default is `UTC`. |
| Department tree | Optional, and used only with HR worker records. The name of the department security tree that `OLR_DEPT_TREE` reads for the department hierarchy. Up to 18 letters, digits, underscores, or hyphens. Oleria converts it to upper case. The default is `DEPT_SECURITY`. |
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](#set-up-peoplesoft-for-oleria):
| Action | What it does | Reversible? |
| :- | :- | :- |
| Disable user account | Locks the user profile. Roles and data are untouched. | Yes - reversed by unlocking the profile. |
| Enable user account | Unlocks the user profile. | Yes - reversed by locking the profile. |
| Add user to role | Grants the role to the user. | Yes - reversed by revoking the role. |
| Remove user from role | Revokes the role from the user. | Yes - reversed by granting the role again. |
| Disable a non-human identity | Locks a service-account user profile. Integration Broker nodes aren't supported, because PeopleSoft has no API to deactivate a node. To stop a node, lock its default user ID instead. Oleria won't lock the default user ID of the `ANONYMOUS` node or of the default local node, because that would break Integration Broker for everyone. | Yes - reversed by unlocking the profile. |
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](mailto:support@oleria.com).
This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.