Security

Development Roles

In your development environment, you will typically have super user access. Most people achieve this by cloning the PS or similar delivered super user. The problem with this is that you end up with a large number of roles (138 in the case of Campus Solutions) which you don't actually need.

Having too many roles can actually slow things down as PeopleSoft needs to look at your security every time you navigate around or do something.

Here's all the roles you really need:

Role Purpose
PeopleSoft Administrator The All Powerful PeopleSoft Administrator Role gives you full access to the portal and menus
PeopleSoft User Basic role that all PeopleSoft users need
ProcessSchedulerAdmin Process scheduler administrator (let's you see view log on everyone's processes)
ReportDistAdmin Report manager administrator (let's you see other people's reports)
XMLP Report Developer XML Publisher Report Developer
Your Institution's Development Role The role used by your institution for development security - if you use one

This should speed things up for you slightly.

Get roles and permissions for a particular user

The following query will return the roles and permission lists that a particular user belongs to. Replace the OPRID value in the line RU.ROLEUSER = 'OPRID' with the operator ID of the user you are interested in (e.g. PS).

select
    RU.ROLEUSER as OPRID,
    RU.ROLENAME as ROLE,
    RC.CLASSID as PERMISSION_LIST
from
    PSROLEUSER RU inner join PSROLECLASS RC
    on RU.ROLENAME = RC.ROLENAME
where
    RU.ROLEUSER = 'OPRID'
order by ROLE, PERMISSION_LIST

Roles

Roles are used by PeopleSoft to implement security.

A role is way of grouping one or more Permission Lists and assigning them to a particular user.

Useful Delivered PeopleTools Roles:

What do they do?

Dynamic Roles not Assigned

While working with dynamic roles, I came across a problem where users were not being assigned a dynamic role despite:

Turns out that the ROLESYNCHEXT_MSG service operation did not have any routings.

ROLESYNCHEXT_MSG-routings.png

To fix, generate the missing routings for this service operation by checking the appropriate generate boxes and saving the routing.

You may also find that there is a bit of a lag between running the dynamic role publishing application engine program DYNROLE_PUBL and when the role is actual assigned as integration broker may be busy processing other messages.

Organization Defaults by Permission List

Certain organization defaults can be set on permission lists in HRMS/Campus Solutions under:

Set Up HRMS > Foundation Tables > Organization > Org Defaults by Permission List

This includes:

As well as some other settings regarding industry, industry sector, payroll system and standard hours. Behind the scenes the key table is PS_OPR_DEF_TBL_HR which sets some user defaults based on the user's primary permission list. This is used in a lot of places for defaulting data so if you ever create a new primary permission list make sure you have configured appropriate settings for your permission list here too.

PeopleSoft Security Hierarchy

To understand PeopleSoft security, it it helps to look at the hierarchy for PeopleSoft security. From top-to-bottom (highest to lowest level of granularity) you can look at security like this:

  1. Portal Security
  2. Menu Security
  3. Roles
  4. Permission Lists and Definition Security
  5. Primary Permission Lists
  6. Row Level Security (user & permission list based)

To explain further:

PeopleSoft Administrator Role

The PeopleSoft Administrator role is all an all powerful role that contains one permission list PSADMIN. This is a special system role which cannot be modified. The underlying permission list, PSADMIN also cannot be modified. In fact this role and permission list is invisible in almost all parts of PeopleTools security except for managing user profiles.

Note that you can view this role & permission list in the underlying PeopleTools Security tables. If you're wondering how they are hidden, logic to exclude this role and permission list is actually hardcoded into the search views PSROLEDEFN_SRCH and PSCLASSDEFN_SRC.

According to Oracle, PeopleSoft code has been hardcoded to grant special access to this role & permission list. That is why the permission list PSADMIN does not appear at all when you select for it from PSAUTHITEM or PSPRSMPERM.

The PeopleSoft Administrator role gives a user full access to all menus (components), pages, component interfaces, web libraries, PeopleTools and just about anything else that can be secured through roles & permission lists. This does not include row level & primary permission list security.

