Skip to Main Content
Return to Navigation

Working With Oracle 11g Security Features

Oracle 11g introduces security features, which from a database security perspective, increase restrictions for database access. These changes are part of the "Secure By Default" configuration of 11g. These changes include setting a defined limit for the PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME associated with the default profile. This section discusses how PeopleSoft systems are affected and what your options are.

Understanding Default Profiles

All Oracle users created in an instance are assigned a default profile, such as the default profile delivered with 11g. There are differences between the default profiles for 10g and 11g.

Oracle Database Version

Default Profile Values

Oracle 10g

PASSWORD_LIFE_TIME: UNLIMITED

PASSWORD_LOCK_TIME: UNLIMITED

PASSWORD_GRACE_TIME: UNLIMITED

Oracle 11g

PASSWORD_LIFE_TIME: 180

PASSWORD_LOCK_TIME: 1

PASSWORD_GRACE_TIME: 7

For pre-11g Oracle releases, the default profile did not specify a PASSWORD_LIFE_TIME limit. As such, by default, the password for a given Oracle user never expired. PASSWORD_LOCK_TIME and PASSWORD_GRACE_TIME were also unlimited. For 11g, the default profile has a PASSWORD_LIFE_TIME of 180 days. PASSWORD_LOCK_TIME and PASSWORD_GRACE_TIME also have limits.

For a PeopleSoft installation on the Oracle platform, several Oracle user IDs are created during the installation. Those Oracle users are:

  • ACCESSID (default is SYSADM)

  • CONNECT ID (default is people)

  • PS (owns the PSDBOWNER table)

The ACCESSID is the schema owner for all database objects related to a specific PeopleSoft application installation. The ACCESSID and ACCESSID password are stored and encrypted in the PeopleSoft security table PSACCESSPRFL.

SQL> descr SYSADM.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)

SQL> SELECT  * from SYSADM.PSACCESSPRFL;;

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

The connect ID is a pseudo logon which allows PeopleSoft to associate multiple PeopleSoft user IDs to the same connect ID. The connect ID has the minimum privileges required to connect to the database (only SELECT privileges on specific PeopleTools tables). After a connection has been established using the connect ID, PeopleSoft security uses the PeopleSoft user ID to control access to objects in the database. The PeopleSoft signon process validates the connect ID on the server, rather than the user ID. The connect ID simplifies database security maintenance, as you don't need to maintain access for all PeopleSoft users, just for the connect ID.

The PS ID is used once, during PeopleSoft database creation, to create the PSDBOWNER table. Once this table has been created, read access and write privileges are made public to everyone, then the PS user ID privileges are revoked.

Encountering Issues Related to Oracle 11g Security

When the PASSWORD_LIFE_TIME has been reached, the PeopleSoft Oracle users (in this case the PeopleSoft ACCESSID and CONNECT ID) will be locked out of the database. This means that any PeopleSoft process cannot access the database, such as application server, Process Scheduler, COBOL, Data Mover, and so on.

If this occurs you will see any of the following Oracle database error messages:

ORA-28000: the account is locked
Cause: The user has entered wrong password consequently for maximum number of times specified by the user's 
profile parameter FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
Action: Wait for PASSWORD_LOCK_TIME or contact DBA
ORA-28001: the password has expired
Cause: The user's account has expired and the password needs to be changed
Action: change the password or contact the DBA
ORA-28002 the password will expire within string days
Cause: The user's account is about to about to expire and the password needs to be
 changed.
Action: Change the password or contact the database administrator.

These messages may appear in a SQL trace, an application server log, a Process Scheduler log, or in an error message in the GUI when attempting to access the database (signon to Application Designer or Data Mover). The following are some select examples of what you can expect to see in log and trace files.

The trace will show the login failing as follows:

CONNECTID.


2-4     13.06.56    1.581000 Cur#0.6060.QE849C42 RC=28001 Dur=1.581000 
Connect=Primary/QE849C42/people/
2-5     13.06.56    0.000000 Cur#0.6060.QE849C42 RC=-1 Dur=0.000000 XER 
rtncd=761802124 msg=
2-6     13.06.56    0.000000 Cur#0.6060.QE849C42 RC=0 Dur=0.000000 ERR rtncd=28001
 msg=ORA-28001: the password has expired

The following illustrates an application server or Process Scheduler boot with passwords already expired:

PeopleTools 8.xx.07 Client Trace - 2008-10-24 
  
PID-Line  Time        Elapsed  Trace Data... 
--------  --------  ---------- --------------------> 
 1-1      14.25.45             Tuxedo session opened {oprid='QEDMO', 
appname='Two Tier', addr='//TwoTier:7000', open at 01C67EC8, pid=4956}
 1-2      14.25.45    0.058000 Cur#0.4956.QE849C41 RC=0 Dur=0.003000 --- router
 PSORA load succeeded
 1-3      14.25.45    0.155000 Cur#0.4956.QE849C41 RC=0 Dur=0.155000 INI
 1-4      14.25.45    0.192000 Cur#0.4956.QE849C41 RC=28002 Dur=0.192000 
