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.