Because this the role is all powerful, you'll want to be careful about who has it. You might even want to go and check that now in your system ...

Dynamic Roles

Roles (and permission lists) can be assigned to users dynamically in PeopleSoft using dynamic role rules. The logic used to assign a dynamic role can be in the form of query, PeopleCode or directory (e.g. LDAP) rule.

The application engine program DYNROLE_PUBL assigns dynamic roles by using the logic in the query, PeopleCode or directory rule to obtain a list of operator IDs. The role itself is assigned by the ROLESYNCHEXT_MSG service operation through internal messenging and integration broker.

You can see which dynamic roles are assigned to a user in the user profile page under the roles tab like any other role. You can also open the dynamic role and view dynamic members (users) of that role. Both areas allow you to test and execute the dynamic role rule. The test rule button returns users that will be given the dynamic role, however it does not assign the role. The execute rule actually assigns the dynamic role by running the DYNROLE_PUBL application engine program.

In addition to using a dynamic role to assign security through one or more permission lists, you may also want to use a dynamic role simply as a tag.

For example you might give students a role of Alumni if they have completed a degree at your University. The logic to determine whether they have completed a degree could be in the form of a query or PeopleCode rule. As students are awarded degrees, they are automatically given the dynamic role. If for some reason, a degree is withdrawn, the role will automatically be removed.

PeopleCode could then simply check if a user has the Alumni role and then show or hide appropriate functionality.

It is important to know that dynamic roles are added and removed automatically based on the results of the rule. You only need to write the rule to determine those users that should get the role.

PeopleSoft will add the role to those users that meet the rule criteria and remove it from those users that have the dynamic role but no longer meet the rule criteria. For example a student with an Alumni dynamic role, who has had their degree withdrawn would lose that role dynamically.

Definition Security

Definition Security is used to control access to PeopleSoft definitions through Application Designer. By default a group called PEOPLETOOLS is delivered. Within this group are all the definitions that are read-only to all users except those with permission lists that contain the group.

If a definition is secured through definition security, you'll soon know about it when you open it in Application Designer and get the message:

definition_security_not_authorized.png

You are not authorized to update the field because it belongs to the PeopleTools group and chances are you won't have security access to the group.

The definition security tool is available through Application Designer:

Go > Definition Security

It is a simple tool to manage definition security groups and associate them with permission lists. To use it, choose:

File > Open > Group
Select the default group: PEOPLETOOLS

This brings up a list of all definitions in the PEOPLETOOLS group. Select your definition type from the drop down (e.g. Records) and let it load all records (can take some time).

definition-security-tool.png

Anything on the left hand side is not excluded from this group, which means that it cannot be altered. Anything on the right hand side is excluded and can be altered. So, find the appropriate definition and move it from the left to the right hand side (not excluded to excluded) using the single right arrow. Double arrows move everything from one side to the other. Finally chose, File > Save to save your changes. Your object can now be modified. You should always put the definition back into the group after you're done. So repeat the process moving from the right hand side (excluded list) to the left hand side (not excluded).

The SQL below shows fields in the PeopleTools group (secured) and those excluded from the group (free for developers to work with).

The underlying tables for definition security include:

By default you can see that there is no permission list with access to the PEOPLETOOLS object group:

select * 
from PSOPROBJ 
where OBJGROUPID = 'PEOPLETOOLS' 
and DISPLAYONLY <> 1;

What this query is searching for is any rows in the PSOPROBJ record that have the PEOPLETOOLS group included (with a value that is not display only which means excluded).

Field Level Auditing

There are two types of delivered auditing in PeopleSoft:

Field level auditing tracks changes to one or more fields on a PeopleSoft record and stores the results in the delivered PSAUDIT table.

Like record level auditing, field level auditing only applies to data updated through PeopleSoft and not directly through the database. So auditing will not apply to data updated by SQL scripts, SQRs and COBOLs unless they are specifically designed to update the audit table.

