A Look at the PSPTLOGINAUDIT Security Table

I was debugging some security code recently and discovered a table that I never new existed. The table is PSPTLOGINAUDIT. I am not sure in which tools release this table was introduced.

There is a section in the 8.54 PeopleBooks Tracking User Sign-In Attempts.

According to PeopleBooks:

For every sign on attempt to the PeopleSoft Pure Internet Architecture the following information can be logged:

• Information about the last successful login, including timestamp and authentication type.
• Information about the last failed attempt, including timestamp, failed logic count and authentication type.

You have to enable this in the application server configuration.

The key point here is that it is storing the “last login” or “last attempt”. I previously wrote about the PSACCESSLOG PeopleTools Table which will log all successful logins.

The keys to the PSPTLOGINAUDIT table are PTSIGNONID (user id entered on login screen) and PT_SIGNON_STATUS (0 = SUCCESS, 1 - FAILURE).

Here is some SQL to find login attempts that seem “abnormal.”

select * from PSPTLOGINAUDIT 
WHERE LASTSIGNONDTTM > SYSDATE - 30 -- LAST 30 DAYS
AND FAILEDLOGINS > 6
and PT_SIGNON_STATUS = 1 -- failure
ORDER BY FAILEDLOGINS DESC ;

Remember that as soon as a user performs a success login, the data in PSPTLOGINAUDIT will be wiped out and replaced with the successful login for that user. So if you were trying to use this to find users that were undergoing a brute forcing password attack on a known user, it might not be too helpful in retrospect. As soon as that user successfully logged in, the FAILEDLOGINS would be reset. So you would need to query this table very frequently to find brute force attempts on known users.

This SQL will find people in the last 30 days whose last login came from another PeopleSoft pillar. This would be from some PeopleSoft to PeopleSoft “trusted node” login.

select * from PSPTLOGINAUDIT
where pt_auth_type = 0 -- token - Psoft to psft sso 
and LASTSIGNONDTTM > SYSDATE - 30
;

In my testing, if you try to login via a PeopleSoft login page and you enter “FAKEUSER” (a known fake use that does not exist in PSOPRDEFN) on the login screen and some arbitrary password, that entry is NOT stored in the PSPTLOGINAUDIT table which seems strange. This seems to be an over-site. I am guessing because “FAKEUSER” does not match to a row in the PSORPDEFN table. I am not sure if this is feature or in the design. It would be nice if these attempts where logged so you could monitor for brute force attacks.

If you are using signon PeopleCode to authenticate to a third party like LDAP, then you can potentially find what user ID was entered on the login screen and what user they successfully logged in as by using this.

select * from PSPTLOGINAUDIT 
WHERE PT_AUTH_TYPE = 2 -- SIGNON PeopleCode 
AND LASTSIGNONDTTM > SYSDATE - 30 -- LAST 30 DAYS
;

PTSIGNONID will have the user entered at the LOGIN screen. OPRID will be the user id that the sign-on PeopleCode resolved that user to. It does not always have to be the same. You might find some interesting nuggets there. In my testing, it seems you will never see a FAILEDLOGINS > 0 when looking at PT_AUTH_TYPE = 2. This may be by design.

Let me know if you have any other creative SQL or use cases for this table.

Additional Reading

• Tracking User Sign-In Attempts.
• PSACCESSLOG PeopleTools Table