Oracle Security Configuration Options

This section discusses options for dealing with Oracle 11g or higher security, including:

  • Setting the PASSWORD_LIFE_TIME to unlimited.

  • Creating a PeopleSoft-specific profile.

  • Resetting the PeopleSoft installation user IDs.

  • Changing the PeopleSoft installation user IDs.

Setting the PASSWORD_LIFE_TIME to Unlimited

You can set the PASSWORD_LIFE_TIME in the default profile to unlimited. If this is done prior to creating the PeopleSoft-specific Oracle user IDs used for the PeopleSoft database installation, then the default behavior will mimic the pre-Oracle 11g behavior.

This can be done by creating the access ID and connect ID using the following command:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
;

Note:

While feasible, this particular solution is counter to the secure by default positioning of Oracle 11g or higher and to regulations requiring periodic changes to important passwords.

Creating a PeopleSoft-Specific Profile

You can create a PeopleSoft-specific profile which sets the PASSWORD_LIFE_TIME to unlimited. Creating the new PeopleSoft profile should be done when you create the database rather than altering PeopleSoft users from the default profiles to the PeopleSoft-specific profiles. Switching the a PeopleSoft-specific profile after you have created the PeopleSoft-specific users expired password limits does not automatically modify the expiry_date column in USER_USERS (done when creating the users with the default profile).

Create the access ID and connect ID user IDs using the delivered scripts, PS_HOME/scripts/PSADMIN.SQL and PS_HOME/scripts/CONNECT.SQL. After doing so, the PeopleSoft Oracle user IDs would have the default profile assigned. Alter the access ID and connect ID user IDs to make use of the alternate profile rather than the default. This can be done using the following commands:

CREATE PROFILE PSPROFILE LIMIT  PASSWORD_LIFE_TIME UNLIMITED
;

This creates the PSPROFILE profile with password limits values set. All values not explicitly listed are derived from the default profile.

The following statements alter both the default access ID and connect ID to utilize the PSPROFILE profile with the password limit set for PASSWORD_LIFE_TIME to unlimited:

ALTER USER SYSADM PROFILE PSPROFILE
;
ALTER USER PEOPLE  PROFILE PSPROFILE
;

Note:

While feasible, this solution will allow the profile expiration behavior to mimic the pre-Oracle 11g behavior, but this runs counter to the intent of regulations that require changing critical passwords on a regular basis.

Resetting the PeopleSoft Installation User IDs

You can reset the PeopleSoft installation Oracle user ID passwords (the access ID and connect ID) in all of the places it needs to be reset. After the passwords expire, reset them to the original value. You can reset the password using the PASSWORD command or by ALTER USER command.

Note:

If using Database Vault, then only the database vault account manager can reset the account, because the access ID cannot login to SQLPLUS to change the password.

Note:

While feasible, this option runs counter to the intent of regulations that require changing critical passwords on a regular basis.

Changing the PeopleSoft Installation User IDs

The recommended option is to change the PeopleSoft installation required Oracle user ID passwords (the access ID and connect ID) after they have expired, and reflect those changes in all required locations. This option enables you to conform to regulations that require changing critical passwords on a regular basis.

If the password expires and an Oracle user ID password is changed within the Oracle database for the access ID or connect ID, the PeopleSoft system will still have the old password stored in the PeopleSoft security meta data tables and configuration files. These changed passwords will have to be reflected in the PeopleSoft security meta data tables and configuration files as well as the database.

At the database level, you can use the PASSWORD and ALTER USER commands to change the access ID and connect ID passwords. For example:

C:\Documents and Settings\>sqlplus people/password@QE855C42

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 21 10:55:57 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-28001: the password has expired

Changing password for people
New password: <changed to  ‘password’>
Retype new password: <changed to  ‘password’>
Password changed

SQL> exit

Or,

ALTER USER QE855C42 IDENTIFIED BY CHANGEPW ACCOUNT UNLOCK;

User altered.

ALTER USER people IDENTIFIED BY password ACCOUNT UNLOCK;

User altered.

SQL> exit

Note:

You may also have to include the UNLOCK keyword to unlock the account (if the password retry has been exceeded).

In PeopleTools, open Configuration Manager and change the Connect Password value on the Startup tab.

Then, open Data Mover in bootstrap mode (using the new access ID password) to run the necessary commands to change the access ID passwords on the appropriate PeopleSoft meta data tables. For example,

SET LOG c:\temp\changeaccessidpswd.out; 
UPDATE PSSTATUS SET OWNERID = <OWNER_ID_NAME>; 
UPDATE PSOPRDEFN SET OPERPSWD = <OPRID_PSWD>, ACCTLOCK=0, 
   ENCRYPTED = 0; 
UPDATE PSACCESSPROFILE SET STM_ACCESS_ID = <ACCESS_ID_NAME>,
   STM_ACCESS_PSWD = <NEW_ACCESS_ID_PSWD>, 
VERSION = 0, ENCRYPTED = 0; 
ENCRYPT_PASSWORD *;

Note:

For Oracle 11g or higher, the password is case sensitive.

Lastly, apply the connect ID changes to the psprcs.cfg and psappsrv.cfg configurations files and rebuild the domains. For example:

[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=<DATABASE_NAME>
DBType=<DATABASE_TYPE>
UserId=<USER_ID>
UserPswd=<USER_ID_PSWD>
ConnectId=<CONNECT_ID>
ConnectPswd=<CONNECT_ID_PSWD>
ServerName=<SERVER_NAME>