To enable field level auditing, simply update the record-field properties of the relevant fields on the PeopleSoft record. For instance, the following screenshot shows how to audit field add, change and delete, on the PROJECTNAME field of PSPROJECTDEFN:

field-audit-projectname.png

Once enabled, make changes to the relevant field of the relevant PeopleSoft record online and confirm your changes are audited by querying the table PSAUDIT.

The record PSAUDIT stores the old and new values of the field along with the audit action (e.g. A for Add, D for Delete and C for Change). It also stores the record name and key fields of of row in the PeopleSoft record that was modified so that you can track back to it. These are the values that appear in the fields KEY1 ... KEY15.

So for example, if you had field level auditing enabled on the field LASTUPDOPRID of the record PSPROJECTDEFN and the LASTUPDOPRID was changed, you would see:

Field auditing is good for tracking a few fields on a record. It becomes cumbersome to track when you have many fields on a particular record being audited. This is where record level auditing is more appropriate, particularly when you want to audit many or all changes on a record.

Record level auditing also uses its own audit table while field level auditing uses the delivered PSAUDIT record which can get very large if a lot of auditing is enabled.

PeopleTools Security

A summary of the main PeopleTools security records.

Operators (Users)

Record Description
PSOPRDEFN Stores all operators (users) in the PeopleSoft system. Also stores their employee ID (EMPLID), encrypted password, primary permission list, default navigator home page, process profile permission list and row security permission list.
PSROLEUSER The highest level of security access is defined by roles (think of them as groups). This table stores the roles the user belongs to.
PSOPRCLS Roles link together permission lists which are the security objects that define access to components, pages, and other areas of the system. This view returns the permission lists that a user has access to via their roles. Note that prior to PeopleTools 8, permission lists were synonymous with classes and most of the security tables still use this convention.
PSOPRALIAS Aliases can be mapped to a particular operator ID (user). The obvious alias is employee ID (EMPLID) but others include external organisation ID (EXT_ORG_ID) and customer ID (CUST_ID). All ways of referring to the same entity.
PSOPRALIASTYPE This is the setup table for operator aliases
PSOPRALIASFIELD This is the setup table that maps operator aliases to records & fields
PSUSERATTR User attributes store the a hint password question & response for a user (if this is enabled)
PSUSEREMAIL Email addresses for users.

Roles

Record Description
PSROLEDEFN Stores roles and their properties. Roles can be assigned dynamically through Query, PeopleCode or LDAP. Roles are also used in conjunction with Workflow and routing.
PSROLECLASS Roles are made of up of one or permission lists, and this table links the two together. Very handy.

Permission Lists

Record Description
PSCLASSDEFN Permission lists are where the security really happens. They provide access to menus, components and pages and a host of other security including PeopleTools, Process security, Component Interfaces, Web Libraries, Web Services, Personalisations, Query and Mass Change.
PSAUTHITEM The link between permission lists and menus
PSAUTHBUSCOMP The link between permission lists and component interfaces and their methods
PSAUTHOPTN The link between permission lists and personalisations
PSAUTHPRCS The link between permission lists and process groups
PSAUTHSIGNON The link between permission lists and signon times
PSAUTHWEBLIBVW A view linking permission lists and access to web libraries (really just Menus in PSAUTHITEM that begin with WEBLIB_).
PSAUTHWS The link between permission lists and web services (service operations)
PS_SCRTY_ACC_GRP The link between permission lists, trees and query access groups
PS_MC_OPR_SECURITY The link between permission lists and mass change templates. This is an odd table, it uses the field OPRID but really it links permission lists

Portal

The PeopleSoft Enterprise Portal basically refers to the navigation system that links menus and components to the PeopleSoft online interface, navigation and search functionality. Access to anything in the online application is handled through portal security. The records associated with this include:

Record Description
PSPRSMDEFN Stores the structure of the portal registry. This data is stored in a hierarchical (tree) structure within the table. The field PORTAL_URI_SEG1 is the menu, PORTAL_URI_SEG2 is the component, and PORTAL_URI_SEG3 is the market.
PSPRSMPERM Stores permission lists associated with access to everything within the portal registry

Which Users have access to a Page

Find out which users (and from what permission lists) have access to a particular page:

select distinct OPRID, OPRCLASS
from PSOPRCLS
where OPRCLASS in (
    select distinct CLASSID
    from sysadm.PSAUTHITEM
    where PNLITEMNAME = '{PAGENAME}'
);

PeopleTools Access

PeopleTools access is set by permission list:

PeopleTools > Security > Permissions & Roles > Permission Lists > {Open a Permission List} > PeopleTools tab

The PeopleTools access you will find on this page is for:

The table PSAUTHITEM is what determines which permission lists have access to what PeopleTools through menus.

So you need to know the menu associated with each of the PeopleTools applications:

PeopleTools Application Menu
Application designer APPLICATION_DESIGNER
Data mover DATA_MOVER
Definition Security OBJECT_SECURITY
Query access QUERY
Performance Monitor PPMI PERFMONPPMI

How did I work all this out? Open the Permission Lists > PeopleTools page in application designer (its called ACL_MISCTOOLS, then click view definition on each of the PeopleTools links (Application Designer, Data Mover etc). Each link is stored in the work record MISCTOOLS_WRK and the PeopleCode for the fields on this record contain the queries against PSAUTHITEM. Note that the application designer one is a little bit more tricky as it contains secondary pages.

Once you know the menus, it is just a matter of querying the relevant menus, finding permission lists with access, and identifying which users have those permission lists. Here's a query that does this for the APPLICATION_DESIGNER menu:

select
    OC.OPRID,
    OC.OPRCLASS
from 
    PSOPRCLS OC
where 
    OPRCLASS = 'PSADMIN' or OPRCLASS in (
        select  CLASSID
        from    PSAUTHITEM        
        where   MENUNAME = 'APPLICATION_DESIGNER'    
        and     AUTHORIZEDACTIONS >= 1
    )
order by OC.OPRID, OPRCLASS

Note that this query takes into account the PeopleSoft Administrator role (permission list PSADMIN) as users with this role will also have access to PeopleTools.

This query only checks authorized actions is > 1 (some action available - e.g. add, update, update/display or correction). If you need to know exactly what the authorized actions are, you would need to return each bar in the menu and check the authorized actions.

Dynamic Roles with PeopleCode Rule

Dynamic roles can be assigned using query rules, PeopleCode rules or directory rules.

Using PeopleCode rules allows you to use more flexible logic as you have the full power of PeopleCode to work with. This article gives an example of how it is done.

The steps are:

Setting up the Role

Navigate to:

PeopleTools > Security > Permissions & Roles > Roles

Where you can add your new role. Configure your role appropriately, and add the relevant permission lists.

In the Dynamic Members tab, check the PeopleCode Rule Enabled check box. Then enter the Record, Field Name, Event and Function name of the PeopleCode function that will be assigning the dynamic role. Here's an example of how this might look:

example-dynamic-role-peoplecode-rule.png

Assigining the role with PeopleCode

Now you can create the PeopleCode to assign the dynamic role to the %RoleDynamicMembers system array. In this example, I use a SQL object that returns a list of operator IDs which are then pushed in the %RoleDynamicMembers array one at a time.

Function AssignExampleRole()
 
   Local string &sOprid;
   Local SQL &sqlGET_EXAMPLE_USERS;
 
   &sqlGET_EXAMPLE_USERS = GetSQL(SQL.GET_EXAMPLE_USERS);
 
   While &sqlGET_EXAMPLE_USERS.Fetch(&sOprid)
      %RoleDynamicMembers.Push(&sOprid);
   End-While;
 
End-Function;

The logic to get your users lives in your SQL definition. All your SQL needs to return is a list of operator IDs. For example it could be as simple as the example below, which finds all users with a description starting with Smith.

SELECT OPRID 
  FROM %Table(PSOPRDEFN) 
 WHERE OPRDEFNDESC %Like('Smith')

If you need to do any pre-processing, you can do that in the while loop before pushing the operator ID to the %RoleDynamicMembers array.

You can test your rule in the dynamic members page using the Test Rule(s) button. Note that this may fail if your working on a large set of operator IDs. Use the Execute Rule(s) button on the dynamic members page to assign the role using your new role.

If required you can combine different rules. For example you use both a Query and PeopleCode rule.
NOTE: if for some reason a user no longer meets the criteria for a dynamic role, PeopleSoft will automatically remove that role from them. So you only need to write the logic to determine which users should have a role and PeopleSoft will take care of removing it when it should no longer be given to the user.

Hiding Portal Folders

A common security requirement is to hide a portal folder in the left hand navigation menu from users. For example, the PeopleTools folder.

This article goes through the approaches available and how to do it correctly.

Hiding a Folder from Portal Navigation

One approach is to go into Portal Structure and Content:

PeopleTools > Portal > Structure and Content

Edit the relevant folder, and check the Hide from portal navigation tick box. The problem with doing this is that hides the folder from EVERYONE. Fortunately there is an override - it will not be hidden from anyone with the PeopleSoft Administrator role. This is quite handy - if you were to hide the PeopleTools folder this way you could be in a bit of strife otherwise!

hide-from-portal-navigation.png

I would only suggest using this approach if you are sure that no one in the organisation will ever need to see that folder and the items underneath it. If there is even one user that this does not apply for (other than a PeopleSoft administrator) then you will need to use permission list security.

Hiding a Folder using Security

Another approach is take away permission lists from the folder security tab in portal structure and content. This seems to work fine until someone comes along and runs portal security sync.

The correct way to show or hide folders is by editing menu and component security in a permission list then running portal security sync to adjust folder security accordingly. Doing it this ways ensures that the one and only reason why a user can see something is because they have access to a permission list (through a particular role) that allows them to.

However you should not be modifying delivered permission lists. Always clone existing permission lists and modify cloned versions. This ensures that next time you patch your security will not be overwritten!

One common issue with this approach is caching. The portal navigation menu is cached and security changes aren't always reflected immediately. You'll typically find that the folder hangs around in the left hand navigation menu even though you've taken away security. Then when you click on a content reference gives the you are not authorized message.

You can tell if you have a caching problem by querying the PSPRSMPERM table to see if your permission list still has access to the relevant folder (e.g. PT_PEOPLETOOLS for the PeopleTools folder). If it doesn't then its a caching issue.

To get around this, use the purge web server memory cache servlet command by issuing the following URL for your environment:

https://{server}/psp/{site}/?cmd=purge&pwd={password}

So at the signon screen, replace ?cmd=login at the end of the URL with ?cmd=purge&pwd=dayoff. Note that dayoff is the default PeopleSoft web profile password.

If you have any issues with running this command or the password see the article on servlet directives.

What is Audited?

When there's an issue, a frequent question that comes up is: what is being audited? This is closely followed by, "can we tell what was changed, and by whom?"

The following SQL will help you determine which records and fields have field and record auditing enabled.

NOTE: this article is specific to PeopleSoft auditing, and does not include trigger based auditing or other database level auditing.

Record Auditing

The following SQL identifies all records that have an audit record associated with them:

select
    RECNAME,
    RECDESCR,
    AUDITRECNAME,
    RECUSE    
from PSRECDEFN
where AUDITRECNAME != ' '
order by RECNAME;

The RECUSE field is a bit field with the following combinations:

So if you have a RECUSE value of 7, that is a combination Add (1) + Change (2) + Delete (4) which gives you a total of 7.

In Oracle, you can use the bitand operator to work this out for you like this:

select
    RECNAME,
    RECDESCR,
    AUDITRECNAME,
    case when bitand(RECUSE,1) > 0 then 'Y' else 'N' END AUDIT_ADD,
    case when bitand(RECUSE,2) > 0 then 'Y' else 'N' END AUDIT_CHANGE,
    case when bitand(RECUSE,4) > 0 then 'Y' else 'N' END AUDIT_DELETE,
    case when bitand(RECUSE,8) > 0 then 'Y' else 'N' END AUDIT_SELECTIVE
from PSRECDEFN
where AUDITRECNAME != ' '
order by RECNAME;

In SQL Server, use the & operator like so:

select
    RECNAME,
    RECDESCR,
    AUDITRECNAME,
    RECUSE,
    case when RECUSE & 1 > 0 then 'Y' else 'N' end AUDIT_ADD,
    case when RECUSE & 2 > 0 then 'Y' else 'N' end AUDIT_CHANGE,
    case when RECUSE & 4 > 0 then 'Y' else 'N' end AUDIT_DELETE,
    case when RECUSE & 8 > 0 then 'Y' else 'N' end AUDIT_SELECTIVE
from PSRECDEFN
where AUDITRECNAME != ' '
order by RECNAME

One problem you may run into is that not every field is audited in the audit record. The following SQL gives the fields that match between the record and the audit record (which means they are actually audited):

select
    D.RECNAME, 
    D.RECDESCR,
    D.AUDITRECNAME,
    D.RECUSE,
    FA.FIELDNAME,
    F.FIELDNUM
from 
    PSRECDEFN D inner join PSRECFIELD F
    on  D.RECNAME = F.RECNAME
    inner join PSRECFIELD FA
    on  FA.RECNAME = D.AUDITRECNAME
    and FA.FIELDNAME = F.FIELDNAME    
where 
    D.AUDITRECNAME != ' '    
order by D.RECNAME, F.FIELDNUM;

Field Auditing

The following SQL identifies fields on records that have field level auditing to PSAUDIT enabled. Note that it uses the USEEDIT field, specifically the following:

This is the script to use for an Oracle database:

select
    F.RECNAME,
    F.FIELDNUM,
    F.FIELDNAME,
    F.USEEDIT,
    case when bitand(F.USEEDIT,8) > 0 then 'Y' else 'N' end AUDIT_FIELD_ADD,
    case when bitand(F.USEEDIT,128) > 0 then 'Y' else 'N' end AUDIT_FIELD_CHANGE,
    case when bitand(F.USEEDIT,1024) > 0 then 'Y' else 'N' end AUDIT_FIELD_DELETE
from
    PSRECFIELD F
where
    F.FIELDNAME = (
        select
            case when (
                bitand(USEEDIT,8) > 0 or
                bitand(USEEDIT,128) > 0 or
                bitand(USEEDIT,1024) > 0
            ) then FIELDNAME else '' end as FIELD_AUDITED
        from PSRECFIELD
        where RECNAME = F.RECNAME
        and FIELDNAME = F.FIELDNAME
    )
order by F.RECNAME, F.FIELDNUM;

This is the equivalent script for a SQL Server database:

select
    F.RECNAME,
    F.FIELDNUM,
    F.FIELDNAME,
    F.USEEDIT,
    case when F.USEEDIT & 8 > 0 then 'Y' else 'N' end AUDIT_FIELD_ADD,
    case when F.USEEDIT & 128 > 0 then 'Y' else 'N' end AUDIT_FIELD_CHANGE,
    case when F.USEEDIT & 1024 > 0 then 'Y' else 'N' end AUDIT_FIELD_DELETE
from
    PSRECFIELD F
where
    F.FIELDNAME = (
        select
            case when (
                USEEDIT & 8  > 0 or
                USEEDIT & 128  > 0 or
                USEEDIT & 1024  > 0
            ) then FIELDNAME else '' end as FIELD_AUDITED
        from PSRECFIELD
        where RECNAME = F.RECNAME
        and FIELDNAME = F.FIELDNAME
    )
order by F.RECNAME, F.FIELDNUM