Process Scheduler

PeopleSoft process scheduler is one of the most critical part in batch scheduling. Because most of the critical job is running under Batch server.

Pretty enough to say about Batch server,Now this post is all about how good Peoplesoft security is in place to post the reports.

We all know after the process went to success reports will get posted to report repository, so we know how the process are picked up and processed in sequential manner.

Let me give small heads up , whenever you run the process from front end ,Application server will issue a request and hence 3 process scheduler tables will get updated, Every 10 seconds Batch server will check for these 3 core tables and if it finds the new request then it will start processing the request, once it finishes this will generate the report output in LOG_OUPUT directory.

Now How reports are getting posted ?

  Here is the weak security we are providing (if in case if you select http protocol) to most critical report repository.

Do you know how?

1.once PSAESRV server completes the request and generates the report in LOG_OUTPUT directory.

2.PSDSTSRV server will pick the report and ready to transfer the report in report repository which is present in webserver machine.

  So here is the question what authentication we are giving to put the report in webserver machine report repository?
if you see the below snapshot we are just giving the server name ,port number and the site name.

Authentication field is empty, but still reports are posted.How?

   The answer is we are not providing any authentication.

So how webserver machine will allow the reports to place in repository?

Behind the scenes SCHEDULER TRANSFER servlet which is running in webserver machine (its a java api servlet ) and its open to all.

PSDSTSRV server will pick the report and it will contact SCHEDULER TRANSFER servlet in clear text and without authentication , because already servlet is opened for all clients.

So once the PSDSTSRV server sends the report , SCHEDULER TRANSFER (running in webserver) will recieve the report and place the reports under report repository folder.

This is why Oracle is recommending to provide full access to report repository folder for the user who is starting WEBSERVER .


   So Peoplesoft has not provided the security ?

The answer is no Peoplesoft is recommending to go for HTTPS/FTP,XCOPY protocol instead of HTTP, Because in HTTP protocol Scheduler Transfer will allow all the incoming reports.

You can also develop a java code to contact Scheduler transfer and post or retrieve the reports because it is open to all the clients.

But if you are using HTTPS protocol there will be a certificate installed in both Process scheduler and Webserver , so all the incoming connection will come with digital certificate and it should match with certificate placed in Webserver (i.e.,Scheduler Transfer). So no one except PSDSTSRV with valid certificate cannot place the reports in Report repository folder.


So are you using HTTP protocol in production environment, this is the risk involved and change it back to HTTPS protocol.

Cheers!!!!!!

Peoplesoft Encryption

This Article explains about Peoplesoft Encryption technology.

Before explaining , let me discuss about some terms related to Encryption.

One Way Encryption/Decryption: - Means once you encrypt any password , you can never be decrypted (Even Oracle/Peoplesoft cannot do this)

Two Way Encryption/Decryption: - Means you can decrypt the encrypted password when it is really required.

Peoplesoft is really Chosen the best technology for thier own security .Now i will explain the technology used by peoplesoft in our login process. As this is most important part, hope you will enjoy it!!!!!

Encryption 1: SHA 1algorithm

  If you look at the OPERPSWD field in PSOPRDEFN table, it will be in encrypted format and this is using SHA 1 which is one way encryption technology which can never be decrypted.

So here is the question why peoplesoft chose one way?

    The secret behind this is, whenever the login process happens the password you provided can again encrypt using the same technology and compare against your PSOPRDEFN table. So no one can decrypt the password, as this is the most important security table.

And if you look at the encrypted field in PSOPRDEFN table there will be 1/0.

Why we need this?

Suppose you are updating the password from backend, then you should update the encrypted field as zero. If not ENCRYPT_PASSWORD function will consider this password is already encrypted.

So simply ENCRYPT_PASSWORD will check the encrypted field , if it is zero it will encrypt otherwise vice versa.

So use this function only to encrypt psoprdefn table encryption

Encryption 2: - 3 DES Algorithm.

  This algorithm is purely developed by peoplesoft in 2 way communication, which means you can decrypt the password to clear text.

So why we need to decrypt and why different algorithm?

If you look at the above snapshot both access id and access password is encrypted. So in my previous post i said ultimately PeopleSoft will login with Access id/Password. So oracle never understand the encrypted format. we need to store ACCESS ID/PWD in encrypted format as well as at the run time we need to decrypt it. So it will login to oracle database with clear text. Thats why we are using 2 way algorithm

If you look at the snippet in the trace(highlighted in red) you will find the SYSADM user , so here we can prove that decryption is happening.

 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

Peoplesoft embeds both encrypt/decrypt in thier tools like APP-D, Data Mover ..etc.

Thats why we don't need to exclusively run any command like ENCRYPT_PASSWORD as we did in above algorithm.

Hint:Just update the password in PSACCESSPRFL make encrypted as zero then try to login via APP-D or Data mover, Peoplesoft will automatically encrypts and updates the encrypted field as 1.


Encryption 3:- Java based encryption.

This encryption is bit different, and this will happen only at the web server level. If we need to boot webserver,then PTWEBSERVER credentials is mandatory. But Peoplesoft never stores any password in clear text any where. So it should be encrypted with some algorithm and decrypt using the same. So they decided to use java based.

But why we need java based ?

So here is the reason behind it, peoplesoft does not have its own weblogic server , so we need to use Oracle Weblogic and this is java based  and it never understands 3DES algorithm which is developed by peoplesoft(2 way communication) also we cannot use SHA1, because it is one way communication. So they have written a java based encryption so that it will be in encrypted format and at the run time it will decrypt using decrypt function.

So we used to use pscipher.bat utility to encrypt the password

Can we decrypt any one of the above password ?

 Encryption 1:- No ..As i said earlier if encryption is 1 way even Oracle/Peoplesoft does not have decrypt function. So you can't, only try you can give is brute force attack.

Encryption 2: - Yes , Our Application designer and datamover is designed in C++ code, so if get the source code for the app-d or data mover , we can find the decrypt function and hit that function. Its not that easy.

Encryption 3:- Yes - This is a java based utility you can open the pscipher.bat

and get the java class as stated above names as PSCIPHER and  decompile it and take a look, you will find a decrypt function. So pscipher.bat file is calling only encrypt function in java class, you can try and call decrypt function then you can made psdecipher.bat file (if you are java expert).

Now you are done!!!!

Happy to help!!!!!!

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!!!!!!!!!