E-UPG PT8.2x, PT8.4x, PT8.5x: How to Preserve Security through Upgrade Passes (Doc ID 642944.1)

Last updated on SEPTEMBER 09, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.50 to 8.51 [Release 8.4]
Information in this document applies to any platform.
***Checked for relevance on 19-Jul-2013***
*** currency checked Jan 2015 ***

8.1x, 8.2x, 8.4x, 8.5x, Upgrade, Security

SOLUTION:

In order to preserve security through your upgrade passes, you will have 3 options.

1) Low Risk - Re-enter your security changes into one of the target databases. Once this is complete, those security tables can be exported out and imported into any other upgraded databases at the same tools release using security migration re<>, included below.

2) Low Risk - Re-do the Initial Upgrade again.This will get the security as it is when you take the copy of production. Many customers chose this option because they are most comfortable with it.Once the initial upgrade is completed, follow the steps outlined in re<> (Included below) to migrate security to your other upgraded databases.

3)High Risk -
Attention! This workaround has not gone through our formal quality regression test cycle.We strongly recommend that you thoroughly test this workaround in a development environment before applying it to your production environment.

Be sure to document this change as this workaround may be detected during your next upgrade. Because this workaround has not yet gone through our formal quality regression test cycle, this workaround will have risk.


You should only follow this option if you only want the latest operator security in production migrated to your upgraded database. Do the Initial Upgrade again, up through the end of Chapter 2 (Updating PeopleTools). You will need to definitely run the Relnnn.sql scripts, copy the projects and do the alters - but to make sure you get everything correct, it would be best to run all steps in Chapter 2.Once completed your tools release will be at the same level as your fully upgraded database that you want operator security imported into. DO NOT FOLLOW <> as you will LOSE ALL security delivered by the new application release you just upgraded to. To migrate your operator security, select the appropriate Data Mover script for your PeopleTools release to export and import operator security:

For PT 8.1X -
******Export******
EXPOPR.dms - Export

This script looks as follows:

UPDATE PSOPRDEFN SET VERSION = 1;

-- Tables updated by the Operator Security Tool
EXPORT PSOPRDEFN;
EXPORT PSOPRCLS;
EXPORT PSACCESSPRFL;

-- Tables populated by the Operator Alias panels (new for 7.X)
EXPORT PSOPRALIAS;
EXPORT PSOPRALIASTYPE;

-- Tables populated by mass change
EXPORT PS_MC_OPR_SECURITY;
EXPORT PS_MC_OPRID;

******Import******
IMPOPR.dms - Import

This script looks as follows:

-- Tables updated by the Operator Security Tool
IMPORT PSOPRDEFN;
IMPORT PSOPRCLS;
IMPORT PSACCESSPRFL;

-- Tables populated by the Operator Alias panels (new for 7.X)
IMPORT PSOPRALIAS;
IMPORT PSOPRALIASTYPE;

-- Tables populated by mass change
IMPORT PS_MC_OPR_SECURITY;
IMPORT PS_MC_OPRID;

--The GRANT_USER * command will fail if any of the operator profiles have a differnt
--access profile than the currently logged on user. (the operator running this
--DataMover script)

GRANT_USER *;
*********************End of PT 8.1x*********************


For PT 8.2x -
******Export******
USEREXPORT.dms

This script looks as follows:

-- User Profiles
EXPORT PSOPRDEFN;
EXPORT PSOPRALIAS;
EXPORT PS_RTE_CNTL_RUSER;
EXPORT PSOPRCLS;

-- User Profiles / Roles
EXPORT PSROLEUSER;
EXPORT PS_ROLEXLATOPR;

******Import******
USERIMPORT.dms

-- Cleanup
DELETE FROM PSOPRDEL;
COMMIT;
DELETE FROM PSOPRDEFN;
DELETE FROM PS_ROLEXLATOPR;
COMMIT;
DELETE FROM PSOPRALIAS;
DELETE FROM PSROLEUSER;
COMMIT;
DELETE FROM PSOPRCLS;
DELETE FROM PS_RTE_CNTL_RUSER;

-- User Profiles
IMPORT PSOPRDEFN;
IMPORT PSOPRALIAS;
IMPORT PSOPRCLS;
IMPORT PS_RTE_CNTL_RUSER;

-- User Profiles / Roles
IMPORT PSROLEUSER;
IMPORT PS_ROLEXLATOPR;

UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'UPM';
UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'UPM';
UPDATE PSOPRDEFN SET VERSION = 1;

