How to Migrate or Copy Security From One Database to Another

NOTE for PeopleTools 8.4x and 8.5x:

Question in 8.4x/8.5x. 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.4x/8.5x?

Answer: The USERIMPORT.DMS, and USEREXPORT.DMS scripts are still available but have a few new tables. The scripts used in PT 8.4 for migrating ALL security tables are called SECURITYEXPORT.DMS and SECURITYIMPORT.DMS.

In PeopleTools 8.4x/8.5x you MUST also update the Portal registry in the target database in order for your new security to take affect correctly. You will need to run the Application Engine program PORTAL_CSS. See Document:624004.1 - E-PORTAL: PT 8.4x and 8.5x: How to Run Portal Security Synch PORTAL_CSS Process for how to do this. But there are some known issues with this process when copying security. see the PORTAL_CSS resolution above for more information on that.

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. 

These scripts export and import all of the PeopleTools security tables.

Here is a list of the tables. Note: For an up-to-date list of tables, please refer to the scripts themselves, that are delivered for your PeopleTools Release.

-- ACCESS PROFILES
PSACCESSPRFL

-- USERS
PSOPRDEFN
PSOPRALIAS
PSROLEUSER
PSUSERATTR
PSUSEREMAIL
PSUSERPRSNLOPTN
PS_ROLEXLATOPR
PS_RTE_CNTL_RUSER

-- ROLES
PSROLEDEFN
PSROLEDEFNLANG
PSROLECANGRANT
PSROLECLASS

-- PERMISSION LISTS
PSCLASSDEFN
PSAUTHBUSCOMP
PSAUTHCHNLMON
PSAUTHCUBE
PSAUTHITEM
PSAUTHOPTN
PSAUTHPRCS
PSAUTHSIGNON
PSPRCSPRFL
PS_MC_OPR_SECURITY
PS_MC_OPRID
PS_SCRTY_ACC_GRP
PS_SCRTY_QUERY

-- DEFINITION SECURITY

EXPORT PSPTDEFSEC_GRPS;

EXPORT PSPTDEFSEC_GRP;

EXPORT PSPTDEFSECINRL;

EXPORT PS_APP_DES_OBJ_CST;

EXPORT PSOPROBJ;

-- PERSONALIZATIONS
PSUSEROPTNDEFN
PSUSEROPTNLANG
PSOPTNCATGRPLNG
PSOPTNCATGRPTBL
PSOPTNCATTBL
PSOPTNCATLANG

-- SECURITY OPTIONS
PSSECOPTIONS

-- SECURITY LINKS
PSUSEROTHER
PSUSERSELFOTHER
PSROLEOTHER
PSPERMLISTOTHER

-- USER ID TYPES
PSOPRALIASTYPE
PSOPRALIASFIELD

-- DELETE USER BYPASS TABLE
PS_BYPASS_TABLE

-- FORGOT EMAIL TEXT
PSPSWDEMAIL

-- PASSWORD HINTS
PSPSWDHINT

-- SIGNON PEOPLECODE
PSSIGNONPPC

-- DIRECTORY
PSDSDIR
PSDSSRVR
DSCONNECTID
PSDSEXT_INSTALL
PSDSSECMAPMAIN
PSDSSECMAPSRVR
DSUSRPRFLMAP
PSDSUSERPRFL
PSDSSECROLERULE
DSSRCH_SBR
DSSRCHATTR
DSSECFILTER
PT_WF_NOT_DSCFG

Please note, that in PT 8.4x/8.5x, it is NOT recommended to use these scripts (userimport/export and securityimport/export) to migrate users/security between two different Tools versions.  The recommendation to sync up users between databases that are on two different Tools version is as follows:

If customers are to migrate data from a content DB at a lower Tools  release to a Portal DB at a higher Tools release, the recommended procedure is as follows:
a) Make a copy (clone) of the content DB.
b) Run a Tools-only upgrade on that DB copy, in order to bring it up to the same release as the Portal DB (which should never be lower than that of  the content DB).
c) Run the UserExport / UserImport dms scripts to copy the Security data from the upgraded DB to the Portal DB (both at the same Tools release)
d)Discard the cloned (content) DB once the Portal DB has received the desired data.
Please note that the Security tables on the content DB should be locked down until this process is completed to avoid updates which would not get propagated.