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-04Which 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 CommitA 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.
No comments:
Post a Comment