Connect=Primary/QE849C41/people/
 1-5      14.25.45    0.000000 Cur#0.4956.QE849C41 RC=-1 Dur=0.000000 XER 
rtncd=761800508 msg=
 1-6      14.25.45    0.000000 Cur#0.4956.QE849C41 RC=0 Dur=0.000000 ERR
rtncd=28002 msg=ORA-28002: 
the password will expire within 7 days 
 1-7      14.25.48    2.718000 Cur#0.4956.notSamTran RC=0 Dur=0.000000 DON
 1-8      14.25.51    2.742000 Tuxedo session opened { DisconnectAll at01C67EC8,
 pid=4956}

The following illustrates a client trace of a application server or Process Scheduler boot:

PeopleTools 8.49.07 Client Trace - 2008-10-24 
  
PID-Line  Time        Elapsed  Trace Data... 
--------  --------  ---------- --------------------> 
 1-1      14.30.38             Tuxedo session opened {oprid='QEDMO', 
appname='Two Tier', addr='//TwoTier:7000', open at 01C67EC8, pid=3328}
 1-2      14.30.38    0.056000 Cur#0.3328.QE849C41 RC=0 Dur=0.004000 --- router
 PSORA load succeeded
 1-3      14.30.38    0.238000 Cur#0.3328.QE849C41 RC=0 Dur=0.238000 INI
 1-4      14.30.38    0.529000 Cur#1.3328.QE849C41 RC=0 Dur=0.529000 
Connect=Primary/QE849C41/people/
 1-5      14.30.38    0.036000 Cur#1.3328.QE849C41 RC=0 Dur=0.000000 GET 
type=1003 dbtype=4
 1-6      14.30.38    0.000000 Cur#1.3328.QE849C41 RC=0 Dur=0.000000 GET 
type=1004 release=11
 1-7      14.30.38    0.076000 Cur#1.3328.QE849C41 RC=0 Dur=0.000000 COM 
Stmt=
SELECT OWNERID FROM PS.PSDBOWNER 
WHERE DBNAME=:1
.
1-41     14.30.40    0.200000 Cur#1.3328.QE849C41 RC=0 Dur=0.200000 Disconnect
 1-42     14.30.40    0.251000 Cur#0.3328.QE849C41 RC=28002 Dur=0.220000 
Connect=Primary/QE849C41/QE849C41/
 1-43     14.30.40    0.000000 Cur#0.3328.QE849C41 RC=-1 Dur=0.000000 XER 
rtncd=18874368 msg=
 1-44     14.30.40    0.000000 Cur#0.3328.QE849C41 RC=0 Dur=0.000000 ERR 
rtncd=28002 msg=ORA-28002: the password will expire within 7 days 
 1-45     14.30.42    2.293000 Cur#0.3328.notSamTran RC=0 Dur=0.000000 DON
 1-46     14.30.43    0.788000 Tuxedo session opened { DisconnectAll 
at01C67EC8, pid=3328}

The failure and return of the GRACE PERIOD warning message gives you time to react before the password actually expires, enabling you to be proactive and reset or change the ACCESSID and/or the CONNECT ID password(s).

Oracle 11g Security Configuration Options

This section discusses options for dealing with Oracle 11g 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 ACCESSID 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 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 ACCESSID 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 ACCESSID 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 ACCESSID 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 ACCESSID 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 ACCESSID 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 ACCESSID or CONNECT ID, the PeopleSoft system will still have the old password stored in the PeopleSoft security metadata tables and configuration files. These changed passwords will have to be reflected in the PeopleSoft security metadata 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/peop1e@QE849C42

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  ‘peop2e’>
Retype new password: <changed to  ‘peop2e’>
Password changed

SQL> exit

Or,

ALTER USER QE849C42 IDENTIFIED BY CHANGEPW ACCOUNT UNLOCK;

User altered.

ALTER USER people IDENTIFIED BY peop2e 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 ACCESSID password) to run the necessary commands to change the ACCESSID passwords on the appropriate PeopleSoft metadata tables. For example,

SET LOG c:\temp\changeaccessidpswd.out; 
UPDATE PSSTATUS SET OWNERID = 'QE849C42'; 
UPDATE PSOPRDEFN SET OPERPSWD = OPRID, ACCTLOCK=0, ENCRYPTED = 0; 
UPDATE PSACCESSPRFL SET ACCESSID = 'QE849C42', ACCESSPSWD = 'CHANGEPW', 
VERSION = 0, ENCRYPTED = 0; 
ENCRYPT_PASSWORD *;

Note: For Oracle 11g, 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=QEDMO
DBType=ORACLE
UserId=QEDMO
UserPswd==QEDMO
ConnectId=people
ConnectPswd=peop2e
ServerName=