COMMIT;

*********************End of PT 8.2x*********************

For PT 8.4x -

******Export******
USEREXPORT.dms

This script looks as follows:

-- USERS
EXPORT PSOPRDEFN;
EXPORT PSOPRALIAS;
EXPORT PSROLEUSER;
EXPORT PSUSERATTR;
EXPORT PSUSEREMAIL;
EXPORT PSUSERPRSNLOPTN;
EXPORT PS_ROLEXLATOPR;
EXPORT PS_RTE_CNTL_RUSER;

******Import******
USERIMPORT.dms

This script looks as follows:

UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'UPM';

REPLACE_DATA *;

UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'SYS';
UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'UPM';

UPDATE PSOPRDEFN SET VERSION = (SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'UPM');


*********************************************************
THIS ONE IS FOR OPTION 2
*********************************************************

<> E-SEC: PT 8.x How to migrate or copy security from one database to another


Goal


Customers want to migrate the security tables from one PeopleSoft database to another and have everything intact.

SOLUTION:
For PeopleTools 8.1x the following is a list of Datamover scripts, which would do the job for you.

1. EXPOPR.DMS exports operator definitions from one database and stores them in a file in DataMover format.  
2. IMPOPR.DMS reads the file created by EXPOPR.DMS and copies the operator definitions into the target database.
3. USEREXPORT.DMS only exports operator definitions.    
4. USERIMPORT.DMS reads the file created by USEREXPORT.DMS and copies the operator definitions to the target database.

Maintaining operator passwords through the migration. :

If you want to maintain the operator passwords from your source database in your target database, you must change the access

password in your source database to match the access password in the target database before exporting the security tables.

PeopleSoft Security Tables
The following is a list of the tables exported and imported by EXPOPR.DMS and IMPOPR.DMS.

Table Name Description                                        

PSOPRDEFN          General attributes of operators, Passwords, language code, etc.
PSOPRCLS One row for each operator-class i.e if MYOPER is in three permission lists, there will be three rows in this

table
PSACCESSPRFL One row for each access profile defined in the system
PSCLASSDEFN General attributes of classes.  Timeout minutes, description, etc.
PSAUTHSIGNON Valid signon start and end times for classes.
PSAUTHITEM Authorized menus for classes
PSAUTHPRCS Authorized process groups for classes
PSPRCSPRFL Process profile for classes
PSAUTHCUBE Cubes a class is authorized to access
PSAUTHBUSCOMP Business Processes a class is authorized to access
PSAUTHCHNLMON Message Channels a class can view in the Message Monitor

Object  Security Table

PSOPROBJ Object Security Groups for a class

Tables populated from the following  component: PeopleTools --> Utilities --> Use --> Query Security

PS_SCRTY_QUERY Query access profile for classes
PS_SCRTY_ACC_GRP Authorized query access groups for classes

Tables populated from the following  component: PeopleTools --> Mass Change --> Use --> Mass Change Operator Security

PS_MC_OPR_SECURITY Authorized Mass Change Template for a operator definition.
PS_MC_OPRID Operators allowed to execute Mass change on line

Tables populated from the following  component: PeopleTools --> Utilities --> Use --> Operator Alias Type, Operator Alias

Value

PSOPRALIAS Alias values for an operator
PSOPRALIASTYPE Type of Operator Alias

Note: When migrating security between databases you may want to make sure that the SYMBOLIC ID, AccessID and Access

Password in the source and target database are the same, this makes for little headaches. You can always change the AccessID

password or create a new Symbolic ID in the target once the migration is done.

NOTE for PeopleTools 8.4x, 8.5x:
In PeopleTools 8.4 you MUST also update the Portal registry in the target database in order for your new security to take

effect correctly. You will need to run the Application Engine program PORTAL_CSS.  
But there are some known issues with this process when copying security. 

Also, you will want to have the Access ID, Access Password and Symbolic ID the same between the source and target databases in order to alleviate confusion after migration.  If you choose not to have this before the migration, then you will have to
make manual updates to the PSOPRDEFN, PSACCESSPRFL, and PSSTATUS in order to be able to log in.  If you are on Oracle or DB2
databases, then you need to update the PSDBOWNER.  

Question in 8.4x. In PT 8.1x there were scripts to migrate security called IMPOPR.DMS, EXPOPR.DMS, USERIMPORT.DMS, and  USEREXPORT.DMS. Where are these scripts for PT 8.4?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms