Sunday, April 04, 2010

Peoplesoft passwords

As we've seen in previous posts reagarding the Peoplesoft connectivity (here, here and here), there are different type of Peoplesoft users : the applications' users and the database user (so called bootstrap mode connection or database owner).

Both of them are stored in Peoplesoft tables. But they are managed separatly, stored in different tables and more over, their password are managed in different way.

The application user and password is stored in PSOPRDEFN :
SQL> select operpswd,encrypted from psoprdefn where oprid='PS';

OPERPSWD ENCRYPTED
-------------------------------- ----------
5iCGeTd2aRl/N+E3E8ZUz72qEe4= 1
SQL> desc psoprdefn
Name Null? Type
----------------------------------------- -------- ----------------------------
OPRID NOT NULL VARCHAR2(30 CHAR)
VERSION NOT NULL NUMBER(38)
OPRDEFNDESC NOT NULL VARCHAR2(30 CHAR)
EMPLID NOT NULL VARCHAR2(11 CHAR)
EMAILID NOT NULL VARCHAR2(70 CHAR)
OPRCLASS NOT NULL VARCHAR2(30 CHAR)
ROWSECCLASS NOT NULL VARCHAR2(30 CHAR)
OPERPSWD NOT NULL VARCHAR2(32 CHAR) <--
ENCRYPTED NOT NULL NUMBER(38)
SYMBOLICID NOT NULL VARCHAR2(8 CHAR)
...
Whereas the database user and password is stored in PSACCESSPRFL table :
SQL> select * from psaccessprfl;

SYMBOLIC VERSION ACCESSID ACCESSPSWD ENCRYPTED
-------- ---------- ---------------- ---------------- ----------
SYSADM1 1 sBzLcYlPrag= sBzLcYlPrag= 1
SQL> desc psaccessprfl;
Name Null? Type
----------------------------------------- -------- ----------------------------
SYMBOLICID NOT NULL VARCHAR2(8 CHAR)
VERSION NOT NULL NUMBER(38)
ACCESSID NOT NULL VARCHAR2(16 CHAR)
ACCESSPSWD NOT NULL VARCHAR2(16 CHAR) <-- ENCRYPTED NOT NULL NUMBER(38)
Two remarks from these output :
=> The userid is not encrypted whereas accessid is encrypted
=> the length of password is not the same

Note, in theory, you could have more than one row in PSACCESSPRFL table (it would mean more than one Peoplesoft applications in one database), in reallity, this option is never used.
Whether we give an application user to the application (DMS, AppDesigner, AppServer...), it is trying to connect as the accessid taken from the following query, assuming we want to connect as PS.
SQL> select a.*
2 from psaccessprfl a, psoprdefn b
3 where b.oprid='PS'
4 and a.symbolicid=b.symbolicid;

SYMBOLIC VERSION ACCESSID ACCESSPSWD ENCRYPTED
-------- ---------- ---------------- ---------------- ----------
SYSADM1 7 sBzLcYlPrag= sBzLcYlPrag= 1

1 row selected.
Started from there, ACCESSID (the Peoplesoft owner) is encrypted. The password is encrypted as well. Since nobody give the database user/password connection string on application connection, Peoplesoft will de-encrypt (decypher) the password to be able to (re)connect to the database. Do not forget the connectid user (people) which is configured (for instance in Configuration Manager) and which is used to retreive all these info from the database.

Let's try to see what Peoplesoft is doing and manages the different passwords.

For the need of the current test, the PS and SYSADM password will be changed to be the same (PASSWORD).

1. The application password
First of all, let's change the PS password in clear within the back end :

Then encrypt the PS's password with DataMover (in bootstrap mode) :

The password has been updated :
The trace file is like the following :
COM Stmt=UPDATE PSOPRDEFN SET VERSION = :2, OPERPSWD = :3, ENCRYPTED = :4, LASTPSWDCHANGE =TO_DATE(:5,'YYYY-MM-DD') WHERE OPRID = :1
Bind-1 type=2 length=2 value=PS
Bind-2 type=8 length=4 value=2
Bind-3 type=2 length=28 value=qxbYphfzHOpVJtrs6e1vd2RZXYI=
Bind-4 type=6 length=4 value=1
Bind-5 type=26 length=10 value=2010-04-04
Which means the password is sent to the database already encrypted, the DataMover tool is encrypting password and update the table as well.

2. The Peoplesoft owner password
It can be changed in different way, the first one is to use the DataMover command change_access_password, linked to the SYMBOLICID the accessid is working with. Note, by this way, the database user password is automatically changed.
The password is changed into the table :
Read carefully the encrypted value password here and compare with the PS encrypted password showed earlier => whether both passwords are same in clear (= PASSWORD) they are not same once encrypted.

An other way to change the ACCESSID's password is to update the back end table, then run encrypt_password * DataMover command :
Note, by this way, you should also change the database user password manually (alter user sysadm identified by PASSWORD).
In both cases, the back end PSACCESSPRFL table is changed.

I used the word "changed" and not "updated". Because it is not an update statement in database sense.
The trace file generated by the command above to change the accessid's password is the following :
COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSSTATUS
COM Stmt=UPDATE PSSTATUS SET DATABASE_OPTIONS = DATABASE_OPTIONS + :1 WHERE MOD(FLOOR(DATABASE_OPTIONS/:2),2) = 0
Bind-1 type=6 length=4 value=32
Bind-2 type=6 length=4 value=32
Commit
COM Stmt=SELECT VERSION,ACCESSID,ACCESSPSWD,ENCRYPTED FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Fetch
Commit
Commit
Commit
COM Stmt=SELECT VERSION, OBJECTTYPENAME FROM PSLOCK WHERE OBJECTTYPENAME IN ('CLM') FOR UPDATE OF VERSION
Fetch
Fetch
COM Stmt=UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME IN ('CLM')
COM Stmt=SELECT VERSION FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Fetch
Connect=Primary/H91TMPLT/sysadm/
COM Stmt=DELETE FROM PSACCESSPRFL WHERE SYMBOLICID = :1
Bind-1 type=2 length=7 value=SYSADM1
Disconnect
Connect=Primary/H91TMPLT/sysadm/
Disconnect
Commit
COM Stmt=SELECT 'PS_DOES_TABLE_EXIST' FROM PSSTATUS
COM Stmt=UPDATE PSSTATUS SET DATABASE_OPTIONS = DATABASE_OPTIONS + :1 WHERE MOD(FLOOR(DATABASE_OPTIONS/:2),2) = 0
Bind-1 type=6 length=4 value=32
Bind-2 type=6 length=4 value=32
Commit
A DELETE is fired onto that PSACCESSPRFL table. But this table is not empty after changing the password, somehow, the DataMover trace file does not show everything. Let's see a little bit more with Oracle Fine Grained Auditing against that particular table.
begin
dbms_fga.add_policy ( object_schema => 'SYSADM',
object_name => 'PSACCESSPRFL',
policy_name => 'PWD',
statement_types => 'DELETE,UPDATE,INSERT' );
end;
/
Then, re-run the password change in DataMover, and finally check the audited statements :
SQL> select * from dba_fga_audit_trail;

6380034 04-APR-10 SYSADM
Administrator
WORKGROUP\ANTLIA
sysadm
SYSADM
PSACCESSPRFL
PWD 5370605
DELETE FROM PSACCESSPRFL WHERE SYMBOLICID = :1
#1(7):SYSADM1
DELETE
04-APR-10 11.48.22.627791 AM +02:00
0
9549 08001F0030160000 108 1

6380034 04-APR-10 SYSADM
Administrator
WORKGROUP\ANTLIA
sysadm
SYSADM
PSACCESSPRFL
PWD 5370607
INSERT INTO PSACCESSPRFL (SYMBOLICID, VERSION, ACCESSID, ACCESSPSWD, ENCRYPTED)
VALUES (:1, :2, :3, :4, :5)
#1(7):SYSADM1 #2(1):7 #3(12):sBzLcYlPrag= #4(12):sBzLcYlPrag= #5(1):1
INSERT
04-APR-10 11.48.22.636170 AM +02:00
0

9549 08001F0030160000 109 2

SQL>
DELETE + INSERT is the way how Peoplesoft is changing the ACCESSID password. And again, the DataMover is directly cypher and update the underlying table.

From the above, we can see Peoplesoft chose two encryptions algorithms :
1. One algorithm to encrypt the application users' password, this is a one way encryption, on connection, Peoplesoft's tool encrypt the password and compare with the stored value in database.
2. An other algorithm dedicated to ACCESSID's password, with the possiblity to decypher the user and password. This is all included into the compiled Peoplesoft programs, without any possiblity to use the decypher mechanism manually.

Note : all the tests above have been done from a W2k8 64bit workstation, on Peopletools 8.50.02 , Peoplesoft OVM database server (Peopletools 8.50.02/HCM9.1), Peoplesoft OVM App/Batch server (Peopletools 8.50.02) and Peoplesoft OVM PIA server (Peopletools 8.50.02).

Nicolas.

7 comments:

Anand said...

PeopleSoftNet
Hello All,
Thanks for such a nice blog.
We would like to have your comments on below site being developed for peoplesoft developers to help PeopleSoft Community.
we expect response from you.
Thanks in advance for your time and comments.
PeopleSoftNet

Anonymous said...

Its a great article.Beautifully explained.Thanks for sharing the knowlegde.

Rojer said...

That was deeply explained and interesting.

PeopleSoft Interview Questions

Unknown said...

Most awesome article on this subject till date i have seen, really helpful.

the only thing i could think to add was the user id you are using each time to login.

realy deep and good article.

Unknown said...

Thanks for the nice explanation.
I have a question here, In which scenario the symbolic Id will be blank???

Nicolas Gasparotto said...

Never.

Nikhil said...

Hi Nicholas ,

First of all , amazing article as always.
I came across this isse , we have a PeopleSoft 9.2 Fin application: "FS92" with Tools version 8.54 . We have one more site : "FS91" with 9.1 version.
The PeopleSoft ID which boots up our application is VP1 for both(which has a password: "VP1" as well) . One of the users (who was new to the organization), last week changed the VP1 password to "welcome321" . The application crashed due to obvious reasons.
Now , I just wanted to confirm , what I will do will help us boot out application server or not.
I checked the PSOPRDEFN table , (select * from PSOPRDEFN where OPRID='VP1') , the OPRPASSWORD there is a new encrypted password now when compared to the same PSOPRDEFN table in FS91. (What I mean is , we have an up and running site already) . So what I was thinking is , if I copy the OPERPSWD value from FS91'S PSOPRDEFN table and update it into PSOPRDEFN table of FS92 , that oughtta do it right?
Please correct me if I am wrong.
Plus also please tell , where all I need to change the password change.