Peoplesoft Login Process

This Article explains how People soft login process happens behind the scene.

Peoplesoft login process hits 4 following tables.

1.PSDBOWNER
2.PSSTATUS
3.PSOPRDEFN
4.PSACCESSPRFL.

Step 1:

When you give database name , user id & password in the application designer login, it will search the corresponding database name in tnsnames.ora.Once it found the entry it will login to the database with connect id.

  why we call connect id?
                Peoplebooks says initial connection to database, but why we need initial connection. So here is the reason behind it, we need to access 4 tables to authenticate the user id and password. So you are "connecting" to database with Connect id.

Connection 1: people/peop1e
Here is the snippet.

 1-3      21.08.46    1.003000 Cur#0.1072.FSCMD92 RC=0 Dur=0.665000 Create Connection Info=Primary/FSCMD92/people/ Handle=0000000002A80C50
 1-4      21.08.46    0.043000 Cur#1.1072.notSamTran RC=0 Dur=0.043000 Open Cursor Handle=0000000002A80C50

Step 2:
Once initial connection established it will issue following statement.

         select ownerid from ps.psdbowner where dbname='FSCMD92';

so output will be SYSADM.

Here Why we need sysadm?

Our peoplesoft database resides under sysadm schema. So oracle is driven by schema.tablename.Thats why we are identifying the peoplesoft schema name.This schema name will be used for further query

Here is the snippet
 1-5      21.08.46    0.024000 Cur#1.1072.FSCMD92 RC=0 Dur=0.000000 COM Stmt=SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME=:1
 1-6      21.08.46    0.000000 Cur#1.1072.FSCMD92 RC=0 Dur=0.000000 Bind-1 type=2 length=7 value=FSCMD92

Step 3:

After that it will check the toolsrelease from psstatus. Here if you look at the snippet it is using SYSADM.PSSTATUS. for this we are having the above psdbowner table.

SELECT OWNERID, TOOLSREL, TO_CHAR(LASTREFRESHDTTM,'YYYY-MM-DD-HH24.MI.SS'), TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD-HH24.MI.SS') FROM SYSADM.PSSTATUS

 1-7      21.08.46    0.377000 Cur#1.1072.FSCMD92 RC=0 Dur=0.215000 COM Stmt=SELECT OWNERID, TOOLSREL, TO_CHAR(LASTREFRESHDTTM,'YYYY-MM-DD-HH24.MI.SS'), TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD-HH24.MI.SS') FROM SYSADM.PSSTATUS

Step 4:

in this step it will just compare your user id and password with psoprdefn table, if its valid then it will take the corresponding symbolic id.

SELECT VERSION, OPERPSWD, OPERPSWDSALT, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = 'VP1';

Output will be SYSADM1

Here is the snippet

 1-9      21.08.46    0.002000 Cur#1.1072.FSCMD92 RC=0 Dur=0.000000 COM Stmt=SELECT VERSION, OPERPSWD, OPERPSWDSALT, ENCRYPTED, SYMBOLICID, ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = :1

Why we call the name as symbolic id?
each peoplesoft id should have access to peoplesoft database, so instead of exposing access id directly we are "symbolically" saying this is my access id, so its called symbolic id.

Step 5:

Once it finds the symbolic id from the above statement , it will take access id from PSACCESSPRFL.

SELECT ACCESSID, ACCESSPSWD, ENCRYPTED FROM SYSADM.PSACCESSPRFL WHERE SYMBOLICID = 'SYSADM1';

Output will be encrypted format of access id and access password.

Snippet here

 1-11     21.08.46    0.276000 Cur#1.1072.FSCMD92 RC=0 Dur=0.000000 COM Stmt=SELECT ACCESSID, ACCESSPSWD, ENCRYPTED FROM SYSADM.PSACCESSPRFL WHERE SYMBOLICID = :1
 1-12     21.08.46    0.000000 Cur#1.1072.FSCMD92 RC=0 Dur=0.000000 Bind-1 type=2 length=7 value=SYSADM1
 1-13     21.08.46    0.192000 Cur#1.1072.FSCMD92 RC=0 Dur=0.000000 Disconnect

Now peoplesoft 3des encryption/decryption algorithm will decrypt the accessid and password.

Now its ready for database connection

Why it is called access id?

We are ultimately "accessing" peoplesoft database through this id and hence called access id.

Step 6:

Finally it will login with Access id and password. Before that it will destroy the initial connection. Here is the snippet.

 1-13     21.08.46    0.192000 Cur#1.1072.FSCMD92 RC=0 Dur=0.000000 Disconnect
 1-14     21.08.47    0.014000 Cur#0.1072.notSamTran RC=0 Dur=0.014000 Destroy Connection Handle=0000000002A80C50
 1-15     21.08.47    0.043000 Cur#0.1072.FSCMD92 RC=0 Dur=0.043000 Create Connection Info=Primary/FSCMD92/SYSADM/ Handle=0000000002A80C50


Now you are done!!!!!


Happy to help!!!!!!!!!